0

We have PostgreSQL 11.1 on x86_64-pc-linux-gnu (Red Hat 4.8.5-28), 64-bit.

We have a table with quite a lot of data (but not extra huge, e.g. weather data for some locations for one month). There is a problem with efficiency.

We have stoped all incoming data, restarted service and reindex the table. No data have been added after that. Still, we have to wait very long to get a relative small range of data.

This is the query:

EXPLAIN (ANALYZE, BUFFERS) SELECT
     valid_for_timestamp at time zone 'UTC'  AS time,
      value AS "value",
      'Ambient Temperature[°C]' AS "metric" 
    FROM bus_020000_history.gridded_weather_data
    WHERE 
    valid_for_timestamp at time zone 'UTC' BETWEEN '2020-08-17T00:39:02.223Z' AND '2020-08-17T06:39:02.223Z' AND
      parameter_type='AMBIENT_TEMPERATURE' AND
      quadrant_inst_id=10010010
    ORDER BY parameter_type,valid_for_timestamp

This is plan generated and executed for it (EDITED: I have replaced fancy image with text output):

Sort  (cost=135102.19..135102.32 rows=49 width=72) (actual time=59517.030..59517.045 rows=120 loops=1)
  Sort Key: gridded_weather_data.valid_for_timestamp
  Sort Method: quicksort  Memory: 41kB
  Buffers: shared hit=7 read=10283
  ->  Result  (cost=0.00..135100.82 rows=49 width=72) (actual time=46493.591..59516.553 rows=120 loops=1)
        Buffers: shared hit=4 read=10283
        ->  Append  (cost=0.00..135100.21 rows=49 width=32) (actual time=46493.577..59516.252 rows=120 loops=1)
              Buffers: shared hit=4 read=10283
              ->  Seq Scan on gridded_weather_data  (cost=0.00..0.00 rows=1 width=48) (actual time=0.004..0.004 rows=0 loops=1)
                    Filter: ((parameter_type = 'AMBIENT_TEMPERATURE'::text) AND (quadrant_inst_id = 10010010) AND (timezone('UTC'::text, valid_for_timestamp) >= '2020-08-17 00:39:02.223'::timestamp without time zone) AND (timezone('UTC'::text, valid_for_timestamp) <= '2020-08-17 06:39:02.223'::timestamp without time zone))
              ->  Bitmap Heap Scan on _hyper_20_272_chunk  (cost=4.18..12.69 rows=1 width=32) (actual time=0.033..0.033 rows=0 loops=1)
                    Recheck Cond: (quadrant_inst_id = 10010010)
                    Filter: ((parameter_type = 'AMBIENT_TEMPERATURE'::text) AND (timezone('UTC'::text, valid_for_timestamp) >= '2020-08-17 00:39:02.223'::timestamp without time zone) AND (timezone('UTC'::text, valid_for_timestamp) <= '2020-08-17 06:39:02.223'::timestamp without time zone))
                    Buffers: shared hit=4
                    ->  Bitmap Index Scan on _hyper_20_272_chunk_gridded_weather_data_quadrant_inst_id_idx  (cost=0.00..4.18 rows=4 width=0) (actual time=0.027..0.027 rows=0 loops=1)
                          Index Cond: (quadrant_inst_id = 10010010)
                          Buffers: shared hit=4
              ->  Bitmap Heap Scan on _hyper_20_471_chunk  (cost=1725.07..135087.27 rows=47 width=32) (actual time=46493.537..59516.106 rows=120 loops=1)
                    Recheck Cond: (quadrant_inst_id = 10010010)
                    Filter: ((parameter_type = 'AMBIENT_TEMPERATURE'::text) AND (timezone('UTC'::text, valid_for_timestamp) >= '2020-08-17 00:39:02.223'::timestamp without time zone) AND (timezone('UTC'::text, valid_for_timestamp) <= '2020-08-17 06:39:02.223'::timestamp without time zone))
                    Rows Removed by Filter: 75496
                    Heap Blocks: exact=9990
                    Buffers: shared read=10283
                    ->  Bitmap Index Scan on _hyper_20_471_chunk_gridded_weather_data_quadrant_inst_id_idx  (cost=0.00..1725.06 rows=75267 width=0) (actual time=383.810..383.810 rows=75616 loops=1)
                          Index Cond: (quadrant_inst_id = 10010010)
                          Buffers: shared read=293
Planning Time: 6.627 ms
Execution Time: 59517.557 ms

These are indices for this table:

enter image description here

Repeating same queries results in very fast answer. However, we have a lot of id_s involved and users will not repeat exactly the same queries - main users are Grafana systems.

Where should we look for the problem? In normal run we will have regular data load, thus we cannot accept solutions for static database. Thanks!

EDIT 2: We made the following two changes - the difference is HUGE (0.6s instead od 223s in one of our test):

  • added index (quadrant_inst_id, valid_for_timestamp, parameter_type)
  • removed at time zone 'UTC' in WHERE part (we kept the first occurrence)
3
  • Do not use 11.1, upgrade to a recent minor release. Please post the output of EXPLAIN (ANALYZE, BUFFERS) as text, not some strange images. – Laurenz Albe Aug 17 '20 at 16:01
  • 2
    Does the at time zone 'UTC' actually do anything? It sees to me that it doesn't change the result, it just inhibits use of an index column. – jjanes Aug 17 '20 at 23:54
  • @jjanes Removing at time zone 'UTC' from WHERE part made a huge difference. If you are ready to shortly explain it in the answer I will accept it. – meolic Aug 18 '20 at 10:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.