merge_append_partially_compressed.out

-- This file and its contents are licensed under the Timescale License.
-- Please see the included NOTICE for copyright information and
-- LICENSE-TIMESCALE for a copy of the license.
-- this test checks the validity of the produced plans for partially compressed chunks
-- when injecting query_pathkeys on top of the append
-- path that combines the uncompressed and compressed parts of a chunk.
-- We're testing the MergeAppend here which is not compatible with parallel plans.
set max_parallel_workers_per_gather = 0;
set timescaledb.enable_decompression_sorted_merge = off;
\set PREFIX 'EXPLAIN (analyze, buffers off, costs off, timing off, summary off)'
CREATE TABLE ht_metrics_compressed(time timestamptz, device int, value float);
SELECT create_hypertable('ht_metrics_compressed','time');
         create_hypertable          
------------------------------------
 (1,public,ht_metrics_compressed,t)

ALTER TABLE ht_metrics_compressed SET (timescaledb.compress, timescaledb.compress_segmentby='device', timescaledb.compress_orderby='time');
INSERT INTO ht_metrics_compressed
SELECT time, device, device * 0.1
FROM generate_series('2020-01-02 00:00:00+00'::timestamptz,'2020-01-18 00:00:00+00'::timestamptz,'6 hour') time,
generate_series(1,3) device;
SELECT compress_chunk(c) FROM show_chunks('ht_metrics_compressed') c;
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_1_1_chunk
 _timescaledb_internal._hyper_1_2_chunk
 _timescaledb_internal._hyper_1_3_chunk

-- make them partially compressed
INSERT INTO ht_metrics_compressed
SELECT time, device, device * 0.1
FROM generate_series('2020-01-02 00:00:00+00'::timestamptz,'2020-01-18 00:00:00+00'::timestamptz,'9 hour') time,
generate_series(1,3) device;
VACUUM ANALYZE ht_metrics_compressed;
SELECT tableoid::regclass, min(time), max(time) from ht_metrics_compressed group by 1 order by 2;
                tableoid                |             min              |             max              
----------------------------------------+------------------------------+------------------------------
 _timescaledb_internal._hyper_1_1_chunk | Wed Jan 01 16:00:00 2020 PST | Wed Jan 08 10:00:00 2020 PST
 _timescaledb_internal._hyper_1_2_chunk | Wed Jan 08 16:00:00 2020 PST | Wed Jan 15 13:00:00 2020 PST
 _timescaledb_internal._hyper_1_3_chunk | Wed Jan 15 16:00:00 2020 PST | Fri Jan 17 16:00:00 2020 PST

SELECT show_chunks('ht_metrics_compressed') AS "CHUNK" LIMIT 1 \gset
-- test UNION with partially compressed chunks
:PREFIX SELECT * FROM :CHUNK UNION ALL SELECT * FROM :CHUNK;
--- QUERY PLAN ---
 Append (actual rows=282.00 loops=1)
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=84.00 loops=1)
         ->  Seq Scan on compress_hyper_2_4_chunk (actual rows=3.00 loops=1)
   ->  Seq Scan on _hyper_1_1_chunk (actual rows=57.00 loops=1)
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk _hyper_1_1_chunk_1 (actual rows=84.00 loops=1)
         ->  Seq Scan on compress_hyper_2_4_chunk compress_hyper_2_4_chunk_1 (actual rows=3.00 loops=1)
   ->  Seq Scan on _hyper_1_1_chunk _hyper_1_1_chunk_1 (actual rows=57.00 loops=1)

:PREFIX SELECT * FROM :CHUNK UNION ALL SELECT * FROM :CHUNK ORDER BY time DESC;
--- QUERY PLAN ---
 Sort (actual rows=282.00 loops=1)
   Sort Key: _hyper_1_1_chunk."time" DESC
   Sort Method: quicksort 
   ->  Append (actual rows=282.00 loops=1)
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=84.00 loops=1)
               ->  Seq Scan on compress_hyper_2_4_chunk (actual rows=3.00 loops=1)
         ->  Seq Scan on _hyper_1_1_chunk (actual rows=57.00 loops=1)
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk _hyper_1_1_chunk_1 (actual rows=84.00 loops=1)
               ->  Seq Scan on compress_hyper_2_4_chunk compress_hyper_2_4_chunk_1 (actual rows=3.00 loops=1)
         ->  Seq Scan on _hyper_1_1_chunk _hyper_1_1_chunk_1 (actual rows=57.00 loops=1)

:PREFIX SELECT device FROM :CHUNK UNION ALL SELECT device FROM :CHUNK ORDER BY device DESC;
--- QUERY PLAN ---
 Merge Append (actual rows=282.00 loops=1)
   Sort Key: _hyper_1_1_chunk.device DESC
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=84.00 loops=1)
         ->  Sort (actual rows=3.00 loops=1)
               Sort Key: compress_hyper_2_4_chunk.device DESC
               Sort Method: quicksort 
               ->  Seq Scan on compress_hyper_2_4_chunk (actual rows=3.00 loops=1)
   ->  Sort (actual rows=57.00 loops=1)
         Sort Key: _hyper_1_1_chunk.device DESC
         Sort Method: quicksort 
         ->  Seq Scan on _hyper_1_1_chunk (actual rows=57.00 loops=1)
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk _hyper_1_1_chunk_1 (actual rows=84.00 loops=1)
         ->  Sort (actual rows=3.00 loops=1)
               Sort Key: compress_hyper_2_4_chunk_1.device DESC
               Sort Method: quicksort 
               ->  Seq Scan on compress_hyper_2_4_chunk compress_hyper_2_4_chunk_1 (actual rows=3.00 loops=1)
   ->  Sort (actual rows=57.00 loops=1)
         Sort Key: _hyper_1_1_chunk_1.device DESC
         Sort Method: quicksort 
         ->  Seq Scan on _hyper_1_1_chunk _hyper_1_1_chunk_1 (actual rows=57.00 loops=1)

:PREFIX SELECT value FROM :CHUNK UNION ALL SELECT value FROM :CHUNK ORDER BY value DESC;
--- QUERY PLAN ---
 Merge Append (actual rows=282.00 loops=1)
   Sort Key: _hyper_1_1_chunk.value DESC
   ->  Sort (actual rows=84.00 loops=1)
         Sort Key: _hyper_1_1_chunk.value DESC
         Sort Method: quicksort 
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=84.00 loops=1)
               ->  Seq Scan on compress_hyper_2_4_chunk (actual rows=3.00 loops=1)
   ->  Sort (actual rows=57.00 loops=1)
         Sort Key: _hyper_1_1_chunk.value DESC
         Sort Method: quicksort 
         ->  Seq Scan on _hyper_1_1_chunk (actual rows=57.00 loops=1)
   ->  Sort (actual rows=84.00 loops=1)
         Sort Key: _hyper_1_1_chunk_1.value DESC
         Sort Method: quicksort 
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk _hyper_1_1_chunk_1 (actual rows=84.00 loops=1)
               ->  Seq Scan on compress_hyper_2_4_chunk compress_hyper_2_4_chunk_1 (actual rows=3.00 loops=1)
   ->  Sort (actual rows=57.00 loops=1)
         Sort Key: _hyper_1_1_chunk_1.value DESC
         Sort Method: quicksort 
         ->  Seq Scan on _hyper_1_1_chunk _hyper_1_1_chunk_1 (actual rows=57.00 loops=1)

-- chunkAppend eligible queries (from tsbench)
-- sort is not pushed down
:PREFIX SELECT * FROM ht_metrics_compressed ORDER BY time DESC, device LIMIT 1;
--- QUERY PLAN ---
 Limit (actual rows=1.00 loops=1)
   ->  Custom Scan (ChunkAppend) on ht_metrics_compressed (actual rows=1.00 loops=1)
         Order: ht_metrics_compressed."time" DESC, ht_metrics_compressed.device
         ->  Merge Append (actual rows=1.00 loops=1)
               Sort Key: ht_metrics_compressed."time" DESC, ht_metrics_compressed.device
               ->  Sort (actual rows=1.00 loops=1)
                     Sort Key: _hyper_1_3_chunk."time" DESC, _hyper_1_3_chunk.device
                     Sort Method: top-N heapsort 
                     ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk (actual rows=27.00 loops=1)
                           ->  Seq Scan on compress_hyper_2_6_chunk (actual rows=3.00 loops=1)
               ->  Sort (actual rows=1.00 loops=1)
                     Sort Key: _hyper_1_3_chunk."time" DESC, _hyper_1_3_chunk.device
                     Sort Method: top-N heapsort 
                     ->  Seq Scan on _hyper_1_3_chunk (actual rows=15.00 loops=1)
         ->  Merge Append (never executed)
               Sort Key: ht_metrics_compressed."time" DESC, ht_metrics_compressed.device
               ->  Sort (never executed)
                     Sort Key: _hyper_1_2_chunk."time" DESC, _hyper_1_2_chunk.device
                     ->  Custom Scan (ColumnarScan) on _hyper_1_2_chunk (never executed)
                           ->  Seq Scan on compress_hyper_2_5_chunk (never executed)
               ->  Sort (never executed)
                     Sort Key: _hyper_1_2_chunk."time" DESC, _hyper_1_2_chunk.device
                     ->  Seq Scan on _hyper_1_2_chunk (never executed)
         ->  Merge Append (never executed)
               Sort Key: ht_metrics_compressed."time" DESC, ht_metrics_compressed.device
               ->  Sort (never executed)
                     Sort Key: _hyper_1_1_chunk."time" DESC, _hyper_1_1_chunk.device
                     ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk (never executed)
                           ->  Seq Scan on compress_hyper_2_4_chunk (never executed)
               ->  Sort (never executed)
                     Sort Key: _hyper_1_1_chunk."time" DESC, _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk (never executed)

