compression_sorted_merge.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.
-- Increase the working memory limit slightly, otherwise the batch sorted merge
-- will be penalized for segmentby cardinalities larger than 100, where it is
-- still faster than sort.
SET work_mem to '16MB';
\set PREFIX 'EXPLAIN (analyze, verbose, buffers off, costs off, timing off, summary off)'
CREATE TABLE test1 (
time timestamptz NOT NULL,
    x1 integer,
    x2 integer,
    x3 integer NOT NULL,
    x4 integer NOT NULL,
    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);
SELECT compress_chunk(i) FROM show_chunks('test1') i;
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_1_1_chunk

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

ALTER TABLE test2 SET (timescaledb.compress, timescaledb.compress_segmentby='x1, x2, x5', timescaledb.compress_orderby = 'time ASC, x3 DESC, x4 DESC');
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);
SELECT compress_chunk(i) FROM show_chunks('test2') i;
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_3_3_chunk

ANALYZE test2;
CREATE TABLE test_with_defined_null (
    time timestamptz NOT NULL,
    x1 integer,
    x2 integer,
    x3 integer);
SELECT FROM create_hypertable('test_with_defined_null','time');
--

ALTER TABLE test_with_defined_null SET (timescaledb.compress,timescaledb.compress_segmentby='x1', timescaledb.compress_orderby='x2 ASC NULLS FIRST');
INSERT INTO test_with_defined_null (time, x1, x2) values('2000-01-01', '1', NULL);
INSERT INTO test_with_defined_null (time, x1, x2) values('2000-01-01','2', NULL);
INSERT INTO test_with_defined_null (time, x1, x2) values('2000-01-01','1',1);
INSERT INTO test_with_defined_null (time, x1, x2) values('2000-01-01','1',2);
SELECT compress_chunk(i) FROM show_chunks('test_with_defined_null') i;
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_5_5_chunk

ANALYZE test_with_defined_null;
-- test1 uses compress_segmentby='x1, x2, x5' and compress_orderby = 'time DESC, x3 ASC, x4 ASC'
-- test2 uses compress_segmentby='x1, x2, x5' and compress_orderby = 'time ASC, x3 DESC, x4 DESC'
-- test_with_defined_null uses compress_segmentby='x1' and compress_orderby = 'x2 ASC NULLS FIRST'
------
-- Tests based on ordering
------
set timescaledb.debug_require_batch_sorted_merge to 'force';
-- Should be optimized (implicit NULLS first)
:PREFIX
SELECT * FROM test1 ORDER BY time DESC;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk (actual rows=4.00 loops=1)
   Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5
   Batch Sorted Merge: true
   Bulk Decompression: false
   ->  Sort (actual rows=3.00 loops=1)
         Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4
         Sort Key: compress_hyper_2_2_chunk._ts_meta_v2_first_time DESC
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_2_2_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4

-- Should be optimized
:PREFIX
SELECT * FROM test1 ORDER BY time DESC NULLS FIRST;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk (actual rows=4.00 loops=1)
   Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5
   Batch Sorted Merge: true
   Bulk Decompression: false
   ->  Sort (actual rows=3.00 loops=1)
         Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4
         Sort Key: compress_hyper_2_2_chunk._ts_meta_v2_first_time DESC
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_2_2_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4

-- Should be optimized (implicit NULLS last)
:PREFIX
SELECT * FROM test1 ORDER BY time ASC;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk (actual rows=4.00 loops=1)
   Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5
   Batch Sorted Merge: true
   Reverse: true
   Bulk Decompression: false
   ->  Sort (actual rows=3.00 loops=1)
         Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4
         Sort Key: compress_hyper_2_2_chunk._ts_meta_v2_last_time
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_2_2_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4

-- Should be optimized
:PREFIX
SELECT * FROM test1 ORDER BY time ASC NULLS LAST;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk (actual rows=4.00 loops=1)
   Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5
   Batch Sorted Merge: true
   Reverse: true
   Bulk Decompression: false
   ->  Sort (actual rows=3.00 loops=1)
         Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4
         Sort Key: compress_hyper_2_2_chunk._ts_meta_v2_last_time
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_2_2_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4

-- Should be optimized
:PREFIX
SELECT * FROM test1 ORDER BY time DESC NULLS FIRST, x3 ASC NULLS LAST;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk (actual rows=4.00 loops=1)
   Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5
   Batch Sorted Merge: true
   Bulk Decompression: false
   ->  Sort (actual rows=3.00 loops=1)
         Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4
         Sort Key: compress_hyper_2_2_chunk._ts_meta_v2_first_time DESC, compress_hyper_2_2_chunk._ts_meta_v2_first_x3
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_2_2_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4

-- Should be optimized
:PREFIX
SELECT * FROM test1 ORDER BY time DESC NULLS FIRST, x3 ASC NULLS LAST, x4 ASC NULLS LAST;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk (actual rows=4.00 loops=1)
   Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5
   Batch Sorted Merge: true
   Bulk Decompression: false
   ->  Sort (actual rows=3.00 loops=1)
         Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4
         Sort Key: compress_hyper_2_2_chunk._ts_meta_v2_first_time DESC, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_2_2_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4

-- Should be optimized (backward scan)
:PREFIX
SELECT * FROM test1 ORDER BY time ASC NULLS LAST;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk (actual rows=4.00 loops=1)
   Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5
   Batch Sorted Merge: true
   Reverse: true
   Bulk Decompression: false
   ->  Sort (actual rows=3.00 loops=1)
         Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4
         Sort Key: compress_hyper_2_2_chunk._ts_meta_v2_last_time
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_2_2_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4

-- Should be optimized (backward scan)
:PREFIX
SELECT * FROM test1 ORDER BY time ASC NULLS LAST, x3 DESC NULLS FIRST;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk (actual rows=4.00 loops=1)
   Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5
   Batch Sorted Merge: true
   Reverse: true
   Bulk Decompression: false
   ->  Sort (actual rows=3.00 loops=1)
         Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4
         Sort Key: compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk._ts_meta_v2_last_x3 DESC
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_2_2_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4

-- Should be optimized (backward scan)
:PREFIX
SELECT * FROM test1 ORDER BY time ASC NULLS LAST, x3 DESC NULLS FIRST, x4 DESC NULLS FIRST;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk (actual rows=4.00 loops=1)
   Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5
   Batch Sorted Merge: true
   Reverse: true
   Bulk Decompression: false
   ->  Sort (actual rows=3.00 loops=1)
         Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4
         Sort Key: compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk._ts_meta_v2_last_x3 DESC, compress_hyper_2_2_chunk._ts_meta_v2_last_x4 DESC
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_2_2_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4

-- Should be optimized
:PREFIX
SELECT * FROM test2 ORDER BY time ASC;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_3_3_chunk (actual rows=4.00 loops=1)
   Output: _hyper_3_3_chunk."time", _hyper_3_3_chunk.x1, _hyper_3_3_chunk.x2, _hyper_3_3_chunk.x3, _hyper_3_3_chunk.x4, _hyper_3_3_chunk.x5
   Batch Sorted Merge: true
   Bulk Decompression: false
   ->  Sort (actual rows=3.00 loops=1)
         Output: compress_hyper_4_4_chunk._ts_meta_count, compress_hyper_4_4_chunk.x1, compress_hyper_4_4_chunk.x2, compress_hyper_4_4_chunk.x5, compress_hyper_4_4_chunk._ts_meta_min_1, compress_hyper_4_4_chunk._ts_meta_max_1, compress_hyper_4_4_chunk._ts_meta_v2_first_time, compress_hyper_4_4_chunk._ts_meta_v2_last_time, compress_hyper_4_4_chunk."time", compress_hyper_4_4_chunk._ts_meta_min_2, compress_hyper_4_4_chunk._ts_meta_max_2, compress_hyper_4_4_chunk._ts_meta_v2_first_x3, compress_hyper_4_4_chunk._ts_meta_v2_last_x3, compress_hyper_4_4_chunk.x3, compress_hyper_4_4_chunk._ts_meta_min_3, compress_hyper_4_4_chunk._ts_meta_max_3, compress_hyper_4_4_chunk._ts_meta_v2_first_x4, compress_hyper_4_4_chunk._ts_meta_v2_last_x4, compress_hyper_4_4_chunk.x4
         Sort Key: compress_hyper_4_4_chunk._ts_meta_v2_first_time
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_4_4_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_4_4_chunk._ts_meta_count, compress_hyper_4_4_chunk.x1, compress_hyper_4_4_chunk.x2, compress_hyper_4_4_chunk.x5, compress_hyper_4_4_chunk._ts_meta_min_1, compress_hyper_4_4_chunk._ts_meta_max_1, compress_hyper_4_4_chunk._ts_meta_v2_first_time, compress_hyper_4_4_chunk._ts_meta_v2_last_time, compress_hyper_4_4_chunk."time", compress_hyper_4_4_chunk._ts_meta_min_2, compress_hyper_4_4_chunk._ts_meta_max_2, compress_hyper_4_4_chunk._ts_meta_v2_first_x3, compress_hyper_4_4_chunk._ts_meta_v2_last_x3, compress_hyper_4_4_chunk.x3, compress_hyper_4_4_chunk._ts_meta_min_3, compress_hyper_4_4_chunk._ts_meta_max_3, compress_hyper_4_4_chunk._ts_meta_v2_first_x4, compress_hyper_4_4_chunk._ts_meta_v2_last_x4, compress_hyper_4_4_chunk.x4

-- Should be optimized
:PREFIX
SELECT * FROM test2 ORDER BY time ASC, x3 DESC;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_3_3_chunk (actual rows=4.00 loops=1)
   Output: _hyper_3_3_chunk."time", _hyper_3_3_chunk.x1, _hyper_3_3_chunk.x2, _hyper_3_3_chunk.x3, _hyper_3_3_chunk.x4, _hyper_3_3_chunk.x5
   Batch Sorted Merge: true
   Bulk Decompression: false
   ->  Sort (actual rows=3.00 loops=1)
         Output: compress_hyper_4_4_chunk._ts_meta_count, compress_hyper_4_4_chunk.x1, compress_hyper_4_4_chunk.x2, compress_hyper_4_4_chunk.x5, compress_hyper_4_4_chunk._ts_meta_min_1, compress_hyper_4_4_chunk._ts_meta_max_1, compress_hyper_4_4_chunk._ts_meta_v2_first_time, compress_hyper_4_4_chunk._ts_meta_v2_last_time, compress_hyper_4_4_chunk."time", compress_hyper_4_4_chunk._ts_meta_min_2, compress_hyper_4_4_chunk._ts_meta_max_2, compress_hyper_4_4_chunk._ts_meta_v2_first_x3, compress_hyper_4_4_chunk._ts_meta_v2_last_x3, compress_hyper_4_4_chunk.x3, compress_hyper_4_4_chunk._ts_meta_min_3, compress_hyper_4_4_chunk._ts_meta_max_3, compress_hyper_4_4_chunk._ts_meta_v2_first_x4, compress_hyper_4_4_chunk._ts_meta_v2_last_x4, compress_hyper_4_4_chunk.x4
         Sort Key: compress_hyper_4_4_chunk._ts_meta_v2_first_time, compress_hyper_4_4_chunk._ts_meta_v2_first_x3 DESC
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_4_4_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_4_4_chunk._ts_meta_count, compress_hyper_4_4_chunk.x1, compress_hyper_4_4_chunk.x2, compress_hyper_4_4_chunk.x5, compress_hyper_4_4_chunk._ts_meta_min_1, compress_hyper_4_4_chunk._ts_meta_max_1, compress_hyper_4_4_chunk._ts_meta_v2_first_time, compress_hyper_4_4_chunk._ts_meta_v2_last_time, compress_hyper_4_4_chunk."time", compress_hyper_4_4_chunk._ts_meta_min_2, compress_hyper_4_4_chunk._ts_meta_max_2, compress_hyper_4_4_chunk._ts_meta_v2_first_x3, compress_hyper_4_4_chunk._ts_meta_v2_last_x3, compress_hyper_4_4_chunk.x3, compress_hyper_4_4_chunk._ts_meta_min_3, compress_hyper_4_4_chunk._ts_meta_max_3, compress_hyper_4_4_chunk._ts_meta_v2_first_x4, compress_hyper_4_4_chunk._ts_meta_v2_last_x4, compress_hyper_4_4_chunk.x4

-- Should be optimized
:PREFIX
SELECT * FROM test2 ORDER BY time ASC, x3 DESC, x4 DESC;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_3_3_chunk (actual rows=4.00 loops=1)
   Output: _hyper_3_3_chunk."time", _hyper_3_3_chunk.x1, _hyper_3_3_chunk.x2, _hyper_3_3_chunk.x3, _hyper_3_3_chunk.x4, _hyper_3_3_chunk.x5
   Batch Sorted Merge: true
   Bulk Decompression: false
   ->  Sort (actual rows=3.00 loops=1)
         Output: compress_hyper_4_4_chunk._ts_meta_count, compress_hyper_4_4_chunk.x1, compress_hyper_4_4_chunk.x2, compress_hyper_4_4_chunk.x5, compress_hyper_4_4_chunk._ts_meta_min_1, compress_hyper_4_4_chunk._ts_meta_max_1, compress_hyper_4_4_chunk._ts_meta_v2_first_time, compress_hyper_4_4_chunk._ts_meta_v2_last_time, compress_hyper_4_4_chunk."time", compress_hyper_4_4_chunk._ts_meta_min_2, compress_hyper_4_4_chunk._ts_meta_max_2, compress_hyper_4_4_chunk._ts_meta_v2_first_x3, compress_hyper_4_4_chunk._ts_meta_v2_last_x3, compress_hyper_4_4_chunk.x3, compress_hyper_4_4_chunk._ts_meta_min_3, compress_hyper_4_4_chunk._ts_meta_max_3, compress_hyper_4_4_chunk._ts_meta_v2_first_x4, compress_hyper_4_4_chunk._ts_meta_v2_last_x4, compress_hyper_4_4_chunk.x4
         Sort Key: compress_hyper_4_4_chunk._ts_meta_v2_first_time, compress_hyper_4_4_chunk._ts_meta_v2_first_x3 DESC, compress_hyper_4_4_chunk._ts_meta_v2_first_x4 DESC
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_4_4_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_4_4_chunk._ts_meta_count, compress_hyper_4_4_chunk.x1, compress_hyper_4_4_chunk.x2, compress_hyper_4_4_chunk.x5, compress_hyper_4_4_chunk._ts_meta_min_1, compress_hyper_4_4_chunk._ts_meta_max_1, compress_hyper_4_4_chunk._ts_meta_v2_first_time, compress_hyper_4_4_chunk._ts_meta_v2_last_time, compress_hyper_4_4_chunk."time", compress_hyper_4_4_chunk._ts_meta_min_2, compress_hyper_4_4_chunk._ts_meta_max_2, compress_hyper_4_4_chunk._ts_meta_v2_first_x3, compress_hyper_4_4_chunk._ts_meta_v2_last_x3, compress_hyper_4_4_chunk.x3, compress_hyper_4_4_chunk._ts_meta_min_3, compress_hyper_4_4_chunk._ts_meta_max_3, compress_hyper_4_4_chunk._ts_meta_v2_first_x4, compress_hyper_4_4_chunk._ts_meta_v2_last_x4, compress_hyper_4_4_chunk.x4

