`SELECT` queries with `ORDER BY time_partition ASC/DESC LIMIT 1` scan through all chunks

[maxtwardowski]

What type of bug is this?

Performance issue

What subsystems and features are affected?

Query planner

What happened?

When executing a SELECT with ORDER BY {time_partition_column} ASC/DESC LIMIT 1 on a hypertable, the planner scans all available chunks.

The expected behaviour is that the planner scans just a single chunk:

• the "earliest" one for ascending sorting
• the "latest" one for descending sorting

TimescaleDB version affected

2.8.1

PostgreSQL version used

14.4

What operating system did you use?

Ubuntu 22.04

What installation method did you use?

Deb/Apt, Docker

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

No response

How can we reproduce the bug?

1. Create a test hypertable

create table test (
	id bigint not null,
	created_at timestamptz not null,
	value text
);
select create_hypertable('test', 'created_at', chunk_time_interval => interval '7 days');

2. Insert some test data

insert into test values
	(1, now(), 'a'), 
	(2, now() - interval '8 days', 'b'), 
	(3, now() - interval '16 days', 'c'), 
	(4, now() - interval '24 days', 'd'), 
	(5, now() - interval '32 days', 'e');

3. EXPLAIN ANALYZE a SELECT w/ ORDER BY ASC LIMIT 1

Query: 

explain analyze
select * from test 
order by created_at asc 
limit 1;

Output: 
Limit  (cost=0.82..0.87 rows=1 width=48) (actual time=0.065..0.066 rows=1 loops=1)
  ->  Merge Append  (cost=0.82..244.93 rows=5350 width=48) (actual time=0.064..0.065 rows=1 loops=1)
        Sort Key: _hyper_69_207_chunk.created_at
        ->  Index Scan Backward using _hyper_69_207_chunk_test_created_at_idx on _hyper_69_207_chunk  (cost=0.15..31.20 rows=1070 width=48) (actual time=0.014..0.014 rows=1 loops=1)
        ->  Index Scan Backward using _hyper_69_206_chunk_test_created_at_idx on _hyper_69_206_chunk  (cost=0.15..31.20 rows=1070 width=48) (actual time=0.011..0.011 rows=1 loops=1)
        ->  Index Scan Backward using _hyper_69_205_chunk_test_created_at_idx on _hyper_69_205_chunk  (cost=0.15..31.20 rows=1070 width=48) (actual time=0.012..0.012 rows=1 loops=1)
        ->  Index Scan Backward using _hyper_69_204_chunk_test_created_at_idx on _hyper_69_204_chunk  (cost=0.15..31.20 rows=1070 width=48) (actual time=0.011..0.011 rows=1 loops=1)
        ->  Index Scan Backward using _hyper_69_203_chunk_test_created_at_idx on _hyper_69_203_chunk  (cost=0.15..31.20 rows=1070 width=48) (actual time=0.014..0.014 rows=1 loops=1)
Planning Time: 0.493 ms
Execution Time: 0.124 ms

Expected output: index scan only on one chunk

3. EXPLAIN ANALYZE a SELECT w/ ORDER BY DESC LIMIT 1

Query: 

explain analyze
select * from test 
order by created_at desc
limit 1;

Output:

Limit  (cost=0.82..0.87 rows=1 width=48) (actual time=0.063..0.064 rows=1 loops=1)
  ->  Merge Append  (cost=0.82..244.93 rows=5350 width=48) (actual time=0.062..0.063 rows=1 loops=1)
        Sort Key: _hyper_69_203_chunk.created_at DESC
        ->  Index Scan using _hyper_69_203_chunk_test_created_at_idx on _hyper_69_203_chunk  (cost=0.15..31.20 rows=1070 width=48) (actual time=0.014..0.014 rows=1 loops=1)
        ->  Index Scan using _hyper_69_204_chunk_test_created_at_idx on _hyper_69_204_chunk  (cost=0.15..31.20 rows=1070 width=48) (actual time=0.010..0.010 rows=1 loops=1)
        ->  Index Scan using _hyper_69_205_chunk_test_created_at_idx on _hyper_69_205_chunk  (cost=0.15..31.20 rows=1070 width=48) (actual time=0.012..0.012 rows=1 loops=1)
        ->  Index Scan using _hyper_69_206_chunk_test_created_at_idx on _hyper_69_206_chunk  (cost=0.15..31.20 rows=1070 width=48) (actual time=0.011..0.011 rows=1 loops=1)
        ->  Index Scan using _hyper_69_207_chunk_test_created_at_idx on _hyper_69_207_chunk  (cost=0.15..31.20 rows=1070 width=48) (actual time=0.013..0.013 rows=1 loops=1)