:PREFIX SELECT * FROM ht_metrics_compressed ORDER BY time_bucket('1d', time) DESC, device LIMIT 1;
--- QUERY PLAN ---
 Limit (actual rows=1.00 loops=1)
   ->  Merge Append (actual rows=1.00 loops=1)
         Sort Key: (time_bucket('@ 1 day'::interval, ht_metrics_compressed."time")) DESC, ht_metrics_compressed.device
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: (time_bucket('@ 1 day'::interval, _hyper_1_1_chunk."time")) DESC, _hyper_1_1_chunk.device
               Sort Method: top-N heapsort 
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=84.00 loops=1)
                     ->  Seq Scan on compress_hyper_2_4_chunk (actual rows=3.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: (time_bucket('@ 1 day'::interval, _hyper_1_1_chunk."time")) DESC, _hyper_1_1_chunk.device
               Sort Method: top-N heapsort 
               ->  Seq Scan on _hyper_1_1_chunk (actual rows=57.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: (time_bucket('@ 1 day'::interval, _hyper_1_2_chunk."time")) DESC, _hyper_1_2_chunk.device
               Sort Method: top-N heapsort 
               ->  Custom Scan (ColumnarScan) on _hyper_1_2_chunk (actual rows=84.00 loops=1)
                     ->  Seq Scan on compress_hyper_2_5_chunk (actual rows=3.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: (time_bucket('@ 1 day'::interval, _hyper_1_2_chunk."time")) DESC, _hyper_1_2_chunk.device
               Sort Method: top-N heapsort 
               ->  Seq Scan on _hyper_1_2_chunk (actual rows=57.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: (time_bucket('@ 1 day'::interval, _hyper_1_3_chunk."time")) DESC, _hyper_1_3_chunk.device
               Sort Method: top-N heapsort 
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk (actual rows=27.00 loops=1)
                     ->  Seq Scan on compress_hyper_2_6_chunk (actual rows=3.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: (time_bucket('@ 1 day'::interval, _hyper_1_3_chunk."time")) DESC, _hyper_1_3_chunk.device
               Sort Method: top-N heapsort 
               ->  Seq Scan on _hyper_1_3_chunk (actual rows=15.00 loops=1)

:PREFIX SELECT * FROM ht_metrics_compressed ORDER BY time desc limit 10;
--- QUERY PLAN ---
 Limit (actual rows=10.00 loops=1)
   ->  Custom Scan (ChunkAppend) on ht_metrics_compressed (actual rows=10.00 loops=1)
         Order: ht_metrics_compressed."time" DESC
         ->  Merge Append (actual rows=10.00 loops=1)
               Sort Key: ht_metrics_compressed."time" DESC
               ->  Sort (actual rows=7.00 loops=1)
                     Sort Key: _hyper_1_3_chunk."time" DESC
                     Sort Method: top-N heapsort 
                     ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk (actual rows=27.00 loops=1)
                           ->  Seq Scan on compress_hyper_2_6_chunk (actual rows=3.00 loops=1)
               ->  Index Scan using _hyper_1_3_chunk_ht_metrics_compressed_time_idx on _hyper_1_3_chunk (actual rows=4.00 loops=1)
         ->  Merge Append (never executed)
               Sort Key: ht_metrics_compressed."time" DESC
               ->  Sort (never executed)
                     Sort Key: _hyper_1_2_chunk."time" DESC
                     ->  Custom Scan (ColumnarScan) on _hyper_1_2_chunk (never executed)
                           ->  Seq Scan on compress_hyper_2_5_chunk (never executed)
               ->  Index Scan using _hyper_1_2_chunk_ht_metrics_compressed_time_idx on _hyper_1_2_chunk (never executed)
         ->  Merge Append (never executed)
               Sort Key: ht_metrics_compressed."time" DESC
               ->  Sort (never executed)
                     Sort Key: _hyper_1_1_chunk."time" DESC
                     ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk (never executed)
                           ->  Seq Scan on compress_hyper_2_4_chunk (never executed)
               ->  Index Scan using _hyper_1_1_chunk_ht_metrics_compressed_time_idx on _hyper_1_1_chunk (never executed)

:PREFIX SELECT * FROM ht_metrics_compressed ORDER BY time_bucket('2d',time) DESC LIMIT 1;
--- QUERY PLAN ---
 Limit (actual rows=1.00 loops=1)
   ->  Custom Scan (ChunkAppend) on ht_metrics_compressed (actual rows=1.00 loops=1)
         Order: time_bucket('@ 2 days'::interval, ht_metrics_compressed."time") DESC
         ->  Merge Append (actual rows=1.00 loops=1)
               Sort Key: (time_bucket('@ 2 days'::interval, ht_metrics_compressed."time")) DESC
               ->  Sort (actual rows=1.00 loops=1)
                     Sort Key: (time_bucket('@ 2 days'::interval, _hyper_1_3_chunk."time")) DESC
                     Sort Method: top-N heapsort 
                     ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk (actual rows=27.00 loops=1)
                           ->  Seq Scan on compress_hyper_2_6_chunk (actual rows=3.00 loops=1)
               ->  Index Scan using _hyper_1_3_chunk_ht_metrics_compressed_time_idx on _hyper_1_3_chunk (actual rows=1.00 loops=1)
         ->  Merge Append (never executed)
               Sort Key: (time_bucket('@ 2 days'::interval, ht_metrics_compressed."time")) DESC
               ->  Sort (never executed)
                     Sort Key: (time_bucket('@ 2 days'::interval, _hyper_1_2_chunk."time")) DESC
                     ->  Custom Scan (ColumnarScan) on _hyper_1_2_chunk (never executed)
                           ->  Seq Scan on compress_hyper_2_5_chunk (never executed)
               ->  Index Scan using _hyper_1_2_chunk_ht_metrics_compressed_time_idx on _hyper_1_2_chunk (never executed)
         ->  Merge Append (never executed)
               Sort Key: (time_bucket('@ 2 days'::interval, ht_metrics_compressed."time")) DESC
               ->  Sort (never executed)
                     Sort Key: (time_bucket('@ 2 days'::interval, _hyper_1_1_chunk."time")) DESC
                     ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk (never executed)
                           ->  Seq Scan on compress_hyper_2_4_chunk (never executed)
               ->  Index Scan using _hyper_1_1_chunk_ht_metrics_compressed_time_idx on _hyper_1_1_chunk (never executed)

:PREFIX SELECT * FROM ht_metrics_compressed WHERE device IN (1,2,3) ORDER BY time DESC LIMIT 1;
--- QUERY PLAN ---
 Limit (actual rows=1.00 loops=1)
   ->  Custom Scan (ChunkAppend) on ht_metrics_compressed (actual rows=1.00 loops=1)
         Order: ht_metrics_compressed."time" DESC
         ->  Merge Append (actual rows=1.00 loops=1)
               Sort Key: ht_metrics_compressed."time" DESC
               ->  Sort (actual rows=1.00 loops=1)
                     Sort Key: _hyper_1_3_chunk."time" DESC
                     Sort Method: top-N heapsort 
                     ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk (actual rows=27.00 loops=1)
                           ->  Seq Scan on compress_hyper_2_6_chunk (actual rows=3.00 loops=1)
                                 Filter: (device = ANY ('{1,2,3}'::integer[]))
               ->  Index Scan using _hyper_1_3_chunk_ht_metrics_compressed_time_idx on _hyper_1_3_chunk (actual rows=1.00 loops=1)
                     Filter: (device = ANY ('{1,2,3}'::integer[]))
         ->  Merge Append (never executed)
               Sort Key: ht_metrics_compressed."time" DESC
               ->  Sort (never executed)
                     Sort Key: _hyper_1_2_chunk."time" DESC
                     ->  Custom Scan (ColumnarScan) on _hyper_1_2_chunk (never executed)
                           ->  Seq Scan on compress_hyper_2_5_chunk (never executed)
                                 Filter: (device = ANY ('{1,2,3}'::integer[]))
               ->  Index Scan using _hyper_1_2_chunk_ht_metrics_compressed_time_idx on _hyper_1_2_chunk (never executed)
                     Filter: (device = ANY ('{1,2,3}'::integer[]))
         ->  Merge Append (never executed)
               Sort Key: ht_metrics_compressed."time" DESC
               ->  Sort (never executed)
                     Sort Key: _hyper_1_1_chunk."time" DESC
                     ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk (never executed)
                           ->  Seq Scan on compress_hyper_2_4_chunk (never executed)
                                 Filter: (device = ANY ('{1,2,3}'::integer[]))
               ->  Index Scan using _hyper_1_1_chunk_ht_metrics_compressed_time_idx on _hyper_1_1_chunk (never executed)
                     Filter: (device = ANY ('{1,2,3}'::integer[]))

:PREFIX SELECT * FROM ht_metrics_compressed WHERE device IN (1,2,3) ORDER BY time, device DESC LIMIT 1;
--- QUERY PLAN ---
 Limit (actual rows=1.00 loops=1)
   ->  Custom Scan (ChunkAppend) on ht_metrics_compressed (actual rows=1.00 loops=1)
         Order: ht_metrics_compressed."time", ht_metrics_compressed.device DESC
         ->  Merge Append (actual rows=1.00 loops=1)
               Sort Key: ht_metrics_compressed."time", ht_metrics_compressed.device DESC
               ->  Sort (actual rows=1.00 loops=1)
                     Sort Key: _hyper_1_1_chunk."time", _hyper_1_1_chunk.device DESC
                     Sort Method: top-N heapsort 
                     ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=84.00 loops=1)
                           ->  Seq Scan on compress_hyper_2_4_chunk (actual rows=3.00 loops=1)
                                 Filter: (device = ANY ('{1,2,3}'::integer[]))
               ->  Sort (actual rows=1.00 loops=1)
                     Sort Key: _hyper_1_1_chunk."time", _hyper_1_1_chunk.device DESC
                     Sort Method: top-N heapsort 
                     ->  Seq Scan on _hyper_1_1_chunk (actual rows=57.00 loops=1)
                           Filter: (device = ANY ('{1,2,3}'::integer[]))
         ->  Merge Append (never executed)
               Sort Key: ht_metrics_compressed."time", ht_metrics_compressed.device DESC
               ->  Sort (never executed)
                     Sort Key: _hyper_1_2_chunk."time", _hyper_1_2_chunk.device DESC
                     ->  Custom Scan (ColumnarScan) on _hyper_1_2_chunk (never executed)
                           ->  Seq Scan on compress_hyper_2_5_chunk (never executed)
                                 Filter: (device = ANY ('{1,2,3}'::integer[]))
               ->  Sort (never executed)
                     Sort Key: _hyper_1_2_chunk."time", _hyper_1_2_chunk.device DESC
                     ->  Seq Scan on _hyper_1_2_chunk (never executed)
                           Filter: (device = ANY ('{1,2,3}'::integer[]))
         ->  Merge Append (never executed)
               Sort Key: ht_metrics_compressed."time", ht_metrics_compressed.device DESC
               ->  Sort (never executed)
                     Sort Key: _hyper_1_3_chunk."time", _hyper_1_3_chunk.device DESC
                     ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk (never executed)
                           ->  Seq Scan on compress_hyper_2_6_chunk (never executed)
                                 Filter: (device = ANY ('{1,2,3}'::integer[]))
               ->  Sort (never executed)
                     Sort Key: _hyper_1_3_chunk."time", _hyper_1_3_chunk.device DESC
                     ->  Seq Scan on _hyper_1_3_chunk (never executed)
                           Filter: (device = ANY ('{1,2,3}'::integer[]))

-- index scan, no sort on top
:PREFIX SELECT * FROM ht_metrics_compressed WHERE device = 3 ORDER BY time DESC LIMIT 1; -- index scan, no resorting required
--- QUERY PLAN ---
 Limit (actual rows=1.00 loops=1)
   ->  Custom Scan (ChunkAppend) on ht_metrics_compressed (actual rows=1.00 loops=1)
         Order: ht_metrics_compressed."time" DESC
         ->  Merge Append (actual rows=1.00 loops=1)
               Sort Key: ht_metrics_compressed."time" DESC
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk (actual rows=1.00 loops=1)
                     ->  Sort (actual rows=1.00 loops=1)
                           Sort Key: compress_hyper_2_6_chunk._ts_meta_v2_first_time DESC, compress_hyper_2_6_chunk._ts_meta_v2_last_time DESC
                           Sort Method: quicksort 
                           ->  Seq Scan on compress_hyper_2_6_chunk (actual rows=1.00 loops=1)
                                 Filter: (device = 3)
                                 Rows Removed by Filter: 2
               ->  Index Scan using _hyper_1_3_chunk_ht_metrics_compressed_time_idx on _hyper_1_3_chunk (actual rows=1.00 loops=1)
                     Filter: (device = 3)
                     Rows Removed by Filter: 2
         ->  Merge Append (never executed)
               Sort Key: ht_metrics_compressed."time" DESC
               ->  Custom Scan (ColumnarScan) on _hyper_1_2_chunk (never executed)
                     ->  Sort (never executed)
                           Sort Key: compress_hyper_2_5_chunk._ts_meta_v2_first_time DESC, compress_hyper_2_5_chunk._ts_meta_v2_last_time DESC
                           ->  Seq Scan on compress_hyper_2_5_chunk (never executed)
                                 Filter: (device = 3)
               ->  Index Scan using _hyper_1_2_chunk_ht_metrics_compressed_time_idx on _hyper_1_2_chunk (never executed)
                     Filter: (device = 3)
         ->  Merge Append (never executed)
               Sort Key: ht_metrics_compressed."time" DESC
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk (never executed)
                     ->  Sort (never executed)
                           Sort Key: compress_hyper_2_4_chunk._ts_meta_v2_first_time DESC, compress_hyper_2_4_chunk._ts_meta_v2_last_time DESC
                           ->  Seq Scan on compress_hyper_2_4_chunk (never executed)
                                 Filter: (device = 3)
               ->  Index Scan using _hyper_1_1_chunk_ht_metrics_compressed_time_idx on _hyper_1_1_chunk (never executed)
                     Filter: (device = 3)

SELECT * FROM ht_metrics_compressed WHERE device = 3 ORDER BY time DESC LIMIT 1;
             time             | device | value 
------------------------------+--------+-------
 Fri Jan 17 16:00:00 2020 PST |      3 |   0.3

:PREFIX SELECT * FROM ht_metrics_compressed WHERE device = 3 ORDER BY device, time DESC LIMIT 1; -- this uses the index and does not do sort on top
--- QUERY PLAN ---
 Limit (actual rows=1.00 loops=1)
   ->  Merge Append (actual rows=1.00 loops=1)
         Sort Key: ht_metrics_compressed."time" DESC
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=1.00 loops=1)
               ->  Sort (actual rows=1.00 loops=1)
                     Sort Key: compress_hyper_2_4_chunk._ts_meta_v2_first_time DESC, compress_hyper_2_4_chunk._ts_meta_v2_last_time DESC
                     Sort Method: quicksort 
                     ->  Seq Scan on compress_hyper_2_4_chunk (actual rows=1.00 loops=1)
                           Filter: (device = 3)
                           Rows Removed by Filter: 2
         ->  Index Scan using _hyper_1_1_chunk_ht_metrics_compressed_time_idx on _hyper_1_1_chunk (actual rows=1.00 loops=1)
               Filter: (device = 3)
               Rows Removed by Filter: 2
         ->  Custom Scan (ColumnarScan) on _hyper_1_2_chunk (actual rows=1.00 loops=1)
               ->  Sort (actual rows=1.00 loops=1)
                     Sort Key: compress_hyper_2_5_chunk._ts_meta_v2_first_time DESC, compress_hyper_2_5_chunk._ts_meta_v2_last_time DESC
                     Sort Method: quicksort 
                     ->  Seq Scan on compress_hyper_2_5_chunk (actual rows=1.00 loops=1)
                           Filter: (device = 3)
                           Rows Removed by Filter: 2
         ->  Index Scan using _hyper_1_2_chunk_ht_metrics_compressed_time_idx on _hyper_1_2_chunk (actual rows=1.00 loops=1)
               Filter: (device = 3)
               Rows Removed by Filter: 2
         ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk (actual rows=1.00 loops=1)
               ->  Sort (actual rows=1.00 loops=1)
                     Sort Key: compress_hyper_2_6_chunk._ts_meta_v2_first_time DESC, compress_hyper_2_6_chunk._ts_meta_v2_last_time DESC
                     Sort Method: quicksort 
                     ->  Seq Scan on compress_hyper_2_6_chunk (actual rows=1.00 loops=1)
                           Filter: (device = 3)
                           Rows Removed by Filter: 2
         ->  Index Scan using _hyper_1_3_chunk_ht_metrics_compressed_time_idx on _hyper_1_3_chunk (actual rows=1.00 loops=1)
               Filter: (device = 3)
               Rows Removed by Filter: 2

SELECT * FROM ht_metrics_compressed WHERE device = 3 ORDER BY device, time DESC LIMIT 1;
             time             | device | value 
------------------------------+--------+-------
 Fri Jan 17 16:00:00 2020 PST |      3 |   0.3

:PREFIX SELECT * FROM ht_metrics_compressed WHERE device = 3 ORDER BY time, device DESC LIMIT 1; -- this also uses the index and does not do sort on top
--- QUERY PLAN ---
 Limit (actual rows=1.00 loops=1)
   ->  Custom Scan (ChunkAppend) on ht_metrics_compressed (actual rows=1.00 loops=1)
         Order: ht_metrics_compressed."time"
         ->  Merge Append (actual rows=1.00 loops=1)
               Sort Key: ht_metrics_compressed."time"
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=1.00 loops=1)
                     ->  Sort (actual rows=1.00 loops=1)
                           Sort Key: compress_hyper_2_4_chunk._ts_meta_v2_first_time, compress_hyper_2_4_chunk._ts_meta_v2_last_time
                           Sort Method: quicksort 
                           ->  Seq Scan on compress_hyper_2_4_chunk (actual rows=1.00 loops=1)
                                 Filter: (device = 3)
                                 Rows Removed by Filter: 2
               ->  Index Scan Backward using _hyper_1_1_chunk_ht_metrics_compressed_time_idx on _hyper_1_1_chunk (actual rows=1.00 loops=1)
                     Filter: (device = 3)
         ->  Merge Append (never executed)
               Sort Key: ht_metrics_compressed."time"
               ->  Custom Scan (ColumnarScan) on _hyper_1_2_chunk (never executed)
                     ->  Sort (never executed)
                           Sort Key: compress_hyper_2_5_chunk._ts_meta_v2_first_time, compress_hyper_2_5_chunk._ts_meta_v2_last_time
                           ->  Seq Scan on compress_hyper_2_5_chunk (never executed)
                                 Filter: (device = 3)
               ->  Index Scan Backward using _hyper_1_2_chunk_ht_metrics_compressed_time_idx on _hyper_1_2_chunk (never executed)
                     Filter: (device = 3)
         ->  Merge Append (never executed)
               Sort Key: ht_metrics_compressed."time"
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk (never executed)
                     ->  Sort (never executed)
                           Sort Key: compress_hyper_2_6_chunk._ts_meta_v2_first_time, compress_hyper_2_6_chunk._ts_meta_v2_last_time
                           ->  Seq Scan on compress_hyper_2_6_chunk (never executed)
                                 Filter: (device = 3)
               ->  Index Scan Backward using _hyper_1_3_chunk_ht_metrics_compressed_time_idx on _hyper_1_3_chunk (never executed)
                     Filter: (device = 3)

SELECT * FROM ht_metrics_compressed WHERE device = 3 ORDER BY time, device DESC LIMIT 1;
             time             | device | value 
------------------------------+--------+-------
 Wed Jan 01 16:00:00 2020 PST |      3 |   0.3

-- not eligible for chunkAppend, but eligible for sort pushdown
:PREFIX SELECT * FROM ht_metrics_compressed ORDER BY device, time DESC LIMIT 1; -- with pushdown
--- QUERY PLAN ---
 Limit (actual rows=1.00 loops=1)
   ->  Merge Append (actual rows=1.00 loops=1)
         Sort Key: ht_metrics_compressed.device, ht_metrics_compressed."time" DESC
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=1.00 loops=1)
               ->  Sort (actual rows=1.00 loops=1)
                     Sort Key: compress_hyper_2_4_chunk.device, compress_hyper_2_4_chunk._ts_meta_v2_first_time DESC, compress_hyper_2_4_chunk._ts_meta_v2_last_time DESC
                     Sort Method: quicksort 
                     ->  Seq Scan on compress_hyper_2_4_chunk (actual rows=3.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_1_1_chunk.device, _hyper_1_1_chunk."time" DESC
               Sort Method: top-N heapsort 
               ->  Seq Scan on _hyper_1_1_chunk (actual rows=57.00 loops=1)
         ->  Custom Scan (ColumnarScan) on _hyper_1_2_chunk (actual rows=1.00 loops=1)
               ->  Sort (actual rows=1.00 loops=1)
                     Sort Key: compress_hyper_2_5_chunk.device, compress_hyper_2_5_chunk._ts_meta_v2_first_time DESC, compress_hyper_2_5_chunk._ts_meta_v2_last_time DESC
                     Sort Method: quicksort 
                     ->  Seq Scan on compress_hyper_2_5_chunk (actual rows=3.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_1_2_chunk.device, _hyper_1_2_chunk."time" DESC
               Sort Method: top-N heapsort 
               ->  Seq Scan on _hyper_1_2_chunk (actual rows=57.00 loops=1)
         ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk (actual rows=1.00 loops=1)
               ->  Sort (actual rows=1.00 loops=1)
                     Sort Key: compress_hyper_2_6_chunk.device, compress_hyper_2_6_chunk._ts_meta_v2_first_time DESC, compress_hyper_2_6_chunk._ts_meta_v2_last_time DESC
                     Sort Method: quicksort 
                     ->  Seq Scan on compress_hyper_2_6_chunk (actual rows=3.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_1_3_chunk.device, _hyper_1_3_chunk."time" DESC
               Sort Method: top-N heapsort 
               ->  Seq Scan on _hyper_1_3_chunk (actual rows=15.00 loops=1)

:PREFIX SELECT * FROM ht_metrics_compressed WHERE device IN (1,2,3) ORDER BY device, time DESC LIMIT 1; -- with pushdown
--- QUERY PLAN ---
 Limit (actual rows=1.00 loops=1)
   ->  Merge Append (actual rows=1.00 loops=1)
         Sort Key: ht_metrics_compressed.device, ht_metrics_compressed."time" DESC
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=1.00 loops=1)
               ->  Sort (actual rows=1.00 loops=1)
                     Sort Key: compress_hyper_2_4_chunk.device, compress_hyper_2_4_chunk._ts_meta_v2_first_time DESC, compress_hyper_2_4_chunk._ts_meta_v2_last_time DESC
                     Sort Method: quicksort 
                     ->  Seq Scan on compress_hyper_2_4_chunk (actual rows=3.00 loops=1)
                           Filter: (device = ANY ('{1,2,3}'::integer[]))
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_1_1_chunk.device, _hyper_1_1_chunk."time" DESC
               Sort Method: top-N heapsort 
               ->  Seq Scan on _hyper_1_1_chunk (actual rows=57.00 loops=1)
                     Filter: (device = ANY ('{1,2,3}'::integer[]))
         ->  Custom Scan (ColumnarScan) on _hyper_1_2_chunk (actual rows=1.00 loops=1)
               ->  Sort (actual rows=1.00 loops=1)
                     Sort Key: compress_hyper_2_5_chunk.device, compress_hyper_2_5_chunk._ts_meta_v2_first_time DESC, compress_hyper_2_5_chunk._ts_meta_v2_last_time DESC
                     Sort Method: quicksort 
                     ->  Seq Scan on compress_hyper_2_5_chunk (actual rows=3.00 loops=1)
                           Filter: (device = ANY ('{1,2,3}'::integer[]))
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_1_2_chunk.device, _hyper_1_2_chunk."time" DESC
               Sort Method: top-N heapsort 
               ->  Seq Scan on _hyper_1_2_chunk (actual rows=57.00 loops=1)
                     Filter: (device = ANY ('{1,2,3}'::integer[]))
         ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk (actual rows=1.00 loops=1)
               ->  Sort (actual rows=1.00 loops=1)
                     Sort Key: compress_hyper_2_6_chunk.device, compress_hyper_2_6_chunk._ts_meta_v2_first_time DESC, compress_hyper_2_6_chunk._ts_meta_v2_last_time DESC
                     Sort Method: quicksort 
                     ->  Seq Scan on compress_hyper_2_6_chunk (actual rows=3.00 loops=1)
                           Filter: (device = ANY ('{1,2,3}'::integer[]))
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_1_3_chunk.device, _hyper_1_3_chunk."time" DESC
               Sort Method: top-N heapsort 
               ->  Seq Scan on _hyper_1_3_chunk (actual rows=15.00 loops=1)
                     Filter: (device = ANY ('{1,2,3}'::integer[]))

-- Test direct ordered select from a single partially compressed chunk
select * from show_chunks('ht_metrics_compressed') chunk order by chunk limit 1 \gset
:PREFIX
SELECT * FROM :chunk ORDER BY device, time LIMIT 5;
--- QUERY PLAN ---
 Limit (actual rows=5.00 loops=1)
   ->  Merge Append (actual rows=5.00 loops=1)
         Sort Key: _hyper_1_1_chunk.device, _hyper_1_1_chunk."time"
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=3.00 loops=1)
               ->  Sort (actual rows=1.00 loops=1)
                     Sort Key: compress_hyper_2_4_chunk.device, compress_hyper_2_4_chunk._ts_meta_v2_first_time, compress_hyper_2_4_chunk._ts_meta_v2_last_time
                     Sort Method: quicksort 
                     ->  Seq Scan on compress_hyper_2_4_chunk (actual rows=3.00 loops=1)
         ->  Sort (actual rows=3.00 loops=1)
               Sort Key: _hyper_1_1_chunk.device, _hyper_1_1_chunk."time"
               Sort Method: top-N heapsort 
               ->  Seq Scan on _hyper_1_1_chunk (actual rows=57.00 loops=1)

SELECT * FROM :chunk ORDER BY device, time LIMIT 5;
             time             | device | value 
------------------------------+--------+-------
 Wed Jan 01 16:00:00 2020 PST |      1 |   0.1
 Wed Jan 01 16:00:00 2020 PST |      1 |   0.1
 Wed Jan 01 22:00:00 2020 PST |      1 |   0.1
 Thu Jan 02 01:00:00 2020 PST |      1 |   0.1
 Thu Jan 02 04:00:00 2020 PST |      1 |   0.1

:PREFIX
SELECT * FROM :chunk ORDER BY device DESC, time DESC LIMIT 5;
--- QUERY PLAN ---
 Limit (actual rows=5.00 loops=1)
   ->  Merge Append (actual rows=5.00 loops=1)
         Sort Key: _hyper_1_1_chunk.device DESC, _hyper_1_1_chunk."time" DESC
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=3.00 loops=1)
               ->  Sort (actual rows=1.00 loops=1)
                     Sort Key: compress_hyper_2_4_chunk.device DESC, compress_hyper_2_4_chunk._ts_meta_v2_first_time DESC, compress_hyper_2_4_chunk._ts_meta_v2_last_time DESC
                     Sort Method: quicksort 
                     ->  Seq Scan on compress_hyper_2_4_chunk (actual rows=3.00 loops=1)
         ->  Sort (actual rows=3.00 loops=1)
               Sort Key: _hyper_1_1_chunk.device DESC, _hyper_1_1_chunk."time" DESC
               Sort Method: top-N heapsort 
               ->  Seq Scan on _hyper_1_1_chunk (actual rows=57.00 loops=1)

SELECT * FROM :chunk ORDER BY device DESC, time DESC LIMIT 5;
             time             | device | value 
------------------------------+--------+-------
 Wed Jan 08 10:00:00 2020 PST |      3 |   0.3
 Wed Jan 08 10:00:00 2020 PST |      3 |   0.3
 Wed Jan 08 04:00:00 2020 PST |      3 |   0.3
 Wed Jan 08 01:00:00 2020 PST |      3 |   0.3
 Tue Jan 07 22:00:00 2020 PST |      3 |   0.3

CREATE TABLE test1 (
time timestamptz NOT NULL,
    x1 integer,
    x2 integer,
    x3 integer,
    x4 integer,
    x5 integer);
SELECT FROM create_hypertable('test1', 'time');
--

ALTER TABLE test1 SET (timescaledb.compress, timescaledb.compress_segmentby='x1, x2, x5', timescaledb.compress_orderby = 'time DESC, x3 ASC, x4 ASC');
INSERT INTO test1 (time, x1, x2, x3, x4, x5) values('2000-01-01 00:00:00-00', 1, 2, 1, 1, 0);
INSERT INTO test1 (time, x1, x2, x3, x4, x5) values('2000-01-01 01:00:00-00', 1, 3, 2, 2, 0);
INSERT INTO test1 (time, x1, x2, x3, x4, x5) values('2000-01-01 02:00:00-00', 2, 1, 3, 3, 0);
INSERT INTO test1 (time, x1, x2, x3, x4, x5) values('2000-01-01 03:00:00-00', 1, 2, 4, 4, 0);
-- Insert some dummy rows to inflate the cost estimates to get it to actually
-- use the MergeAppend. We will filter them out using the "x4 % 11 != 0" which
-- thwarts the normal selectivity estimation.
INSERT INTO test1 select '2000-01-01 04:00:00-00', /* x1 = */ 0, /* x2 = */ 0,
    /* x3 = */ 0, /* x4 = */ 11 * (11 + x), 0 from generate_series(1, 10000) x;
SELECT compress_chunk(i) FROM show_chunks('test1') i;
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_3_7_chunk

-- make all the chunks partially compressed
INSERT INTO test1 (time, x1, x2, x3, x4, x5) values('2000-01-01 02:01:00-00', 10, 20, 30, 40 ,50);
ANALYZE test1;
-- tests that require resorting (pushdown below decompressChunk node cannot happen)
-- requires resorting, no pushdown can happen
:PREFIX
SELECT * FROM test1 WHERE x4 % 11 != 0 ORDER BY time DESC LIMIT 10;
--- QUERY PLAN ---
 Limit (actual rows=5.00 loops=1)
   ->  Custom Scan (ChunkAppend) on test1 (actual rows=5.00 loops=1)
         Order: test1."time" DESC
         ->  Merge Append (actual rows=5.00 loops=1)
               Sort Key: test1."time" DESC
               ->  Sort (actual rows=4.00 loops=1)
                     Sort Key: _hyper_3_7_chunk."time" DESC
                     Sort Method: quicksort 
                     ->  Custom Scan (ColumnarScan) on _hyper_3_7_chunk (actual rows=4.00 loops=1)
                           Filter: ((x4 % 11) <> 0)
                           Rows Removed by Filter: 10000
                           ->  Seq Scan on compress_hyper_4_8_chunk (actual rows=13.00 loops=1)
               ->  Index Scan using _hyper_3_7_chunk_test1_time_idx on _hyper_3_7_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)