-- Should be optimized (backward scan)
:PREFIX
SELECT * FROM test2 ORDER BY time DESC NULLS FIRST;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_3_3_chunk (actual rows=4.00 loops=1)
   Output: _hyper_3_3_chunk."time", _hyper_3_3_chunk.x1, _hyper_3_3_chunk.x2, _hyper_3_3_chunk.x3, _hyper_3_3_chunk.x4, _hyper_3_3_chunk.x5
   Batch Sorted Merge: true
   Reverse: true
   Bulk Decompression: false
   ->  Sort (actual rows=3.00 loops=1)
         Output: compress_hyper_4_4_chunk._ts_meta_count, compress_hyper_4_4_chunk.x1, compress_hyper_4_4_chunk.x2, compress_hyper_4_4_chunk.x5, compress_hyper_4_4_chunk._ts_meta_min_1, compress_hyper_4_4_chunk._ts_meta_max_1, compress_hyper_4_4_chunk._ts_meta_v2_first_time, compress_hyper_4_4_chunk._ts_meta_v2_last_time, compress_hyper_4_4_chunk."time", compress_hyper_4_4_chunk._ts_meta_min_2, compress_hyper_4_4_chunk._ts_meta_max_2, compress_hyper_4_4_chunk._ts_meta_v2_first_x3, compress_hyper_4_4_chunk._ts_meta_v2_last_x3, compress_hyper_4_4_chunk.x3, compress_hyper_4_4_chunk._ts_meta_min_3, compress_hyper_4_4_chunk._ts_meta_max_3, compress_hyper_4_4_chunk._ts_meta_v2_first_x4, compress_hyper_4_4_chunk._ts_meta_v2_last_x4, compress_hyper_4_4_chunk.x4
         Sort Key: compress_hyper_4_4_chunk._ts_meta_v2_last_time DESC
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_4_4_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_4_4_chunk._ts_meta_count, compress_hyper_4_4_chunk.x1, compress_hyper_4_4_chunk.x2, compress_hyper_4_4_chunk.x5, compress_hyper_4_4_chunk._ts_meta_min_1, compress_hyper_4_4_chunk._ts_meta_max_1, compress_hyper_4_4_chunk._ts_meta_v2_first_time, compress_hyper_4_4_chunk._ts_meta_v2_last_time, compress_hyper_4_4_chunk."time", compress_hyper_4_4_chunk._ts_meta_min_2, compress_hyper_4_4_chunk._ts_meta_max_2, compress_hyper_4_4_chunk._ts_meta_v2_first_x3, compress_hyper_4_4_chunk._ts_meta_v2_last_x3, compress_hyper_4_4_chunk.x3, compress_hyper_4_4_chunk._ts_meta_min_3, compress_hyper_4_4_chunk._ts_meta_max_3, compress_hyper_4_4_chunk._ts_meta_v2_first_x4, compress_hyper_4_4_chunk._ts_meta_v2_last_x4, compress_hyper_4_4_chunk.x4

-- Should be optimized (backward scan)
:PREFIX
SELECT * FROM test2 ORDER BY time DESC NULLS FIRST, x3 ASC NULLS LAST;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_3_3_chunk (actual rows=4.00 loops=1)
   Output: _hyper_3_3_chunk."time", _hyper_3_3_chunk.x1, _hyper_3_3_chunk.x2, _hyper_3_3_chunk.x3, _hyper_3_3_chunk.x4, _hyper_3_3_chunk.x5
   Batch Sorted Merge: true
   Reverse: true
   Bulk Decompression: false
   ->  Sort (actual rows=3.00 loops=1)
         Output: compress_hyper_4_4_chunk._ts_meta_count, compress_hyper_4_4_chunk.x1, compress_hyper_4_4_chunk.x2, compress_hyper_4_4_chunk.x5, compress_hyper_4_4_chunk._ts_meta_min_1, compress_hyper_4_4_chunk._ts_meta_max_1, compress_hyper_4_4_chunk._ts_meta_v2_first_time, compress_hyper_4_4_chunk._ts_meta_v2_last_time, compress_hyper_4_4_chunk."time", compress_hyper_4_4_chunk._ts_meta_min_2, compress_hyper_4_4_chunk._ts_meta_max_2, compress_hyper_4_4_chunk._ts_meta_v2_first_x3, compress_hyper_4_4_chunk._ts_meta_v2_last_x3, compress_hyper_4_4_chunk.x3, compress_hyper_4_4_chunk._ts_meta_min_3, compress_hyper_4_4_chunk._ts_meta_max_3, compress_hyper_4_4_chunk._ts_meta_v2_first_x4, compress_hyper_4_4_chunk._ts_meta_v2_last_x4, compress_hyper_4_4_chunk.x4
         Sort Key: compress_hyper_4_4_chunk._ts_meta_v2_last_time DESC, compress_hyper_4_4_chunk._ts_meta_v2_last_x3
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_4_4_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_4_4_chunk._ts_meta_count, compress_hyper_4_4_chunk.x1, compress_hyper_4_4_chunk.x2, compress_hyper_4_4_chunk.x5, compress_hyper_4_4_chunk._ts_meta_min_1, compress_hyper_4_4_chunk._ts_meta_max_1, compress_hyper_4_4_chunk._ts_meta_v2_first_time, compress_hyper_4_4_chunk._ts_meta_v2_last_time, compress_hyper_4_4_chunk."time", compress_hyper_4_4_chunk._ts_meta_min_2, compress_hyper_4_4_chunk._ts_meta_max_2, compress_hyper_4_4_chunk._ts_meta_v2_first_x3, compress_hyper_4_4_chunk._ts_meta_v2_last_x3, compress_hyper_4_4_chunk.x3, compress_hyper_4_4_chunk._ts_meta_min_3, compress_hyper_4_4_chunk._ts_meta_max_3, compress_hyper_4_4_chunk._ts_meta_v2_first_x4, compress_hyper_4_4_chunk._ts_meta_v2_last_x4, compress_hyper_4_4_chunk.x4

-- Should be optimized (backward scan)
:PREFIX
SELECT * FROM test2 ORDER BY time DESC NULLS FIRST, x3 ASC NULLS LAST, x4 NULLS LAST;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_3_3_chunk (actual rows=4.00 loops=1)
   Output: _hyper_3_3_chunk."time", _hyper_3_3_chunk.x1, _hyper_3_3_chunk.x2, _hyper_3_3_chunk.x3, _hyper_3_3_chunk.x4, _hyper_3_3_chunk.x5
   Batch Sorted Merge: true
   Reverse: true
   Bulk Decompression: false
   ->  Sort (actual rows=3.00 loops=1)
         Output: compress_hyper_4_4_chunk._ts_meta_count, compress_hyper_4_4_chunk.x1, compress_hyper_4_4_chunk.x2, compress_hyper_4_4_chunk.x5, compress_hyper_4_4_chunk._ts_meta_min_1, compress_hyper_4_4_chunk._ts_meta_max_1, compress_hyper_4_4_chunk._ts_meta_v2_first_time, compress_hyper_4_4_chunk._ts_meta_v2_last_time, compress_hyper_4_4_chunk."time", compress_hyper_4_4_chunk._ts_meta_min_2, compress_hyper_4_4_chunk._ts_meta_max_2, compress_hyper_4_4_chunk._ts_meta_v2_first_x3, compress_hyper_4_4_chunk._ts_meta_v2_last_x3, compress_hyper_4_4_chunk.x3, compress_hyper_4_4_chunk._ts_meta_min_3, compress_hyper_4_4_chunk._ts_meta_max_3, compress_hyper_4_4_chunk._ts_meta_v2_first_x4, compress_hyper_4_4_chunk._ts_meta_v2_last_x4, compress_hyper_4_4_chunk.x4
         Sort Key: compress_hyper_4_4_chunk._ts_meta_v2_last_time DESC, compress_hyper_4_4_chunk._ts_meta_v2_last_x3, compress_hyper_4_4_chunk._ts_meta_v2_last_x4
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_4_4_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_4_4_chunk._ts_meta_count, compress_hyper_4_4_chunk.x1, compress_hyper_4_4_chunk.x2, compress_hyper_4_4_chunk.x5, compress_hyper_4_4_chunk._ts_meta_min_1, compress_hyper_4_4_chunk._ts_meta_max_1, compress_hyper_4_4_chunk._ts_meta_v2_first_time, compress_hyper_4_4_chunk._ts_meta_v2_last_time, compress_hyper_4_4_chunk."time", compress_hyper_4_4_chunk._ts_meta_min_2, compress_hyper_4_4_chunk._ts_meta_max_2, compress_hyper_4_4_chunk._ts_meta_v2_first_x3, compress_hyper_4_4_chunk._ts_meta_v2_last_x3, compress_hyper_4_4_chunk.x3, compress_hyper_4_4_chunk._ts_meta_min_3, compress_hyper_4_4_chunk._ts_meta_max_3, compress_hyper_4_4_chunk._ts_meta_v2_first_x4, compress_hyper_4_4_chunk._ts_meta_v2_last_x4, compress_hyper_4_4_chunk.x4

-- Should be optimized
:PREFIX
SELECT * FROM test_with_defined_null WHERE x2 IS NOT NULL ORDER BY x2 ASC NULLS FIRST;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_5_5_chunk (actual rows=2.00 loops=1)
   Output: _hyper_5_5_chunk."time", _hyper_5_5_chunk.x1, _hyper_5_5_chunk.x2, _hyper_5_5_chunk.x3
   Filter: (_hyper_5_5_chunk.x2 IS NOT NULL)
   Rows Removed by Filter: 2
   Batch Sorted Merge: true
   Bulk Decompression: false
   ->  Sort (actual rows=2.00 loops=1)
         Output: compress_hyper_6_6_chunk._ts_meta_count, compress_hyper_6_6_chunk.x1, compress_hyper_6_6_chunk._ts_meta_min_2, compress_hyper_6_6_chunk._ts_meta_max_2, compress_hyper_6_6_chunk._ts_meta_v2_first_time, compress_hyper_6_6_chunk._ts_meta_v2_last_time, compress_hyper_6_6_chunk."time", compress_hyper_6_6_chunk._ts_meta_min_1, compress_hyper_6_6_chunk._ts_meta_max_1, compress_hyper_6_6_chunk._ts_meta_v2_first_x2, compress_hyper_6_6_chunk._ts_meta_v2_last_x2, compress_hyper_6_6_chunk.x2, compress_hyper_6_6_chunk.x3
         Sort Key: compress_hyper_6_6_chunk._ts_meta_v2_first_x2 NULLS FIRST
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_6_6_chunk (actual rows=2.00 loops=1)
               Output: compress_hyper_6_6_chunk._ts_meta_count, compress_hyper_6_6_chunk.x1, compress_hyper_6_6_chunk._ts_meta_min_2, compress_hyper_6_6_chunk._ts_meta_max_2, compress_hyper_6_6_chunk._ts_meta_v2_first_time, compress_hyper_6_6_chunk._ts_meta_v2_last_time, compress_hyper_6_6_chunk."time", compress_hyper_6_6_chunk._ts_meta_min_1, compress_hyper_6_6_chunk._ts_meta_max_1, compress_hyper_6_6_chunk._ts_meta_v2_first_x2, compress_hyper_6_6_chunk._ts_meta_v2_last_x2, compress_hyper_6_6_chunk.x2, compress_hyper_6_6_chunk.x3

-- Should be optimized (backward scan)
:PREFIX
SELECT * FROM test_with_defined_null WHERE x2 IS NOT NULL ORDER BY x2 DESC NULLS LAST;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_5_5_chunk (actual rows=2.00 loops=1)
   Output: _hyper_5_5_chunk."time", _hyper_5_5_chunk.x1, _hyper_5_5_chunk.x2, _hyper_5_5_chunk.x3
   Filter: (_hyper_5_5_chunk.x2 IS NOT NULL)
   Rows Removed by Filter: 2
   Batch Sorted Merge: true
   Reverse: true
   Bulk Decompression: false
   ->  Sort (actual rows=2.00 loops=1)
         Output: compress_hyper_6_6_chunk._ts_meta_count, compress_hyper_6_6_chunk.x1, compress_hyper_6_6_chunk._ts_meta_min_2, compress_hyper_6_6_chunk._ts_meta_max_2, compress_hyper_6_6_chunk._ts_meta_v2_first_time, compress_hyper_6_6_chunk._ts_meta_v2_last_time, compress_hyper_6_6_chunk."time", compress_hyper_6_6_chunk._ts_meta_min_1, compress_hyper_6_6_chunk._ts_meta_max_1, compress_hyper_6_6_chunk._ts_meta_v2_first_x2, compress_hyper_6_6_chunk._ts_meta_v2_last_x2, compress_hyper_6_6_chunk.x2, compress_hyper_6_6_chunk.x3
         Sort Key: compress_hyper_6_6_chunk._ts_meta_v2_last_x2 DESC NULLS LAST
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_6_6_chunk (actual rows=2.00 loops=1)
               Output: compress_hyper_6_6_chunk._ts_meta_count, compress_hyper_6_6_chunk.x1, compress_hyper_6_6_chunk._ts_meta_min_2, compress_hyper_6_6_chunk._ts_meta_max_2, compress_hyper_6_6_chunk._ts_meta_v2_first_time, compress_hyper_6_6_chunk._ts_meta_v2_last_time, compress_hyper_6_6_chunk."time", compress_hyper_6_6_chunk._ts_meta_min_1, compress_hyper_6_6_chunk._ts_meta_max_1, compress_hyper_6_6_chunk._ts_meta_v2_first_x2, compress_hyper_6_6_chunk._ts_meta_v2_last_x2, compress_hyper_6_6_chunk.x2, compress_hyper_6_6_chunk.x3

set timescaledb.debug_require_batch_sorted_merge to 'forbid';
-- Should not be optimized (wrong order for x3 in backward scan)
:PREFIX
SELECT * FROM test2 ORDER BY time DESC NULLS LAST, x3 DESC NULLS FIRST, x4 NULLS FIRST;
--- QUERY PLAN ---
 Sort (actual rows=4.00 loops=1)
   Output: _hyper_3_3_chunk."time", _hyper_3_3_chunk.x1, _hyper_3_3_chunk.x2, _hyper_3_3_chunk.x3, _hyper_3_3_chunk.x4, _hyper_3_3_chunk.x5
   Sort Key: _hyper_3_3_chunk."time" DESC NULLS LAST, _hyper_3_3_chunk.x3 DESC, _hyper_3_3_chunk.x4 NULLS FIRST
   Sort Method: quicksort 
   ->  Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_3_3_chunk (actual rows=4.00 loops=1)
         Output: _hyper_3_3_chunk."time", _hyper_3_3_chunk.x1, _hyper_3_3_chunk.x2, _hyper_3_3_chunk.x3, _hyper_3_3_chunk.x4, _hyper_3_3_chunk.x5
         Bulk Decompression: true
         ->  Seq Scan on _timescaledb_internal.compress_hyper_4_4_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_4_4_chunk._ts_meta_count, compress_hyper_4_4_chunk.x1, compress_hyper_4_4_chunk.x2, compress_hyper_4_4_chunk.x5, compress_hyper_4_4_chunk._ts_meta_min_1, compress_hyper_4_4_chunk._ts_meta_max_1, compress_hyper_4_4_chunk._ts_meta_v2_first_time, compress_hyper_4_4_chunk._ts_meta_v2_last_time, compress_hyper_4_4_chunk."time", compress_hyper_4_4_chunk._ts_meta_min_2, compress_hyper_4_4_chunk._ts_meta_max_2, compress_hyper_4_4_chunk._ts_meta_v2_first_x3, compress_hyper_4_4_chunk._ts_meta_v2_last_x3, compress_hyper_4_4_chunk.x3, compress_hyper_4_4_chunk._ts_meta_min_3, compress_hyper_4_4_chunk._ts_meta_max_3, compress_hyper_4_4_chunk._ts_meta_v2_first_x4, compress_hyper_4_4_chunk._ts_meta_v2_last_x4, compress_hyper_4_4_chunk.x4

