All geek questions in one place

The first call to a large table is unexpectedly slow

I have a request that feels like it takes longer than it should be. This only applies to the first request for a given set of parameters, so there is no problem with caching.

I'm not sure what to expect, however, given the settings and the settings, I was hoping that someone could shed some light on a few questions and give some idea of ​​what can be done to speed up the request. The table in question is quite large, and the Postgres rating is about 155963000 (14 GB).

Query

    select ts, sum(amp) as total_amp, sum(230 * factor) as wh
    from data_cbm_aggregation_15_min
    where virtual_id in (1818) and ts between '2015-02-01 00:00:00' and '2015-03-31 23:59:59'
    and deleted is null
    group by ts
    order by ts

When I started to study this query, it took about 15 seconds, after some changes I got it up to about 10 seconds, which still seems long for a simple query like this. Here are the results from explain analyze: http://explain.depesz.com/s/97V1 . Note why it GroupAggregatereturns the same number of rows; this example uses only one virtual_id, but there may be more.

Table and index

The requested table has values ​​inserted into it every 15 minutes.

CREATE TABLE data_cbm_aggregation_15_min (
  virtual_id integer NOT NULL,
  ts timestamp without time zone NOT NULL,
  amp real,
  recs smallint,
  min_amp real,
  max_amp real,
  deleted boolean,
  factor real DEFAULT 0.25,
  min_amp_ts timestamp without time zone,
  max_amp_ts timestamp without time zone
)

ALTER TABLE data_cbm_aggregation_15_min ALTER COLUMN virtual_id SET STATISTICS 1000;
ALTER TABLE data_cbm_aggregation_15_min ALTER COLUMN ts SET STATISTICS 1000;

The index used in the query

CREATE UNIQUE INDEX idx_data_cbm_aggregation_15_min_virtual_id_ts
ON data_cbm_aggregation_15_min USING btree (virtual_id, ts DESC);

ALTER TABLE data_cbm_aggregation_15_min
CLUSTER ON idx_data_cbm_aggregation_15_min_virtual_id_ts;

Postgres Settings

Other default settings.

default_statistics_target = 100 
maintenance_work_mem = 2GB 
effective_cache_size = 11GB
work_mem = 256MB
shared_buffers = 3840MB
random_page_cost = 1

What i tried

I watched things before posting to https://wiki.postgresql.org/wiki/Slow_Query_Questions , and the results in more detail were as follows:

  • Postgres, random_page_cost, , , , , , random_page_cost .
  • virtual_id ts, WHERE. .
  • the idx_data_cbm_aggregation_15_min_virtual_id_ts, , , , .
  • VACUUM , autovacuum, .
  • REINDEX ( 50%!), .
+4
1

A 

SELECT ts, sum(amp) AS total_amp, sum(factor) * 230  AS wh
FROM   data_cbm_aggregation_15_min
WHERE  virtual_id = 1818
AND    ts >= '2015-02-01 00:00'
AND    ts <  '2015-04-01 00:00'
AND    deleted IS NULL
GROUP  BY ts
ORDER  BY ts;

  • sum(230 * factor) - : sum(factor) * 230 , NULL.

  • ts between '2015-02-01 00:00:00' and '2015-03-31 23:59:59' . 2015 , . BETWEEN ts >= lower AND ts <= upper. , .

  • virtual_id in (1818) - virtual_id = 1818.

, 

CREATE INDEX data_cbm_aggregation_15_min_special_idx
ON data_cbm_aggregation_15_min (virtual_id, ts, amp, factor)
WHERE deleted IS NULL;

  • , DESC . Index Scan Backward , Index Scan, .

  • , Postgres 9.2. , (amp, factor), , .

  • , , , . , .
    , , - ( ), Postgres , .

, , 8 :

CREATE TABLE data_cbm_aggregation_15_min (
   virtual_id integer NOT NULL,
   recs smallint,
   deleted boolean,
   ts timestamp NOT NULL,
   amp real,
   min_amp real,
   max_amp real,
   factor real DEFAULT 0.25,
   min_amp_ts timestamp,
   max_amp_ts timestamp
);

:

  • , . . Postgres , .

  • , . - Postgres MVCC . Postgres , ( ). . dba.SE:

,

  • SET STATISTICS 1000 ts virtual_id , random_page_cost = 1, .

  • random_page_cost = 1 Postgres, , . , () . , , ( Postgres ). random_page_cost = 1.1 , , .

  • - . , , . : ?

  • work_mem , RAM, , , , , .. work_mem = 256MB . . , , .

  • REINDEX CLUSTER, . REINDEX , .

+5

Source: https://habr.com/ru/post/1605602/

More articles:


All Articles