-- requires resorting
:PREFIX
SELECT * FROM test1 WHERE x4 % 11 != 0 ORDER BY time DESC NULLS FIRST, x3 ASC NULLS LAST LIMIT 10;
--- QUERY PLAN ---
 Limit (actual rows=5.00 loops=1)
   ->  Custom Scan (ChunkAppend) on test1 (actual rows=5.00 loops=1)
         Order: test1."time" DESC, test1.x3
         ->  Merge Append (actual rows=5.00 loops=1)
               Sort Key: test1."time" DESC, test1.x3
               ->  Sort (actual rows=4.00 loops=1)
                     Sort Key: _hyper_3_7_chunk."time" DESC, _hyper_3_7_chunk.x3
                     Sort Method: quicksort 
                     ->  Custom Scan (ColumnarScan) on _hyper_3_7_chunk (actual rows=4.00 loops=1)
                           Filter: ((x4 % 11) <> 0)
                           Rows Removed by Filter: 10000
                           ->  Seq Scan on compress_hyper_4_8_chunk (actual rows=13.00 loops=1)
               ->  Sort (actual rows=1.00 loops=1)
                     Sort Key: _hyper_3_7_chunk."time" DESC, _hyper_3_7_chunk.x3
                     Sort Method: quicksort 
                     ->  Seq Scan on _hyper_3_7_chunk (actual rows=1.00 loops=1)
                           Filter: ((x4 % 11) <> 0)