-- Should not be optimized (wrong order for x3 in backward scan)
:PREFIX
SELECT * FROM test2 ORDER BY time DESC NULLS LAST, x3 DESC NULLS LAST, x4 NULLS FIRST;
--- QUERY PLAN ---
 Sort (actual rows=4.00 loops=1)
   Output: _hyper_3_3_chunk."time", _hyper_3_3_chunk.x1, _hyper_3_3_chunk.x2, _hyper_3_3_chunk.x3, _hyper_3_3_chunk.x4, _hyper_3_3_chunk.x5
   Sort Key: _hyper_3_3_chunk."time" DESC NULLS LAST, _hyper_3_3_chunk.x3 DESC NULLS LAST, _hyper_3_3_chunk.x4 NULLS FIRST
   Sort Method: quicksort 
   ->  Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_3_3_chunk (actual rows=4.00 loops=1)
         Output: _hyper_3_3_chunk."time", _hyper_3_3_chunk.x1, _hyper_3_3_chunk.x2, _hyper_3_3_chunk.x3, _hyper_3_3_chunk.x4, _hyper_3_3_chunk.x5
         Bulk Decompression: true
         ->  Seq Scan on _timescaledb_internal.compress_hyper_4_4_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_4_4_chunk._ts_meta_count, compress_hyper_4_4_chunk.x1, compress_hyper_4_4_chunk.x2, compress_hyper_4_4_chunk.x5, compress_hyper_4_4_chunk._ts_meta_min_1, compress_hyper_4_4_chunk._ts_meta_max_1, compress_hyper_4_4_chunk._ts_meta_v2_first_time, compress_hyper_4_4_chunk._ts_meta_v2_last_time, compress_hyper_4_4_chunk."time", compress_hyper_4_4_chunk._ts_meta_min_2, compress_hyper_4_4_chunk._ts_meta_max_2, compress_hyper_4_4_chunk._ts_meta_v2_first_x3, compress_hyper_4_4_chunk._ts_meta_v2_last_x3, compress_hyper_4_4_chunk.x3, compress_hyper_4_4_chunk._ts_meta_min_3, compress_hyper_4_4_chunk._ts_meta_max_3, compress_hyper_4_4_chunk._ts_meta_v2_first_x4, compress_hyper_4_4_chunk._ts_meta_v2_last_x4, compress_hyper_4_4_chunk.x4

-- Should not be optimized (NULL order wrong)
:PREFIX
SELECT * FROM test1 ORDER BY time DESC NULLS LAST;
--- QUERY PLAN ---
 Sort (actual rows=4.00 loops=1)
   Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5
   Sort Key: _hyper_1_1_chunk."time" DESC NULLS LAST
   Sort Method: quicksort 
   ->  Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk (actual rows=4.00 loops=1)
         Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5
         Bulk Decompression: true
         ->  Seq Scan on _timescaledb_internal.compress_hyper_2_2_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4

-- Should not be optimized (NULL order wrong)
:PREFIX
SELECT * FROM test1 ORDER BY time ASC NULLS FIRST;
--- QUERY PLAN ---
 Sort (actual rows=4.00 loops=1)
   Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5
   Sort Key: _hyper_1_1_chunk."time" NULLS FIRST
   Sort Method: quicksort 
   ->  Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk (actual rows=4.00 loops=1)
         Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5
         Bulk Decompression: true
         ->  Seq Scan on _timescaledb_internal.compress_hyper_2_2_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4

-- Should not be optimized (wrong order for x4)
:PREFIX
SELECT * FROM test1 ORDER BY time DESC NULLS FIRST, x3 ASC NULLS LAST, x4 DESC NULLS FIRST;
--- QUERY PLAN ---
 Sort (actual rows=4.00 loops=1)
   Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5
   Sort Key: _hyper_1_1_chunk."time" DESC, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4 DESC
   Sort Method: quicksort 
   ->  Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk (actual rows=4.00 loops=1)
         Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5
         Bulk Decompression: true
         ->  Seq Scan on _timescaledb_internal.compress_hyper_2_2_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4

-- Should not be optimized (wrong order for x4 in backward scan)
:PREFIX
SELECT * FROM test1 ORDER BY time ASC NULLS FIRST, x3 DESC NULLS LAST, x4 ASC;
--- QUERY PLAN ---
 Sort (actual rows=4.00 loops=1)
   Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5
   Sort Key: _hyper_1_1_chunk."time" NULLS FIRST, _hyper_1_1_chunk.x3 DESC NULLS LAST, _hyper_1_1_chunk.x4
   Sort Method: quicksort 
   ->  Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk (actual rows=4.00 loops=1)
         Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5
         Bulk Decompression: true
         ->  Seq Scan on _timescaledb_internal.compress_hyper_2_2_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4

-- Should not be optimized (wrong order for x3)
:PREFIX
SELECT * FROM test2 ORDER BY time ASC, x3 ASC NULLS LAST, x4 DESC;
--- QUERY PLAN ---
 Sort (actual rows=4.00 loops=1)
   Output: _hyper_3_3_chunk."time", _hyper_3_3_chunk.x1, _hyper_3_3_chunk.x2, _hyper_3_3_chunk.x3, _hyper_3_3_chunk.x4, _hyper_3_3_chunk.x5
   Sort Key: _hyper_3_3_chunk."time", _hyper_3_3_chunk.x3, _hyper_3_3_chunk.x4 DESC
   Sort Method: quicksort 
   ->  Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_3_3_chunk (actual rows=4.00 loops=1)
         Output: _hyper_3_3_chunk."time", _hyper_3_3_chunk.x1, _hyper_3_3_chunk.x2, _hyper_3_3_chunk.x3, _hyper_3_3_chunk.x4, _hyper_3_3_chunk.x5
         Bulk Decompression: true
         ->  Seq Scan on _timescaledb_internal.compress_hyper_4_4_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_4_4_chunk._ts_meta_count, compress_hyper_4_4_chunk.x1, compress_hyper_4_4_chunk.x2, compress_hyper_4_4_chunk.x5, compress_hyper_4_4_chunk._ts_meta_min_1, compress_hyper_4_4_chunk._ts_meta_max_1, compress_hyper_4_4_chunk._ts_meta_v2_first_time, compress_hyper_4_4_chunk._ts_meta_v2_last_time, compress_hyper_4_4_chunk."time", compress_hyper_4_4_chunk._ts_meta_min_2, compress_hyper_4_4_chunk._ts_meta_max_2, compress_hyper_4_4_chunk._ts_meta_v2_first_x3, compress_hyper_4_4_chunk._ts_meta_v2_last_x3, compress_hyper_4_4_chunk.x3, compress_hyper_4_4_chunk._ts_meta_min_3, compress_hyper_4_4_chunk._ts_meta_max_3, compress_hyper_4_4_chunk._ts_meta_v2_first_x4, compress_hyper_4_4_chunk._ts_meta_v2_last_x4, compress_hyper_4_4_chunk.x4

-- Should not be optimized (wrong order for x3)
:PREFIX
SELECT * FROM test2 ORDER BY time ASC, x3 ASC NULLS FIRST, x4 DESC;
--- QUERY PLAN ---
 Sort (actual rows=4.00 loops=1)
   Output: _hyper_3_3_chunk."time", _hyper_3_3_chunk.x1, _hyper_3_3_chunk.x2, _hyper_3_3_chunk.x3, _hyper_3_3_chunk.x4, _hyper_3_3_chunk.x5
   Sort Key: _hyper_3_3_chunk."time", _hyper_3_3_chunk.x3 NULLS FIRST, _hyper_3_3_chunk.x4 DESC
   Sort Method: quicksort 
   ->  Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_3_3_chunk (actual rows=4.00 loops=1)
         Output: _hyper_3_3_chunk."time", _hyper_3_3_chunk.x1, _hyper_3_3_chunk.x2, _hyper_3_3_chunk.x3, _hyper_3_3_chunk.x4, _hyper_3_3_chunk.x5
         Bulk Decompression: true
         ->  Seq Scan on _timescaledb_internal.compress_hyper_4_4_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_4_4_chunk._ts_meta_count, compress_hyper_4_4_chunk.x1, compress_hyper_4_4_chunk.x2, compress_hyper_4_4_chunk.x5, compress_hyper_4_4_chunk._ts_meta_min_1, compress_hyper_4_4_chunk._ts_meta_max_1, compress_hyper_4_4_chunk._ts_meta_v2_first_time, compress_hyper_4_4_chunk._ts_meta_v2_last_time, compress_hyper_4_4_chunk."time", compress_hyper_4_4_chunk._ts_meta_min_2, compress_hyper_4_4_chunk._ts_meta_max_2, compress_hyper_4_4_chunk._ts_meta_v2_first_x3, compress_hyper_4_4_chunk._ts_meta_v2_last_x3, compress_hyper_4_4_chunk.x3, compress_hyper_4_4_chunk._ts_meta_min_3, compress_hyper_4_4_chunk._ts_meta_max_3, compress_hyper_4_4_chunk._ts_meta_v2_first_x4, compress_hyper_4_4_chunk._ts_meta_v2_last_x4, compress_hyper_4_4_chunk.x4

-- Should not be optimized
:PREFIX
SELECT * FROM test_with_defined_null ORDER BY x2 ASC NULLS LAST;
--- QUERY PLAN ---
 Sort (actual rows=4.00 loops=1)
   Output: _hyper_5_5_chunk."time", _hyper_5_5_chunk.x1, _hyper_5_5_chunk.x2, _hyper_5_5_chunk.x3
   Sort Key: _hyper_5_5_chunk.x2
   Sort Method: quicksort 
   ->  Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_5_5_chunk (actual rows=4.00 loops=1)
         Output: _hyper_5_5_chunk."time", _hyper_5_5_chunk.x1, _hyper_5_5_chunk.x2, _hyper_5_5_chunk.x3
         Bulk Decompression: true
         ->  Seq Scan on _timescaledb_internal.compress_hyper_6_6_chunk (actual rows=2.00 loops=1)
               Output: compress_hyper_6_6_chunk._ts_meta_count, compress_hyper_6_6_chunk.x1, compress_hyper_6_6_chunk._ts_meta_min_2, compress_hyper_6_6_chunk._ts_meta_max_2, compress_hyper_6_6_chunk._ts_meta_v2_first_time, compress_hyper_6_6_chunk._ts_meta_v2_last_time, compress_hyper_6_6_chunk."time", compress_hyper_6_6_chunk._ts_meta_min_1, compress_hyper_6_6_chunk._ts_meta_max_1, compress_hyper_6_6_chunk._ts_meta_v2_first_x2, compress_hyper_6_6_chunk._ts_meta_v2_last_x2, compress_hyper_6_6_chunk.x2, compress_hyper_6_6_chunk.x3

-- Should not be optimized
:PREFIX
SELECT * FROM test_with_defined_null ORDER BY x2 DESC NULLS FIRST;
--- QUERY PLAN ---
 Sort (actual rows=4.00 loops=1)
   Output: _hyper_5_5_chunk."time", _hyper_5_5_chunk.x1, _hyper_5_5_chunk.x2, _hyper_5_5_chunk.x3
   Sort Key: _hyper_5_5_chunk.x2 DESC
   Sort Method: quicksort 
   ->  Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_5_5_chunk (actual rows=4.00 loops=1)
         Output: _hyper_5_5_chunk."time", _hyper_5_5_chunk.x1, _hyper_5_5_chunk.x2, _hyper_5_5_chunk.x3
         Bulk Decompression: true
         ->  Seq Scan on _timescaledb_internal.compress_hyper_6_6_chunk (actual rows=2.00 loops=1)
               Output: compress_hyper_6_6_chunk._ts_meta_count, compress_hyper_6_6_chunk.x1, compress_hyper_6_6_chunk._ts_meta_min_2, compress_hyper_6_6_chunk._ts_meta_max_2, compress_hyper_6_6_chunk._ts_meta_v2_first_time, compress_hyper_6_6_chunk._ts_meta_v2_last_time, compress_hyper_6_6_chunk."time", compress_hyper_6_6_chunk._ts_meta_min_1, compress_hyper_6_6_chunk._ts_meta_max_1, compress_hyper_6_6_chunk._ts_meta_v2_first_x2, compress_hyper_6_6_chunk._ts_meta_v2_last_x2, compress_hyper_6_6_chunk.x2, compress_hyper_6_6_chunk.x3

------
-- Tests based on attributes
------
set timescaledb.debug_require_batch_sorted_merge to 'force';
-- Should be optimized (some batches qualify by pushed down filter on _ts_meta_max_3)
:PREFIX
SELECT * FROM test1 WHERE x4 > 0 ORDER BY time DESC;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk (actual rows=4.00 loops=1)
   Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5
   Filter: (_hyper_1_1_chunk.x4 > 0)
   Batch Sorted Merge: true
   Bulk Decompression: false
   ->  Sort (actual rows=3.00 loops=1)
         Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4
         Sort Key: compress_hyper_2_2_chunk._ts_meta_v2_first_time DESC
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_2_2_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4
               Filter: (compress_hyper_2_2_chunk._ts_meta_max_3 > 0)

-- Should be optimized (no batches qualify by pushed down filter on _ts_meta_max_3)
:PREFIX
SELECT * FROM test1 WHERE x4 > 100 ORDER BY time DESC;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk (actual rows=0.00 loops=1)
   Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5
   Filter: (_hyper_1_1_chunk.x4 > 100)
   Batch Sorted Merge: true
   Bulk Decompression: false
   ->  Sort (actual rows=0.00 loops=1)
         Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4
         Sort Key: compress_hyper_2_2_chunk._ts_meta_v2_first_time DESC
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_2_2_chunk (actual rows=0.00 loops=1)
               Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4
               Filter: (compress_hyper_2_2_chunk._ts_meta_max_3 > 100)
               Rows Removed by Filter: 3

-- Should be optimized
:PREFIX
SELECT * FROM test1 WHERE x4 > 100 ORDER BY time DESC, x3, x4;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk (actual rows=0.00 loops=1)
   Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5
   Filter: (_hyper_1_1_chunk.x4 > 100)
   Batch Sorted Merge: true
   Bulk Decompression: false
   ->  Sort (actual rows=0.00 loops=1)
         Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4
         Sort Key: compress_hyper_2_2_chunk._ts_meta_v2_first_time DESC, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_2_2_chunk (actual rows=0.00 loops=1)
               Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4
               Filter: (compress_hyper_2_2_chunk._ts_meta_max_3 > 100)
               Rows Removed by Filter: 3

-- Should be optimized (duplicate order by attributes)
:PREFIX
SELECT * FROM test1 WHERE x4 > 100 ORDER BY time DESC, x3, x3;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk (actual rows=0.00 loops=1)
   Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5
   Filter: (_hyper_1_1_chunk.x4 > 100)
   Batch Sorted Merge: true
   Bulk Decompression: false
   ->  Sort (actual rows=0.00 loops=1)
         Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4
         Sort Key: compress_hyper_2_2_chunk._ts_meta_v2_first_time DESC, compress_hyper_2_2_chunk._ts_meta_v2_first_x3
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_2_2_chunk (actual rows=0.00 loops=1)
               Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4
               Filter: (compress_hyper_2_2_chunk._ts_meta_max_3 > 100)
               Rows Removed by Filter: 3