Planning Time: 0.493 ms
Execution Time: 0.121 ms

Expected output: index scan only on one chunk

[svenklemm]

You might have to run VACUUM ANALYZE. In my test environment only the most recent chunk is scanned:

sven@i5102[3743887]=# EXPLAIN ANALYZE SELECT * FROM test ORDER BY created_at LIMIT 1;
                                                                                   QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.15..0.21 rows=1 width=48) (actual time=0.011..0.013 rows=1 loops=1)
   ->  Custom Scan (ChunkAppend) on test  (cost=0.15..64.20 rows=1070 width=48) (actual time=0.010..0.011 rows=1 loops=1)
         Order: test.created_at
         ->  Index Scan Backward using _hyper_1_5_chunk_test_created_at_idx on _hyper_1_5_chunk  (cost=0.15..64.20 rows=1070 width=48) (actual time=0.009..0.009 rows=1 loops=1)
         ->  Index Scan Backward using _hyper_1_4_chunk_test_created_at_idx on _hyper_1_4_chunk  (cost=0.15..64.20 rows=1070 width=48) (never executed)
         ->  Index Scan Backward using _hyper_1_3_chunk_test_created_at_idx on _hyper_1_3_chunk  (cost=0.15..64.20 rows=1070 width=48) (never executed)
         ->  Index Scan Backward using _hyper_1_2_chunk_test_created_at_idx on _hyper_1_2_chunk  (cost=0.15..64.20 rows=1070 width=48) (never executed)
         ->  Index Scan Backward using _hyper_1_1_chunk_test_created_at_idx on _hyper_1_1_chunk  (cost=0.15..64.20 rows=1070 width=48) (never executed)
 Planning Time: 3.996 ms
 Execution Time: 0.063 ms
(10 rows)

You can see never executed on all but the most recent chunks.

[maxtwardowski]

I ran VACUUM ANALYZE but the other chunks still get scanned

[bkief]

Is this a duplicate of #2897?

[svenklemm]

@maxtwardowski I repeated your steps and for me chunks were not executed so there must be something else going on why that happens.

[bkief]

@maxtwardowski @svenklemm - I was able to replicate this recently with one small change, using a non-default index. My index is conditions (location, time DESC), which works well for my workload since we are the vast majority of queries are looking at specific device ids. I actually discovered it because of terrible performance of continuous aggregates when using a NULL with end_offset. Example below.

CREATE TABLE conditions (
   time        TIMESTAMPTZ       NOT NULL,
   location    TEXT              NOT NULL,
   temperature DOUBLE PRECISION  NULL,
   humidity    DOUBLE PRECISION  NULL
);

SELECT create_hypertable('conditions', 'time', chunk_time_interval => interval '1 day', create_default_indexes => false);
CREATE UNIQUE INDEX conditions_idx ON conditions (location, time DESC);

insert into conditions values
	(now(), 'here', 1.0, 20.0), 
	(now() - interval '1 days', 'there', 2, 19), 
	(now() - interval '2 days', 'here', 3, 18), 
	(now() - interval '3 days', 'there', 4, 17), 
	(now() - interval '4 days', 'here', 5, 16);

explain analyze
select * from conditions 
order by time desc 
limit 1;

Returns the following plan:

"Limit  (cost=292.50..292.50 rows=1 width=56) (actual time=0.027..0.029 rows=1 loops=1)"
"  ->  Sort  (cost=292.50..304.62 rows=4850 width=56) (actual time=0.026..0.027 rows=1 loops=1)"
"        Sort Key: _hyper_22_2041_chunk.""time"" DESC"
"        Sort Method: top-N heapsort  Memory: 25kB"
"        ->  Append  (cost=0.00..268.25 rows=4850 width=56) (actual time=0.006..0.018 rows=5 loops=1)"
"              ->  Seq Scan on _hyper_22_2041_chunk  (cost=0.00..39.10 rows=970 width=56) (actual time=0.005..0.006 rows=1 loops=1)"
"              ->  Seq Scan on _hyper_22_2042_chunk  (cost=0.00..39.10 rows=970 width=56) (actual time=0.002..0.003 rows=1 loops=1)"
"              ->  Seq Scan on _hyper_22_2043_chunk  (cost=0.00..39.10 rows=970 width=56) (actual time=0.002..0.002 rows=1 loops=1)"
"              ->  Seq Scan on _hyper_22_2044_chunk  (cost=0.00..39.10 rows=970 width=56) (actual time=0.003..0.003 rows=1 loops=1)"
"              ->  Seq Scan on _hyper_22_2045_chunk  (cost=0.00..39.10 rows=970 width=56) (actual time=0.002..0.002 rows=1 loops=1)"
"Planning Time: 0.419 ms"
"Execution Time: 0.055 ms"

While the planner can't use the index since time isn't the lead column in the composite, there still should be enough enough information for the planner to narrow the hypertable chunks and I'm surprised more users don't run into this since it can be expensive to maintain two inverse indexes.

[nikkhils]

@bkief Do get the point that it might be expensive to maintain an additional index on TIME. But most other users haven't complained probably because create_hypertable creates an index by default on the time column and that then handles such kind of LIMIT queries appropriately.

Having said that we can certainly investigate if metadata from such composite indexes or metadata for chunks with respect to time ranges can be used to optimize such kinds of queries in the timescaledb planner.

[bkief]

Some overlap in the compressed chunk space too #4223

Some hypertable statistics, both compressed and uncompressed, seems like a good answer. The simple existence of the chunk, sans DELETE statements, should indicate rows exist in that time range. Maybe a AFTER DELETE trigger that would check if the the chunk(s) have been emptied and update the stats, but hopefully not many are using delete on their time-series data.

[alex-tate]

I wasn't sure where to put this comment as there are a number of Github issues that relate to 'LIMIT 1' queries but this one is still open and it follows on from the comment from @bkief . I've got high frequency hypertables with thousands of chunks and selecting the latest value (using ORDER BY time_partition DESC LIMIT 1) is taking considerable query planning time when compared to adding a time-based WHERE clause. While the vast majority of chunk scans are never executed, it would be great if chunk metadata could be utilised to reduce planning time with these specific (but presumably common) types of queries. This type of functionality was mentioned by @svenklemm in #4223 but that issue was targeted at querying fully compressed chunks. and is now closed.

[teemuk815]

Our team is experiencing the same issue where ORDER BY {time_partition_column} ASC/DESC LIMIT 1 is unnecessarily opening all chunks. Is there any update/advice on this issue?

[bkief]

I've also noted that adding a BRIN index on time does not help. The query planner appears to require a BTREE index on time for this optimization

[OscarD07]

I'm having same issue with this query SELECT "tagid","intvalue","floatvalue","stringvalue","datevalue","t_stamp","dataintegrity" FROM sqlth_1_data WHERE "tagid" IN (?) and "t_stamp"<? ORDER BY "t_stamp" DESC LIMIT 1 apparently it scan all chunks

[michalk-k]

I have a similar issue.

With indexes covering both sort directions

