hi:
I have a Timescaledb data table, which is partitioned according to 15 days. When I wrote some data in it, I found that when executing a simple query, the query was the slowest for the data in the latest chunk. When querying the data of the latest chunk, the situation basically appears Buffers: shared hit=4 read=314. A large amount of data is read from disk, and the query is very slow, but if it is other chunks, this problem will not occur;
I created the following index on the hypertable：
CREATE INDEX t_naqpmstest_d1_l1_s_hour_predictiontime_idx ON public.t_naqpmstest_d1_l1_s_hour USING btree (predictiontime DESC)
This is the latest chunk query statement:
EXPLAIN(ANALYZE on,buffers on) SELECT * FROM "t_naqpmstest_d1_l1_s_hour"
where predictiontime = '2024-7-1 20:00:00'
and code = '420100076'
and datadate >= '2024-7-2 20:00:00'
and datadate <= '2024-7-15 20:00:00'
Index Scan using _hyper_947_7566_chunk_t_naqpmstest_d1_l1_s_hour_idx on _hyper_947_7566_chunk (cost=0.56…282.86 rows=249 width=124) (actual time=7.700…1412.034 rows=313 loops=1)
Index Cond: ((code = ‘420100076’::text) AND (predictiontime = ‘2024-07-01 20:00:00+08’::timestamp with time zone) AND (datadate >= ‘2024-07-02 20:00:00+08’::timestamp with time zone) AND (datadate <= ‘2024-07-15 20:00:00+08’::timestamp with time zone))
Buffers: shared hit=4 read=314
Planning:
Buffers: shared hit=22
Planning Time: 0.286 ms
Execution Time: 1412.206 ms
This is the query status of other chunk:
EXPLAIN(ANALYZE on,buffers on) SELECT * FROM "t_naqpmstest_d1_l1_s_hour"
where predictiontime = '2024-6-1 20:00:00'
and code = '420100076'
and datadate >= '2024-6-2 20:00:00'
and datadate <= '2024-6-15 20:00:00'
Index Scan using _hyper_947_7563_chunk_t_naqpmstest_d1_l1_s_hour_idx on _hyper_947_7563_chunk (cost=0.56…82.94 rows=156 width=124) (actual time=51.096…76.151 rows=313 loops=1)
Index Cond: ((code = ‘420100076’::text) AND (predictiontime = ‘2024-06-01 20:00:00+08’::timestamp with time zone) AND (datadate >= ‘2024-06-02 20:00:00+08’::timestamp with time zone) AND (datadate <= ‘2024-06-15 20:00:00+08’::timestamp with time zone))
Buffers: shared hit=263 read=15
Planning:
Buffers: shared hit=22
Planning Time: 0.748 ms
Execution Time: 76.243 ms
This is the size information of all chunks:
Can you help me answer the possible reasons for the problem?