-- Should be optimized (duplicate order by attributes)
:PREFIX
SELECT * FROM test1 WHERE x4 > 100 ORDER BY time DESC, x3, x4, x3, x4;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk (actual rows=0.00 loops=1)
   Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5
   Filter: (_hyper_1_1_chunk.x4 > 100)
   Batch Sorted Merge: true
   Bulk Decompression: false
   ->  Sort (actual rows=0.00 loops=1)
         Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4
         Sort Key: compress_hyper_2_2_chunk._ts_meta_v2_first_time DESC, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_2_2_chunk (actual rows=0.00 loops=1)
               Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4
               Filter: (compress_hyper_2_2_chunk._ts_meta_max_3 > 100)
               Rows Removed by Filter: 3

set timescaledb.debug_require_batch_sorted_merge to 'forbid';
-- Should not be optimized
:PREFIX
SELECT * FROM test1 WHERE x4 > 100 ORDER BY time DESC, x4, x3;
--- QUERY PLAN ---
 Sort (actual rows=0.00 loops=1)
   Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5
   Sort Key: _hyper_1_1_chunk."time" DESC, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x3
   Sort Method: quicksort 
   ->  Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk (actual rows=0.00 loops=1)
         Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5
         Vectorized Filter: (_hyper_1_1_chunk.x4 > 100)
         Bulk Decompression: true
         ->  Seq Scan on _timescaledb_internal.compress_hyper_2_2_chunk (actual rows=0.00 loops=1)
               Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4
               Filter: (compress_hyper_2_2_chunk._ts_meta_max_3 > 100)
               Rows Removed by Filter: 3

-- Should not be optimized
:PREFIX
SELECT * FROM test1 WHERE x4 > 100 ORDER BY time ASC, x3, x4;
--- QUERY PLAN ---
 Sort (actual rows=0.00 loops=1)
   Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5
   Sort Key: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4
   Sort Method: quicksort 
   ->  Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk (actual rows=0.00 loops=1)
         Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5
         Vectorized Filter: (_hyper_1_1_chunk.x4 > 100)
         Bulk Decompression: true
         ->  Seq Scan on _timescaledb_internal.compress_hyper_2_2_chunk (actual rows=0.00 loops=1)
               Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4
               Filter: (compress_hyper_2_2_chunk._ts_meta_max_3 > 100)
               Rows Removed by Filter: 3

------
-- Tests based on results
------
set timescaledb.debug_require_batch_sorted_merge to 'force';
-- Forward scan
SELECT * FROM test1 ORDER BY time DESC;
             time             | x1 | x2 | x3 | x4 | x5 
------------------------------+----+----+----+----+----
 Fri Dec 31 19:00:00 1999 PST |  1 |  2 |  4 |  4 |  0
 Fri Dec 31 18:00:00 1999 PST |  2 |  1 |  3 |  3 |  0
 Fri Dec 31 17:00:00 1999 PST |  1 |  3 |  2 |  2 |  0
 Fri Dec 31 16:00:00 1999 PST |  1 |  2 |  1 |  1 |  0

-- Forward scan
SELECT * FROM test2 ORDER BY time ASC;
             time             | x1 | x2 | x3 | x4 | x5 
------------------------------+----+----+----+----+----
 Fri Dec 31 16:00:00 1999 PST |  1 |  2 |  1 |  1 |  0
 Fri Dec 31 17:00:00 1999 PST |  1 |  3 |  2 |  2 |  0
 Fri Dec 31 18:00:00 1999 PST |  2 |  1 |  3 |  3 |  0
 Fri Dec 31 19:00:00 1999 PST |  1 |  2 |  4 |  4 |  0

set timescaledb.debug_require_batch_sorted_merge to 'force';
-- Backward scan
SELECT * FROM test1 ORDER BY time ASC NULLS LAST;
             time             | x1 | x2 | x3 | x4 | x5 
------------------------------+----+----+----+----+----
 Fri Dec 31 16:00:00 1999 PST |  1 |  2 |  1 |  1 |  0
 Fri Dec 31 17:00:00 1999 PST |  1 |  3 |  2 |  2 |  0
 Fri Dec 31 18:00:00 1999 PST |  2 |  1 |  3 |  3 |  0
 Fri Dec 31 19:00:00 1999 PST |  1 |  2 |  4 |  4 |  0

-- Backward scan
SELECT * FROM test2 ORDER BY time DESC NULLS FIRST;
             time             | x1 | x2 | x3 | x4 | x5 
------------------------------+----+----+----+----+----
 Fri Dec 31 19:00:00 1999 PST |  1 |  2 |  4 |  4 |  0
 Fri Dec 31 18:00:00 1999 PST |  2 |  1 |  3 |  3 |  0
 Fri Dec 31 17:00:00 1999 PST |  1 |  3 |  2 |  2 |  0
 Fri Dec 31 16:00:00 1999 PST |  1 |  2 |  1 |  1 |  0

set timescaledb.debug_require_batch_sorted_merge to 'force';
-- With selection on compressed column (value larger as max value for all batches, so no batch has to be opened)
SELECT * FROM test1 WHERE x4 > 100 ORDER BY time DESC;
 time | x1 | x2 | x3 | x4 | x5 
------+----+----+----+----+----

-- With selection on compressed column (value smaller as max value for some batches, so batches are opened and filter has to be applied)
SELECT * FROM test1 WHERE x4 > 2 ORDER BY time DESC;
             time             | x1 | x2 | x3 | x4 | x5 
------------------------------+----+----+----+----+----
 Fri Dec 31 19:00:00 1999 PST |  1 |  2 |  4 |  4 |  0
 Fri Dec 31 18:00:00 1999 PST |  2 |  1 |  3 |  3 |  0

-- With selection on segment_by column
SELECT * FROM test1 WHERE time < '1980-01-01 00:00:00-00' ORDER BY time DESC;
 time | x1 | x2 | x3 | x4 | x5 
------+----+----+----+----+----

SELECT * FROM test1 WHERE time > '1980-01-01 00:00:00-00' ORDER BY time DESC;
             time             | x1 | x2 | x3 | x4 | x5 
------------------------------+----+----+----+----+----
 Fri Dec 31 19:00:00 1999 PST |  1 |  2 |  4 |  4 |  0
 Fri Dec 31 18:00:00 1999 PST |  2 |  1 |  3 |  3 |  0
 Fri Dec 31 17:00:00 1999 PST |  1 |  3 |  2 |  2 |  0
 Fri Dec 31 16:00:00 1999 PST |  1 |  2 |  1 |  1 |  0

-- With selection on segment_by and compressed column
SELECT * FROM test1 WHERE time > '1980-01-01 00:00:00-00' ORDER BY time DESC;
             time             | x1 | x2 | x3 | x4 | x5 
------------------------------+----+----+----+----+----
 Fri Dec 31 19:00:00 1999 PST |  1 |  2 |  4 |  4 |  0
 Fri Dec 31 18:00:00 1999 PST |  2 |  1 |  3 |  3 |  0
 Fri Dec 31 17:00:00 1999 PST |  1 |  3 |  2 |  2 |  0
 Fri Dec 31 16:00:00 1999 PST |  1 |  2 |  1 |  1 |  0

SELECT * FROM test1 WHERE time > '1980-01-01 00:00:00-00' AND x4 > 100 ORDER BY time DESC;
 time | x1 | x2 | x3 | x4 | x5 
------+----+----+----+----+----

-- Without projection
SELECT * FROM test1 ORDER BY time DESC;
             time             | x1 | x2 | x3 | x4 | x5 
------------------------------+----+----+----+----+----
 Fri Dec 31 19:00:00 1999 PST |  1 |  2 |  4 |  4 |  0
 Fri Dec 31 18:00:00 1999 PST |  2 |  1 |  3 |  3 |  0
 Fri Dec 31 17:00:00 1999 PST |  1 |  3 |  2 |  2 |  0
 Fri Dec 31 16:00:00 1999 PST |  1 |  2 |  1 |  1 |  0

-- With projection on time
SELECT time FROM test1 ORDER BY time DESC;
             time             
------------------------------
 Fri Dec 31 19:00:00 1999 PST
 Fri Dec 31 18:00:00 1999 PST
 Fri Dec 31 17:00:00 1999 PST
 Fri Dec 31 16:00:00 1999 PST

-- With projection on x3
SELECT x3 FROM test1 ORDER BY time DESC;
 x3 
----
  4
  3
  2
  1

-- With projection on x3 and time
SELECT x3,time FROM test1 ORDER BY time DESC;
 x3 |             time             
----+------------------------------
  4 | Fri Dec 31 19:00:00 1999 PST
  3 | Fri Dec 31 18:00:00 1999 PST
  2 | Fri Dec 31 17:00:00 1999 PST
  1 | Fri Dec 31 16:00:00 1999 PST

-- With projection on time and x3
SELECT time,x3 FROM test1 ORDER BY time DESC;
             time             | x3 
------------------------------+----
 Fri Dec 31 19:00:00 1999 PST |  4
 Fri Dec 31 18:00:00 1999 PST |  3
 Fri Dec 31 17:00:00 1999 PST |  2
 Fri Dec 31 16:00:00 1999 PST |  1

-- Test with projection and constants
EXPLAIN (verbose, buffers off, costs off) SELECT 1 as one, 2 as two, 3 as three, time, x2 FROM test1 ORDER BY time DESC;
--- QUERY PLAN ---
 Result
   Output: 1, 2, 3, _hyper_1_1_chunk."time", _hyper_1_1_chunk.x2
   ->  Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk
         Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x2
         Batch Sorted Merge: true
         ->  Sort
               Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4
               Sort Key: compress_hyper_2_2_chunk._ts_meta_v2_first_time DESC
               ->  Seq Scan on _timescaledb_internal.compress_hyper_2_2_chunk
                     Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4

SELECT 1 as one, 2 as two, 3 as three, time, x2 FROM test1 ORDER BY time DESC;
 one | two | three |             time             | x2 
-----+-----+-------+------------------------------+----
   1 |   2 |     3 | Fri Dec 31 19:00:00 1999 PST |  2
   1 |   2 |     3 | Fri Dec 31 18:00:00 1999 PST |  1
   1 |   2 |     3 | Fri Dec 31 17:00:00 1999 PST |  3
   1 |   2 |     3 | Fri Dec 31 16:00:00 1999 PST |  2

-- Test with projection and constants
EXPLAIN (verbose, buffers off, costs off) SELECT 1 as one, 2 as two, 3 as three, x2, time FROM test1 ORDER BY time DESC;
--- QUERY PLAN ---
 Result
   Output: 1, 2, 3, _hyper_1_1_chunk.x2, _hyper_1_1_chunk."time"
   ->  Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk
         Output: _hyper_1_1_chunk.x2, _hyper_1_1_chunk."time"
         Batch Sorted Merge: true
         ->  Sort
               Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4
               Sort Key: compress_hyper_2_2_chunk._ts_meta_v2_first_time DESC
               ->  Seq Scan on _timescaledb_internal.compress_hyper_2_2_chunk
                     Output: compress_hyper_2_2_chunk._ts_meta_count, compress_hyper_2_2_chunk.x1, compress_hyper_2_2_chunk.x2, compress_hyper_2_2_chunk.x5, compress_hyper_2_2_chunk._ts_meta_min_1, compress_hyper_2_2_chunk._ts_meta_max_1, compress_hyper_2_2_chunk._ts_meta_v2_first_time, compress_hyper_2_2_chunk._ts_meta_v2_last_time, compress_hyper_2_2_chunk."time", compress_hyper_2_2_chunk._ts_meta_min_2, compress_hyper_2_2_chunk._ts_meta_max_2, compress_hyper_2_2_chunk._ts_meta_v2_first_x3, compress_hyper_2_2_chunk._ts_meta_v2_last_x3, compress_hyper_2_2_chunk.x3, compress_hyper_2_2_chunk._ts_meta_min_3, compress_hyper_2_2_chunk._ts_meta_max_3, compress_hyper_2_2_chunk._ts_meta_v2_first_x4, compress_hyper_2_2_chunk._ts_meta_v2_last_x4, compress_hyper_2_2_chunk.x4

SELECT 1 as one, 2 as two, 3 as three, x2, time FROM test1 ORDER BY time DESC;
 one | two | three | x2 |             time             
-----+-----+-------+----+------------------------------
   1 |   2 |     3 |  2 | Fri Dec 31 19:00:00 1999 PST
   1 |   2 |     3 |  1 | Fri Dec 31 18:00:00 1999 PST
   1 |   2 |     3 |  3 | Fri Dec 31 17:00:00 1999 PST
   1 |   2 |     3 |  2 | Fri Dec 31 16:00:00 1999 PST

-- With projection and selection on compressed column (value smaller as max value for some batches, so batches are opened and filter has to be applied)
SELECT x4 FROM test1 WHERE x4 > 2 ORDER BY time DESC;
 x4 
----
  4
  3

set timescaledb.debug_require_batch_sorted_merge to 'forbid';
-- Aggregation with count
SELECT count(*) FROM test1;
 count 
-------
     4

-- Test with default values
ALTER TABLE test1 ADD COLUMN c1 int;
ALTER TABLE test1 ADD COLUMN c2 int NOT NULL DEFAULT 42;
set timescaledb.debug_require_batch_sorted_merge to 'force';
SELECT * FROM test1 ORDER BY time DESC;
             time             | x1 | x2 | x3 | x4 | x5 | c1 | c2 
------------------------------+----+----+----+----+----+----+----
 Fri Dec 31 19:00:00 1999 PST |  1 |  2 |  4 |  4 |  0 |    | 42
 Fri Dec 31 18:00:00 1999 PST |  2 |  1 |  3 |  3 |  0 |    | 42
 Fri Dec 31 17:00:00 1999 PST |  1 |  3 |  2 |  2 |  0 |    | 42
 Fri Dec 31 16:00:00 1999 PST |  1 |  2 |  1 |  1 |  0 |    | 42

-- Recompress
SELECT decompress_chunk(i) FROM show_chunks('test1') i;
            decompress_chunk            
----------------------------------------
 _timescaledb_internal._hyper_1_1_chunk

SELECT compress_chunk(i) FROM show_chunks('test1') i;
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_1_1_chunk

ANALYZE test1;
-- Test with a changed physical layout
-- build_physical_tlist() can not be used for the scan on the compressed chunk anymore
SELECT * FROM test1 ORDER BY time DESC;
             time             | x1 | x2 | x3 | x4 | x5 | c1 | c2 
------------------------------+----+----+----+----+----+----+----
 Fri Dec 31 19:00:00 1999 PST |  1 |  2 |  4 |  4 |  0 |    | 42
 Fri Dec 31 18:00:00 1999 PST |  2 |  1 |  3 |  3 |  0 |    | 42
 Fri Dec 31 17:00:00 1999 PST |  1 |  3 |  2 |  2 |  0 |    | 42
 Fri Dec 31 16:00:00 1999 PST |  1 |  2 |  1 |  1 |  0 |    | 42

ALTER TABLE test1 DROP COLUMN c2;
SELECT * FROM test1 ORDER BY time DESC;
             time             | x1 | x2 | x3 | x4 | x5 | c1 
------------------------------+----+----+----+----+----+----
 Fri Dec 31 19:00:00 1999 PST |  1 |  2 |  4 |  4 |  0 |   
 Fri Dec 31 18:00:00 1999 PST |  2 |  1 |  3 |  3 |  0 |   
 Fri Dec 31 17:00:00 1999 PST |  1 |  3 |  2 |  2 |  0 |   
 Fri Dec 31 16:00:00 1999 PST |  1 |  2 |  1 |  1 |  0 |   

-- Test with a re-created column
ALTER TABLE test1 ADD COLUMN c2 int NOT NULL DEFAULT 43;
SELECT * FROM test1 ORDER BY time DESC;
             time             | x1 | x2 | x3 | x4 | x5 | c1 | c2 