-- all these require resorting, no pushdown can happen
:PREFIX
SELECT * FROM test1 WHERE x4 % 11 != 0 ORDER BY time DESC NULLS FIRST, x3 ASC NULLS LAST, x4 ASC NULLS LAST LIMIT 10;
--- QUERY PLAN ---
 Limit (actual rows=5.00 loops=1)
   ->  Custom Scan (ChunkAppend) on test1 (actual rows=5.00 loops=1)
         Order: test1."time" DESC, test1.x3, test1.x4
         ->  Merge Append (actual rows=5.00 loops=1)
               Sort Key: test1."time" DESC, test1.x3, test1.x4
               ->  Sort (actual rows=4.00 loops=1)
                     Sort Key: _hyper_3_7_chunk."time" DESC, _hyper_3_7_chunk.x3, _hyper_3_7_chunk.x4
                     Sort Method: quicksort 
                     ->  Custom Scan (ColumnarScan) on _hyper_3_7_chunk (actual rows=4.00 loops=1)
                           Filter: ((x4 % 11) <> 0)
                           Rows Removed by Filter: 10000
                           ->  Seq Scan on compress_hyper_4_8_chunk (actual rows=13.00 loops=1)
               ->  Sort (actual rows=1.00 loops=1)
                     Sort Key: _hyper_3_7_chunk."time" DESC, _hyper_3_7_chunk.x3, _hyper_3_7_chunk.x4
                     Sort Method: quicksort 
                     ->  Seq Scan on _hyper_3_7_chunk (actual rows=1.00 loops=1)
                           Filter: ((x4 % 11) <> 0)

:PREFIX
SELECT * FROM test1 WHERE x4 % 11 != 0 ORDER BY time DESC NULLS FIRST, x3 ASC NULLS LAST, x4 DESC NULLS FIRST LIMIT 10;
--- QUERY PLAN ---
 Limit (actual rows=5.00 loops=1)
   ->  Custom Scan (ChunkAppend) on test1 (actual rows=5.00 loops=1)
         Order: test1."time" DESC, test1.x3, test1.x4 DESC
         ->  Merge Append (actual rows=5.00 loops=1)
               Sort Key: test1."time" DESC, test1.x3, test1.x4 DESC
               ->  Sort (actual rows=4.00 loops=1)
                     Sort Key: _hyper_3_7_chunk."time" DESC, _hyper_3_7_chunk.x3, _hyper_3_7_chunk.x4 DESC
                     Sort Method: quicksort 
                     ->  Custom Scan (ColumnarScan) on _hyper_3_7_chunk (actual rows=4.00 loops=1)
                           Filter: ((x4 % 11) <> 0)
                           Rows Removed by Filter: 10000
                           ->  Seq Scan on compress_hyper_4_8_chunk (actual rows=13.00 loops=1)
               ->  Sort (actual rows=1.00 loops=1)
                     Sort Key: _hyper_3_7_chunk."time" DESC, _hyper_3_7_chunk.x3, _hyper_3_7_chunk.x4 DESC
                     Sort Method: quicksort 
                     ->  Seq Scan on _hyper_3_7_chunk (actual rows=1.00 loops=1)
                           Filter: ((x4 % 11) <> 0)

:PREFIX
SELECT * FROM test1 WHERE x4 % 11 != 0 ORDER BY time ASC NULLS LAST LIMIT 10;
--- QUERY PLAN ---
 Limit (actual rows=5.00 loops=1)
   ->  Custom Scan (ChunkAppend) on test1 (actual rows=5.00 loops=1)
         Order: test1."time"
         ->  Merge Append (actual rows=5.00 loops=1)
               Sort Key: test1."time"
               ->  Sort (actual rows=4.00 loops=1)
                     Sort Key: _hyper_3_7_chunk."time"
                     Sort Method: quicksort 
                     ->  Custom Scan (ColumnarScan) on _hyper_3_7_chunk (actual rows=4.00 loops=1)
                           Filter: ((x4 % 11) <> 0)
                           Rows Removed by Filter: 10000
                           ->  Seq Scan on compress_hyper_4_8_chunk (actual rows=13.00 loops=1)
               ->  Index Scan Backward using _hyper_3_7_chunk_test1_time_idx on _hyper_3_7_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)

:PREFIX
SELECT * FROM test1 WHERE x4 % 11 != 0 ORDER BY time ASC NULLS LAST, x3 DESC NULLS FIRST LIMIT 10;
--- QUERY PLAN ---
 Limit (actual rows=5.00 loops=1)
   ->  Custom Scan (ChunkAppend) on test1 (actual rows=5.00 loops=1)
         Order: test1."time", test1.x3 DESC
         ->  Merge Append (actual rows=5.00 loops=1)
               Sort Key: test1."time", test1.x3 DESC
               ->  Sort (actual rows=4.00 loops=1)
                     Sort Key: _hyper_3_7_chunk."time", _hyper_3_7_chunk.x3 DESC
                     Sort Method: quicksort 
                     ->  Custom Scan (ColumnarScan) on _hyper_3_7_chunk (actual rows=4.00 loops=1)
                           Filter: ((x4 % 11) <> 0)
                           Rows Removed by Filter: 10000
                           ->  Seq Scan on compress_hyper_4_8_chunk (actual rows=13.00 loops=1)
               ->  Sort (actual rows=1.00 loops=1)
                     Sort Key: _hyper_3_7_chunk."time", _hyper_3_7_chunk.x3 DESC
                     Sort Method: quicksort 
                     ->  Seq Scan on _hyper_3_7_chunk (actual rows=1.00 loops=1)
                           Filter: ((x4 % 11) <> 0)

:PREFIX
SELECT * FROM test1 WHERE x4 % 11 != 0 ORDER BY time ASC NULLS LAST, x3 DESC NULLS FIRST, x4 DESC NULLS FIRST LIMIT 10;
--- QUERY PLAN ---
 Limit (actual rows=5.00 loops=1)
   ->  Custom Scan (ChunkAppend) on test1 (actual rows=5.00 loops=1)
         Order: test1."time", test1.x3 DESC, test1.x4 DESC
         ->  Merge Append (actual rows=5.00 loops=1)
               Sort Key: test1."time", test1.x3 DESC, test1.x4 DESC
               ->  Sort (actual rows=4.00 loops=1)
                     Sort Key: _hyper_3_7_chunk."time", _hyper_3_7_chunk.x3 DESC, _hyper_3_7_chunk.x4 DESC
                     Sort Method: quicksort 
                     ->  Custom Scan (ColumnarScan) on _hyper_3_7_chunk (actual rows=4.00 loops=1)
                           Filter: ((x4 % 11) <> 0)
                           Rows Removed by Filter: 10000
                           ->  Seq Scan on compress_hyper_4_8_chunk (actual rows=13.00 loops=1)
               ->  Sort (actual rows=1.00 loops=1)
                     Sort Key: _hyper_3_7_chunk."time", _hyper_3_7_chunk.x3 DESC, _hyper_3_7_chunk.x4 DESC
                     Sort Method: quicksort 
                     ->  Seq Scan on _hyper_3_7_chunk (actual rows=1.00 loops=1)
                           Filter: ((x4 % 11) <> 0)

:PREFIX
SELECT * FROM test1 WHERE x4 % 11 != 0 ORDER BY time ASC NULLS FIRST, x3 DESC NULLS LAST, x4 ASC LIMIT 10;
--- QUERY PLAN ---
 Limit (actual rows=5.00 loops=1)
   ->  Custom Scan (ChunkAppend) on test1 (actual rows=5.00 loops=1)
         Order: test1."time" NULLS FIRST, test1.x3 DESC NULLS LAST, test1.x4
         ->  Merge Append (actual rows=5.00 loops=1)
               Sort Key: test1."time" NULLS FIRST, test1.x3 DESC NULLS LAST, test1.x4
               ->  Sort (actual rows=4.00 loops=1)
                     Sort Key: _hyper_3_7_chunk."time" NULLS FIRST, _hyper_3_7_chunk.x3 DESC NULLS LAST, _hyper_3_7_chunk.x4
                     Sort Method: quicksort 
                     ->  Custom Scan (ColumnarScan) on _hyper_3_7_chunk (actual rows=4.00 loops=1)
                           Filter: ((x4 % 11) <> 0)
                           Rows Removed by Filter: 10000
                           ->  Seq Scan on compress_hyper_4_8_chunk (actual rows=13.00 loops=1)
               ->  Sort (actual rows=1.00 loops=1)
                     Sort Key: _hyper_3_7_chunk."time" NULLS FIRST, _hyper_3_7_chunk.x3 DESC NULLS LAST, _hyper_3_7_chunk.x4
                     Sort Method: quicksort 
                     ->  Seq Scan on _hyper_3_7_chunk (actual rows=1.00 loops=1)
                           Filter: ((x4 % 11) <> 0)

set enable_hashagg to off; -- different on PG13
:PREFIX
SELECT x1, x2, max(time) FROM (SELECT * FROM test1 WHERE x4 % 11 != 0 ORDER BY time, x1, x2 LIMIT 10) t
GROUP BY x1, x2, time ORDER BY time limit 10;
--- QUERY PLAN ---
 Limit (actual rows=5.00 loops=1)
   ->  GroupAggregate (actual rows=5.00 loops=1)
         Group Key: test1."time", test1.x1, test1.x2
         ->  Limit (actual rows=5.00 loops=1)
               ->  Result (actual rows=5.00 loops=1)
                     ->  Custom Scan (ChunkAppend) on test1 (actual rows=5.00 loops=1)
                           Order: test1."time", test1.x1, test1.x2
                           ->  Merge Append (actual rows=5.00 loops=1)
                                 Sort Key: test1."time", test1.x1, test1.x2
                                 ->  Sort (actual rows=4.00 loops=1)
                                       Sort Key: _hyper_3_7_chunk."time", _hyper_3_7_chunk.x1, _hyper_3_7_chunk.x2
                                       Sort Method: quicksort 
                                       ->  Custom Scan (ColumnarScan) on _hyper_3_7_chunk (actual rows=4.00 loops=1)
                                             Filter: ((x4 % 11) <> 0)
                                             Rows Removed by Filter: 10000
                                             ->  Seq Scan on compress_hyper_4_8_chunk (actual rows=13.00 loops=1)
                                 ->  Sort (actual rows=1.00 loops=1)
                                       Sort Key: _hyper_3_7_chunk."time", _hyper_3_7_chunk.x1, _hyper_3_7_chunk.x2
                                       Sort Method: quicksort 
                                       ->  Seq Scan on _hyper_3_7_chunk (actual rows=1.00 loops=1)
                                             Filter: ((x4 % 11) <> 0)