CREATE TABLE public.ltss (
	"time" timestamptz NOT NULL,
	entity_id varchar(255) NOT NULL,
	state varchar(255) NULL,
	"attributes" jsonb NULL,
	"location" public.geometry(point, 4326) NULL,
	CONSTRAINT ltss_pkey PRIMARY KEY ("time", entity_id)
);
CREATE INDEX idx_ltss_location ON public.ltss USING gist (location);
CREATE INDEX ix_ltss_state ON public.ltss USING btree (state);
CREATE INDEX ltss_attributes_idx ON public.ltss USING gin (attributes);
CREATE INDEX ltss_entityid_time_composite_idx ON public.ltss USING btree (entity_id, "time" DESC);
CREATE INDEX ltss_time_idx ON public.ltss USING btree ("time" DESC);

Finding the last value before given given time scans all chunks

EXPLAIN ANALYZE
  SELECT *
  FROM ltss
  WHERE time < '2025-05-26 12:00:00' -- AND time > '2025-05-1 12:00:00'
  ORDER BY time DESC
  LIMIT 1

click to see the plan

``` QUERY PLAN | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ Limit (cost=0.43..0.49 rows=1 width=272) (actual time=0.129..0.142 rows=1 loops=1) | -> Custom Scan (ChunkAppend) on ltss (cost=0.43..445726.34 rows=7883127 width=272) (actual time=0.127..0.138 rows=1 loops=1) | Order: ltss."time" DESC | -> Index Scan using _hyper_1_268_chunk_ltss_time_idx on _hyper_1_268_chunk (cost=0.43..445726.34 rows=7883127 width=270) (actual time=0.125..0.125 rows=1 loops=1)| Index Cond: ("time" Index Scan using _hyper_1_83_chunk_ltss_time_idx on _hyper_1_83_chunk (cost=0.43..863908.62 rows=15156316 width=273) (never executed) | Index Cond: ("time" Sort (cost=71371.29..106828.79 rows=14183000 width=271) (never executed) | Sort Key: _hyper_1_55_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_55_chunk (cost=0.03..456.29 rows=14183000 width=271) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_269_chunk (cost=0.00..456.29 rows=14183 width=166) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=68731.74..102879.24 rows=13659000 width=170) (never executed) | Sort Key: _hyper_1_33_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_33_chunk (cost=0.03..436.74 rows=13659000 width=170) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_84_chunk (cost=0.00..436.74 rows=13659 width=166) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=56938.43..85223.43 rows=11314000 width=170) (never executed) | Sort Key: _hyper_1_5_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_5_chunk (cost=0.03..368.43 rows=11314000 width=170) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_58_chunk (cost=0.00..368.43 rows=11314 width=166) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=52799.14..79026.64 rows=10491000 width=170) (never executed) | Sort Key: _hyper_1_2_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_2_chunk (cost=0.03..344.14 rows=10491000 width=170) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_57_chunk (cost=0.00..344.14 rows=10491 width=166) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=75.50..108.00 rows=13000 width=1104) (never executed) | Sort Key: _hyper_1_32_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_32_chunk (cost=0.81..10.50 rows=13000 width=1104) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_82_chunk (cost=0.00..10.50 rows=13 width=664) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=75.50..108.00 rows=13000 width=1104) (never executed) | Sort Key: _hyper_1_31_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_31_chunk (cost=0.81..10.50 rows=13000 width=1104) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_81_chunk (cost=0.00..10.50 rows=13 width=664) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=11082.39..16559.89 rows=2191000 width=170) (never executed) | Sort Key: _hyper_1_1_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_1_chunk (cost=0.06..127.39 rows=2191000 width=170) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_56_chunk (cost=0.00..127.39 rows=2191 width=172) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=630.56..943.06 rows=125000 width=172) (never executed) | Sort Key: _hyper_1_30_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_30_chunk (cost=0.04..5.56 rows=125000 width=172) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_80_chunk (cost=0.00..5.56 rows=125 width=167) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=470.16..702.66 rows=93000 width=169) (never executed) | Sort Key: _hyper_1_26_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_26_chunk (cost=0.06..5.16 rows=93000 width=169) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_79_chunk (cost=0.00..5.16 rows=93 width=167) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=75.50..108.00 rows=13000 width=1104) (never executed) | Sort Key: _hyper_1_25_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_25_chunk (cost=0.81..10.50 rows=13000 width=1104) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_78_chunk (cost=0.00..10.50 rows=13 width=664) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=75.50..108.00 rows=13000 width=1104) (never executed) | Sort Key: _hyper_1_24_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_24_chunk (cost=0.81..10.50 rows=13000 width=1104) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_77_chunk (cost=0.00..10.50 rows=13 width=664) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=75.50..108.00 rows=13000 width=1104) (never executed) | Sort Key: _hyper_1_23_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_23_chunk (cost=0.81..10.50 rows=13000 width=1104) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_76_chunk (cost=0.00..10.50 rows=13 width=664) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=75.50..108.00 rows=13000 width=1104) (never executed) | Sort Key: _hyper_1_22_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_22_chunk (cost=0.81..10.50 rows=13000 width=1104) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_75_chunk (cost=0.00..10.50 rows=13 width=664) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=75.50..108.00 rows=13000 width=1104) (never executed) | Sort Key: _hyper_1_21_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_21_chunk (cost=0.81..10.50 rows=13000 width=1104) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_74_chunk (cost=0.00..10.50 rows=13 width=664) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=75.50..108.00 rows=13000 width=1104) (never executed) | Sort Key: _hyper_1_20_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_20_chunk (cost=0.81..10.50 rows=13000 width=1104) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_73_chunk (cost=0.00..10.50 rows=13 width=664) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=75.50..108.00 rows=13000 width=1104) (never executed) | Sort Key: _hyper_1_19_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_19_chunk (cost=0.81..10.50 rows=13000 width=1104) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_72_chunk (cost=0.00..10.50 rows=13 width=664) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=75.50..108.00 rows=13000 width=1104) (never executed) | Sort Key: _hyper_1_18_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_18_chunk (cost=0.81..10.50 rows=13000 width=1104) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_71_chunk (cost=0.00..10.50 rows=13 width=664) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=75.50..108.00 rows=13000 width=1104) (never executed) | Sort Key: _hyper_1_17_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_17_chunk (cost=0.81..10.50 rows=13000 width=1104) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_70_chunk (cost=0.00..10.50 rows=13 width=664) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=75.50..108.00 rows=13000 width=1104) (never executed) | Sort Key: _hyper_1_16_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_16_chunk (cost=0.81..10.50 rows=13000 width=1104) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_69_chunk (cost=0.00..10.50 rows=13 width=664) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=75.50..108.00 rows=13000 width=1104) (never executed) | Sort Key: _hyper_1_15_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_15_chunk (cost=0.81..10.50 rows=13000 width=1104) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_68_chunk (cost=0.00..10.50 rows=13 width=664) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=75.50..108.00 rows=13000 width=1104) (never executed) | Sort Key: _hyper_1_14_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_14_chunk (cost=0.81..10.50 rows=13000 width=1104) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_67_chunk (cost=0.00..10.50 rows=13 width=664) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=75.50..108.00 rows=13000 width=1104) (never executed) | Sort Key: _hyper_1_13_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_13_chunk (cost=0.81..10.50 rows=13000 width=1104) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_66_chunk (cost=0.00..10.50 rows=13 width=664) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=75.50..108.00 rows=13000 width=1104) (never executed) | Sort Key: _hyper_1_12_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_12_chunk (cost=0.81..10.50 rows=13000 width=1104) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_65_chunk (cost=0.00..10.50 rows=13 width=664) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=75.50..108.00 rows=13000 width=1104) (never executed) | Sort Key: _hyper_1_11_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_11_chunk (cost=0.81..10.50 rows=13000 width=1104) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_64_chunk (cost=0.00..10.50 rows=13 width=664) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=75.50..108.00 rows=13000 width=1104) (never executed) | Sort Key: _hyper_1_10_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_10_chunk (cost=0.81..10.50 rows=13000 width=1104) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_63_chunk (cost=0.00..10.50 rows=13 width=664) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=75.50..108.00 rows=13000 width=1104) (never executed) | Sort Key: _hyper_1_9_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_9_chunk (cost=0.81..10.50 rows=13000 width=1104) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_62_chunk (cost=0.00..10.50 rows=13 width=664) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=75.50..108.00 rows=13000 width=1104) (never executed) | Sort Key: _hyper_1_8_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_8_chunk (cost=0.81..10.50 rows=13000 width=1104) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_61_chunk (cost=0.00..10.50 rows=13 width=664) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=75.50..108.00 rows=13000 width=1104) (never executed) | Sort Key: _hyper_1_7_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_7_chunk (cost=0.81..10.50 rows=13000 width=1104) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_60_chunk (cost=0.00..10.50 rows=13 width=664) (never executed) | Filter: (_ts_meta_min_1 Sort (cost=75.50..108.00 rows=13000 width=1104) (never executed) | Sort Key: _hyper_1_6_chunk."time" DESC | -> Custom Scan (DecompressChunk) on _hyper_1_6_chunk (cost=0.81..10.50 rows=13000 width=1104) (never executed) | Vectorized Filter: ("time" Seq Scan on compress_hyper_11_59_chunk (cost=0.00..10.50 rows=13 width=664) (never executed) | Filter: (_ts_meta_min_1

Moreover, this is a suggested method of providing the surrounding values to interpolate() function: https://docs.timescale.com/api/latest/hyperfunctions/gapfilling/time_bucket_gapfill/#use-time_bucket_gapfill-and-use-linear-interpolation-with-prev-and-next-expression

I'm aware I can improve the situation by limiting the data to scan (see commented out part), but again, this is not aligned with the documentation. And at least by intuition, tsdb should be able to point to the correct chunk since this information is stored in timescaledb_information.chunks.

[LyohaLyoha]

We have the same issue in production, with PgSql17 and Timescale 2.20

• Few hundreds of compressed chunks, each 3.5GB before compression (as per 25% RAM recommendation)

• Table keeps records for a ~1 year

• All queries discussed below query data from today, or yesterday - so from 2-3 latest chunks only

• It's especially important for query with DESC LIMIT 1 - it actually returns record/result from TODAY chunk, so it gets it from 1-2 most recent chunks always!

• Table is created as CREATE TABLE "rawValues" ( "moment" TIMESTAMPTZ NOT NULL, "indicatorId"" BIGINT NOT NULL, "valueType" INT NOT NULL, "value" BYTEA STORAGE EXTERNAL )

• then hypertable created as SELECT create_hypertable( '"rawValues"', by_range('moment', INTERVAL 12 HOURS') );

• then index added as CREATE INDEX "rv_indicator_moment_idx"" ON "rawValues" ("indicatorId", "moment" DESC);

• then compression as ALTER TABLE "rawValues" SET ( timescaledb.compress, timescaledb.compress_segmentby = '"indicatorId"', timescaledb.compress_orderby = '"moment" DESC' );

Queries like SELECT * FROM "rawValues" WHERE "indicatorId"=$1 AND "moment">$2 AND "moment"<$3 ORDER BY "moment" DESC are lightning fast - executes in 10-20 milliseconds, including scenarios when $2 and $3 are datetimes like "today 6AM" and "today 10AM" corrsponding to a pair of most recent chunks.

HOWEVER: SELECT * FROM "rawValues" WHERE "indicatorId"=$1 AND "moment"<$2 ORDER BY "moment" DESC LIMIT 1 is horribly, really horribly slow, executes 2-3 SECONDS - having datetime $2 like "today 10AM", again, and having matching records in a pair of most recent chunks.

And according to EXPLAIN - whole time is wasted by PLANNER - it decompresses ALL few hundred chunks for the whole year - yet, as i said above, it actually returns as a result, the most recent pair of chunk always (since full chunk always has at least 1 record of given $1 indicatorId).

So planner just wastes an enormous amount of time on totally useless decompression of all previous chunks.

That feels very wrong.

[OscarD07]

Any solution?