------------------------------+----+----+----+----+----+----+----
 Fri Dec 31 19:00:00 1999 PST |  1 |  2 |  4 |  4 |  0 |    | 43
 Fri Dec 31 18:00:00 1999 PST |  2 |  1 |  3 |  3 |  0 |    | 43
 Fri Dec 31 17:00:00 1999 PST |  1 |  3 |  2 |  2 |  0 |    | 43
 Fri Dec 31 16:00:00 1999 PST |  1 |  2 |  1 |  1 |  0 |    | 43

-- Test with the recreated column
:PREFIX
SELECT * FROM test1 ORDER BY time DESC;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk (actual rows=4.00 loops=1)
   Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5, _hyper_1_1_chunk.c1, _hyper_1_1_chunk.c2
   Batch Sorted Merge: true
   Bulk Decompression: false
   ->  Sort (actual rows=3.00 loops=1)
         Output: compress_hyper_2_7_chunk."time", compress_hyper_2_7_chunk._ts_meta_v2_last_time, compress_hyper_2_7_chunk._ts_meta_v2_first_time, compress_hyper_2_7_chunk.x1, compress_hyper_2_7_chunk.x2, compress_hyper_2_7_chunk.x3, compress_hyper_2_7_chunk._ts_meta_v2_first_x3, compress_hyper_2_7_chunk._ts_meta_v2_last_x3, compress_hyper_2_7_chunk.x4, compress_hyper_2_7_chunk._ts_meta_v2_first_x4, compress_hyper_2_7_chunk._ts_meta_v2_last_x4, compress_hyper_2_7_chunk.x5, compress_hyper_2_7_chunk.c1, compress_hyper_2_7_chunk.c2, compress_hyper_2_7_chunk._ts_meta_count
         Sort Key: compress_hyper_2_7_chunk._ts_meta_v2_first_time DESC
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_2_7_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_2_7_chunk."time", compress_hyper_2_7_chunk._ts_meta_v2_last_time, compress_hyper_2_7_chunk._ts_meta_v2_first_time, compress_hyper_2_7_chunk.x1, compress_hyper_2_7_chunk.x2, compress_hyper_2_7_chunk.x3, compress_hyper_2_7_chunk._ts_meta_v2_first_x3, compress_hyper_2_7_chunk._ts_meta_v2_last_x3, compress_hyper_2_7_chunk.x4, compress_hyper_2_7_chunk._ts_meta_v2_first_x4, compress_hyper_2_7_chunk._ts_meta_v2_last_x4, compress_hyper_2_7_chunk.x5, compress_hyper_2_7_chunk.c1, compress_hyper_2_7_chunk.c2, compress_hyper_2_7_chunk._ts_meta_count

SELECT * FROM test1 ORDER BY time DESC;
             time             | x1 | x2 | x3 | x4 | x5 | c1 | c2 
------------------------------+----+----+----+----+----+----+----
 Fri Dec 31 19:00:00 1999 PST |  1 |  2 |  4 |  4 |  0 |    | 43
 Fri Dec 31 18:00:00 1999 PST |  2 |  1 |  3 |  3 |  0 |    | 43
 Fri Dec 31 17:00:00 1999 PST |  1 |  3 |  2 |  2 |  0 |    | 43
 Fri Dec 31 16:00:00 1999 PST |  1 |  2 |  1 |  1 |  0 |    | 43

-- Test with projection and recreated column
:PREFIX
SELECT time, x2, x1, c2 FROM test1 ORDER BY time DESC;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk (actual rows=4.00 loops=1)
   Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x1, _hyper_1_1_chunk.c2
   Batch Sorted Merge: true
   Bulk Decompression: false
   ->  Sort (actual rows=3.00 loops=1)
         Output: compress_hyper_2_7_chunk."time", compress_hyper_2_7_chunk._ts_meta_v2_last_time, compress_hyper_2_7_chunk._ts_meta_v2_first_time, compress_hyper_2_7_chunk.x2, compress_hyper_2_7_chunk.x1, compress_hyper_2_7_chunk.c2, compress_hyper_2_7_chunk._ts_meta_count
         Sort Key: compress_hyper_2_7_chunk._ts_meta_v2_first_time DESC
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_2_7_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_2_7_chunk."time", compress_hyper_2_7_chunk._ts_meta_v2_last_time, compress_hyper_2_7_chunk._ts_meta_v2_first_time, compress_hyper_2_7_chunk.x2, compress_hyper_2_7_chunk.x1, compress_hyper_2_7_chunk.c2, compress_hyper_2_7_chunk._ts_meta_count

SELECT time, x2, x1, c2 FROM test1 ORDER BY time DESC;
             time             | x2 | x1 | c2 
------------------------------+----+----+----
 Fri Dec 31 19:00:00 1999 PST |  2 |  1 | 43
 Fri Dec 31 18:00:00 1999 PST |  1 |  2 | 43
 Fri Dec 31 17:00:00 1999 PST |  3 |  1 | 43
 Fri Dec 31 16:00:00 1999 PST |  2 |  1 | 43

-- Test with projection and recreated column
:PREFIX
SELECT x2, x1, c2, time FROM test1 ORDER BY time DESC;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk (actual rows=4.00 loops=1)
   Output: _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x1, _hyper_1_1_chunk.c2, _hyper_1_1_chunk."time"
   Batch Sorted Merge: true
   Bulk Decompression: false
   ->  Sort (actual rows=3.00 loops=1)
         Output: compress_hyper_2_7_chunk.x2, compress_hyper_2_7_chunk.x1, compress_hyper_2_7_chunk.c2, compress_hyper_2_7_chunk."time", compress_hyper_2_7_chunk._ts_meta_v2_last_time, compress_hyper_2_7_chunk._ts_meta_v2_first_time, compress_hyper_2_7_chunk._ts_meta_count
         Sort Key: compress_hyper_2_7_chunk._ts_meta_v2_first_time DESC
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_2_7_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_2_7_chunk.x2, compress_hyper_2_7_chunk.x1, compress_hyper_2_7_chunk.c2, compress_hyper_2_7_chunk."time", compress_hyper_2_7_chunk._ts_meta_v2_last_time, compress_hyper_2_7_chunk._ts_meta_v2_first_time, compress_hyper_2_7_chunk._ts_meta_count

SELECT x2, x1, c2, time FROM test1 ORDER BY time DESC;
 x2 | x1 | c2 |             time             
----+----+----+------------------------------
  2 |  1 | 43 | Fri Dec 31 19:00:00 1999 PST
  1 |  2 | 43 | Fri Dec 31 18:00:00 1999 PST
  3 |  1 | 43 | Fri Dec 31 17:00:00 1999 PST
  2 |  1 | 43 | Fri Dec 31 16:00:00 1999 PST

-- Test with projection, constants and recreated column
:PREFIX
SELECT 1 as one, 2 as two, 3 as three, x2, x1, c2, time FROM test1 ORDER BY time DESC;
--- QUERY PLAN ---
 Result (actual rows=4.00 loops=1)
   Output: 1, 2, 3, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x1, _hyper_1_1_chunk.c2, _hyper_1_1_chunk."time"
   ->  Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk (actual rows=4.00 loops=1)
         Output: _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x1, _hyper_1_1_chunk.c2, _hyper_1_1_chunk."time"
         Batch Sorted Merge: true
         Bulk Decompression: false
         ->  Sort (actual rows=3.00 loops=1)
               Output: compress_hyper_2_7_chunk.x2, compress_hyper_2_7_chunk.x1, compress_hyper_2_7_chunk.c2, compress_hyper_2_7_chunk."time", compress_hyper_2_7_chunk._ts_meta_v2_last_time, compress_hyper_2_7_chunk._ts_meta_v2_first_time, compress_hyper_2_7_chunk._ts_meta_count
               Sort Key: compress_hyper_2_7_chunk._ts_meta_v2_first_time DESC
               Sort Method: quicksort 
               ->  Seq Scan on _timescaledb_internal.compress_hyper_2_7_chunk (actual rows=3.00 loops=1)
                     Output: compress_hyper_2_7_chunk.x2, compress_hyper_2_7_chunk.x1, compress_hyper_2_7_chunk.c2, compress_hyper_2_7_chunk."time", compress_hyper_2_7_chunk._ts_meta_v2_last_time, compress_hyper_2_7_chunk._ts_meta_v2_first_time, compress_hyper_2_7_chunk._ts_meta_count

SELECT 1 as one, 2 as two, 3 as three, x2, x1, c2, time FROM test1 ORDER BY time DESC;
 one | two | three | x2 | x1 | c2 |             time             
-----+-----+-------+----+----+----+------------------------------
   1 |   2 |     3 |  2 |  1 | 43 | Fri Dec 31 19:00:00 1999 PST
   1 |   2 |     3 |  1 |  2 | 43 | Fri Dec 31 18:00:00 1999 PST
   1 |   2 |     3 |  3 |  1 | 43 | Fri Dec 31 17:00:00 1999 PST
   1 |   2 |     3 |  2 |  1 | 43 | Fri Dec 31 16:00:00 1999 PST

-- Test with null values: should optimize with firstlast index
set timescaledb.debug_require_batch_sorted_merge to 'force';
SELECT time, x2 FROM test_with_defined_null ORDER BY x2 ASC NULLS FIRST;
             time             | x2 
------------------------------+----
 Sat Jan 01 00:00:00 2000 PST |   
 Sat Jan 01 00:00:00 2000 PST |   
 Sat Jan 01 00:00:00 2000 PST |  1
 Sat Jan 01 00:00:00 2000 PST |  2

SELECT time, x2 FROM test_with_defined_null ORDER BY x2 DESC NULLS LAST;
             time             | x2 
------------------------------+----
 Sat Jan 01 00:00:00 2000 PST |  2
 Sat Jan 01 00:00:00 2000 PST |  1
 Sat Jan 01 00:00:00 2000 PST |   
 Sat Jan 01 00:00:00 2000 PST |   

-- should not optimize (NULL order wrong)
set timescaledb.debug_require_batch_sorted_merge to 'forbid';
SELECT time, x2 FROM test_with_defined_null ORDER BY x2 ASC NULLS LAST;
             time             | x2 
------------------------------+----
 Sat Jan 01 00:00:00 2000 PST |  1
 Sat Jan 01 00:00:00 2000 PST |  2
 Sat Jan 01 00:00:00 2000 PST |   
 Sat Jan 01 00:00:00 2000 PST |   

SELECT time, x2 FROM test_with_defined_null ORDER BY x2 DESC NULLS FIRST;
             time             | x2 
------------------------------+----
 Sat Jan 01 00:00:00 2000 PST |   
 Sat Jan 01 00:00:00 2000 PST |   
 Sat Jan 01 00:00:00 2000 PST |  2
 Sat Jan 01 00:00:00 2000 PST |  1

------
-- Tests based on compressed chunk state
------
-- Should be optimized
set timescaledb.debug_require_batch_sorted_merge to 'force';
:PREFIX
SELECT * FROM test1 ORDER BY time ASC NULLS LAST;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk (actual rows=4.00 loops=1)
   Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5, _hyper_1_1_chunk.c1, _hyper_1_1_chunk.c2
   Batch Sorted Merge: true
   Reverse: true
   Bulk Decompression: false
   ->  Sort (actual rows=3.00 loops=1)
         Output: compress_hyper_2_7_chunk."time", compress_hyper_2_7_chunk._ts_meta_v2_last_time, compress_hyper_2_7_chunk._ts_meta_v2_first_time, compress_hyper_2_7_chunk.x1, compress_hyper_2_7_chunk.x2, compress_hyper_2_7_chunk.x3, compress_hyper_2_7_chunk._ts_meta_v2_first_x3, compress_hyper_2_7_chunk._ts_meta_v2_last_x3, compress_hyper_2_7_chunk.x4, compress_hyper_2_7_chunk._ts_meta_v2_first_x4, compress_hyper_2_7_chunk._ts_meta_v2_last_x4, compress_hyper_2_7_chunk.x5, compress_hyper_2_7_chunk.c1, compress_hyper_2_7_chunk.c2, compress_hyper_2_7_chunk._ts_meta_count
         Sort Key: compress_hyper_2_7_chunk._ts_meta_v2_last_time
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_2_7_chunk (actual rows=3.00 loops=1)
               Output: compress_hyper_2_7_chunk."time", compress_hyper_2_7_chunk._ts_meta_v2_last_time, compress_hyper_2_7_chunk._ts_meta_v2_first_time, compress_hyper_2_7_chunk.x1, compress_hyper_2_7_chunk.x2, compress_hyper_2_7_chunk.x3, compress_hyper_2_7_chunk._ts_meta_v2_first_x3, compress_hyper_2_7_chunk._ts_meta_v2_last_x3, compress_hyper_2_7_chunk.x4, compress_hyper_2_7_chunk._ts_meta_v2_first_x4, compress_hyper_2_7_chunk._ts_meta_v2_last_x4, compress_hyper_2_7_chunk.x5, compress_hyper_2_7_chunk.c1, compress_hyper_2_7_chunk.c2, compress_hyper_2_7_chunk._ts_meta_count

BEGIN TRANSACTION;
INSERT INTO test1 (time, x1, x2, x3, x4, x5) values('2000-01-01 02:01:00-00', 10, 20, 30, 40, 50);
-- Should be optimized using a merge append path between the compressed and uncompressed part of the chunk
:PREFIX
SELECT * FROM test1 ORDER BY time ASC NULLS LAST;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on public.test1 (actual rows=5.00 loops=1)
   Output: test1."time", test1.x1, test1.x2, test1.x3, test1.x4, test1.x5, test1.c1, test1.c2
   Order: test1."time"
   Startup Exclusion: false
   Runtime Exclusion: false
   ->  Merge Append (actual rows=5.00 loops=1)
         Sort Key: test1."time"
         ->  Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_1_1_chunk (actual rows=4.00 loops=1)
               Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5, _hyper_1_1_chunk.c1, _hyper_1_1_chunk.c2
               Chunk Status: PARTIAL
               Batch Sorted Merge: true
               Reverse: true
               Bulk Decompression: false
               ->  Sort (actual rows=3.00 loops=1)
                     Output: compress_hyper_2_7_chunk."time", compress_hyper_2_7_chunk._ts_meta_v2_last_time, compress_hyper_2_7_chunk._ts_meta_v2_first_time, compress_hyper_2_7_chunk.x1, compress_hyper_2_7_chunk.x2, compress_hyper_2_7_chunk.x3, compress_hyper_2_7_chunk._ts_meta_v2_first_x3, compress_hyper_2_7_chunk._ts_meta_v2_last_x3, compress_hyper_2_7_chunk.x4, compress_hyper_2_7_chunk._ts_meta_v2_first_x4, compress_hyper_2_7_chunk._ts_meta_v2_last_x4, compress_hyper_2_7_chunk.x5, compress_hyper_2_7_chunk.c1, compress_hyper_2_7_chunk.c2, compress_hyper_2_7_chunk._ts_meta_count
                     Sort Key: compress_hyper_2_7_chunk._ts_meta_v2_last_time
                     Sort Method: quicksort 
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_2_7_chunk (actual rows=3.00 loops=1)
                           Output: compress_hyper_2_7_chunk."time", compress_hyper_2_7_chunk._ts_meta_v2_last_time, compress_hyper_2_7_chunk._ts_meta_v2_first_time, compress_hyper_2_7_chunk.x1, compress_hyper_2_7_chunk.x2, compress_hyper_2_7_chunk.x3, compress_hyper_2_7_chunk._ts_meta_v2_first_x3, compress_hyper_2_7_chunk._ts_meta_v2_last_x3, compress_hyper_2_7_chunk.x4, compress_hyper_2_7_chunk._ts_meta_v2_first_x4, compress_hyper_2_7_chunk._ts_meta_v2_last_x4, compress_hyper_2_7_chunk.x5, compress_hyper_2_7_chunk.c1, compress_hyper_2_7_chunk.c2, compress_hyper_2_7_chunk._ts_meta_count
         ->  Index Scan Backward using _hyper_1_1_chunk_test1_time_idx on _timescaledb_internal._hyper_1_1_chunk (actual rows=1.00 loops=1)
               Output: _hyper_1_1_chunk."time", _hyper_1_1_chunk.x1, _hyper_1_1_chunk.x2, _hyper_1_1_chunk.x3, _hyper_1_1_chunk.x4, _hyper_1_1_chunk.x5, _hyper_1_1_chunk.c1, _hyper_1_1_chunk.c2

