What type of enhancement is this?

Performance

What subsystems and features will be improved?

Compression, SkipScan, Query planner

What does the enhancement do?

Currently we don't push sort down into compressed unordered chunks, i.e. we do not use ordered indexes (segmentby_col, ..., orderby_col...) defined on compressed chunks if those chunks are unordered.

However, we can use compressed chunk indexes if the only columns required by the query are segmentby columns and min/max over orderby columns, for example here we should be able to push sort down in the below queries:

CREATE TABLE metrics (time TIMESTAMPTZ NOT NULL, device TEXT, value float) 
WITH (tsdb.hypertable, tsdb.orderby='time desc', tsdb.segmentby='device');

-- Will create unordered chunks
SET timescaledb.enable_direct_compress_insert = true;
INSERT INTO metrics SELECT '2025-01-01'::timestamptz + (i || ' minute')::interval, 'd1', i::float FROM generate_series(0,3000) i;

-- Should be able to use compressed index on (device, _ts_meta_min_1 DESC, _ts_meta_max_1 DESC)
select device, min(time) from metrics group by device order by device;

We can also use compressed index in queries where we only need the first decompessed tuple from each batch, i.e. where we do not need to merge overlapping tuples. For example, we can use compressed index on below DISTINCT ON query:

SELECT DISTINCT ON (device) * FROM metrics ORDER BY device;

If we are able to use compressed indexes on unordered compressed chunks in some scenarios, we can also use SkipScan in such scenarios for great performance benefit.

It is important to get the best possible plans for unordered chunks as they are produced by Direct Compress, and we need Direct Compress to produce data which can be queried fast.

Implementation challenges

Should be fast and easy to implement.