reset enable_hashagg;
:PREFIX
SELECT * FROM test1 WHERE x4 % 11 != 0 ORDER BY x1, x2, x5, x4, time LIMIT 10;
--- QUERY PLAN ---
 Limit (actual rows=5.00 loops=1)
   ->  Merge Append (actual rows=5.00 loops=1)
         Sort Key: test1.x1, test1.x2, test1.x5, test1.x4, test1."time"
         ->  Sort (actual rows=4.00 loops=1)
               Sort Key: _hyper_3_7_chunk.x1, _hyper_3_7_chunk.x2, _hyper_3_7_chunk.x5, _hyper_3_7_chunk.x4, _hyper_3_7_chunk."time"
               Sort Method: quicksort 
               ->  Custom Scan (ColumnarScan) on _hyper_3_7_chunk (actual rows=4.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
                     Rows Removed by Filter: 10000
                     ->  Seq Scan on compress_hyper_4_8_chunk (actual rows=13.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_3_7_chunk.x1, _hyper_3_7_chunk.x2, _hyper_3_7_chunk.x5, _hyper_3_7_chunk.x4, _hyper_3_7_chunk."time"
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_3_7_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)

:PREFIX
SELECT * FROM test1 WHERE x4 % 11 != 0 ORDER BY x1, x2, x5, time, x4 LIMIT 10;
--- QUERY PLAN ---
 Limit (actual rows=5.00 loops=1)
   ->  Merge Append (actual rows=5.00 loops=1)
         Sort Key: test1.x1, test1.x2, test1.x5, test1."time", test1.x4
         ->  Sort (actual rows=4.00 loops=1)
               Sort Key: _hyper_3_7_chunk.x1, _hyper_3_7_chunk.x2, _hyper_3_7_chunk.x5, _hyper_3_7_chunk."time", _hyper_3_7_chunk.x4
               Sort Method: quicksort 
               ->  Custom Scan (ColumnarScan) on _hyper_3_7_chunk (actual rows=4.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
                     Rows Removed by Filter: 10000
                     ->  Seq Scan on compress_hyper_4_8_chunk (actual rows=13.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_3_7_chunk.x1, _hyper_3_7_chunk.x2, _hyper_3_7_chunk.x5, _hyper_3_7_chunk."time", _hyper_3_7_chunk.x4
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_3_7_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)

:PREFIX
SELECT * FROM test1 WHERE x4 % 11 != 0 ORDER BY x1, x2, x5, time, x3 LIMIT 10;
--- QUERY PLAN ---
 Limit (actual rows=5.00 loops=1)
   ->  Merge Append (actual rows=5.00 loops=1)
         Sort Key: test1.x1, test1.x2, test1.x5, test1."time", test1.x3
         ->  Sort (actual rows=4.00 loops=1)
               Sort Key: _hyper_3_7_chunk.x1, _hyper_3_7_chunk.x2, _hyper_3_7_chunk.x5, _hyper_3_7_chunk."time", _hyper_3_7_chunk.x3
               Sort Method: quicksort 
               ->  Custom Scan (ColumnarScan) on _hyper_3_7_chunk (actual rows=4.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
                     Rows Removed by Filter: 10000
                     ->  Seq Scan on compress_hyper_4_8_chunk (actual rows=13.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_3_7_chunk.x1, _hyper_3_7_chunk.x2, _hyper_3_7_chunk.x5, _hyper_3_7_chunk."time", _hyper_3_7_chunk.x3
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_3_7_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)

:PREFIX
SELECT * FROM test1 WHERE x4 % 11 != 0 ORDER BY x1, x2, x5, time, x3, x4 LIMIT 10;
--- QUERY PLAN ---
 Limit (actual rows=5.00 loops=1)
   ->  Merge Append (actual rows=5.00 loops=1)
         Sort Key: test1.x1, test1.x2, test1.x5, test1."time", test1.x3, test1.x4
         ->  Sort (actual rows=4.00 loops=1)
               Sort Key: _hyper_3_7_chunk.x1, _hyper_3_7_chunk.x2, _hyper_3_7_chunk.x5, _hyper_3_7_chunk."time", _hyper_3_7_chunk.x3, _hyper_3_7_chunk.x4
               Sort Method: quicksort 
               ->  Custom Scan (ColumnarScan) on _hyper_3_7_chunk (actual rows=4.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
                     Rows Removed by Filter: 10000
                     ->  Seq Scan on compress_hyper_4_8_chunk (actual rows=13.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_3_7_chunk.x1, _hyper_3_7_chunk.x2, _hyper_3_7_chunk.x5, _hyper_3_7_chunk."time", _hyper_3_7_chunk.x3, _hyper_3_7_chunk.x4
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_3_7_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)

:PREFIX
SELECT * FROM test1 WHERE x4 % 11 != 0 ORDER BY x1, x2, x5, time, x4 DESC LIMIT 10; -- no pushdown because orderby does not match
--- QUERY PLAN ---
 Limit (actual rows=5.00 loops=1)
   ->  Merge Append (actual rows=5.00 loops=1)
         Sort Key: test1.x1, test1.x2, test1.x5, test1."time", test1.x4 DESC
         ->  Sort (actual rows=4.00 loops=1)
               Sort Key: _hyper_3_7_chunk.x1, _hyper_3_7_chunk.x2, _hyper_3_7_chunk.x5, _hyper_3_7_chunk."time", _hyper_3_7_chunk.x4 DESC
               Sort Method: quicksort 
               ->  Custom Scan (ColumnarScan) on _hyper_3_7_chunk (actual rows=4.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
                     Rows Removed by Filter: 10000
                     ->  Seq Scan on compress_hyper_4_8_chunk (actual rows=13.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_3_7_chunk.x1, _hyper_3_7_chunk.x2, _hyper_3_7_chunk.x5, _hyper_3_7_chunk."time", _hyper_3_7_chunk.x4 DESC
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_3_7_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)

-- queries with pushdown
:PREFIX
SELECT * FROM test1 WHERE x4 % 11 != 0 ORDER BY x1, x2, x5, time LIMIT 10;
--- QUERY PLAN ---
 Limit (actual rows=5.00 loops=1)
   ->  Merge Append (actual rows=5.00 loops=1)
         Sort Key: test1.x1, test1.x2, test1.x5, test1."time"
         ->  Custom Scan (ColumnarScan) on _hyper_3_7_chunk (actual rows=4.00 loops=1)
               Filter: ((x4 % 11) <> 0)
               Rows Removed by Filter: 10000
               ->  Sort (actual rows=13.00 loops=1)
                     Sort Key: compress_hyper_4_8_chunk.x1, compress_hyper_4_8_chunk.x2, compress_hyper_4_8_chunk.x5, compress_hyper_4_8_chunk._ts_meta_v2_last_time, compress_hyper_4_8_chunk._ts_meta_v2_first_time
                     Sort Method: quicksort 
                     ->  Seq Scan on compress_hyper_4_8_chunk (actual rows=13.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_3_7_chunk.x1, _hyper_3_7_chunk.x2, _hyper_3_7_chunk.x5, _hyper_3_7_chunk."time"
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_3_7_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)

:PREFIX
SELECT * FROM test1 WHERE x4 % 11 != 0 ORDER BY x1, x2, x5, time DESC, x3 ASC, x4 ASC LIMIT 10; -- pushdown
--- QUERY PLAN ---
 Limit (actual rows=5.00 loops=1)
   ->  Merge Append (actual rows=5.00 loops=1)
         Sort Key: test1.x1, test1.x2, test1.x5, test1."time" DESC, test1.x3, test1.x4
         ->  Custom Scan (ColumnarScan) on _hyper_3_7_chunk (actual rows=4.00 loops=1)
               Filter: ((x4 % 11) <> 0)
               Rows Removed by Filter: 10000
               ->  Index Scan using compress_hyper_4_8_chunk_x1_x2_x5__ts_meta_v2_last_time__ts_idx on compress_hyper_4_8_chunk (actual rows=13.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_3_7_chunk.x1, _hyper_3_7_chunk.x2, _hyper_3_7_chunk.x5, _hyper_3_7_chunk."time" DESC, _hyper_3_7_chunk.x3, _hyper_3_7_chunk.x4
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_3_7_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)

:PREFIX
SELECT * FROM test1 WHERE x4 % 11 != 0 ORDER BY x1, x2, x5, time ASC, x3 DESC, x4 DESC LIMIT 10; -- pushdown
--- QUERY PLAN ---
 Limit (actual rows=5.00 loops=1)
   ->  Merge Append (actual rows=5.00 loops=1)
         Sort Key: test1.x1, test1.x2, test1.x5, test1."time", test1.x3 DESC, test1.x4 DESC
         ->  Custom Scan (ColumnarScan) on _hyper_3_7_chunk (actual rows=4.00 loops=1)
               Filter: ((x4 % 11) <> 0)
               Rows Removed by Filter: 10000
               ->  Sort (actual rows=13.00 loops=1)
                     Sort Key: compress_hyper_4_8_chunk.x1, compress_hyper_4_8_chunk.x2, compress_hyper_4_8_chunk.x5, compress_hyper_4_8_chunk._ts_meta_v2_last_time, compress_hyper_4_8_chunk._ts_meta_v2_first_time, compress_hyper_4_8_chunk._ts_meta_v2_first_x3 DESC, compress_hyper_4_8_chunk._ts_meta_v2_last_x3 DESC, compress_hyper_4_8_chunk._ts_meta_v2_first_x4 DESC, compress_hyper_4_8_chunk._ts_meta_v2_last_x4 DESC
                     Sort Method: quicksort 
                     ->  Seq Scan on compress_hyper_4_8_chunk (actual rows=13.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_3_7_chunk.x1, _hyper_3_7_chunk.x2, _hyper_3_7_chunk.x5, _hyper_3_7_chunk."time", _hyper_3_7_chunk.x3 DESC, _hyper_3_7_chunk.x4 DESC
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_3_7_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)

:PREFIX
SELECT * FROM test1 WHERE x4 % 11 != 0 ORDER BY x1, x2, x5, time, x3 DESC LIMIT 10;
--- QUERY PLAN ---
 Limit (actual rows=5.00 loops=1)
   ->  Merge Append (actual rows=5.00 loops=1)
         Sort Key: test1.x1, test1.x2, test1.x5, test1."time", test1.x3 DESC
         ->  Custom Scan (ColumnarScan) on _hyper_3_7_chunk (actual rows=4.00 loops=1)
               Filter: ((x4 % 11) <> 0)
               Rows Removed by Filter: 10000
               ->  Sort (actual rows=13.00 loops=1)
                     Sort Key: compress_hyper_4_8_chunk.x1, compress_hyper_4_8_chunk.x2, compress_hyper_4_8_chunk.x5, compress_hyper_4_8_chunk._ts_meta_v2_last_time, compress_hyper_4_8_chunk._ts_meta_v2_first_time, compress_hyper_4_8_chunk._ts_meta_v2_first_x3 DESC, compress_hyper_4_8_chunk._ts_meta_v2_last_x3 DESC
                     Sort Method: quicksort 
                     ->  Seq Scan on compress_hyper_4_8_chunk (actual rows=13.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_3_7_chunk.x1, _hyper_3_7_chunk.x2, _hyper_3_7_chunk.x5, _hyper_3_7_chunk."time", _hyper_3_7_chunk.x3 DESC
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_3_7_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)

-- test append with join column in orderby
-- #6975
CREATE TABLE join_table (
	x1 integer,
	y1 float);
INSERT INTO join_table VALUES (1, 1.0), (2,2.0);
:PREFIX
SELECT * FROM test1 t1 JOIN join_table jt ON t1.x1 = jt.x1
ORDER BY t1.x1, jt.y1;
--- QUERY PLAN ---
 Sort (actual rows=4.00 loops=1)
   Sort Key: t1.x1, jt.y1
   Sort Method: quicksort 
   ->  Hash Join (actual rows=4.00 loops=1)
         Hash Cond: (jt.x1 = t1.x1)
         ->  Seq Scan on join_table jt (actual rows=2.00 loops=1)
         ->  Hash (actual rows=10005.00 loops=1)
               ->  Append (actual rows=10005.00 loops=1)
                     ->  Custom Scan (ColumnarScan) on _hyper_3_7_chunk t1_1 (actual rows=10004.00 loops=1)
                           ->  Seq Scan on compress_hyper_4_8_chunk (actual rows=13.00 loops=1)
                     ->  Seq Scan on _hyper_3_7_chunk t1_1 (actual rows=1.00 loops=1)

---------------------------------------------------------------------------
-- test queries without ordered append, but still eligible for sort pushdown
---------------------------------------------------------------------------
CREATE TABLE test2 (
time timestamptz NOT NULL,
    x1 integer,
    x2 integer,
    x3 integer,
    x4 integer,
    x5 integer);
SELECT FROM create_hypertable('test2', 'time');
--

ALTER TABLE test2 SET (timescaledb.compress, timescaledb.compress_segmentby='x1, x2, x5', timescaledb.compress_orderby = 'x3 ASC, x4 ASC');
INSERT INTO test2 (time, x1, x2, x3, x4, x5) values('2000-01-01 00:00:00-00', 1, 2, 1, 1, 0);
INSERT INTO test2 (time, x1, x2, x3, x4, x5) values('2000-01-01 01:00:00-00', 1, 3, 2, 2, 0);
INSERT INTO test2 (time, x1, x2, x3, x4, x5) values('2000-01-01 02:00:00-00', 2, 1, 3, 3, 0);
INSERT INTO test2 (time, x1, x2, x3, x4, x5) values('2000-01-01 03:00:00-00', 1, 2, 4, 4, 0);
-- chunk 2
INSERT INTO test2 (time, x1, x2, x3, x4, x5) values('2000-01-10 00:00:00-00', 1, 2, 5, 5, 0);
INSERT INTO test2 (time, x1, x2, x3, x4, x5) values('2000-01-10 01:00:00-00', 1, 3, 6, 6, 0);
INSERT INTO test2 (time, x1, x2, x3, x4, x5) values('2000-01-10 02:00:00-00', 2, 1, 7, 7, 0);
INSERT INTO test2 (time, x1, x2, x3, x4, x5) values('2000-01-10 03:00:00-00', 1, 2, 8, 8, 0);
-- Insert some dummy rows to inflate the cost estimates to get it to actually
-- use the MergeAppend. We will filter them out using the "x4 % 11 != 0" which
-- thwarts the normal selectivity estimation.
INSERT INTO test2 select unnest(array['2000-01-01 04:00:00-00', '2000-01-10 04:00:00-00']::timestamptz[]),
    /* x1 = */ 0, /* x2 = */ 0,
    /* x3 = */ 0, /* x4 = */ 11 * (11 + x), 0 from generate_series(1, 10000) x;
SELECT compress_chunk(i) FROM show_chunks('test2') i;
             compress_chunk              
-----------------------------------------
 _timescaledb_internal._hyper_5_9_chunk
 _timescaledb_internal._hyper_5_10_chunk

-- make them partially compressed
INSERT INTO test2 (time, x1, x2, x3, x4, x5) values('2000-01-01 00:02:01-00', 1, 2,  9,  9, 0);
INSERT INTO test2 (time, x1, x2, x3, x4, x5) values('2000-01-10 00:02:01-00', 1, 2, 10, 10, 0);
ANALYZE test2;
set enable_indexscan = off;
-- queries where sort is pushed down
:PREFIX SELECT * FROM test2 WHERE x4 % 11 != 0 ORDER BY x1, x2, x5, x3 LIMIT 10;
--- QUERY PLAN ---
 Limit (actual rows=10.00 loops=1)
   ->  Merge Append (actual rows=10.00 loops=1)
         Sort Key: test2.x1, test2.x2, test2.x5, test2.x3
         ->  Custom Scan (ColumnarScan) on _hyper_5_9_chunk (actual rows=4.00 loops=1)
               Filter: ((x4 % 11) <> 0)
               Rows Removed by Filter: 10000
               ->  Sort (actual rows=13.00 loops=1)
                     Sort Key: compress_hyper_6_11_chunk.x1, compress_hyper_6_11_chunk.x2, compress_hyper_6_11_chunk.x5, compress_hyper_6_11_chunk._ts_meta_v2_first_x3, compress_hyper_6_11_chunk._ts_meta_v2_last_x3
                     Sort Method: quicksort 
                     ->  Seq Scan on compress_hyper_6_11_chunk (actual rows=13.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_5_9_chunk.x1, _hyper_5_9_chunk.x2, _hyper_5_9_chunk.x5, _hyper_5_9_chunk.x3
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_5_9_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
         ->  Custom Scan (ColumnarScan) on _hyper_5_10_chunk (actual rows=4.00 loops=1)
               Filter: ((x4 % 11) <> 0)
               Rows Removed by Filter: 10000
               ->  Sort (actual rows=13.00 loops=1)
                     Sort Key: compress_hyper_6_12_chunk.x1, compress_hyper_6_12_chunk.x2, compress_hyper_6_12_chunk.x5, compress_hyper_6_12_chunk._ts_meta_v2_first_x3, compress_hyper_6_12_chunk._ts_meta_v2_last_x3
                     Sort Method: quicksort 
                     ->  Seq Scan on compress_hyper_6_12_chunk (actual rows=13.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_5_10_chunk.x1, _hyper_5_10_chunk.x2, _hyper_5_10_chunk.x5, _hyper_5_10_chunk.x3
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_5_10_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)

SELECT * FROM test2 WHERE x4 % 11 != 0 ORDER BY x1, x2, x5, x3 LIMIT 10;
             time             | x1 | x2 | x3 | x4 | x5 
------------------------------+----+----+----+----+----
 Fri Dec 31 16:00:00 1999 PST |  1 |  2 |  1 |  1 |  0
 Fri Dec 31 19:00:00 1999 PST |  1 |  2 |  4 |  4 |  0
 Sun Jan 09 16:00:00 2000 PST |  1 |  2 |  5 |  5 |  0
 Sun Jan 09 19:00:00 2000 PST |  1 |  2 |  8 |  8 |  0
 Fri Dec 31 16:02:01 1999 PST |  1 |  2 |  9 |  9 |  0
 Sun Jan 09 16:02:01 2000 PST |  1 |  2 | 10 | 10 |  0
 Fri Dec 31 17:00:00 1999 PST |  1 |  3 |  2 |  2 |  0
 Sun Jan 09 17:00:00 2000 PST |  1 |  3 |  6 |  6 |  0
 Fri Dec 31 18:00:00 1999 PST |  2 |  1 |  3 |  3 |  0
 Sun Jan 09 18:00:00 2000 PST |  2 |  1 |  7 |  7 |  0

:PREFIX SELECT * FROM test2 WHERE x4 % 11 != 0 ORDER BY x1, x2, x5, x3, x4 LIMIT 10;
--- QUERY PLAN ---
 Limit (actual rows=10.00 loops=1)
   ->  Merge Append (actual rows=10.00 loops=1)
         Sort Key: test2.x1, test2.x2, test2.x5, test2.x3, test2.x4
         ->  Custom Scan (ColumnarScan) on _hyper_5_9_chunk (actual rows=4.00 loops=1)
               Filter: ((x4 % 11) <> 0)
               Rows Removed by Filter: 10000
               ->  Sort (actual rows=13.00 loops=1)
                     Sort Key: compress_hyper_6_11_chunk.x1, compress_hyper_6_11_chunk.x2, compress_hyper_6_11_chunk.x5, compress_hyper_6_11_chunk._ts_meta_v2_first_x3, compress_hyper_6_11_chunk._ts_meta_v2_last_x3, compress_hyper_6_11_chunk._ts_meta_v2_first_x4, compress_hyper_6_11_chunk._ts_meta_v2_last_x4
                     Sort Method: quicksort 
                     ->  Seq Scan on compress_hyper_6_11_chunk (actual rows=13.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_5_9_chunk.x1, _hyper_5_9_chunk.x2, _hyper_5_9_chunk.x5, _hyper_5_9_chunk.x3, _hyper_5_9_chunk.x4
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_5_9_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
         ->  Custom Scan (ColumnarScan) on _hyper_5_10_chunk (actual rows=4.00 loops=1)
               Filter: ((x4 % 11) <> 0)
               Rows Removed by Filter: 10000
               ->  Sort (actual rows=13.00 loops=1)
                     Sort Key: compress_hyper_6_12_chunk.x1, compress_hyper_6_12_chunk.x2, compress_hyper_6_12_chunk.x5, compress_hyper_6_12_chunk._ts_meta_v2_first_x3, compress_hyper_6_12_chunk._ts_meta_v2_last_x3, compress_hyper_6_12_chunk._ts_meta_v2_first_x4, compress_hyper_6_12_chunk._ts_meta_v2_last_x4
                     Sort Method: quicksort 
                     ->  Seq Scan on compress_hyper_6_12_chunk (actual rows=13.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_5_10_chunk.x1, _hyper_5_10_chunk.x2, _hyper_5_10_chunk.x5, _hyper_5_10_chunk.x3, _hyper_5_10_chunk.x4
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_5_10_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)

SELECT * FROM test2 WHERE x4 % 11 != 0 ORDER BY x1, x2, x5, x3, x4 LIMIT 10;
             time             | x1 | x2 | x3 | x4 | x5 
------------------------------+----+----+----+----+----
 Fri Dec 31 16:00:00 1999 PST |  1 |  2 |  1 |  1 |  0
 Fri Dec 31 19:00:00 1999 PST |  1 |  2 |  4 |  4 |  0
 Sun Jan 09 16:00:00 2000 PST |  1 |  2 |  5 |  5 |  0
 Sun Jan 09 19:00:00 2000 PST |  1 |  2 |  8 |  8 |  0
 Fri Dec 31 16:02:01 1999 PST |  1 |  2 |  9 |  9 |  0
 Sun Jan 09 16:02:01 2000 PST |  1 |  2 | 10 | 10 |  0
 Fri Dec 31 17:00:00 1999 PST |  1 |  3 |  2 |  2 |  0
 Sun Jan 09 17:00:00 2000 PST |  1 |  3 |  6 |  6 |  0
 Fri Dec 31 18:00:00 1999 PST |  2 |  1 |  3 |  3 |  0
 Sun Jan 09 18:00:00 2000 PST |  2 |  1 |  7 |  7 |  0

-- queries where sort is not pushed down
:PREFIX SELECT * FROM test2 WHERE x4 % 11 != 0 ORDER BY x1, x2, x3 LIMIT 10;
--- QUERY PLAN ---
 Limit (actual rows=10.00 loops=1)
   ->  Merge Append (actual rows=10.00 loops=1)
         Sort Key: test2.x1, test2.x2, test2.x3
         ->  Sort (actual rows=4.00 loops=1)
               Sort Key: _hyper_5_9_chunk.x1, _hyper_5_9_chunk.x2, _hyper_5_9_chunk.x3
               Sort Method: quicksort 
               ->  Custom Scan (ColumnarScan) on _hyper_5_9_chunk (actual rows=4.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
                     Rows Removed by Filter: 10000
                     ->  Seq Scan on compress_hyper_6_11_chunk (actual rows=13.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_5_9_chunk.x1, _hyper_5_9_chunk.x2, _hyper_5_9_chunk.x3
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_5_9_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
         ->  Sort (actual rows=4.00 loops=1)
               Sort Key: _hyper_5_10_chunk.x1, _hyper_5_10_chunk.x2, _hyper_5_10_chunk.x3
               Sort Method: quicksort 
               ->  Custom Scan (ColumnarScan) on _hyper_5_10_chunk (actual rows=4.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
                     Rows Removed by Filter: 10000
                     ->  Seq Scan on compress_hyper_6_12_chunk (actual rows=13.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_5_10_chunk.x1, _hyper_5_10_chunk.x2, _hyper_5_10_chunk.x3
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_5_10_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)

SELECT * FROM test2 WHERE x4 % 11 != 0 ORDER BY x1, x2, x3 LIMIT 10;
             time             | x1 | x2 | x3 | x4 | x5 
------------------------------+----+----+----+----+----
 Fri Dec 31 16:00:00 1999 PST |  1 |  2 |  1 |  1 |  0
 Fri Dec 31 19:00:00 1999 PST |  1 |  2 |  4 |  4 |  0
 Sun Jan 09 16:00:00 2000 PST |  1 |  2 |  5 |  5 |  0
 Sun Jan 09 19:00:00 2000 PST |  1 |  2 |  8 |  8 |  0
 Fri Dec 31 16:02:01 1999 PST |  1 |  2 |  9 |  9 |  0
 Sun Jan 09 16:02:01 2000 PST |  1 |  2 | 10 | 10 |  0
 Fri Dec 31 17:00:00 1999 PST |  1 |  3 |  2 |  2 |  0
 Sun Jan 09 17:00:00 2000 PST |  1 |  3 |  6 |  6 |  0
 Fri Dec 31 18:00:00 1999 PST |  2 |  1 |  3 |  3 |  0
 Sun Jan 09 18:00:00 2000 PST |  2 |  1 |  7 |  7 |  0

:PREFIX SELECT * FROM test2 WHERE x4 % 11 != 0 ORDER BY x1, x2, x5, x4 LIMIT 10;
--- QUERY PLAN ---
 Limit (actual rows=10.00 loops=1)
   ->  Merge Append (actual rows=10.00 loops=1)
         Sort Key: test2.x1, test2.x2, test2.x5, test2.x4
         ->  Sort (actual rows=4.00 loops=1)
               Sort Key: _hyper_5_9_chunk.x1, _hyper_5_9_chunk.x2, _hyper_5_9_chunk.x5, _hyper_5_9_chunk.x4
               Sort Method: quicksort 
               ->  Custom Scan (ColumnarScan) on _hyper_5_9_chunk (actual rows=4.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
                     Rows Removed by Filter: 10000
                     ->  Seq Scan on compress_hyper_6_11_chunk (actual rows=13.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_5_9_chunk.x1, _hyper_5_9_chunk.x2, _hyper_5_9_chunk.x5, _hyper_5_9_chunk.x4
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_5_9_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
         ->  Sort (actual rows=4.00 loops=1)
               Sort Key: _hyper_5_10_chunk.x1, _hyper_5_10_chunk.x2, _hyper_5_10_chunk.x5, _hyper_5_10_chunk.x4
               Sort Method: quicksort 
               ->  Custom Scan (ColumnarScan) on _hyper_5_10_chunk (actual rows=4.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
                     Rows Removed by Filter: 10000
                     ->  Seq Scan on compress_hyper_6_12_chunk (actual rows=13.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_5_10_chunk.x1, _hyper_5_10_chunk.x2, _hyper_5_10_chunk.x5, _hyper_5_10_chunk.x4
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_5_10_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)

SELECT * FROM test2 WHERE x4 % 11 != 0 ORDER BY x1, x2, x5, x4 LIMIT 10;
             time             | x1 | x2 | x3 | x4 | x5 
------------------------------+----+----+----+----+----
 Fri Dec 31 16:00:00 1999 PST |  1 |  2 |  1 |  1 |  0
 Fri Dec 31 19:00:00 1999 PST |  1 |  2 |  4 |  4 |  0
 Sun Jan 09 16:00:00 2000 PST |  1 |  2 |  5 |  5 |  0
 Sun Jan 09 19:00:00 2000 PST |  1 |  2 |  8 |  8 |  0
 Fri Dec 31 16:02:01 1999 PST |  1 |  2 |  9 |  9 |  0
 Sun Jan 09 16:02:01 2000 PST |  1 |  2 | 10 | 10 |  0
 Fri Dec 31 17:00:00 1999 PST |  1 |  3 |  2 |  2 |  0
 Sun Jan 09 17:00:00 2000 PST |  1 |  3 |  6 |  6 |  0
 Fri Dec 31 18:00:00 1999 PST |  2 |  1 |  3 |  3 |  0
 Sun Jan 09 18:00:00 2000 PST |  2 |  1 |  7 |  7 |  0

:PREFIX SELECT * FROM test2 WHERE x4 % 11 != 0 ORDER BY x1, x2, x5, time LIMIT 10;
--- QUERY PLAN ---
 Limit (actual rows=10.00 loops=1)
   ->  Merge Append (actual rows=10.00 loops=1)
         Sort Key: test2.x1, test2.x2, test2.x5, test2."time"
         ->  Sort (actual rows=4.00 loops=1)
               Sort Key: _hyper_5_9_chunk.x1, _hyper_5_9_chunk.x2, _hyper_5_9_chunk.x5, _hyper_5_9_chunk."time"
               Sort Method: quicksort 
               ->  Custom Scan (ColumnarScan) on _hyper_5_9_chunk (actual rows=4.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
                     Rows Removed by Filter: 10000
                     ->  Seq Scan on compress_hyper_6_11_chunk (actual rows=13.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_5_9_chunk.x1, _hyper_5_9_chunk.x2, _hyper_5_9_chunk.x5, _hyper_5_9_chunk."time"
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_5_9_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
         ->  Sort (actual rows=4.00 loops=1)
               Sort Key: _hyper_5_10_chunk.x1, _hyper_5_10_chunk.x2, _hyper_5_10_chunk.x5, _hyper_5_10_chunk."time"
               Sort Method: quicksort 
               ->  Custom Scan (ColumnarScan) on _hyper_5_10_chunk (actual rows=4.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
                     Rows Removed by Filter: 10000
                     ->  Seq Scan on compress_hyper_6_12_chunk (actual rows=13.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_5_10_chunk.x1, _hyper_5_10_chunk.x2, _hyper_5_10_chunk.x5, _hyper_5_10_chunk."time"
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_5_10_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)

SELECT * FROM test2 WHERE x4 % 11 != 0 ORDER BY x1, x2, x5, time LIMIT 10;
             time             | x1 | x2 | x3 | x4 | x5 
------------------------------+----+----+----+----+----
 Fri Dec 31 16:00:00 1999 PST |  1 |  2 |  1 |  1 |  0
 Fri Dec 31 16:02:01 1999 PST |  1 |  2 |  9 |  9 |  0
 Fri Dec 31 19:00:00 1999 PST |  1 |  2 |  4 |  4 |  0
 Sun Jan 09 16:00:00 2000 PST |  1 |  2 |  5 |  5 |  0
 Sun Jan 09 16:02:01 2000 PST |  1 |  2 | 10 | 10 |  0
 Sun Jan 09 19:00:00 2000 PST |  1 |  2 |  8 |  8 |  0
 Fri Dec 31 17:00:00 1999 PST |  1 |  3 |  2 |  2 |  0
 Sun Jan 09 17:00:00 2000 PST |  1 |  3 |  6 |  6 |  0
 Fri Dec 31 18:00:00 1999 PST |  2 |  1 |  3 |  3 |  0
 Sun Jan 09 18:00:00 2000 PST |  2 |  1 |  7 |  7 |  0

-----------------------------
-- tests with space partitioning
-----------------------------
CREATE TABLE test3 (
time timestamptz NOT NULL,
    x1 integer,
    x2 integer,
    x3 integer,
    x4 integer,
    x5 integer);
SELECT FROM create_hypertable('test3', 'time');
--

SELECT add_dimension('test3', 'x1', number_partitions => 2);
     add_dimension     
-----------------------
 (5,public,test3,x1,t)

ALTER TABLE test3 SET (timescaledb.compress, timescaledb.compress_segmentby='x1, x2, x5', timescaledb.compress_orderby = 'x3 ASC, x4 ASC');
INSERT INTO test3 (time, x1, x2, x3, x4, x5) values('2000-01-01 00:00:00-00', 1, 2, 1, 1, 0);
INSERT INTO test3 (time, x1, x2, x3, x4, x5) values('2000-01-01 01:00:00-00', 1, 3, 2, 2, 0);
INSERT INTO test3 (time, x1, x2, x3, x4, x5) values('2000-01-01 02:00:00-00', 2, 1, 3, 3, 0);
INSERT INTO test3 (time, x1, x2, x3, x4, x5) values('2000-01-01 03:00:00-00', 1, 2, 4, 4, 0);
-- chunk 2
INSERT INTO test3 (time, x1, x2, x3, x4, x5) values('2000-01-10 00:00:00-00', 1, 2, 5, 5, 0);
INSERT INTO test3 (time, x1, x2, x3, x4, x5) values('2000-01-10 01:00:00-00', 1, 3, 6, 6, 0);
INSERT INTO test3 (time, x1, x2, x3, x4, x5) values('2000-01-10 02:00:00-00', 2, 1, 7, 7, 0);
INSERT INTO test3 (time, x1, x2, x3, x4, x5) values('2000-01-10 03:00:00-00', 1, 2, 8, 8, 0);
-- Insert some dummy rows to inflate the cost estimates to get it to actually
-- use the MergeAppend. We will filter them out using the "x4 % 11 != 0" which
-- thwarts the normal selectivity estimation.
INSERT INTO test3 select time, x1,
    /* x2 = */ 0, /* x3 = */ 0, /* x4 = */ 11 * (11 + x), 0
from generate_series(1, 10000) x,
    unnest(array['2000-01-01 04:00:00-00', '2000-01-10 04:00:00-00']::timestamptz[]) time,
    unnest(array[1, 2]::int[]) x1
;
SELECT compress_chunk(i) FROM show_chunks('test3') i;
             compress_chunk              
-----------------------------------------
 _timescaledb_internal._hyper_7_13_chunk
 _timescaledb_internal._hyper_7_14_chunk
 _timescaledb_internal._hyper_7_15_chunk
 _timescaledb_internal._hyper_7_16_chunk

-- make them partially compressed
INSERT INTO test3 (time, x1, x2, x3, x4, x5) values('2000-01-01 00:02:01-00', 1, 2,  9,  9, 0);
INSERT INTO test3 (time, x1, x2, x3, x4, x5) values('2000-01-10 00:02:01-00', 1, 2, 10, 10, 0);
INSERT INTO test3 (time, x1, x2, x3, x4, x5) values('2000-01-01 00:02:01-00', 2, 2,  9,  9, 0);
INSERT INTO test3 (time, x1, x2, x3, x4, x5) values('2000-01-10 00:02:01-00', 2, 2, 10, 10, 0);
ANALYZE test3;
set enable_indexscan = off;
-- queries where sort is pushed down
:PREFIX SELECT * FROM test3 WHERE x4 % 11 != 0 ORDER BY x1, x2, x5, x3 LIMIT 10;
--- QUERY PLAN ---
 Limit (actual rows=10.00 loops=1)
   ->  Merge Append (actual rows=10.00 loops=1)
         Sort Key: test3.x1, test3.x2, test3.x5, test3.x3
         ->  Custom Scan (ColumnarScan) on _hyper_7_13_chunk (actual rows=3.00 loops=1)
               Filter: ((x4 % 11) <> 0)
               Rows Removed by Filter: 10000
               ->  Sort (actual rows=12.00 loops=1)
                     Sort Key: compress_hyper_8_17_chunk.x1, compress_hyper_8_17_chunk.x2, compress_hyper_8_17_chunk.x5, compress_hyper_8_17_chunk._ts_meta_v2_first_x3, compress_hyper_8_17_chunk._ts_meta_v2_last_x3
                     Sort Method: quicksort 
                     ->  Seq Scan on compress_hyper_8_17_chunk (actual rows=12.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_7_13_chunk.x1, _hyper_7_13_chunk.x2, _hyper_7_13_chunk.x5, _hyper_7_13_chunk.x3
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_7_13_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
         ->  Custom Scan (ColumnarScan) on _hyper_7_14_chunk (actual rows=1.00 loops=1)
               Filter: ((x4 % 11) <> 0)
               Rows Removed by Filter: 10000
               ->  Sort (actual rows=11.00 loops=1)
                     Sort Key: compress_hyper_8_18_chunk.x1, compress_hyper_8_18_chunk.x2, compress_hyper_8_18_chunk.x5, compress_hyper_8_18_chunk._ts_meta_v2_first_x3, compress_hyper_8_18_chunk._ts_meta_v2_last_x3
                     Sort Method: quicksort 
                     ->  Seq Scan on compress_hyper_8_18_chunk (actual rows=11.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_7_14_chunk.x1, _hyper_7_14_chunk.x2, _hyper_7_14_chunk.x5, _hyper_7_14_chunk.x3
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_7_14_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
         ->  Custom Scan (ColumnarScan) on _hyper_7_15_chunk (actual rows=3.00 loops=1)
               Filter: ((x4 % 11) <> 0)
               Rows Removed by Filter: 10000
               ->  Sort (actual rows=12.00 loops=1)
                     Sort Key: compress_hyper_8_19_chunk.x1, compress_hyper_8_19_chunk.x2, compress_hyper_8_19_chunk.x5, compress_hyper_8_19_chunk._ts_meta_v2_first_x3, compress_hyper_8_19_chunk._ts_meta_v2_last_x3
                     Sort Method: quicksort 
                     ->  Seq Scan on compress_hyper_8_19_chunk (actual rows=12.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_7_15_chunk.x1, _hyper_7_15_chunk.x2, _hyper_7_15_chunk.x5, _hyper_7_15_chunk.x3
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_7_15_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
         ->  Custom Scan (ColumnarScan) on _hyper_7_16_chunk (actual rows=1.00 loops=1)
               Filter: ((x4 % 11) <> 0)
               Rows Removed by Filter: 10000
               ->  Sort (actual rows=11.00 loops=1)
                     Sort Key: compress_hyper_8_20_chunk.x1, compress_hyper_8_20_chunk.x2, compress_hyper_8_20_chunk.x5, compress_hyper_8_20_chunk._ts_meta_v2_first_x3, compress_hyper_8_20_chunk._ts_meta_v2_last_x3
                     Sort Method: quicksort 
                     ->  Seq Scan on compress_hyper_8_20_chunk (actual rows=11.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_7_16_chunk.x1, _hyper_7_16_chunk.x2, _hyper_7_16_chunk.x5, _hyper_7_16_chunk.x3
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_7_16_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)

SELECT * FROM test3 WHERE x4 % 11 != 0 ORDER BY x1, x2, x5, x3 LIMIT 10;
             time             | x1 | x2 | x3 | x4 | x5 
------------------------------+----+----+----+----+----
 Fri Dec 31 16:00:00 1999 PST |  1 |  2 |  1 |  1 |  0
 Fri Dec 31 19:00:00 1999 PST |  1 |  2 |  4 |  4 |  0
 Sun Jan 09 16:00:00 2000 PST |  1 |  2 |  5 |  5 |  0
 Sun Jan 09 19:00:00 2000 PST |  1 |  2 |  8 |  8 |  0
 Fri Dec 31 16:02:01 1999 PST |  1 |  2 |  9 |  9 |  0
 Sun Jan 09 16:02:01 2000 PST |  1 |  2 | 10 | 10 |  0
 Fri Dec 31 17:00:00 1999 PST |  1 |  3 |  2 |  2 |  0
 Sun Jan 09 17:00:00 2000 PST |  1 |  3 |  6 |  6 |  0
 Fri Dec 31 18:00:00 1999 PST |  2 |  1 |  3 |  3 |  0
 Sun Jan 09 18:00:00 2000 PST |  2 |  1 |  7 |  7 |  0

:PREFIX SELECT * FROM test3 WHERE x4 % 11 != 0 ORDER BY x1, x2, x5, x3, x4 LIMIT 10;
--- QUERY PLAN ---
 Limit (actual rows=10.00 loops=1)
   ->  Merge Append (actual rows=10.00 loops=1)
         Sort Key: test3.x1, test3.x2, test3.x5, test3.x3, test3.x4
         ->  Custom Scan (ColumnarScan) on _hyper_7_13_chunk (actual rows=3.00 loops=1)
               Filter: ((x4 % 11) <> 0)
               Rows Removed by Filter: 10000
               ->  Sort (actual rows=12.00 loops=1)
                     Sort Key: compress_hyper_8_17_chunk.x1, compress_hyper_8_17_chunk.x2, compress_hyper_8_17_chunk.x5, compress_hyper_8_17_chunk._ts_meta_v2_first_x3, compress_hyper_8_17_chunk._ts_meta_v2_last_x3, compress_hyper_8_17_chunk._ts_meta_v2_first_x4, compress_hyper_8_17_chunk._ts_meta_v2_last_x4
                     Sort Method: quicksort 
                     ->  Seq Scan on compress_hyper_8_17_chunk (actual rows=12.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_7_13_chunk.x1, _hyper_7_13_chunk.x2, _hyper_7_13_chunk.x5, _hyper_7_13_chunk.x3, _hyper_7_13_chunk.x4
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_7_13_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
         ->  Custom Scan (ColumnarScan) on _hyper_7_14_chunk (actual rows=1.00 loops=1)
               Filter: ((x4 % 11) <> 0)
               Rows Removed by Filter: 10000
               ->  Sort (actual rows=11.00 loops=1)
                     Sort Key: compress_hyper_8_18_chunk.x1, compress_hyper_8_18_chunk.x2, compress_hyper_8_18_chunk.x5, compress_hyper_8_18_chunk._ts_meta_v2_first_x3, compress_hyper_8_18_chunk._ts_meta_v2_last_x3, compress_hyper_8_18_chunk._ts_meta_v2_first_x4, compress_hyper_8_18_chunk._ts_meta_v2_last_x4
                     Sort Method: quicksort 
                     ->  Seq Scan on compress_hyper_8_18_chunk (actual rows=11.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_7_14_chunk.x1, _hyper_7_14_chunk.x2, _hyper_7_14_chunk.x5, _hyper_7_14_chunk.x3, _hyper_7_14_chunk.x4
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_7_14_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
         ->  Custom Scan (ColumnarScan) on _hyper_7_15_chunk (actual rows=3.00 loops=1)
               Filter: ((x4 % 11) <> 0)
               Rows Removed by Filter: 10000
               ->  Sort (actual rows=12.00 loops=1)
                     Sort Key: compress_hyper_8_19_chunk.x1, compress_hyper_8_19_chunk.x2, compress_hyper_8_19_chunk.x5, compress_hyper_8_19_chunk._ts_meta_v2_first_x3, compress_hyper_8_19_chunk._ts_meta_v2_last_x3, compress_hyper_8_19_chunk._ts_meta_v2_first_x4, compress_hyper_8_19_chunk._ts_meta_v2_last_x4
                     Sort Method: quicksort 
                     ->  Seq Scan on compress_hyper_8_19_chunk (actual rows=12.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_7_15_chunk.x1, _hyper_7_15_chunk.x2, _hyper_7_15_chunk.x5, _hyper_7_15_chunk.x3, _hyper_7_15_chunk.x4
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_7_15_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
         ->  Custom Scan (ColumnarScan) on _hyper_7_16_chunk (actual rows=1.00 loops=1)
               Filter: ((x4 % 11) <> 0)
               Rows Removed by Filter: 10000
               ->  Sort (actual rows=11.00 loops=1)
                     Sort Key: compress_hyper_8_20_chunk.x1, compress_hyper_8_20_chunk.x2, compress_hyper_8_20_chunk.x5, compress_hyper_8_20_chunk._ts_meta_v2_first_x3, compress_hyper_8_20_chunk._ts_meta_v2_last_x3, compress_hyper_8_20_chunk._ts_meta_v2_first_x4, compress_hyper_8_20_chunk._ts_meta_v2_last_x4
                     Sort Method: quicksort 
                     ->  Seq Scan on compress_hyper_8_20_chunk (actual rows=11.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_7_16_chunk.x1, _hyper_7_16_chunk.x2, _hyper_7_16_chunk.x5, _hyper_7_16_chunk.x3, _hyper_7_16_chunk.x4
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_7_16_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)

SELECT * FROM test3 WHERE x4 % 11 != 0 ORDER BY x1, x2, x5, x3, x4 LIMIT 10;
             time             | x1 | x2 | x3 | x4 | x5 
------------------------------+----+----+----+----+----
 Fri Dec 31 16:00:00 1999 PST |  1 |  2 |  1 |  1 |  0
 Fri Dec 31 19:00:00 1999 PST |  1 |  2 |  4 |  4 |  0
 Sun Jan 09 16:00:00 2000 PST |  1 |  2 |  5 |  5 |  0
 Sun Jan 09 19:00:00 2000 PST |  1 |  2 |  8 |  8 |  0
 Fri Dec 31 16:02:01 1999 PST |  1 |  2 |  9 |  9 |  0
 Sun Jan 09 16:02:01 2000 PST |  1 |  2 | 10 | 10 |  0
 Fri Dec 31 17:00:00 1999 PST |  1 |  3 |  2 |  2 |  0
 Sun Jan 09 17:00:00 2000 PST |  1 |  3 |  6 |  6 |  0
 Fri Dec 31 18:00:00 1999 PST |  2 |  1 |  3 |  3 |  0
 Sun Jan 09 18:00:00 2000 PST |  2 |  1 |  7 |  7 |  0

-- queries where sort is not pushed down
:PREFIX SELECT * FROM test3 WHERE x4 % 11 != 0 ORDER BY x1, x2, x3 LIMIT 10;
--- QUERY PLAN ---
 Limit (actual rows=10.00 loops=1)
   ->  Merge Append (actual rows=10.00 loops=1)
         Sort Key: test3.x1, test3.x2, test3.x3
         ->  Sort (actual rows=3.00 loops=1)
               Sort Key: _hyper_7_13_chunk.x1, _hyper_7_13_chunk.x2, _hyper_7_13_chunk.x3
               Sort Method: quicksort 
               ->  Custom Scan (ColumnarScan) on _hyper_7_13_chunk (actual rows=3.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
                     Rows Removed by Filter: 10000
                     ->  Seq Scan on compress_hyper_8_17_chunk (actual rows=12.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_7_13_chunk.x1, _hyper_7_13_chunk.x2, _hyper_7_13_chunk.x3
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_7_13_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_7_14_chunk.x1, _hyper_7_14_chunk.x2, _hyper_7_14_chunk.x3
               Sort Method: quicksort 
               ->  Custom Scan (ColumnarScan) on _hyper_7_14_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
                     Rows Removed by Filter: 10000
                     ->  Seq Scan on compress_hyper_8_18_chunk (actual rows=11.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_7_14_chunk.x1, _hyper_7_14_chunk.x2, _hyper_7_14_chunk.x3
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_7_14_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
         ->  Sort (actual rows=3.00 loops=1)
               Sort Key: _hyper_7_15_chunk.x1, _hyper_7_15_chunk.x2, _hyper_7_15_chunk.x3
               Sort Method: quicksort 
               ->  Custom Scan (ColumnarScan) on _hyper_7_15_chunk (actual rows=3.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
                     Rows Removed by Filter: 10000
                     ->  Seq Scan on compress_hyper_8_19_chunk (actual rows=12.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_7_15_chunk.x1, _hyper_7_15_chunk.x2, _hyper_7_15_chunk.x3
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_7_15_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_7_16_chunk.x1, _hyper_7_16_chunk.x2, _hyper_7_16_chunk.x3
               Sort Method: quicksort 
               ->  Custom Scan (ColumnarScan) on _hyper_7_16_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
                     Rows Removed by Filter: 10000
                     ->  Seq Scan on compress_hyper_8_20_chunk (actual rows=11.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_7_16_chunk.x1, _hyper_7_16_chunk.x2, _hyper_7_16_chunk.x3
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_7_16_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)

SELECT * FROM test3 WHERE x4 % 11 != 0 ORDER BY x1, x2, x3 LIMIT 10;
             time             | x1 | x2 | x3 | x4 | x5 
------------------------------+----+----+----+----+----
 Fri Dec 31 16:00:00 1999 PST |  1 |  2 |  1 |  1 |  0
 Fri Dec 31 19:00:00 1999 PST |  1 |  2 |  4 |  4 |  0
 Sun Jan 09 16:00:00 2000 PST |  1 |  2 |  5 |  5 |  0
 Sun Jan 09 19:00:00 2000 PST |  1 |  2 |  8 |  8 |  0
 Fri Dec 31 16:02:01 1999 PST |  1 |  2 |  9 |  9 |  0
 Sun Jan 09 16:02:01 2000 PST |  1 |  2 | 10 | 10 |  0
 Fri Dec 31 17:00:00 1999 PST |  1 |  3 |  2 |  2 |  0
 Sun Jan 09 17:00:00 2000 PST |  1 |  3 |  6 |  6 |  0
 Fri Dec 31 18:00:00 1999 PST |  2 |  1 |  3 |  3 |  0
 Sun Jan 09 18:00:00 2000 PST |  2 |  1 |  7 |  7 |  0

:PREFIX SELECT * FROM test3 WHERE x4 % 11 != 0 ORDER BY x1, x2, x5, x4 LIMIT 10;
--- QUERY PLAN ---
 Limit (actual rows=10.00 loops=1)
   ->  Merge Append (actual rows=10.00 loops=1)
         Sort Key: test3.x1, test3.x2, test3.x5, test3.x4
         ->  Sort (actual rows=3.00 loops=1)
               Sort Key: _hyper_7_13_chunk.x1, _hyper_7_13_chunk.x2, _hyper_7_13_chunk.x5, _hyper_7_13_chunk.x4
               Sort Method: quicksort 
               ->  Custom Scan (ColumnarScan) on _hyper_7_13_chunk (actual rows=3.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
                     Rows Removed by Filter: 10000
                     ->  Seq Scan on compress_hyper_8_17_chunk (actual rows=12.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_7_13_chunk.x1, _hyper_7_13_chunk.x2, _hyper_7_13_chunk.x5, _hyper_7_13_chunk.x4
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_7_13_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_7_14_chunk.x1, _hyper_7_14_chunk.x2, _hyper_7_14_chunk.x5, _hyper_7_14_chunk.x4
               Sort Method: quicksort 
               ->  Custom Scan (ColumnarScan) on _hyper_7_14_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
                     Rows Removed by Filter: 10000
                     ->  Seq Scan on compress_hyper_8_18_chunk (actual rows=11.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_7_14_chunk.x1, _hyper_7_14_chunk.x2, _hyper_7_14_chunk.x5, _hyper_7_14_chunk.x4
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_7_14_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
         ->  Sort (actual rows=3.00 loops=1)
               Sort Key: _hyper_7_15_chunk.x1, _hyper_7_15_chunk.x2, _hyper_7_15_chunk.x5, _hyper_7_15_chunk.x4
               Sort Method: quicksort 
               ->  Custom Scan (ColumnarScan) on _hyper_7_15_chunk (actual rows=3.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
                     Rows Removed by Filter: 10000
                     ->  Seq Scan on compress_hyper_8_19_chunk (actual rows=12.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_7_15_chunk.x1, _hyper_7_15_chunk.x2, _hyper_7_15_chunk.x5, _hyper_7_15_chunk.x4
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_7_15_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_7_16_chunk.x1, _hyper_7_16_chunk.x2, _hyper_7_16_chunk.x5, _hyper_7_16_chunk.x4
               Sort Method: quicksort 
               ->  Custom Scan (ColumnarScan) on _hyper_7_16_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
                     Rows Removed by Filter: 10000
                     ->  Seq Scan on compress_hyper_8_20_chunk (actual rows=11.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_7_16_chunk.x1, _hyper_7_16_chunk.x2, _hyper_7_16_chunk.x5, _hyper_7_16_chunk.x4
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_7_16_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)

SELECT * FROM test3 WHERE x4 % 11 != 0 ORDER BY x1, x2, x5, x4 LIMIT 10;
             time             | x1 | x2 | x3 | x4 | x5 
------------------------------+----+----+----+----+----
 Fri Dec 31 16:00:00 1999 PST |  1 |  2 |  1 |  1 |  0
 Fri Dec 31 19:00:00 1999 PST |  1 |  2 |  4 |  4 |  0
 Sun Jan 09 16:00:00 2000 PST |  1 |  2 |  5 |  5 |  0
 Sun Jan 09 19:00:00 2000 PST |  1 |  2 |  8 |  8 |  0
 Fri Dec 31 16:02:01 1999 PST |  1 |  2 |  9 |  9 |  0
 Sun Jan 09 16:02:01 2000 PST |  1 |  2 | 10 | 10 |  0
 Fri Dec 31 17:00:00 1999 PST |  1 |  3 |  2 |  2 |  0
 Sun Jan 09 17:00:00 2000 PST |  1 |  3 |  6 |  6 |  0
 Fri Dec 31 18:00:00 1999 PST |  2 |  1 |  3 |  3 |  0
 Sun Jan 09 18:00:00 2000 PST |  2 |  1 |  7 |  7 |  0

:PREFIX SELECT * FROM test3 WHERE x4 % 11 != 0 ORDER BY x1, x2, x5, time LIMIT 10;
--- QUERY PLAN ---
 Limit (actual rows=10.00 loops=1)
   ->  Merge Append (actual rows=10.00 loops=1)
         Sort Key: test3.x1, test3.x2, test3.x5, test3."time"
         ->  Sort (actual rows=3.00 loops=1)
               Sort Key: _hyper_7_13_chunk.x1, _hyper_7_13_chunk.x2, _hyper_7_13_chunk.x5, _hyper_7_13_chunk."time"
               Sort Method: quicksort 
               ->  Custom Scan (ColumnarScan) on _hyper_7_13_chunk (actual rows=3.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
                     Rows Removed by Filter: 10000
                     ->  Seq Scan on compress_hyper_8_17_chunk (actual rows=12.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_7_13_chunk.x1, _hyper_7_13_chunk.x2, _hyper_7_13_chunk.x5, _hyper_7_13_chunk."time"
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_7_13_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_7_14_chunk.x1, _hyper_7_14_chunk.x2, _hyper_7_14_chunk.x5, _hyper_7_14_chunk."time"
               Sort Method: quicksort 
               ->  Custom Scan (ColumnarScan) on _hyper_7_14_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
                     Rows Removed by Filter: 10000
                     ->  Seq Scan on compress_hyper_8_18_chunk (actual rows=11.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_7_14_chunk.x1, _hyper_7_14_chunk.x2, _hyper_7_14_chunk.x5, _hyper_7_14_chunk."time"
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_7_14_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
         ->  Sort (actual rows=3.00 loops=1)
               Sort Key: _hyper_7_15_chunk.x1, _hyper_7_15_chunk.x2, _hyper_7_15_chunk.x5, _hyper_7_15_chunk."time"
               Sort Method: quicksort 
               ->  Custom Scan (ColumnarScan) on _hyper_7_15_chunk (actual rows=3.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
                     Rows Removed by Filter: 10000
                     ->  Seq Scan on compress_hyper_8_19_chunk (actual rows=12.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_7_15_chunk.x1, _hyper_7_15_chunk.x2, _hyper_7_15_chunk.x5, _hyper_7_15_chunk."time"
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_7_15_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_7_16_chunk.x1, _hyper_7_16_chunk.x2, _hyper_7_16_chunk.x5, _hyper_7_16_chunk."time"
               Sort Method: quicksort 
               ->  Custom Scan (ColumnarScan) on _hyper_7_16_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)
                     Rows Removed by Filter: 10000
                     ->  Seq Scan on compress_hyper_8_20_chunk (actual rows=11.00 loops=1)
         ->  Sort (actual rows=1.00 loops=1)
               Sort Key: _hyper_7_16_chunk.x1, _hyper_7_16_chunk.x2, _hyper_7_16_chunk.x5, _hyper_7_16_chunk."time"
               Sort Method: quicksort 
               ->  Seq Scan on _hyper_7_16_chunk (actual rows=1.00 loops=1)
                     Filter: ((x4 % 11) <> 0)

SELECT * FROM test3 WHERE x4 % 11 != 0 ORDER BY x1, x2, x5, time LIMIT 10;
             time             | x1 | x2 | x3 | x4 | x5 
------------------------------+----+----+----+----+----
 Fri Dec 31 16:00:00 1999 PST |  1 |  2 |  1 |  1 |  0
 Fri Dec 31 16:02:01 1999 PST |  1 |  2 |  9 |  9 |  0
 Fri Dec 31 19:00:00 1999 PST |  1 |  2 |  4 |  4 |  0
 Sun Jan 09 16:00:00 2000 PST |  1 |  2 |  5 |  5 |  0
 Sun Jan 09 16:02:01 2000 PST |  1 |  2 | 10 | 10 |  0
 Sun Jan 09 19:00:00 2000 PST |  1 |  2 |  8 |  8 |  0
 Fri Dec 31 17:00:00 1999 PST |  1 |  3 |  2 |  2 |  0
 Sun Jan 09 17:00:00 2000 PST |  1 |  3 |  6 |  6 |  0
 Fri Dec 31 18:00:00 1999 PST |  2 |  1 |  3 |  3 |  0
 Sun Jan 09 18:00:00 2000 PST |  2 |  1 |  7 |  7 |  0

reset enable_indexscan;
-- test ordering on single chunk queries
CREATE TABLE test4(time timestamptz not null, device text, value float);
SELECT table_name FROM create_hypertable('test4','time',chunk_time_interval:='1 year'::interval);
 table_name 
------------
 test4

ALTER TABLE test4 SET (tsdb.compress, tsdb.compress_segmentby='device', tsdb.compress_orderby='time');
INSERT INTO test4 SELECT '2025-01-01', NULL, 0.1;
INSERT INTO test4 SELECT '2025-01-02', NULL, 0.1;
INSERT INTO test4 SELECT '2025-01-02', 'd', 0.1;
SELECT count(compress_chunk(ch)) FROM show_chunks('test4') ch;
 count 
-------
     1

INSERT INTO test4 SELECT '2025-01-02', 'd', 0.1;
VACUUM ANALYZE test4;
set enable_hashagg TO false;
SELECT time, device FROM _timescaledb_internal._hyper_9_21_chunk GROUP BY time, device;
             time             | device 
------------------------------+--------
 Wed Jan 01 00:00:00 2025 PST | 
 Thu Jan 02 00:00:00 2025 PST | d
 Thu Jan 02 00:00:00 2025 PST | 

EXPLAIN (buffers off, costs off, analyze, timing off, summary off) SELECT time, device FROM _timescaledb_internal._hyper_9_21_chunk GROUP BY time, device;
--- QUERY PLAN ---
 Group (actual rows=3.00 loops=1)
   Group Key: _hyper_9_21_chunk."time", _hyper_9_21_chunk.device
   ->  Sort (actual rows=4.00 loops=1)
         Sort Key: _hyper_9_21_chunk."time", _hyper_9_21_chunk.device
         Sort Method: quicksort 
         ->  Append (actual rows=4.00 loops=1)
               ->  Custom Scan (ColumnarScan) on _hyper_9_21_chunk (actual rows=3.00 loops=1)
                     ->  Seq Scan on compress_hyper_10_22_chunk (actual rows=2.00 loops=1)
               ->  Seq Scan on _hyper_9_21_chunk (actual rows=1.00 loops=1)

reset timescaledb.enable_decompression_sorted_merge;
reset max_parallel_workers_per_gather;