-- The inserted value should be visible
SELECT * FROM test1 ORDER BY time ASC NULLS LAST;
             time             | x1 | x2 | x3 | x4 | x5 | c1 | c2 
------------------------------+----+----+----+----+----+----+----
 Fri Dec 31 16:00:00 1999 PST |  1 |  2 |  1 |  1 |  0 |    | 43
 Fri Dec 31 17:00:00 1999 PST |  1 |  3 |  2 |  2 |  0 |    | 43
 Fri Dec 31 18:00:00 1999 PST |  2 |  1 |  3 |  3 |  0 |    | 43
 Fri Dec 31 18:01:00 1999 PST | 10 | 20 | 30 | 40 | 50 |    | 43
 Fri Dec 31 19:00:00 1999 PST |  1 |  2 |  4 |  4 |  0 |    | 43

ROLLBACK;
------
-- Tests on a larger relation
------
set timescaledb.debug_require_batch_sorted_merge to 'allow';
CREATE UNLOGGED TABLE sensor_data (
time timestamptz NOT NULL,
sensor_id integer NOT NULL,
cpu double precision NULL,
temperature double precision NULL);
SELECT FROM create_hypertable('sensor_data','time');
--

INSERT INTO sensor_data
SELECT
time + (INTERVAL '1 minute' * random()) AS time,
sensor_id,
random() AS cpu,
random() * 100 AS temperature
FROM
generate_series('1980-01-01 00:00:00-00', '1980-02-01 00:00:00-00', INTERVAL '10 minute') AS g1(time),
generate_series(1, 100, 1 ) AS g2(sensor_id)
ORDER BY
time;
ALTER TABLE sensor_data SET (timescaledb.compress, timescaledb.compress_segmentby='sensor_id', timescaledb.compress_orderby = 'time DESC');
SELECT add_compression_policy('sensor_data','1 minute'::INTERVAL);
 add_compression_policy 
------------------------
                   1000

SELECT compress_chunk(i) FROM show_chunks('sensor_data') i;
             compress_chunk              
-----------------------------------------
 _timescaledb_internal._hyper_7_8_chunk
 _timescaledb_internal._hyper_7_9_chunk
 _timescaledb_internal._hyper_7_10_chunk
 _timescaledb_internal._hyper_7_11_chunk
 _timescaledb_internal._hyper_7_12_chunk
 _timescaledb_internal._hyper_7_13_chunk

VACUUM ANALYZE sensor_data;
-- Ensure the optimization is used for queries on this table
:PREFIX
SELECT * FROM sensor_data ORDER BY time DESC LIMIT 1;
--- QUERY PLAN ---
 Limit (actual rows=1.00 loops=1)
   Output: sensor_data."time", sensor_data.sensor_id, sensor_data.cpu, sensor_data.temperature
   ->  Custom Scan (ChunkAppend) on public.sensor_data (actual rows=1.00 loops=1)
         Output: sensor_data."time", sensor_data.sensor_id, sensor_data.cpu, sensor_data.temperature
         Order: sensor_data."time" DESC
         Startup Exclusion: false
         Runtime Exclusion: false
         ->  Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_7_13_chunk (actual rows=1.00 loops=1)
               Output: _hyper_7_13_chunk."time", _hyper_7_13_chunk.sensor_id, _hyper_7_13_chunk.cpu, _hyper_7_13_chunk.temperature
               Batch Sorted Merge: true
               Bulk Decompression: false
               ->  Sort (actual rows=2.00 loops=1)
                     Output: compress_hyper_8_19_chunk._ts_meta_count, compress_hyper_8_19_chunk.sensor_id, compress_hyper_8_19_chunk._ts_meta_min_1, compress_hyper_8_19_chunk._ts_meta_max_1, compress_hyper_8_19_chunk._ts_meta_v2_first_time, compress_hyper_8_19_chunk._ts_meta_v2_last_time, compress_hyper_8_19_chunk."time", compress_hyper_8_19_chunk.cpu, compress_hyper_8_19_chunk.temperature
                     Sort Key: compress_hyper_8_19_chunk._ts_meta_v2_first_time DESC
                     Sort Method: quicksort 
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_8_19_chunk (actual rows=100.00 loops=1)
                           Output: compress_hyper_8_19_chunk._ts_meta_count, compress_hyper_8_19_chunk.sensor_id, compress_hyper_8_19_chunk._ts_meta_min_1, compress_hyper_8_19_chunk._ts_meta_max_1, compress_hyper_8_19_chunk._ts_meta_v2_first_time, compress_hyper_8_19_chunk._ts_meta_v2_last_time, compress_hyper_8_19_chunk."time", compress_hyper_8_19_chunk.cpu, compress_hyper_8_19_chunk.temperature
         ->  Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_7_12_chunk (never executed)
               Output: _hyper_7_12_chunk."time", _hyper_7_12_chunk.sensor_id, _hyper_7_12_chunk.cpu, _hyper_7_12_chunk.temperature
               Batch Sorted Merge: true
               Bulk Decompression: false
               ->  Sort (never executed)
                     Output: compress_hyper_8_18_chunk._ts_meta_count, compress_hyper_8_18_chunk.sensor_id, compress_hyper_8_18_chunk._ts_meta_min_1, compress_hyper_8_18_chunk._ts_meta_max_1, compress_hyper_8_18_chunk._ts_meta_v2_first_time, compress_hyper_8_18_chunk._ts_meta_v2_last_time, compress_hyper_8_18_chunk."time", compress_hyper_8_18_chunk.cpu, compress_hyper_8_18_chunk.temperature
                     Sort Key: compress_hyper_8_18_chunk._ts_meta_v2_first_time DESC
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_8_18_chunk (never executed)
                           Output: compress_hyper_8_18_chunk._ts_meta_count, compress_hyper_8_18_chunk.sensor_id, compress_hyper_8_18_chunk._ts_meta_min_1, compress_hyper_8_18_chunk._ts_meta_max_1, compress_hyper_8_18_chunk._ts_meta_v2_first_time, compress_hyper_8_18_chunk._ts_meta_v2_last_time, compress_hyper_8_18_chunk."time", compress_hyper_8_18_chunk.cpu, compress_hyper_8_18_chunk.temperature
         ->  Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_7_11_chunk (never executed)
               Output: _hyper_7_11_chunk."time", _hyper_7_11_chunk.sensor_id, _hyper_7_11_chunk.cpu, _hyper_7_11_chunk.temperature
               Batch Sorted Merge: true
               Bulk Decompression: false
               ->  Sort (never executed)
                     Output: compress_hyper_8_17_chunk._ts_meta_count, compress_hyper_8_17_chunk.sensor_id, compress_hyper_8_17_chunk._ts_meta_min_1, compress_hyper_8_17_chunk._ts_meta_max_1, compress_hyper_8_17_chunk._ts_meta_v2_first_time, compress_hyper_8_17_chunk._ts_meta_v2_last_time, compress_hyper_8_17_chunk."time", compress_hyper_8_17_chunk.cpu, compress_hyper_8_17_chunk.temperature
                     Sort Key: compress_hyper_8_17_chunk._ts_meta_v2_first_time DESC
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_8_17_chunk (never executed)
                           Output: compress_hyper_8_17_chunk._ts_meta_count, compress_hyper_8_17_chunk.sensor_id, compress_hyper_8_17_chunk._ts_meta_min_1, compress_hyper_8_17_chunk._ts_meta_max_1, compress_hyper_8_17_chunk._ts_meta_v2_first_time, compress_hyper_8_17_chunk._ts_meta_v2_last_time, compress_hyper_8_17_chunk."time", compress_hyper_8_17_chunk.cpu, compress_hyper_8_17_chunk.temperature
         ->  Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_7_10_chunk (never executed)
               Output: _hyper_7_10_chunk."time", _hyper_7_10_chunk.sensor_id, _hyper_7_10_chunk.cpu, _hyper_7_10_chunk.temperature
               Batch Sorted Merge: true
               Bulk Decompression: false
               ->  Sort (never executed)
                     Output: compress_hyper_8_16_chunk._ts_meta_count, compress_hyper_8_16_chunk.sensor_id, compress_hyper_8_16_chunk._ts_meta_min_1, compress_hyper_8_16_chunk._ts_meta_max_1, compress_hyper_8_16_chunk._ts_meta_v2_first_time, compress_hyper_8_16_chunk._ts_meta_v2_last_time, compress_hyper_8_16_chunk."time", compress_hyper_8_16_chunk.cpu, compress_hyper_8_16_chunk.temperature
                     Sort Key: compress_hyper_8_16_chunk._ts_meta_v2_first_time DESC
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_8_16_chunk (never executed)
                           Output: compress_hyper_8_16_chunk._ts_meta_count, compress_hyper_8_16_chunk.sensor_id, compress_hyper_8_16_chunk._ts_meta_min_1, compress_hyper_8_16_chunk._ts_meta_max_1, compress_hyper_8_16_chunk._ts_meta_v2_first_time, compress_hyper_8_16_chunk._ts_meta_v2_last_time, compress_hyper_8_16_chunk."time", compress_hyper_8_16_chunk.cpu, compress_hyper_8_16_chunk.temperature
         ->  Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_7_9_chunk (never executed)
               Output: _hyper_7_9_chunk."time", _hyper_7_9_chunk.sensor_id, _hyper_7_9_chunk.cpu, _hyper_7_9_chunk.temperature
               Batch Sorted Merge: true
               Bulk Decompression: false
               ->  Sort (never executed)
                     Output: compress_hyper_8_15_chunk._ts_meta_count, compress_hyper_8_15_chunk.sensor_id, compress_hyper_8_15_chunk._ts_meta_min_1, compress_hyper_8_15_chunk._ts_meta_max_1, compress_hyper_8_15_chunk._ts_meta_v2_first_time, compress_hyper_8_15_chunk._ts_meta_v2_last_time, compress_hyper_8_15_chunk."time", compress_hyper_8_15_chunk.cpu, compress_hyper_8_15_chunk.temperature
                     Sort Key: compress_hyper_8_15_chunk._ts_meta_v2_first_time DESC
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_8_15_chunk (never executed)
                           Output: compress_hyper_8_15_chunk._ts_meta_count, compress_hyper_8_15_chunk.sensor_id, compress_hyper_8_15_chunk._ts_meta_min_1, compress_hyper_8_15_chunk._ts_meta_max_1, compress_hyper_8_15_chunk._ts_meta_v2_first_time, compress_hyper_8_15_chunk._ts_meta_v2_last_time, compress_hyper_8_15_chunk."time", compress_hyper_8_15_chunk.cpu, compress_hyper_8_15_chunk.temperature
         ->  Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_7_8_chunk (never executed)
               Output: _hyper_7_8_chunk."time", _hyper_7_8_chunk.sensor_id, _hyper_7_8_chunk.cpu, _hyper_7_8_chunk.temperature
               Batch Sorted Merge: true
               Bulk Decompression: false
               ->  Sort (never executed)
                     Output: compress_hyper_8_14_chunk._ts_meta_count, compress_hyper_8_14_chunk.sensor_id, compress_hyper_8_14_chunk._ts_meta_min_1, compress_hyper_8_14_chunk._ts_meta_max_1, compress_hyper_8_14_chunk._ts_meta_v2_first_time, compress_hyper_8_14_chunk._ts_meta_v2_last_time, compress_hyper_8_14_chunk."time", compress_hyper_8_14_chunk.cpu, compress_hyper_8_14_chunk.temperature
                     Sort Key: compress_hyper_8_14_chunk._ts_meta_v2_first_time DESC
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_8_14_chunk (never executed)
                           Output: compress_hyper_8_14_chunk._ts_meta_count, compress_hyper_8_14_chunk.sensor_id, compress_hyper_8_14_chunk._ts_meta_min_1, compress_hyper_8_14_chunk._ts_meta_max_1, compress_hyper_8_14_chunk._ts_meta_v2_first_time, compress_hyper_8_14_chunk._ts_meta_v2_last_time, compress_hyper_8_14_chunk."time", compress_hyper_8_14_chunk.cpu, compress_hyper_8_14_chunk.temperature

-- Verify that we produce the same order without and with the optimization
CREATE PROCEDURE order_test(query text) LANGUAGE plpgsql AS $$
        DECLARE
            count integer;
        BEGIN

        SET timescaledb.enable_decompression_sorted_merge = 0;
        EXECUTE format('CREATE TEMP TABLE temp_data1 AS %s;', query);

        SET timescaledb.enable_decompression_sorted_merge = 1;
        EXECUTE format('CREATE TEMP TABLE temp_data2 AS %s;', query);

        CREATE TEMP TABLE temp_data3 AS (
            SELECT ctid as new_id, * FROM temp_data1 UNION ALL SELECT ctid as new_id, * FROM temp_data2
        );

        count := (SELECT COUNT(*) FROM (SELECT COUNT(*) FROM temp_data3 GROUP BY time, new_id HAVING COUNT(*) != 2) AS s);

        IF count > 0 THEN
            RAISE EXCEPTION 'Detected different order with and without the optimization %', count;
        END IF;

        -- Drop old tables
        DROP TABLE temp_data1;
        DROP TABLE temp_data2;
        DROP TABLE temp_data3;

        END;
$$;
CALL order_test('SELECT * FROM sensor_data ORDER BY time DESC');
CALL order_test('SELECT * FROM sensor_data ORDER BY time DESC LIMIT 100');
CALL order_test('SELECT * FROM sensor_data ORDER BY time ASC NULLS FIRST');
CALL order_test('SELECT * FROM sensor_data ORDER BY time ASC NULLS FIRST LIMIT 100');
CALL order_test('SELECT * FROM test1 ORDER BY time DESC');
CALL order_test('SELECT * FROM test1 ORDER BY time ASC NULLS LAST');
------
-- Test window functions
------
CREATE TABLE insert_test(id INT);
INSERT INTO insert_test SELECT time_bucket_gapfill(1,time,1,5) FROM (VALUES (1),(2)) v(time) GROUP BY 1 ORDER BY 1;
SELECT * FROM insert_test AS ref_0
WHERE EXISTS (
    SELECT
        sum(ref_0.id) OVER (partition by ref_0.id ORDER BY ref_0.id,ref_0.id,sample_0.time)
    FROM
        sensor_data AS sample_0
    WHERE (1 > sample_0.temperature)
);
 id 
----
  1
  2
  3
  4

------
-- Test enabling and disabling the optimization based on costs
------
CREATE TABLE test_costs (
time timestamptz NOT NULL,
segment_by integer NOT NULL,
x1 integer NOT NULL);
SELECT FROM create_hypertable('test_costs', 'time');
--

