I’m experiencing very slow (10’s of seconds) query response times when trying to get the first time value in a hypertable. The query I am using is:
SELECT time FROM tablename ORDER BY time LIMIT 1;
The table has around 44 million rows.
The planning seems to take up the bulk of the time. It involves a lot of backwards index scans on the hypertable chunks as you can see from the output below of EXPLAIN ANALYZE on that query.
Can anyone provide some insight into why this might take so long and how to speed things up. Thanks.
data=# explain analyze select time from tablename order by time limit 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.28..0.30 rows=1 width=8) (actual time=199.896..199.921 rows=1 loops=1)
-> Custom Scan (ChunkAppend) on tablename (cost=0.28..62.30 rows=2888 width=8) (actual time=199.894..199.918 rows=1 loops=1)
Order: tablename."time"
-> Index Only Scan Backward using _hyper_859_3610_chunk_tablename_time_idx on _hyper_859_3610_chunk (cost=0.28..62.30 rows=2888 width=8) (actual time=199.892..199.893 rows=1 loops=1)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_3619_chunk_tablename_time_idx on _hyper_859_3619_chunk (cost=0.42..12025.44 rows=588781 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_3663_chunk_tablename_time_idx on _hyper_859_3663_chunk (cost=0.42..11514.10 rows=563785 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_3730_chunk_tablename_time_idx on _hyper_859_3730_chunk (cost=0.42..11984.42 rows=586780 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_3774_chunk_tablename_time_idx on _hyper_859_3774_chunk (cost=0.42..12048.33 rows=589867 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_4154_chunk_tablename_time_idx on _hyper_859_4154_chunk (cost=0.42..10757.50 rows=526692 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_4395_chunk_tablename_time_idx on _hyper_859_4395_chunk (cost=0.42..8481.33 rows=415207 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_4463_chunk_tablename_time_idx on _hyper_859_4463_chunk (cost=0.42..8791.16 rows=430436 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_4517_chunk_tablename_time_idx on _hyper_859_4517_chunk (cost=0.42..12088.95 rows=591915 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_4571_chunk_tablename_time_idx on _hyper_859_4571_chunk (cost=0.42..12096.65 rows=592282 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_4621_chunk_tablename_time_idx on _hyper_859_4621_chunk (cost=0.42..12102.28 rows=592584 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_4698_chunk_tablename_time_idx on _hyper_859_4698_chunk (cost=0.42..11919.53 rows=583627 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_4758_chunk_tablename_time_idx on _hyper_859_4758_chunk (cost=0.42..12101.99 rows=592564 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_4813_chunk_tablename_time_idx on _hyper_859_4813_chunk (cost=0.42..12057.31 rows=590319 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_4864_chunk_tablename_time_idx on _hyper_859_4864_chunk (cost=0.42..11856.26 rows=581096 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_4926_chunk_tablename_time_idx on _hyper_859_4926_chunk (cost=0.42..9946.56 rows=486929 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_4975_chunk_tablename_time_idx on _hyper_859_4975_chunk (cost=0.42..11997.64 rows=587441 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_5010_chunk_tablename_time_idx on _hyper_859_5010_chunk (cost=0.42..11994.24 rows=587288 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_5043_chunk_tablename_time_idx on _hyper_859_5043_chunk (cost=0.42..12006.49 rows=587884 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_5076_chunk_tablename_time_idx on _hyper_859_5076_chunk (cost=0.42..10798.50 rows=528692 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_5108_chunk_tablename_time_idx on _hyper_859_5108_chunk (cost=0.42..10317.10 rows=505105 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_5124_chunk_tablename_time_idx on _hyper_859_5124_chunk (cost=0.29..1604.46 rows=78491 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_5148_chunk_tablename_time_idx on _hyper_859_5148_chunk (cost=0.42..8247.42 rows=403720 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_5157_chunk_tablename_time_idx on _hyper_859_5157_chunk (cost=0.28..52.46 rows=2452 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_5166_chunk_tablename_time_idx on _hyper_859_5166_chunk (cost=0.42..8456.44 rows=413988 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_5440_chunk_tablename_time_idx on _hyper_859_5440_chunk (cost=0.28..96.65 rows=3858 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_5932_chunk_tablename_time_idx on _hyper_859_5932_chunk (cost=0.28..100.36 rows=4765 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_5950_chunk_tablename_time_idx on _hyper_859_5950_chunk (cost=0.42..9093.69 rows=445178 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_6016_chunk_tablename_time_idx on _hyper_859_6016_chunk (cost=0.42..9857.72 rows=482620 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_6064_chunk_tablename_time_idx on _hyper_859_6064_chunk (cost=0.42..9273.34 rows=454001 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_6102_chunk_tablename_time_idx on _hyper_859_6102_chunk (cost=0.29..1680.93 rows=82269 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_6265_chunk_tablename_time_idx on _hyper_859_6265_chunk (cost=0.29..375.33 rows=18256 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_6307_chunk_tablename_time_idx on _hyper_859_6307_chunk (cost=0.42..12001.06 rows=587596 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_6391_chunk_tablename_time_idx on _hyper_859_6391_chunk (cost=0.42..11934.33 rows=584320 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_6462_chunk_tablename_time_idx on _hyper_859_6462_chunk (cost=0.42..10060.48 rows=492544 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_6622_chunk_tablename_time_idx on _hyper_859_6622_chunk (cost=0.42..10382.22 rows=508273 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_6657_chunk_tablename_time_idx on _hyper_859_6657_chunk (cost=0.42..12004.34 rows=587741 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_6745_chunk_tablename_time_idx on _hyper_859_6745_chunk (cost=0.42..11854.11 rows=580366 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_6811_chunk_tablename_time_idx on _hyper_859_6811_chunk (cost=0.42..9923.97 rows=485863 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_6945_chunk_tablename_time_idx on _hyper_859_6945_chunk (cost=0.29..345.98 rows=16813 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_6967_chunk_tablename_time_idx on _hyper_859_6967_chunk (cost=0.29..1623.44 rows=79463 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_7085_chunk_tablename_time_idx on _hyper_859_7085_chunk (cost=0.42..10711.76 rows=524376 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_7129_chunk_tablename_time_idx on _hyper_859_7129_chunk (cost=0.29..1692.94 rows=82850 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_8002_chunk_tablename_time_idx on _hyper_859_8002_chunk (cost=0.42..7406.65 rows=362555 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_8038_chunk_tablename_time_idx on _hyper_859_8038_chunk (cost=0.42..10663.04 rows=522081 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_8165_chunk_tablename_time_idx on _hyper_859_8165_chunk (cost=0.42..11552.78 rows=565704 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_8369_chunk_tablename_time_idx on _hyper_859_8369_chunk (cost=0.42..11934.97 rows=584363 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_8692_chunk_tablename_time_idx on _hyper_859_8692_chunk (cost=0.42..11769.37 rows=576256 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_8846_chunk_tablename_time_idx on _hyper_859_8846_chunk (cost=0.42..11888.41 rows=582066 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_8971_chunk_tablename_time_idx on _hyper_859_8971_chunk (cost=0.42..11776.26 rows=576569 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_9122_chunk_tablename_time_idx on _hyper_859_9122_chunk (cost=0.42..11816.02 rows=578560 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_9288_chunk_tablename_time_idx on _hyper_859_9288_chunk (cost=0.42..11981.91 rows=586686 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_9453_chunk_tablename_time_idx on _hyper_859_9453_chunk (cost=0.42..11768.70 rows=576212 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_9617_chunk_tablename_time_idx on _hyper_859_9617_chunk (cost=0.42..11932.26 rows=584256 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_9766_chunk_tablename_time_idx on _hyper_859_9766_chunk (cost=0.42..11985.26 rows=586836 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_9894_chunk_tablename_time_idx on _hyper_859_9894_chunk (cost=0.42..10623.31 rows=520166 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_10027_chunk_tablename_time_idx on _hyper_859_10027_chunk (cost=0.42..11693.63 rows=572527 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_10237_chunk_tablename_time_idx on _hyper_859_10237_chunk (cost=0.42..11977.41 rows=586459 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_10679_chunk_tablename_time_idx on _hyper_859_10679_chunk (cost=0.42..7123.60 rows=348745 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_10896_chunk_tablename_time_idx on _hyper_859_10896_chunk (cost=0.42..10502.37 rows=514230 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_10993_chunk_tablename_time_idx on _hyper_859_10993_chunk (cost=0.42..11927.19 rows=583991 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_11181_chunk_tablename_time_idx on _hyper_859_11181_chunk (cost=0.42..11743.40 rows=574965 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_11518_chunk_tablename_time_idx on _hyper_859_11518_chunk (cost=0.42..11929.26 rows=584056 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_11655_chunk_tablename_time_idx on _hyper_859_11655_chunk (cost=0.42..11965.27 rows=585870 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_11852_chunk_tablename_time_idx on _hyper_859_11852_chunk (cost=0.42..11955.49 rows=585364 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_12050_chunk_tablename_time_idx on _hyper_859_12050_chunk (cost=0.42..10376.28 rows=508024 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_12232_chunk_tablename_time_idx on _hyper_859_12232_chunk (cost=0.42..11931.34 rows=584194 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_12372_chunk_tablename_time_idx on _hyper_859_12372_chunk (cost=0.42..11972.31 rows=586192 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_12538_chunk_tablename_time_idx on _hyper_859_12538_chunk (cost=0.42..11971.70 rows=586152 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_12733_chunk_tablename_time_idx on _hyper_859_12733_chunk (cost=0.42..11969.90 rows=586105 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_12892_chunk_tablename_time_idx on _hyper_859_12892_chunk (cost=0.42..11972.31 rows=586192 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_13034_chunk_tablename_time_idx on _hyper_859_13034_chunk (cost=0.42..11962.99 rows=585718 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_13161_chunk_tablename_time_idx on _hyper_859_13161_chunk (cost=0.42..11972.03 rows=586174 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_13308_chunk_tablename_time_idx on _hyper_859_13308_chunk (cost=0.42..11938.48 rows=584524 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_13467_chunk_tablename_time_idx on _hyper_859_13467_chunk (cost=0.42..11969.69 rows=586091 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_13610_chunk_tablename_time_idx on _hyper_859_13610_chunk (cost=0.42..11964.10 rows=585792 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_13768_chunk_tablename_time_idx on _hyper_859_13768_chunk (cost=0.42..11965.09 rows=585858 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_13897_chunk_tablename_time_idx on _hyper_859_13897_chunk (cost=0.42..11972.18 rows=586184 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_14014_chunk_tablename_time_idx on _hyper_859_14014_chunk (cost=0.42..11956.46 rows=585429 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_14121_chunk_tablename_time_idx on _hyper_859_14121_chunk (cost=0.42..11968.30 rows=585998 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_14220_chunk_tablename_time_idx on _hyper_859_14220_chunk (cost=0.42..11962.99 rows=585718 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_14299_chunk_tablename_time_idx on _hyper_859_14299_chunk (cost=0.42..11392.15 rows=557782 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_14451_chunk_tablename_time_idx on _hyper_859_14451_chunk (cost=0.42..11956.42 rows=585426 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_14600_chunk_tablename_time_idx on _hyper_859_14600_chunk (cost=0.42..11973.43 rows=586267 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_14731_chunk_tablename_time_idx on _hyper_859_14731_chunk (cost=0.42..11969.55 rows=586082 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_14858_chunk_tablename_time_idx on _hyper_859_14858_chunk (cost=0.42..11977.94 rows=586494 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_14959_chunk_tablename_time_idx on _hyper_859_14959_chunk (cost=0.42..11976.28 rows=586384 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_15037_chunk_tablename_time_idx on _hyper_859_15037_chunk (cost=0.42..129210.38 rows=586190 width=8) (never executed)
Heap Fetches: 0
-> Index Only Scan Backward using _hyper_859_15125_chunk_tablename_time_idx on _hyper_859_15125_chunk (cost=0.42..33638.92 rows=152600 width=8) (never executed)
Heap Fetches: 0
Planning Time: 71895.627 ms
Execution Time: 201.788 ms
(183 rows)