ALTER TABLE test_costs SET (timescaledb.compress, timescaledb.compress_segmentby='segment_by', timescaledb.compress_orderby = 'time DESC, x1');
-- Create 100 segments
INSERT INTO test_costs
SELECT
time,
segment_by,
random() as x1
FROM
generate_series(1, 100, 1) AS g2(segment_by),
generate_series('2000-01-01 02:01:00-00', '2000-01-06 00:00:00+00', interval '1 hour') time
ORDER BY time;
SELECT compress_chunk(i) FROM show_chunks('test_costs') i;
             compress_chunk              
-----------------------------------------
 _timescaledb_internal._hyper_9_20_chunk

ANALYZE test_costs;
-- Number of segments
SELECT count(*) FROM (SELECT segment_by from test_costs group by segment_by) AS s;
 count 
-------
   100

-- Test query plan (should be optimized due to 100 different segments)
:PREFIX
SELECT time, segment_by, x1 FROM test_costs ORDER BY time DESC;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_9_20_chunk (actual rows=11800.00 loops=1)
   Output: _hyper_9_20_chunk."time", _hyper_9_20_chunk.segment_by, _hyper_9_20_chunk.x1
   Batch Sorted Merge: true
   Bulk Decompression: false
   ->  Sort (actual rows=100.00 loops=1)
         Output: compress_hyper_10_21_chunk._ts_meta_count, compress_hyper_10_21_chunk.segment_by, compress_hyper_10_21_chunk._ts_meta_min_1, compress_hyper_10_21_chunk._ts_meta_max_1, compress_hyper_10_21_chunk._ts_meta_v2_first_time, compress_hyper_10_21_chunk._ts_meta_v2_last_time, compress_hyper_10_21_chunk."time", compress_hyper_10_21_chunk._ts_meta_min_2, compress_hyper_10_21_chunk._ts_meta_max_2, compress_hyper_10_21_chunk._ts_meta_v2_first_x1, compress_hyper_10_21_chunk._ts_meta_v2_last_x1, compress_hyper_10_21_chunk.x1
         Sort Key: compress_hyper_10_21_chunk._ts_meta_v2_first_time DESC
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_10_21_chunk (actual rows=100.00 loops=1)
               Output: compress_hyper_10_21_chunk._ts_meta_count, compress_hyper_10_21_chunk.segment_by, compress_hyper_10_21_chunk._ts_meta_min_1, compress_hyper_10_21_chunk._ts_meta_max_1, compress_hyper_10_21_chunk._ts_meta_v2_first_time, compress_hyper_10_21_chunk._ts_meta_v2_last_time, compress_hyper_10_21_chunk."time", compress_hyper_10_21_chunk._ts_meta_min_2, compress_hyper_10_21_chunk._ts_meta_max_2, compress_hyper_10_21_chunk._ts_meta_v2_first_x1, compress_hyper_10_21_chunk._ts_meta_v2_last_x1, compress_hyper_10_21_chunk.x1

-- Decompress chunk
SELECT decompress_chunk(i) FROM show_chunks('test_costs') i;
            decompress_chunk             
-----------------------------------------
 _timescaledb_internal._hyper_9_20_chunk

-- Add 900 segments (1000 segments total)
INSERT INTO test_costs
SELECT
time,
segment_by,
random() as x1
FROM
generate_series(100, 1000, 1) AS g2(segment_by),
generate_series('2000-01-01 02:01:00-00', '2000-01-06 00:00:00+00', interval '1 hour') time
ORDER BY time;
-- Recompress chunk
SELECT compress_chunk(i) FROM show_chunks('test_costs') i;
             compress_chunk              
-----------------------------------------
 _timescaledb_internal._hyper_9_20_chunk

ANALYZE test_costs;
-- Number of segments
SELECT count(*) FROM (SELECT segment_by from test_costs group by segment_by) AS s;
 count 
-------
  1000

-- Test query plan (should not be optimized due to 1000 different segments)
set timescaledb.debug_require_batch_sorted_merge to 'forbid';
:PREFIX
SELECT time, segment_by, x1 FROM test_costs ORDER BY time DESC;
--- QUERY PLAN ---
 Sort (actual rows=118118.00 loops=1)
   Output: _hyper_9_20_chunk."time", _hyper_9_20_chunk.segment_by, _hyper_9_20_chunk.x1
   Sort Key: _hyper_9_20_chunk."time" DESC
   Sort Method: quicksort 
   ->  Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_9_20_chunk (actual rows=118118.00 loops=1)
         Output: _hyper_9_20_chunk."time", _hyper_9_20_chunk.segment_by, _hyper_9_20_chunk.x1
         Bulk Decompression: true
         ->  Seq Scan on _timescaledb_internal.compress_hyper_10_22_chunk (actual rows=1000.00 loops=1)
               Output: compress_hyper_10_22_chunk._ts_meta_count, compress_hyper_10_22_chunk.segment_by, compress_hyper_10_22_chunk._ts_meta_min_1, compress_hyper_10_22_chunk._ts_meta_max_1, compress_hyper_10_22_chunk._ts_meta_v2_first_time, compress_hyper_10_22_chunk._ts_meta_v2_last_time, compress_hyper_10_22_chunk."time", compress_hyper_10_22_chunk._ts_meta_min_2, compress_hyper_10_22_chunk._ts_meta_max_2, compress_hyper_10_22_chunk._ts_meta_v2_first_x1, compress_hyper_10_22_chunk._ts_meta_v2_last_x1, compress_hyper_10_22_chunk.x1

-- Test query plan with predicate (query should be optimized due to ~100 segments)
set timescaledb.debug_require_batch_sorted_merge to 'require';
:PREFIX
SELECT time, segment_by, x1 FROM test_costs WHERE segment_by > 900 and segment_by < 999 ORDER BY time DESC;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_9_20_chunk (actual rows=11564.00 loops=1)
   Output: _hyper_9_20_chunk."time", _hyper_9_20_chunk.segment_by, _hyper_9_20_chunk.x1
   Batch Sorted Merge: true
   Bulk Decompression: false
   ->  Sort (actual rows=98.00 loops=1)
         Output: compress_hyper_10_22_chunk._ts_meta_count, compress_hyper_10_22_chunk.segment_by, compress_hyper_10_22_chunk._ts_meta_min_1, compress_hyper_10_22_chunk._ts_meta_max_1, compress_hyper_10_22_chunk._ts_meta_v2_first_time, compress_hyper_10_22_chunk._ts_meta_v2_last_time, compress_hyper_10_22_chunk."time", compress_hyper_10_22_chunk._ts_meta_min_2, compress_hyper_10_22_chunk._ts_meta_max_2, compress_hyper_10_22_chunk._ts_meta_v2_first_x1, compress_hyper_10_22_chunk._ts_meta_v2_last_x1, compress_hyper_10_22_chunk.x1
         Sort Key: compress_hyper_10_22_chunk._ts_meta_v2_first_time DESC
         Sort Method: quicksort 
         ->  Index Scan using compress_hyper_10_22_chunk_segment_by__ts_meta_v2_last_time_idx on _timescaledb_internal.compress_hyper_10_22_chunk (actual rows=98.00 loops=1)
               Output: compress_hyper_10_22_chunk._ts_meta_count, compress_hyper_10_22_chunk.segment_by, compress_hyper_10_22_chunk._ts_meta_min_1, compress_hyper_10_22_chunk._ts_meta_max_1, compress_hyper_10_22_chunk._ts_meta_v2_first_time, compress_hyper_10_22_chunk._ts_meta_v2_last_time, compress_hyper_10_22_chunk."time", compress_hyper_10_22_chunk._ts_meta_min_2, compress_hyper_10_22_chunk._ts_meta_max_2, compress_hyper_10_22_chunk._ts_meta_v2_first_x1, compress_hyper_10_22_chunk._ts_meta_v2_last_x1, compress_hyper_10_22_chunk.x1
               Index Cond: ((compress_hyper_10_22_chunk.segment_by > 900) AND (compress_hyper_10_22_chunk.segment_by < 999))

-- Target list creation - Issue 5738
CREATE TABLE bugtab(
 time timestamp without time zone,
 hin   character varying(128) NOT NULL,
 model character varying(128) NOT NULL,
 block character varying(128) NOT NULL,
 message_name character varying(128) NOT NULL,
 signal_name  character varying(128) NOT NULL,
 signal_numeric_value double precision,
 signal_string_value character varying(128)
);
SELECT create_hypertable('bugtab', 'time');
WARNING:  column type "timestamp without time zone" used for "time" does not follow best practices
WARNING:  column type "character varying" used for "hin" does not follow best practices
WARNING:  column type "character varying" used for "model" does not follow best practices
WARNING:  column type "character varying" used for "block" does not follow best practices
WARNING:  column type "character varying" used for "message_name" does not follow best practices
WARNING:  column type "character varying" used for "signal_name" does not follow best practices
WARNING:  column type "character varying" used for "signal_string_value" does not follow best practices
  create_hypertable   
----------------------
 (11,public,bugtab,t)

INSERT INTO bugtab values('2020-01-01 10:00', 'hin1111', 'model111', 'blok111', 'message_here', 'signal1', 12.34, '12.34');
ALTER TABLE bugtab SET (timescaledb.compress, timescaledb.compress_segmentby = 'hin, signal_name', timescaledb.compress_orderby = 'time');
SELECT chunk_schema || '.' || chunk_name AS "chunk_table_bugtab"
       FROM timescaledb_information.chunks
       WHERE hypertable_name = 'bugtab' ORDER BY range_start LIMIT 1 \gset
SELECT compress_chunk(i) FROM show_chunks('bugtab') i;
              compress_chunk              
------------------------------------------
 _timescaledb_internal._hyper_11_23_chunk

ANALYZE bugtab;
set timescaledb.debug_require_batch_sorted_merge to 'force';
:PREFIX
SELECT "time","hin"::text,"model"::text,"block"::text,"message_name"::text,"signal_name"::text,"signal_numeric_value","signal_string_value"::text FROM :chunk_table_bugtab ORDER BY "time" DESC;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_11_23_chunk (actual rows=1.00 loops=1)
   Output: _hyper_11_23_chunk."time", (_hyper_11_23_chunk.hin)::text, (_hyper_11_23_chunk.model)::text, (_hyper_11_23_chunk.block)::text, (_hyper_11_23_chunk.message_name)::text, (_hyper_11_23_chunk.signal_name)::text, _hyper_11_23_chunk.signal_numeric_value, (_hyper_11_23_chunk.signal_string_value)::text
   Batch Sorted Merge: true
   Reverse: true
   Bulk Decompression: false
   ->  Sort (actual rows=1.00 loops=1)
         Output: compress_hyper_12_24_chunk._ts_meta_count, compress_hyper_12_24_chunk.hin, compress_hyper_12_24_chunk.signal_name, compress_hyper_12_24_chunk._ts_meta_min_1, compress_hyper_12_24_chunk._ts_meta_max_1, compress_hyper_12_24_chunk._ts_meta_v2_first_time, compress_hyper_12_24_chunk._ts_meta_v2_last_time, compress_hyper_12_24_chunk."time", compress_hyper_12_24_chunk.model, compress_hyper_12_24_chunk.block, compress_hyper_12_24_chunk.message_name, compress_hyper_12_24_chunk.signal_numeric_value, compress_hyper_12_24_chunk.signal_string_value
         Sort Key: compress_hyper_12_24_chunk._ts_meta_v2_last_time DESC
         Sort Method: quicksort 
         ->  Seq Scan on _timescaledb_internal.compress_hyper_12_24_chunk (actual rows=1.00 loops=1)
               Output: compress_hyper_12_24_chunk._ts_meta_count, compress_hyper_12_24_chunk.hin, compress_hyper_12_24_chunk.signal_name, compress_hyper_12_24_chunk._ts_meta_min_1, compress_hyper_12_24_chunk._ts_meta_max_1, compress_hyper_12_24_chunk._ts_meta_v2_first_time, compress_hyper_12_24_chunk._ts_meta_v2_last_time, compress_hyper_12_24_chunk."time", compress_hyper_12_24_chunk.model, compress_hyper_12_24_chunk.block, compress_hyper_12_24_chunk.message_name, compress_hyper_12_24_chunk.signal_numeric_value, compress_hyper_12_24_chunk.signal_string_value

SELECT "time","hin"::text,"model"::text,"block"::text,"message_name"::text,"signal_name"::text,"signal_numeric_value","signal_string_value"::text FROM :chunk_table_bugtab ORDER BY "time" DESC;
           time           |   hin   |  model   |  block  | message_name | signal_name | signal_numeric_value | signal_string_value 
--------------------------+---------+----------+---------+--------------+-------------+----------------------+---------------------
 Wed Jan 01 10:00:00 2020 | hin1111 | model111 | blok111 | message_here | signal1     |                12.34 | 12.34

SELECT "time","hin"::text,"model"::text,"block"::text,"message_name"::text,"signal_name"::text,"signal_numeric_value","signal_string_value"::text FROM bugtab ORDER BY "time" DESC;
           time           |   hin   |  model   |  block  | message_name | signal_name | signal_numeric_value | signal_string_value 
--------------------------+---------+----------+---------+--------------+-------------+----------------------+---------------------
 Wed Jan 01 10:00:00 2020 | hin1111 | model111 | blok111 | message_here | signal1     |                12.34 | 12.34

drop table bugtab cascade;
-- Condition that filter the first tuple of a batch - Issue 5797
CREATE TABLE test (
    id      bigint,
    dttm    timestamp NOT NULL,
    otherId    int,
    valueFk int,
    otherFk int,
    measure double precision
);
SELECT create_hypertable('test', 'dttm');
WARNING:  column type "timestamp without time zone" used for "dttm" does not follow best practices
 create_hypertable  
--------------------
 (13,public,test,t)

ALTER TABLE test SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'otherId,valueFk,otherFk'
);
INSERT INTO public.test (id, dttm, otherid, valuefk, otherfk, measure)
VALUES  (109288, '2023-05-25 23:12:13.000000', 130, 14499, 13, 0.13216569884001217),
        (109286, '2023-05-25 23:12:13.000000', 130, 14500, 13, 0.3740651978942786),
        (107617, '2023-05-25 14:24:12.000000', 130, 14850, 13, 0.5978259144311195),
        (103864, '2023-05-25 13:30:51.000000', 130, 16760, 13, 0.4733429856616205),
        (104977, '2023-05-25 12:11:47.000000', 133, 14843, 13, 0.24366893909655118),
        (108321, '2023-05-25 18:39:07.000000', 133, 15294, 13, 0.8768629101819378),
        (108320, '2023-05-25 16:09:17.000000', 133, 15294, 13, 0.6185638532799445),
        (104987, '2023-05-25 13:27:19.000000', 133, 15294, 13, 0.9830846939109854),
        (104737, '2023-05-25 19:59:54.000000', 135, 14238, 13, 0.2388520055224177),
        (106278, '2023-05-25 19:59:54.000000', 135, 14238, 13, 0.6305156586688518),
        (104741, '2023-05-25 19:59:54.000000', 135, 14238, 13, 0.4990673076480263),
        (106277, '2023-05-25 12:53:34.000000', 135, 14238, 13, 0.46086278330000496),
        (97409, '2023-05-25 12:38:48.000000', 137, 14533, 13, 0.8308173375978924),
        (105234, '2023-05-25 12:38:45.000000', 137, 14533, 13, 0.10860962941223917),
        (105233, '2023-05-25 12:06:35.000000', 137, 14533, 13, 0.09058791972962155),
        (97434, '2023-05-25 12:39:46.000000', 137, 14657, 13, 0.023315916140422388),
        (108167, '2023-05-25 15:41:30.000000', 137, 14964, 13, 0.21757999385617666),
        (107741, '2023-05-25 14:40:37.000000', 137, 14964, 13, 0.3449447147508202),
        (106312, '2023-05-25 14:40:16.000000', 137, 14964, 13, 0.11890456868959376),
        (106134, '2023-05-25 12:56:18.000000', 137, 14964, 13, 0.8004332371337775),
        (103696, '2023-05-25 12:54:31.000000', 137, 14964, 13, 0.30147495793613643),
        (106311, '2023-05-25 12:44:22.000000', 137, 14964, 13, 0.7412968055185551),
        (106133, '2023-05-25 12:44:22.000000', 137, 14964, 13, 0.12940337622720932),
        (105711, '2023-05-25 12:43:57.000000', 137, 14964, 13, 0.1044849979830822),
        (105710, '2023-05-25 12:34:04.000000', 137, 14964, 13, 0.9113563410974876),
        (108787, '2023-05-25 17:59:35.000000', 137, 15377, 13, 0.921829256160489),
        (107833, '2023-05-25 14:53:08.000000', 137, 16302, 13, 0.9663117845438407),
        (105435, '2023-05-25 12:30:59.000000', 137, 16568, 13, 0.13774896612028797),
        (105434, '2023-05-25 12:29:29.000000', 137, 16568, 13, 0.3891495411502035),
        (108357, '2023-05-25 16:18:39.000000', 137, 16665, 13, 0.44701901843246716),
        (98564, '2023-05-25 17:12:43.000000', 138, 14760, 13, 0.8463114782142114),
        (109032, '2023-05-25 19:00:00.000000', 138, 14992, 13, 0.025578609447126865),
        (108800, '2023-05-25 18:43:18.000000', 138, 14992, 13, 0.5397724043221928),
        (108799, '2023-05-25 18:00:00.000000', 138, 14992, 13, 0.0321658507434357),
        (107320, '2023-05-25 14:00:01.000000', 138, 14992, 13, 0.9042941365487067),
        (107296, '2023-05-25 14:00:00.000000', 138, 14992, 13, 0.7821178685669885),
        (104700, '2023-05-25 12:36:55.000000', 138, 14992, 13, 0.6854496458178119),
        (105177, '2023-05-25 12:00:01.000000', 138, 14992, 13, 0.23780719110724746),
        (109330, '2023-05-25 23:59:13.000000', 138, 15080, 13, 0.5409015970284159),
        (107400, '2023-05-25 16:45:13.000000', 138, 15080, 13, 0.6233594483468217),
        (107399, '2023-05-25 14:03:49.000000', 138, 15080, 13, 0.8192327792045404),
        (105004, '2023-05-25 13:37:49.000000', 138, 15080, 13, 0.2993620446103442),
        (102592, '2023-05-25 13:31:48.000000', 138, 15080, 13, 0.24649704579496046),
        (109028, '2023-05-25 19:00:00.000000', 138, 15123, 13, 0.5442767942906279),
        (108794, '2023-05-25 18:43:18.000000', 138, 15123, 13, 0.29095714680616425),
        (108793, '2023-05-25 18:00:00.000000', 138, 15123, 13, 0.681894893772391),
        (107314, '2023-05-25 14:00:01.000000', 138, 15123, 13, 0.9637603904838059),
        (107292, '2023-05-25 14:00:00.000000', 138, 15123, 13, 0.05956707862994293),
        (104696, '2023-05-25 12:36:55.000000', 138, 15123, 13, 0.27039489547807705),
        (105171, '2023-05-25 12:00:01.000000', 138, 15123, 13, 0.1269705046788907),
        (106625, '2023-05-25 13:22:19.000000', 138, 15326, 13, 0.7712280764026431),
        (106624, '2023-05-25 13:15:49.000000', 138, 15326, 13, 0.585381418741779),
        (105699, '2023-05-25 12:44:33.000000', 138, 15326, 13, 0.3710994669938259),
        (105698, '2023-05-25 12:33:41.000000', 138, 15326, 13, 0.8992328857980105),
        (108514, '2023-05-25 16:47:37.000000', 138, 15620, 13, 0.40346934167556725),
        (102691, '2023-05-25 13:33:57.000000', 138, 15620, 13, 0.8046719989908304),
        (103655, '2023-05-25 13:34:39.000000', 138, 15740, 13, 0.2541099322817928),
        (106987, '2023-05-25 13:37:36.000000', 138, 15766, 13, 0.8407818724583045),
        (102180, '2023-05-25 13:37:11.000000', 138, 15766, 13, 0.19149633299917213),
        (102717, '2023-05-25 13:38:17.000000', 138, 15868, 13, 0.03196157886032225),
        (102719, '2023-05-25 13:38:42.000000', 138, 15921, 13, 0.9986438564169191),
        (103659, '2023-05-25 13:35:11.000000', 138, 15926, 13, 0.8549591705597201),
        (108796, '2023-05-25 18:43:18.000000', 138, 15932, 13, 0.6213586835883191),
        (108795, '2023-05-25 18:00:00.000000', 138, 15932, 13, 0.6730718577847092),
        (107326, '2023-05-25 14:00:01.000000', 138, 15932, 13, 0.278131899094646),
        (107298, '2023-05-25 14:00:00.000000', 138, 15932, 13, 0.92423751071723),
        (104702, '2023-05-25 12:36:55.000000', 138, 15932, 13, 0.22221315122722984),
        (105175, '2023-05-25 12:00:01.000000', 138, 15932, 13, 0.28839114292751233),
        (102736, '2023-05-25 16:15:29.000000', 138, 16052, 13, 0.431037595792759),
        (99163, '2023-05-25 13:47:30.000000', 138, 16419, 13, 0.5291021511946319),
        (102738, '2023-05-25 13:45:05.000000', 138, 16420, 13, 0.6506497895856924),
        (99109, '2023-05-25 13:37:49.000000', 138, 16420, 13, 0.019501542758906254),
        (108798, '2023-05-25 18:43:18.000000', 138, 16590, 13, 0.8990882904615916),
        (108797, '2023-05-25 18:00:00.000000', 138, 16590, 13, 0.8888186371755147),
        (107328, '2023-05-25 14:00:01.000000', 138, 16590, 13, 0.019486942610562608),
        (107300, '2023-05-25 14:00:00.000000', 138, 16590, 13, 0.5614292991802508),
        (104698, '2023-05-25 12:36:55.000000', 138, 16590, 13, 0.01866956387405594),
        (105173, '2023-05-25 12:00:01.000000', 138, 16590, 13, 0.25661478763909074),
        (107224, '2023-05-25 13:51:57.000000', 138, 16633, 13, 0.0010321723593804677),
        (99064, '2023-05-25 13:37:49.000000', 138, 16633, 13, 0.8675616866165861),
        (109225, '2023-05-25 22:13:01.000000', 138, 16669, 13, 0.1076822852142385),
        (109224, '2023-05-25 21:11:56.000000', 138, 16669, 13, 0.24001365186054713);
SELECT compress_chunk(show_chunks('test', older_than => INTERVAL '1 week'), true);
              compress_chunk              
------------------------------------------
 _timescaledb_internal._hyper_13_25_chunk

SELECT t.dttm FROM test t WHERE t.dttm > '2023-05-25T14:23:12' ORDER BY t.dttm;
           dttm           
--------------------------
 Thu May 25 14:24:12 2023
 Thu May 25 14:40:16 2023
 Thu May 25 14:40:37 2023
 Thu May 25 14:53:08 2023
 Thu May 25 15:41:30 2023
 Thu May 25 16:09:17 2023
 Thu May 25 16:15:29 2023
 Thu May 25 16:18:39 2023
 Thu May 25 16:45:13 2023
 Thu May 25 16:47:37 2023
 Thu May 25 17:12:43 2023
 Thu May 25 17:59:35 2023
 Thu May 25 18:00:00 2023
 Thu May 25 18:00:00 2023
 Thu May 25 18:00:00 2023
 Thu May 25 18:00:00 2023
 Thu May 25 18:39:07 2023
 Thu May 25 18:43:18 2023
 Thu May 25 18:43:18 2023
 Thu May 25 18:43:18 2023
 Thu May 25 18:43:18 2023
 Thu May 25 19:00:00 2023
 Thu May 25 19:00:00 2023
 Thu May 25 19:59:54 2023
 Thu May 25 19:59:54 2023
 Thu May 25 19:59:54 2023
 Thu May 25 21:11:56 2023
 Thu May 25 22:13:01 2023
 Thu May 25 23:12:13 2023
 Thu May 25 23:12:13 2023
 Thu May 25 23:59:13 2023

-- Test that the enable_sort GUC doesn't disable the batch sorted merge plan.
SET enable_sort TO OFF;
set timescaledb.debug_require_batch_sorted_merge = 'require';
:PREFIX
SELECT t.dttm FROM test t ORDER BY t.dttm LIMIT 1;
--- QUERY PLAN ---
 Limit (actual rows=1.00 loops=1)
   Output: t.dttm
   ->  Custom Scan (ColumnarScan) on _timescaledb_internal._hyper_13_25_chunk t (actual rows=1.00 loops=1)
         Output: t.dttm
         Batch Sorted Merge: true
         Reverse: true
         Bulk Decompression: false
         ->  Sort (actual rows=5.00 loops=1)
               Output: compress_hyper_14_26_chunk._ts_meta_count, compress_hyper_14_26_chunk.otherid, compress_hyper_14_26_chunk.valuefk, compress_hyper_14_26_chunk.otherfk, compress_hyper_14_26_chunk.id, compress_hyper_14_26_chunk._ts_meta_min_1, compress_hyper_14_26_chunk._ts_meta_max_1, compress_hyper_14_26_chunk._ts_meta_v2_first_dttm, compress_hyper_14_26_chunk._ts_meta_v2_last_dttm, compress_hyper_14_26_chunk.dttm, compress_hyper_14_26_chunk.measure
               Sort Key: compress_hyper_14_26_chunk._ts_meta_v2_last_dttm
               Sort Method: quicksort 
               ->  Seq Scan on _timescaledb_internal.compress_hyper_14_26_chunk (actual rows=32.00 loops=1)
                     Output: compress_hyper_14_26_chunk._ts_meta_count, compress_hyper_14_26_chunk.otherid, compress_hyper_14_26_chunk.valuefk, compress_hyper_14_26_chunk.otherfk, compress_hyper_14_26_chunk.id, compress_hyper_14_26_chunk._ts_meta_min_1, compress_hyper_14_26_chunk._ts_meta_max_1, compress_hyper_14_26_chunk._ts_meta_v2_first_dttm, compress_hyper_14_26_chunk._ts_meta_v2_last_dttm, compress_hyper_14_26_chunk.dttm, compress_hyper_14_26_chunk.measure

RESET enable_sort;
drop table test cascade;
-- Test issue #9922: wrong sort order for Batch Sorted Merge with multikey minmax index
\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE TABLE bsm_segby(ts timestamptz NOT NULL, grp int NOT NULL, name text NOT NULL);
SELECT table_name FROM create_hypertable('bsm_segby','ts',chunk_time_interval=>interval '100 day');
 table_name 
------------
 bsm_segby

ALTER TABLE bsm_segby SET (timescaledb.compress,  timescaledb.compress_segmentby='grp', timescaledb.compress_orderby='name asc, ts asc');
INSERT INTO bsm_segby
SELECT '2024-08-01'::timestamptz + g*interval '1 min', g%4, 'k'||lpad(((g*5)%17)::text,3,'0')
FROM generate_series(0,4379) g;
SELECT count(compress_chunk(c)) FROM show_chunks('bsm_segby') c;
 count 
-------
     1

select cs.compress_relid::regclass chunk from _timescaledb_catalog.chunk ch
    join _timescaledb_catalog.compression_settings cs
        on cs.relid = format('%I.%I', ch.schema_name, ch.table_name)::regclass
    where ch.hypertable_id = (select id from _timescaledb_catalog.hypertable
        where table_name = 'bsm_segby') limit 1
\gset
-- Firstlast index is used with Batch Sorted Merge: correct result
set timescaledb.debug_require_batch_sorted_merge = 'force';
SELECT count(*) misorder FROM (
  SELECT name, ts, lag(name) OVER () pn, lag(ts) OVER () pt
  FROM (SELECT name, ts FROM bsm_segby ORDER BY name, ts) s) z
WHERE pn IS NOT NULL AND (name<pn OR (name=pn AND ts<pt));
 misorder 
----------
        0

-- Remove firstlast index from leading column: still OK to use Batch Sorted Merge
update _timescaledb_catalog.compression_settings
set index = '[{"type": "minmax", "column": "name", "source": "orderby"}, {"type": "minmax", "column": "ts", "source": "orderby"}, {"type": "firstlast", "column": "ts", "source": "orderby"}]'
where relid = 'bsm_segby'::regclass;
update _timescaledb_catalog.compression_settings
set index = '[{"type": "minmax", "column": "name", "source": "orderby"}, {"type": "minmax", "column": "ts", "source": "orderby"}, {"type": "firstlast", "column": "ts", "source": "orderby"}]'
where relid = (select format('%I.%I', schema_name, table_name)::regclass from _timescaledb_catalog.chunk
    where hypertable_id = (select id from _timescaledb_catalog.hypertable
        where table_name = 'bsm_segby') limit 1);
create index compressed_index_minmax_firstlast on :chunk (grp, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_ts, _ts_meta_v2_last_ts);
-- Correct result with Batch Sorted Merge
set timescaledb.debug_require_batch_sorted_merge = 'force';
SELECT count(*) misorder FROM (
  SELECT name, ts, lag(name) OVER () pn, lag(ts) OVER () pt
  FROM (SELECT name, ts FROM bsm_segby ORDER BY name, ts) s) z
WHERE pn IS NOT NULL AND (name<pn OR (name=pn AND ts<pt));
 misorder 
----------
        0

-- Now use minmax index on non-leading column "ts"
drop index _timescaledb_internal.compressed_index_minmax_firstlast;
create index compressed_index_minmax_minmax on :chunk (grp, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_min_2, _ts_meta_max_2);
-- Remove firstlast index from all columns
update _timescaledb_catalog.compression_settings
set index = '[{"type": "minmax", "column": "name", "source": "orderby"}, {"type": "minmax", "column": "ts", "source": "orderby"}]'
where relid = 'bsm_segby'::regclass;
update _timescaledb_catalog.compression_settings
set index = '[{"type": "minmax", "column": "name", "source": "orderby"}, {"type": "minmax", "column": "ts", "source": "orderby"}]'
where relid = (select format('%I.%I', schema_name, table_name)::regclass from _timescaledb_catalog.chunk
    where hypertable_id = (select id from _timescaledb_catalog.hypertable
        where table_name = 'bsm_segby') limit 1);
-- Correct result only when cannot use Batch Sorted Merge
set timescaledb.debug_require_batch_sorted_merge = 'forbid';
SELECT count(*) misorder FROM (
  SELECT name, ts, lag(name) OVER () pn, lag(ts) OVER () pt
  FROM (SELECT name, ts FROM bsm_segby ORDER BY name, ts) s) z
WHERE pn IS NOT NULL AND (name<pn OR (name=pn AND ts<pt));
 misorder 
----------
        0

drop table bsm_segby cascade;
drop table test1 cascade;
drop table test2 cascade;
drop table test_with_defined_null cascade;
drop table test_costs cascade;
drop table insert_test cascade;
reset timescaledb.debug_require_batch_sorted_merge;