columnar_index_scan-17.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.
SET max_parallel_workers_per_gather = 0;
-- disable vectorized aggregation to prevent plan switches when running on 32-bit
SET timescaledb.enable_vectorized_aggregation = off;
\set TEST_BASE_NAME columnar_index_scan
SELECT format('include/%s_query.sql', :'TEST_BASE_NAME') as "TEST_QUERY_NAME",
       format('%s/results/%s_results_optimized.out', :'TEST_OUTPUT_DIR', :'TEST_BASE_NAME') as "TEST_RESULTS_OPTIMIZED",
       format('%s/results/%s_results_unoptimized.out', :'TEST_OUTPUT_DIR', :'TEST_BASE_NAME') as "TEST_RESULTS_UNOPTIMIZED"
\gset
SELECT format('\! diff -u --label "Unoptimized results" --label "Optimized results" %s %s', :'TEST_RESULTS_UNOPTIMIZED', :'TEST_RESULTS_OPTIMIZED') as "DIFF_CMD"
\gset
CREATE TABLE metrics(
    time timestamptz NOT NULL,
    device text,
    sensor text,
    value float,
    value2 float
) WITH (tsdb.hypertable,tsdb.orderby='time desc',tsdb.segmentby='device,sensor',tsdb.index='minmax(value)');
NOTICE:  using column "time" as partitioning column
INSERT INTO metrics VALUES
('2025-01-01 00:00:00 PST', 'd1', 'A', 10.0, 10.1),
('2025-01-01 01:00:00 PST', 'd1', 'A', 20.0, 20.1),
('2025-01-01 02:00:00 PST', 'd1', 'A', 15.0, 15.1),
('2025-01-01 00:30:00 PST', 'd1', 'B', 4.0, 4.1),
('2025-01-01 01:30:00 PST', 'd1', 'B', 25.0, 25.1),
('2025-01-01 02:30:00 PST', 'd1', 'B', 29.0, 29.1),
('2025-01-01 00:00:00 PST', 'd2', 'A', 10.0, 10.1),
('2025-01-01 01:00:00 PST', 'd2', 'A', 20.0, 20.1),
('2025-01-01 02:00:00 PST', 'd2', 'A', 15.0, 15.1),
('2025-01-01 00:30:00 PST', 'd2', 'C', 6.0, 6.1),
('2025-01-01 01:30:00 PST', 'd2', 'C', 25.0, 25.1),
('2025-01-01 02:30:00 PST', 'd2', 'C', 31.0, 31.1);
-- Compress all chunks
SELECT compress_chunk(c) FROM show_chunks('metrics') c;
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_1_1_chunk

-- first run with hypertable with 1 fully compressed chunk
-- get query plans
\set PREFIX 'EXPLAIN (costs off)'
\set ECHO all
SET timescaledb.enable_columnarindexscan = on;
\ir :TEST_QUERY_NAME
-- 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.
-- canary for test diff
SHOW timescaledb.enable_columnarindexscan;
 timescaledb.enable_columnarindexscan 
--------------------------------------
 on

-- simple query with count without grouping
:PREFIX SELECT device, count(*) FROM metrics GROUP BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- simple query with count with grouping
:PREFIX SELECT device, count(*) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- simple query with multiple count with grouping
:PREFIX SELECT device, count(*), count(*) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- simple query with 1 max aggregate that can use optimization
:PREFIX SELECT device, max(time) FROM metrics GROUP BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- simple query with 1 min aggregate that can use optimization
:PREFIX SELECT sensor, min(time) FROM metrics GROUP BY sensor ORDER BY sensor;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: sensor
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Sort
               Sort Key: compress_hyper_2_2_chunk.sensor
               ->  Seq Scan on compress_hyper_2_2_chunk

-- simple query with 1 first aggregate that can use optimization
:PREFIX SELECT device, first(time, time) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- simple query with 1 last aggregate that can use optimization
:PREFIX SELECT device, last(time, time) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- explicit index columns dont prevent optimization
:PREFIX SELECT sensor, min(value) FROM metrics GROUP BY sensor ORDER BY sensor;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: sensor
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Sort
               Sort Key: compress_hyper_2_2_chunk.sensor
               ->  Seq Scan on compress_hyper_2_2_chunk

:PREFIX SELECT sensor, min(value), max(value) FROM metrics GROUP BY sensor ORDER BY sensor;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: sensor
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Sort
               Sort Key: compress_hyper_2_2_chunk.sensor
               ->  Seq Scan on compress_hyper_2_2_chunk

-- multiple aggregates on same column
:PREFIX SELECT sensor, min(time), max(time), first(time,time), last(time,time) FROM metrics GROUP BY sensor ORDER BY sensor;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: sensor
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Sort
               Sort Key: compress_hyper_2_2_chunk.sensor
               ->  Seq Scan on compress_hyper_2_2_chunk

-- same aggregate on multiple columns
:PREFIX SELECT sensor, min(time), min(value)  FROM metrics GROUP BY sensor ORDER BY sensor;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: sensor
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Sort
               Sort Key: compress_hyper_2_2_chunk.sensor
               ->  Seq Scan on compress_hyper_2_2_chunk

-- same aggregate on multiple columns but different order
:PREFIX SELECT sensor, min(value), min(time), sensor  FROM metrics GROUP BY sensor ORDER BY sensor;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: sensor
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Sort
               Sort Key: compress_hyper_2_2_chunk.sensor
               ->  Seq Scan on compress_hyper_2_2_chunk

-- multiple aggregates on multiple columns
:PREFIX SELECT sensor, first(time,time), last(time,time), first(value, value), last(value, value)  FROM metrics GROUP BY sensor ORDER BY sensor;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: sensor
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Sort
               Sort Key: compress_hyper_2_2_chunk.sensor
               ->  Seq Scan on compress_hyper_2_2_chunk

-- test multiple group by columns
:PREFIX SELECT device, sensor, max(time) FROM metrics GROUP BY device,sensor ORDER BY device,sensor;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device, sensor
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- filter on segmentby allows optimization
:PREFIX SELECT device, min(time) FROM metrics WHERE device IN ('d1','d2') GROUP BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
               Index Cond: (device = ANY ('{d1,d2}'::text[]))

:PREFIX SELECT device, min(time) FROM metrics WHERE device =ANY(ARRAY['d1','d2']) AND sensor='B' GROUP BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
               Index Cond: ((device = ANY ('{d1,d2}'::text[])) AND (sensor = 'B'::text))

-- HAVING on segmentby (pushed down to lower scan)
:PREFIX SELECT device, min(time) FROM metrics GROUP BY device HAVING device IN ('d1','d2');
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
               Index Cond: (device = ANY ('{d1,d2}'::text[]))

:PREFIX SELECT device, min(time) FROM metrics GROUP BY device,sensor HAVING device =ANY(ARRAY['d1','d2']) AND sensor='B';
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
               Index Cond: ((device = ANY ('{d1,d2}'::text[])) AND (sensor = 'B'::text))

-- HAVING with aggregate function
:PREFIX SELECT device, min(value) FROM metrics GROUP BY device HAVING min(value) > 20;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   Filter: (min(value) > '20'::double precision)
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

:PREFIX SELECT device, max(value) FROM metrics GROUP BY device HAVING min(value) < 25;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   Filter: (min(value) < '25'::double precision)
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- HAVING
:PREFIX SELECT device, min(time) FROM metrics GROUP BY device HAVING min(time) > '2025-01-01 00:30:00 PST';
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   Filter: (min("time") > 'Wed Jan 01 00:30:00 2025 PST'::timestamp with time zone)
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- tableoid doesnt prevent optimization
:PREFIX SELECT tableoid, device, max(time) FROM metrics GROUP BY device, tableoid;
--- QUERY PLAN ---
 HashAggregate
   Group Key: device, tableoid
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Seq Scan on compress_hyper_2_2_chunk

:PREFIX SELECT device, max(time), min(tableoid) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- multiple aggregates on same column use optimization
:PREFIX SELECT device, min(time), max(time) FROM metrics GROUP BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- multiple aggregates on same column with first/last
:PREFIX SELECT device, min(time), first(time, time), last(time, time), max(time) FROM metrics GROUP BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- segmentby column at end of targetlist
:PREFIX SELECT first(time, time), last(time, time), device FROM metrics GROUP BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- multiple aggregates on different columns use optimization
:PREFIX SELECT device, min(time), max(value) FROM metrics GROUP BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- multiple aggregates on same non-time column use optimization
:PREFIX SELECT device, min(value), max(value) FROM metrics GROUP BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- multiple aggregates on multiple columns
:PREFIX SELECT device, min(time), max(time), min(value), max(value) FROM metrics GROUP BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- expression on aggregates
:PREFIX SELECT device, max(time), min(time), max(time) - min(time)  FROM metrics GROUP BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- test with sort
:PREFIX SELECT device, sensor, first(time,time), last(time,time) from metrics group by device, sensor order by 1,2;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device, sensor
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- test with subquery (SubqueryScan)
:PREFIX SELECT * FROM (SELECT device, min(time), max(time) FROM metrics GROUP BY device) sub ORDER BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

:PREFIX SELECT mx, mn, device FROM (SELECT device, min(time) mn, max(time) mx FROM metrics GROUP BY device) sub ORDER BY device;
--- QUERY PLAN ---
 Subquery Scan on sub
   ->  GroupAggregate
         Group Key: device
         ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
               ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- test with CTE (also uses SubqueryScan)
:PREFIX WITH agg_data AS (
    SELECT device, min(time) as min_time, max(time) as max_time FROM metrics GROUP BY device
)
SELECT * FROM agg_data ORDER BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- test parallel queries (not supported with ColumnarIndexScan)
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'on', false);
 set_config 
------------
 on

:PREFIX SELECT device, min(time), max(time) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'off', false);
 set_config 
------------
 off

-- test with UNION ALL (Append node)
:PREFIX SELECT device, min(time) FROM metrics WHERE device = 'd1' GROUP BY device
UNION ALL
SELECT device, min(time) FROM metrics WHERE device = 'd2' GROUP BY device
ORDER BY device;
--- QUERY PLAN ---
 Sort
   Sort Key: device
   ->  Append
         ->  GroupAggregate
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
                           Index Cond: (device = 'd1'::text)
         ->  GroupAggregate
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk compress_hyper_2_2_chunk_1
                           Index Cond: (device = 'd2'::text)

:PREFIX SELECT device, min(time), max(time) FROM metrics GROUP BY device
UNION ALL
SELECT device, max(time), min(time) FROM metrics GROUP BY device ORDER BY 1,2,3;
--- QUERY PLAN ---
 Incremental Sort
   Sort Key: device, (min("time")), (max("time"))
   Presorted Key: device
   ->  Merge Append
         Sort Key: device
         ->  GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk compress_hyper_2_2_chunk_1

-- test WINDOW functions
:PREFIX SELECT device, max(time), lead(device) OVER (PARTITION BY max(time)) from metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Sort
   Sort Key: device
   ->  WindowAgg
         ->  Sort
               Sort Key: (max("time"))
               ->  GroupAggregate
                     Group Key: device
                     ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                           ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

:PREFIX SELECT device, max(time), lead(device) OVER (PARTITION BY device) from metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 WindowAgg
   ->  GroupAggregate
         Group Key: device
         ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
               ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- currently unoptimized queries
-- count on dimension column (could be optimized but currently is not)
:PREFIX SELECT count(time) FROM metrics;
--- QUERY PLAN ---
 Aggregate
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Seq Scan on compress_hyper_2_2_chunk

-- count with segmentby column
:PREFIX SELECT count(device) FROM metrics;
--- QUERY PLAN ---
 Aggregate
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Seq Scan on compress_hyper_2_2_chunk

-- count with other columns
:PREFIX SELECT count(value) FROM metrics;
--- QUERY PLAN ---
 Aggregate
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Seq Scan on compress_hyper_2_2_chunk

-- group by on non-segmentby prevents optimization
:PREFIX SELECT max(time) FROM metrics GROUP BY value;
--- QUERY PLAN ---
 HashAggregate
   Group Key: _hyper_1_1_chunk.value
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Seq Scan on compress_hyper_2_2_chunk

:PREFIX SELECT device, max(time) FROM metrics GROUP BY device,value;
--- QUERY PLAN ---
 HashAggregate
   Group Key: _hyper_1_1_chunk.device, _hyper_1_1_chunk.value
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Seq Scan on compress_hyper_2_2_chunk

-- unsupported aggregates on any column
:PREFIX SELECT avg(value) FROM metrics;
--- QUERY PLAN ---
 Aggregate
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Seq Scan on compress_hyper_2_2_chunk

:PREFIX SELECT string_agg(device, ',') FROM metrics;
--- QUERY PLAN ---
 Aggregate
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Seq Scan on compress_hyper_2_2_chunk

-- aggregate on segmentby column does not use optimization
:PREFIX SELECT device, min(sensor) FROM metrics GROUP BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: _hyper_1_1_chunk.device
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- aggregate on column without metadata
:PREFIX SELECT min(value2) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: _hyper_1_1_chunk.device
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- aggregates with FILTER clause
:PREFIX SELECT count(*) FILTER(WHERE device='dev 1') FROM metrics;
--- QUERY PLAN ---
 Aggregate
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Seq Scan on compress_hyper_2_2_chunk

:PREFIX SELECT min(value) FILTER(WHERE device='dev 1') FROM metrics;
--- QUERY PLAN ---
 Aggregate
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Seq Scan on compress_hyper_2_2_chunk

-- aggregates with DISTINCT
:PREFIX SELECT count(DISTINCT device) FROM metrics;
--- QUERY PLAN ---
 Aggregate
   ->  Custom Scan (SkipScan) on _hyper_1_1_chunk
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
               ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- aggregates with ORDER
:PREFIX SELECT min(value ORDER BY time) FROM metrics;
--- QUERY PLAN ---
 Aggregate
   ->  Sort
         Sort Key: _hyper_1_1_chunk."time"
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
               ->  Seq Scan on compress_hyper_2_2_chunk

-- WHERE clause on non-segmentby column
:PREFIX SELECT count(*) FROM metrics WHERE value > 10;
--- QUERY PLAN ---
 Aggregate
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         Vectorized Filter: (value > '10'::double precision)
         ->  Seq Scan on compress_hyper_2_2_chunk
               Filter: (_ts_meta_v2_max_value > '10'::double precision)

-- first/last with different arg1 and arg2
:PREFIX SELECT first(value, time) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: _hyper_1_1_chunk.device
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

:PREFIX SELECT last(value, time) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: _hyper_1_1_chunk.device
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- GROUPING SETS / ROLLUP / CUBE are not supported by ColumnarIndexScan
:PREFIX SELECT device, sensor, count(*) FROM metrics GROUP BY ROLLUP(device, sensor) ORDER BY device, sensor;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: _hyper_1_1_chunk.device, _hyper_1_1_chunk.sensor
   Group Key: _hyper_1_1_chunk.device
   Group Key: ()
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

:PREFIX SELECT device, sensor, count(*) FROM metrics GROUP BY CUBE(device, sensor) ORDER BY device, sensor;
--- QUERY PLAN ---
 Sort
   Sort Key: _hyper_1_1_chunk.device, _hyper_1_1_chunk.sensor
   ->  MixedAggregate
         Hash Key: _hyper_1_1_chunk.sensor
         Group Key: _hyper_1_1_chunk.device, _hyper_1_1_chunk.sensor
         Group Key: _hyper_1_1_chunk.device
         Group Key: ()
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
               ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

:PREFIX SELECT device, sensor, count(*) FROM metrics GROUP BY GROUPING SETS ((device), (sensor), ()) ORDER BY device, sensor;
--- QUERY PLAN ---
 Sort
   Sort Key: _hyper_1_1_chunk.device, _hyper_1_1_chunk.sensor
   ->  MixedAggregate
         Hash Key: _hyper_1_1_chunk.sensor
         Group Key: _hyper_1_1_chunk.device
         Group Key: ()
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
               ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- DISTINCT on a segmentby column (Agg with no Aggrefs)
:PREFIX SELECT DISTINCT device FROM metrics ORDER BY device;
--- QUERY PLAN ---
 Unique
   ->  Custom Scan (SkipScan) on _hyper_1_1_chunk
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
               ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- count(*) without GROUP BY
:PREFIX SELECT count(*) FROM metrics;
--- QUERY PLAN ---
 Aggregate
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Seq Scan on compress_hyper_2_2_chunk

-- aggregate only in HAVING (not in SELECT)
:PREFIX SELECT device FROM metrics GROUP BY device HAVING min(value) > 10 ORDER BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   Filter: (min(value) > '10'::double precision)
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- GROUP BY ordinal position
:PREFIX SELECT device, min(time) FROM metrics GROUP BY 1 ORDER BY 1;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- GROUP BY () empty grouping
:PREFIX SELECT count(*) FROM metrics GROUP BY ();
--- QUERY PLAN ---
 Aggregate
   Group Key: ()
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Seq Scan on compress_hyper_2_2_chunk

-- aggregate wrapped in COALESCE
:PREFIX SELECT device, COALESCE(min(time), '2000-01-01'::timestamptz) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- aggregate above a JOIN (Agg not directly above ColumnarScan)
:PREFIX SELECT m.device, min(m.time) FROM metrics m JOIN (VALUES ('d1'), ('d2')) AS d(device) ON m.device = d.device GROUP BY m.device ORDER BY m.device;
--- QUERY PLAN ---
 Sort
   Sort Key: m.device
   ->  HashAggregate
         Group Key: m.device
         ->  Nested Loop
               ->  Values Scan on "*VALUES*"
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk m
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
                           Index Cond: (device = "*VALUES*".column1)

-- ordered-set aggregate (not supported)
:PREFIX SELECT device, percentile_cont(0.5) WITHIN GROUP (ORDER BY value) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: _hyper_1_1_chunk.device
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- bool_and / bool_or (not supported)
:PREFIX SELECT device, bool_and(value > 0), bool_or(value > 0) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: _hyper_1_1_chunk.device
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- partition-wise aggregate
SET enable_partitionwise_aggregate = on;
:PREFIX SELECT device, min(time), max(time) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

SET enable_partitionwise_aggregate = off;
-- Aggref inside a SubLink in the outer tlist (inner aggregate belongs to a separate Agg)
:PREFIX SELECT device, count(*), (SELECT count(*) FROM (VALUES (1),(2),(3)) v(x) WHERE x::text < device) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
   SubPlan 1
     ->  Aggregate
           ->  Values Scan on "*VALUES*"
                 Filter: ((column1)::text < device)

-- HAVING with non-pushable expression on grouping columns
:PREFIX SELECT device, sensor, min(time) FROM metrics GROUP BY device, sensor HAVING (device || sensor) LIKE 'd1%' ORDER BY device, sensor;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device, sensor
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Sort
               Sort Key: compress_hyper_2_2_chunk.device, compress_hyper_2_2_chunk.sensor
               ->  Seq Scan on compress_hyper_2_2_chunk
                     Filter: ((device || sensor) ~~ 'd1%'::text)

-- same Aggref referenced multiple times in tlist
:PREFIX SELECT device, min(time), date_trunc('day', min(time)) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- GROUP BY column not in SELECT (appears only as resjunk in Agg tlist)
:PREFIX SELECT count(*) FROM metrics GROUP BY device ORDER BY count(*);
--- QUERY PLAN ---
 Sort
   Sort Key: (COALESCE(sum(compress_hyper_2_2_chunk._ts_meta_count), '0'::bigint))
   ->  GroupAggregate
         Group Key: device
         ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
               ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- WHERE mixes segmentby and non-segmentby column
:PREFIX SELECT device, count(*) FROM metrics WHERE device = 'd1' AND value > 10 GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 GroupAggregate
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         Vectorized Filter: (value > '10'::double precision)
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
               Index Cond: (device = 'd1'::text)
               Filter: (_ts_meta_v2_max_value > '10'::double precision)

-- WHERE with a SubPlan on a segmentby column (not pushable, must not be dropped)
:PREFIX SELECT count(*) FROM metrics WHERE device NOT IN (SELECT 'd' || g FROM generate_series(1,1) g);
--- QUERY PLAN ---
 Aggregate
   ->  Custom Scan (ChunkAppend) on metrics
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
               Filter: (NOT (ANY (device = (hashed SubPlan 1).col1)))
               ->  Seq Scan on compress_hyper_2_2_chunk
               SubPlan 1
                 ->  Function Scan on generate_series g

:PREFIX SELECT count(*) FROM metrics WHERE device IN (SELECT 'd' || g FROM generate_series(1,2) g);
--- QUERY PLAN ---
 Aggregate
   ->  Nested Loop
         ->  Unique
               ->  Sort
                     Sort Key: (('d'::text || (g.g)::text))
                     ->  Function Scan on generate_series g
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
               ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
                     Index Cond: (device = ('d'::text || (g.g)::text))

-- HAVING with NOT around Aggref
:PREFIX SELECT device, min(value) FROM metrics GROUP BY device HAVING NOT (min(value) > 10) ORDER BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   Filter: (min(value) <= '10'::double precision)
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- HAVING with Aggref IS NULL
:PREFIX SELECT device, min(value) FROM metrics GROUP BY device HAVING min(value) IS NULL ORDER BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: device
   Filter: (min(value) IS NULL)
   ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
         ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk

-- GROUP BY with explicit collation (not a bare Var)
:PREFIX SELECT device COLLATE "C", count(*) FROM metrics GROUP BY device COLLATE "C" ORDER BY 1;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: (_hyper_1_1_chunk.device)::text
   ->  Result
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
               ->  Sort
                     Sort Key: compress_hyper_2_2_chunk.device COLLATE "C"
                     ->  Seq Scan on compress_hyper_2_2_chunk

-- self-join on the same hypertable
:PREFIX SELECT m1.device, min(m1.time) FROM metrics m1 JOIN metrics m2 ON m1.device = m2.device GROUP BY m1.device ORDER BY m1.device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: m1.device
   ->  Merge Join
         Merge Cond: (m1.device = m2.device)
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk m1
               ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Materialize
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk m2
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk compress_hyper_2_2_chunk_1

\set PREFIX ''
\set ECHO errors
--- Unoptimized results
+++ Optimized results
@@ -1,6 +1,6 @@
  timescaledb.enable_columnarindexscan 
 --------------------------------------
- off
+ on
 
  device | count 
SET timescaledb.enable_columnarindexscan = on;
\ir :TEST_QUERY_NAME
-- 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.
-- canary for test diff
SHOW timescaledb.enable_columnarindexscan;
 timescaledb.enable_columnarindexscan 
--------------------------------------
 on

-- simple query with count without grouping
:PREFIX SELECT device, count(*) FROM metrics GROUP BY device;
--- QUERY PLAN ---
 Finalize HashAggregate
   Group Key: metrics.device
   ->  Append
         ->  Partial HashAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial HashAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Seq Scan on _hyper_1_1_chunk

-- simple query with count with grouping
:PREFIX SELECT device, count(*) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

-- simple query with multiple count with grouping
:PREFIX SELECT device, count(*), count(*) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

-- simple query with 1 max aggregate that can use optimization
:PREFIX SELECT device, max(time) FROM metrics GROUP BY device;
--- QUERY PLAN ---
 Finalize HashAggregate
   Group Key: metrics.device
   ->  Append
         ->  Partial HashAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial HashAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Seq Scan on _hyper_1_1_chunk

-- simple query with 1 min aggregate that can use optimization
:PREFIX SELECT sensor, min(time) FROM metrics GROUP BY sensor ORDER BY sensor;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.sensor
   ->  Merge Append
         Sort Key: metrics.sensor
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.sensor
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.sensor
                     ->  Seq Scan on _hyper_1_1_chunk

-- simple query with 1 first aggregate that can use optimization
:PREFIX SELECT device, first(time, time) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

-- simple query with 1 last aggregate that can use optimization
:PREFIX SELECT device, last(time, time) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

-- explicit index columns dont prevent optimization
:PREFIX SELECT sensor, min(value) FROM metrics GROUP BY sensor ORDER BY sensor;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.sensor
   ->  Merge Append
         Sort Key: metrics.sensor
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.sensor
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.sensor
                     ->  Seq Scan on _hyper_1_1_chunk

:PREFIX SELECT sensor, min(value), max(value) FROM metrics GROUP BY sensor ORDER BY sensor;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.sensor
   ->  Merge Append
         Sort Key: metrics.sensor
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.sensor
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.sensor
                     ->  Seq Scan on _hyper_1_1_chunk

-- multiple aggregates on same column
:PREFIX SELECT sensor, min(time), max(time), first(time,time), last(time,time) FROM metrics GROUP BY sensor ORDER BY sensor;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.sensor
   ->  Merge Append
         Sort Key: metrics.sensor
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.sensor
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.sensor
                     ->  Seq Scan on _hyper_1_1_chunk

-- same aggregate on multiple columns
:PREFIX SELECT sensor, min(time), min(value)  FROM metrics GROUP BY sensor ORDER BY sensor;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.sensor
   ->  Merge Append
         Sort Key: metrics.sensor
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.sensor
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.sensor
                     ->  Seq Scan on _hyper_1_1_chunk

-- same aggregate on multiple columns but different order
:PREFIX SELECT sensor, min(value), min(time), sensor  FROM metrics GROUP BY sensor ORDER BY sensor;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.sensor
   ->  Merge Append
         Sort Key: metrics.sensor
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.sensor
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.sensor
                     ->  Seq Scan on _hyper_1_1_chunk

-- multiple aggregates on multiple columns
:PREFIX SELECT sensor, first(time,time), last(time,time), first(value, value), last(value, value)  FROM metrics GROUP BY sensor ORDER BY sensor;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.sensor
   ->  Merge Append
         Sort Key: metrics.sensor
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.sensor
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.sensor
                     ->  Seq Scan on _hyper_1_1_chunk

-- test multiple group by columns
:PREFIX SELECT device, sensor, max(time) FROM metrics GROUP BY device,sensor ORDER BY device,sensor;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device, metrics.sensor
   ->  Merge Append
         Sort Key: metrics.device, metrics.sensor
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device, _hyper_1_1_chunk.sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device, compress_hyper_2_2_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device, _hyper_1_1_chunk.sensor
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device, _hyper_1_1_chunk.sensor
                     ->  Seq Scan on _hyper_1_1_chunk

-- filter on segmentby allows optimization
:PREFIX SELECT device, min(time) FROM metrics WHERE device IN ('d1','d2') GROUP BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Sort
         Sort Key: metrics.device
         ->  Append
               ->  Partial GroupAggregate
                     Group Key: _hyper_1_1_chunk.device
                     ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                           ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
                                 Index Cond: (device = ANY ('{d1,d2}'::text[]))
               ->  Partial GroupAggregate
                     Group Key: _hyper_1_1_chunk.device
                     ->  Sort
                           Sort Key: _hyper_1_1_chunk.device
                           ->  Seq Scan on _hyper_1_1_chunk
                                 Filter: (device = ANY ('{d1,d2}'::text[]))

:PREFIX SELECT device, min(time) FROM metrics WHERE device =ANY(ARRAY['d1','d2']) AND sensor='B' GROUP BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Sort
         Sort Key: metrics.device
         ->  Append
               ->  Partial GroupAggregate
                     Group Key: _hyper_1_1_chunk.device
                     ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                           ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
                                 Index Cond: ((device = ANY ('{d1,d2}'::text[])) AND (sensor = 'B'::text))
               ->  Partial GroupAggregate
                     Group Key: _hyper_1_1_chunk.device
                     ->  Sort
                           Sort Key: _hyper_1_1_chunk.device
                           ->  Seq Scan on _hyper_1_1_chunk
                                 Filter: ((device = ANY ('{d1,d2}'::text[])) AND (sensor = 'B'::text))

-- HAVING on segmentby (pushed down to lower scan)
:PREFIX SELECT device, min(time) FROM metrics GROUP BY device HAVING device IN ('d1','d2');
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Sort
         Sort Key: metrics.device
         ->  Append
               ->  Partial GroupAggregate
                     Group Key: _hyper_1_1_chunk.device
                     ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                           ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
                                 Index Cond: (device = ANY ('{d1,d2}'::text[]))
               ->  Partial GroupAggregate
                     Group Key: _hyper_1_1_chunk.device
                     ->  Sort
                           Sort Key: _hyper_1_1_chunk.device
                           ->  Seq Scan on _hyper_1_1_chunk
                                 Filter: (device = ANY ('{d1,d2}'::text[]))

:PREFIX SELECT device, min(time) FROM metrics GROUP BY device,sensor HAVING device =ANY(ARRAY['d1','d2']) AND sensor='B';
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Sort
         Sort Key: metrics.device
         ->  Append
               ->  Partial GroupAggregate
                     Group Key: _hyper_1_1_chunk.device
                     ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                           ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
                                 Index Cond: ((device = ANY ('{d1,d2}'::text[])) AND (sensor = 'B'::text))
               ->  Partial GroupAggregate
                     Group Key: _hyper_1_1_chunk.device
                     ->  Sort
                           Sort Key: _hyper_1_1_chunk.device
                           ->  Seq Scan on _hyper_1_1_chunk
                                 Filter: ((device = ANY ('{d1,d2}'::text[])) AND (sensor = 'B'::text))

-- HAVING with aggregate function
:PREFIX SELECT device, min(value) FROM metrics GROUP BY device HAVING min(value) > 20;
--- QUERY PLAN ---
 Finalize HashAggregate
   Group Key: metrics.device
   Filter: (min(metrics.value) > '20'::double precision)
   ->  Append
         ->  Partial HashAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial HashAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Seq Scan on _hyper_1_1_chunk

:PREFIX SELECT device, max(value) FROM metrics GROUP BY device HAVING min(value) < 25;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   Filter: (min(metrics.value) < '25'::double precision)
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

-- HAVING
:PREFIX SELECT device, min(time) FROM metrics GROUP BY device HAVING min(time) > '2025-01-01 00:30:00 PST';
--- QUERY PLAN ---
 Finalize HashAggregate
   Group Key: metrics.device
   Filter: (min(metrics."time") > 'Wed Jan 01 00:30:00 2025 PST'::timestamp with time zone)
   ->  Append
         ->  Partial HashAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial HashAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Seq Scan on _hyper_1_1_chunk

-- tableoid doesnt prevent optimization
:PREFIX SELECT tableoid, device, max(time) FROM metrics GROUP BY device, tableoid;
--- QUERY PLAN ---
 Finalize HashAggregate
   Group Key: metrics.device, metrics.tableoid
   ->  Append
         ->  Partial HashAggregate
               Group Key: _hyper_1_1_chunk.device, _hyper_1_1_chunk.tableoid
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial HashAggregate
               Group Key: _hyper_1_1_chunk.device, _hyper_1_1_chunk.tableoid
               ->  Seq Scan on _hyper_1_1_chunk

:PREFIX SELECT device, max(time), min(tableoid) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

-- multiple aggregates on same column use optimization
:PREFIX SELECT device, min(time), max(time) FROM metrics GROUP BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

-- multiple aggregates on same column with first/last
:PREFIX SELECT device, min(time), first(time, time), last(time, time), max(time) FROM metrics GROUP BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

-- segmentby column at end of targetlist
:PREFIX SELECT first(time, time), last(time, time), device FROM metrics GROUP BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

-- multiple aggregates on different columns use optimization
:PREFIX SELECT device, min(time), max(value) FROM metrics GROUP BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

-- multiple aggregates on same non-time column use optimization
:PREFIX SELECT device, min(value), max(value) FROM metrics GROUP BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

-- multiple aggregates on multiple columns
:PREFIX SELECT device, min(time), max(time), min(value), max(value) FROM metrics GROUP BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

-- expression on aggregates
:PREFIX SELECT device, max(time), min(time), max(time) - min(time)  FROM metrics GROUP BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

-- test with sort
:PREFIX SELECT device, sensor, first(time,time), last(time,time) from metrics group by device, sensor order by 1,2;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device, metrics.sensor
   ->  Merge Append
         Sort Key: metrics.device, metrics.sensor
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device, _hyper_1_1_chunk.sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device, compress_hyper_2_2_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device, _hyper_1_1_chunk.sensor
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device, _hyper_1_1_chunk.sensor
                     ->  Seq Scan on _hyper_1_1_chunk

-- test with subquery (SubqueryScan)
:PREFIX SELECT * FROM (SELECT device, min(time), max(time) FROM metrics GROUP BY device) sub ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

:PREFIX SELECT mx, mn, device FROM (SELECT device, min(time) mn, max(time) mx FROM metrics GROUP BY device) sub ORDER BY device;
--- QUERY PLAN ---
 Subquery Scan on sub
   ->  Finalize GroupAggregate
         Group Key: metrics.device
         ->  Merge Append
               Sort Key: metrics.device
               ->  Partial GroupAggregate
                     Group Key: _hyper_1_1_chunk.device
                     ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                           ->  Sort
                                 Sort Key: compress_hyper_2_2_chunk.device
                                 ->  Seq Scan on compress_hyper_2_2_chunk
               ->  Partial GroupAggregate
                     Group Key: _hyper_1_1_chunk.device
                     ->  Sort
                           Sort Key: _hyper_1_1_chunk.device
                           ->  Seq Scan on _hyper_1_1_chunk

-- test with CTE (also uses SubqueryScan)
:PREFIX WITH agg_data AS (
    SELECT device, min(time) as min_time, max(time) as max_time FROM metrics GROUP BY device
)
SELECT * FROM agg_data ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

-- test parallel queries (not supported with ColumnarIndexScan)
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'on', false);
 set_config 
------------
 on

:PREFIX SELECT device, min(time), max(time) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'off', false);
 set_config 
------------
 off

-- test with UNION ALL (Append node)
:PREFIX SELECT device, min(time) FROM metrics WHERE device = 'd1' GROUP BY device
UNION ALL
SELECT device, min(time) FROM metrics WHERE device = 'd2' GROUP BY device
ORDER BY device;
--- QUERY PLAN ---
 Sort
   Sort Key: metrics.device
   ->  Append
         ->  Finalize GroupAggregate
               ->  Append
                     ->  Partial GroupAggregate
                           ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                                 ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
                                       Index Cond: (device = 'd1'::text)
                     ->  Partial GroupAggregate
                           ->  Seq Scan on _hyper_1_1_chunk
                                 Filter: (device = 'd1'::text)
         ->  Finalize GroupAggregate
               ->  Append
                     ->  Partial GroupAggregate
                           ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk _hyper_1_1_chunk_1
                                 ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk compress_hyper_2_2_chunk_1
                                       Index Cond: (device = 'd2'::text)
                     ->  Partial GroupAggregate
                           ->  Seq Scan on _hyper_1_1_chunk _hyper_1_1_chunk_1
                                 Filter: (device = 'd2'::text)

:PREFIX SELECT device, min(time), max(time) FROM metrics GROUP BY device
UNION ALL
SELECT device, max(time), min(time) FROM metrics GROUP BY device ORDER BY 1,2,3;
--- QUERY PLAN ---
 Incremental Sort
   Sort Key: metrics.device, (min(metrics."time")), (max(metrics."time"))
   Presorted Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Finalize GroupAggregate
               Group Key: metrics.device
               ->  Merge Append
                     Sort Key: metrics.device
                     ->  Partial GroupAggregate
                           Group Key: _hyper_1_1_chunk.device
                           ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                                 ->  Sort
                                       Sort Key: compress_hyper_2_2_chunk.device
                                       ->  Seq Scan on compress_hyper_2_2_chunk
                     ->  Partial GroupAggregate
                           Group Key: _hyper_1_1_chunk.device
                           ->  Sort
                                 Sort Key: _hyper_1_1_chunk.device
                                 ->  Seq Scan on _hyper_1_1_chunk
         ->  Finalize GroupAggregate
               Group Key: metrics_1.device
               ->  Merge Append
                     Sort Key: metrics_1.device
                     ->  Partial GroupAggregate
                           Group Key: _hyper_1_1_chunk_1.device
                           ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk _hyper_1_1_chunk_1
                                 ->  Sort
                                       Sort Key: compress_hyper_2_2_chunk_1.device
                                       ->  Seq Scan on compress_hyper_2_2_chunk compress_hyper_2_2_chunk_1
                     ->  Partial GroupAggregate
                           Group Key: _hyper_1_1_chunk_1.device
                           ->  Sort
                                 Sort Key: _hyper_1_1_chunk_1.device
                                 ->  Seq Scan on _hyper_1_1_chunk _hyper_1_1_chunk_1

-- test WINDOW functions
:PREFIX SELECT device, max(time), lead(device) OVER (PARTITION BY max(time)) from metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Sort
   Sort Key: metrics.device
   ->  WindowAgg
         ->  Sort
               Sort Key: (max(metrics."time"))
               ->  Finalize HashAggregate
                     Group Key: metrics.device
                     ->  Append
                           ->  Partial HashAggregate
                                 Group Key: _hyper_1_1_chunk.device
                                 ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                                       ->  Seq Scan on compress_hyper_2_2_chunk
                           ->  Partial HashAggregate
                                 Group Key: _hyper_1_1_chunk.device
                                 ->  Seq Scan on _hyper_1_1_chunk

:PREFIX SELECT device, max(time), lead(device) OVER (PARTITION BY device) from metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 WindowAgg
   ->  Finalize GroupAggregate
         Group Key: metrics.device
         ->  Merge Append
               Sort Key: metrics.device
               ->  Partial GroupAggregate
                     Group Key: _hyper_1_1_chunk.device
                     ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                           ->  Sort
                                 Sort Key: compress_hyper_2_2_chunk.device
                                 ->  Seq Scan on compress_hyper_2_2_chunk
               ->  Partial GroupAggregate
                     Group Key: _hyper_1_1_chunk.device
                     ->  Sort
                           Sort Key: _hyper_1_1_chunk.device
                           ->  Seq Scan on _hyper_1_1_chunk

-- currently unoptimized queries
-- count on dimension column (could be optimized but currently is not)
:PREFIX SELECT count(time) FROM metrics;
--- QUERY PLAN ---
 Finalize Aggregate
   ->  Append
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial Aggregate
               ->  Seq Scan on _hyper_1_1_chunk

-- count with segmentby column
:PREFIX SELECT count(device) FROM metrics;
--- QUERY PLAN ---
 Finalize Aggregate
   ->  Append
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial Aggregate
               ->  Seq Scan on _hyper_1_1_chunk

-- count with other columns
:PREFIX SELECT count(value) FROM metrics;
--- QUERY PLAN ---
 Finalize Aggregate
   ->  Append
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial Aggregate
               ->  Seq Scan on _hyper_1_1_chunk

-- group by on non-segmentby prevents optimization
:PREFIX SELECT max(time) FROM metrics GROUP BY value;
--- QUERY PLAN ---
 Finalize HashAggregate
   Group Key: metrics.value
   ->  Append
         ->  Partial HashAggregate
               Group Key: _hyper_1_1_chunk.value
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial HashAggregate
               Group Key: _hyper_1_1_chunk.value
               ->  Seq Scan on _hyper_1_1_chunk

:PREFIX SELECT device, max(time) FROM metrics GROUP BY device,value;
--- QUERY PLAN ---
 Finalize HashAggregate
   Group Key: metrics.device, metrics.value
   ->  Append
         ->  Partial HashAggregate
               Group Key: _hyper_1_1_chunk.device, _hyper_1_1_chunk.value
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial HashAggregate
               Group Key: _hyper_1_1_chunk.device, _hyper_1_1_chunk.value
               ->  Seq Scan on _hyper_1_1_chunk

-- unsupported aggregates on any column
:PREFIX SELECT avg(value) FROM metrics;
--- QUERY PLAN ---
 Finalize Aggregate
   ->  Append
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial Aggregate
               ->  Seq Scan on _hyper_1_1_chunk

:PREFIX SELECT string_agg(device, ',') FROM metrics;
--- QUERY PLAN ---
 Finalize Aggregate
   ->  Append
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial Aggregate
               ->  Seq Scan on _hyper_1_1_chunk

-- aggregate on segmentby column does not use optimization
:PREFIX SELECT device, min(sensor) FROM metrics GROUP BY device;
--- QUERY PLAN ---
 Finalize HashAggregate
   Group Key: metrics.device
   ->  Append
         ->  Partial HashAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial HashAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Seq Scan on _hyper_1_1_chunk

-- aggregate on column without metadata
:PREFIX SELECT min(value2) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

-- aggregates with FILTER clause
:PREFIX SELECT count(*) FILTER(WHERE device='dev 1') FROM metrics;
--- QUERY PLAN ---
 Finalize Aggregate
   ->  Append
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial Aggregate
               ->  Seq Scan on _hyper_1_1_chunk

:PREFIX SELECT min(value) FILTER(WHERE device='dev 1') FROM metrics;
--- QUERY PLAN ---
 Finalize Aggregate
   ->  Append
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial Aggregate
               ->  Seq Scan on _hyper_1_1_chunk

-- aggregates with DISTINCT
:PREFIX SELECT count(DISTINCT device) FROM metrics;
--- QUERY PLAN ---
 Aggregate
   ->  Merge Append
         Sort Key: metrics.device
         ->  Custom Scan (SkipScan) on _hyper_1_1_chunk
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Sort
               Sort Key: _hyper_1_1_chunk.device
               ->  Seq Scan on _hyper_1_1_chunk

-- aggregates with ORDER
:PREFIX SELECT min(value ORDER BY time) FROM metrics;
--- QUERY PLAN ---
 Aggregate
   ->  Sort
         Sort Key: metrics."time"
         ->  Append
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
               ->  Seq Scan on _hyper_1_1_chunk

-- WHERE clause on non-segmentby column
:PREFIX SELECT count(*) FROM metrics WHERE value > 10;
--- QUERY PLAN ---
 Finalize Aggregate
   ->  Append
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     Vectorized Filter: (value > '10'::double precision)
                     ->  Seq Scan on compress_hyper_2_2_chunk
                           Filter: (_ts_meta_v2_max_value > '10'::double precision)
         ->  Partial Aggregate
               ->  Seq Scan on _hyper_1_1_chunk
                     Filter: (value > '10'::double precision)

-- first/last with different arg1 and arg2
:PREFIX SELECT first(value, time) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

:PREFIX SELECT last(value, time) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

-- GROUPING SETS / ROLLUP / CUBE are not supported by ColumnarIndexScan
:PREFIX SELECT device, sensor, count(*) FROM metrics GROUP BY ROLLUP(device, sensor) ORDER BY device, sensor;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: metrics.device, metrics.sensor
   Group Key: metrics.device
   Group Key: ()
   ->  Merge Append
         Sort Key: metrics.device, metrics.sensor
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
               ->  Sort
                     Sort Key: compress_hyper_2_2_chunk.device, compress_hyper_2_2_chunk.sensor
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Sort
               Sort Key: _hyper_1_1_chunk.device, _hyper_1_1_chunk.sensor
               ->  Seq Scan on _hyper_1_1_chunk

:PREFIX SELECT device, sensor, count(*) FROM metrics GROUP BY CUBE(device, sensor) ORDER BY device, sensor;
--- QUERY PLAN ---
 Sort
   Sort Key: metrics.device, metrics.sensor
   ->  MixedAggregate
         Hash Key: metrics.sensor
         Group Key: metrics.device, metrics.sensor
         Group Key: metrics.device
         Group Key: ()
         ->  Merge Append
               Sort Key: metrics.device, metrics.sensor
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device, compress_hyper_2_2_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_2_chunk
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device, _hyper_1_1_chunk.sensor
                     ->  Seq Scan on _hyper_1_1_chunk

:PREFIX SELECT device, sensor, count(*) FROM metrics GROUP BY GROUPING SETS ((device), (sensor), ()) ORDER BY device, sensor;
--- QUERY PLAN ---
 Sort
   Sort Key: metrics.device, metrics.sensor
   ->  MixedAggregate
         Hash Key: metrics.device
         Hash Key: metrics.sensor
         Group Key: ()
         ->  Append
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
               ->  Seq Scan on _hyper_1_1_chunk

-- DISTINCT on a segmentby column (Agg with no Aggrefs)
:PREFIX SELECT DISTINCT device FROM metrics ORDER BY device;
--- QUERY PLAN ---
 Unique
   ->  Merge Append
         Sort Key: metrics.device
         ->  Custom Scan (SkipScan) on _hyper_1_1_chunk
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Sort
               Sort Key: _hyper_1_1_chunk.device
               ->  Seq Scan on _hyper_1_1_chunk

-- count(*) without GROUP BY
:PREFIX SELECT count(*) FROM metrics;
--- QUERY PLAN ---
 Finalize Aggregate
   ->  Append
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial Aggregate
               ->  Seq Scan on _hyper_1_1_chunk

-- aggregate only in HAVING (not in SELECT)
:PREFIX SELECT device FROM metrics GROUP BY device HAVING min(value) > 10 ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   Filter: (min(metrics.value) > '10'::double precision)
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

-- GROUP BY ordinal position
:PREFIX SELECT device, min(time) FROM metrics GROUP BY 1 ORDER BY 1;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

-- GROUP BY () empty grouping
:PREFIX SELECT count(*) FROM metrics GROUP BY ();
--- QUERY PLAN ---
 Aggregate
   Group Key: ()
   ->  Append
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
               ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Seq Scan on _hyper_1_1_chunk

-- aggregate wrapped in COALESCE
:PREFIX SELECT device, COALESCE(min(time), '2000-01-01'::timestamptz) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

-- aggregate above a JOIN (Agg not directly above ColumnarScan)
:PREFIX SELECT m.device, min(m.time) FROM metrics m JOIN (VALUES ('d1'), ('d2')) AS d(device) ON m.device = d.device GROUP BY m.device ORDER BY m.device;
--- QUERY PLAN ---
 Sort
   Sort Key: m.device
   ->  HashAggregate
         Group Key: m.device
         ->  Hash Join
               Hash Cond: (m.device = "*VALUES*".column1)
               ->  Append
                     ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk m_1
                           ->  Seq Scan on compress_hyper_2_2_chunk
                     ->  Seq Scan on _hyper_1_1_chunk m_1
               ->  Hash
                     ->  Values Scan on "*VALUES*"

-- ordered-set aggregate (not supported)
:PREFIX SELECT device, percentile_cont(0.5) WITHIN GROUP (ORDER BY value) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
               ->  Sort
                     Sort Key: compress_hyper_2_2_chunk.device
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Sort
               Sort Key: _hyper_1_1_chunk.device
               ->  Seq Scan on _hyper_1_1_chunk

-- bool_and / bool_or (not supported)
:PREFIX SELECT device, bool_and(value > 0), bool_or(value > 0) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

-- partition-wise aggregate
SET enable_partitionwise_aggregate = on;
:PREFIX SELECT device, min(time), max(time) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

SET enable_partitionwise_aggregate = off;
-- Aggref inside a SubLink in the outer tlist (inner aggregate belongs to a separate Agg)
:PREFIX SELECT device, count(*), (SELECT count(*) FROM (VALUES (1),(2),(3)) v(x) WHERE x::text < device) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk
   SubPlan 1
     ->  Aggregate
           ->  Values Scan on "*VALUES*"
                 Filter: ((column1)::text < metrics.device)

-- HAVING with non-pushable expression on grouping columns
:PREFIX SELECT device, sensor, min(time) FROM metrics GROUP BY device, sensor HAVING (device || sensor) LIKE 'd1%' ORDER BY device, sensor;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device, metrics.sensor
   ->  Merge Append
         Sort Key: metrics.device, metrics.sensor
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device, _hyper_1_1_chunk.sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device, compress_hyper_2_2_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_2_chunk
                                 Filter: ((device || sensor) ~~ 'd1%'::text)
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device, _hyper_1_1_chunk.sensor
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device, _hyper_1_1_chunk.sensor
                     ->  Seq Scan on _hyper_1_1_chunk
                           Filter: ((device || sensor) ~~ 'd1%'::text)

-- same Aggref referenced multiple times in tlist
:PREFIX SELECT device, min(time), date_trunc('day', min(time)) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

-- GROUP BY column not in SELECT (appears only as resjunk in Agg tlist)
:PREFIX SELECT count(*) FROM metrics GROUP BY device ORDER BY count(*);
--- QUERY PLAN ---
 Sort
   Sort Key: (sum(compress_hyper_2_2_chunk._ts_meta_count))
   ->  Finalize HashAggregate
         Group Key: metrics.device
         ->  Append
               ->  Partial HashAggregate
                     Group Key: _hyper_1_1_chunk.device
                     ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                           ->  Seq Scan on compress_hyper_2_2_chunk
               ->  Partial HashAggregate
                     Group Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

-- WHERE mixes segmentby and non-segmentby column
:PREFIX SELECT device, count(*) FROM metrics WHERE device = 'd1' AND value > 10 GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   ->  Append
         ->  Partial GroupAggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     Filter: (device = 'd1'::text)
                     Vectorized Filter: (value > '10'::double precision)
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
                           Index Cond: (device = 'd1'::text)
                           Filter: (_ts_meta_v2_max_value > '10'::double precision)
         ->  Partial GroupAggregate
               ->  Seq Scan on _hyper_1_1_chunk
                     Filter: ((value > '10'::double precision) AND (device = 'd1'::text))

-- WHERE with a SubPlan on a segmentby column (not pushable, must not be dropped)
:PREFIX SELECT count(*) FROM metrics WHERE device NOT IN (SELECT 'd' || g FROM generate_series(1,1) g);
--- QUERY PLAN ---
 Finalize Aggregate
   ->  Custom Scan (ChunkAppend) on metrics
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     Filter: (NOT (ANY (device = (hashed SubPlan 1).col1)))
                     ->  Seq Scan on compress_hyper_2_2_chunk
                     SubPlan 1
                       ->  Function Scan on generate_series g
         ->  Partial Aggregate
               ->  Seq Scan on _hyper_1_1_chunk
                     Filter: (NOT (ANY (device = (hashed SubPlan 1).col1)))

:PREFIX SELECT count(*) FROM metrics WHERE device IN (SELECT 'd' || g FROM generate_series(1,2) g);
--- QUERY PLAN ---
 Aggregate
   ->  Hash Semi Join
         Hash Cond: (metrics.device = ('d'::text || (g.g)::text))
         ->  Append
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
               ->  Seq Scan on _hyper_1_1_chunk
         ->  Hash
               ->  Function Scan on generate_series g

-- HAVING with NOT around Aggref
:PREFIX SELECT device, min(value) FROM metrics GROUP BY device HAVING NOT (min(value) > 10) ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   Filter: (min(metrics.value) <= '10'::double precision)
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Sort
                     Sort Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

-- HAVING with Aggref IS NULL
:PREFIX SELECT device, min(value) FROM metrics GROUP BY device HAVING min(value) IS NULL ORDER BY device;
--- QUERY PLAN ---
 Sort
   Sort Key: metrics.device
   ->  Finalize HashAggregate
         Group Key: metrics.device
         Filter: (min(metrics.value) IS NULL)
         ->  Append
               ->  Partial HashAggregate
                     Group Key: _hyper_1_1_chunk.device
                     ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk
                           ->  Seq Scan on compress_hyper_2_2_chunk
               ->  Partial HashAggregate
                     Group Key: _hyper_1_1_chunk.device
                     ->  Seq Scan on _hyper_1_1_chunk

-- GROUP BY with explicit collation (not a bare Var)
:PREFIX SELECT device COLLATE "C", count(*) FROM metrics GROUP BY device COLLATE "C" ORDER BY 1;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: ((metrics.device)::text)
   ->  Merge Append
         Sort Key: ((metrics.device)::text) COLLATE "C"
         ->  Partial GroupAggregate
               Group Key: (_hyper_1_1_chunk.device)::text
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device COLLATE "C"
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: ((_hyper_1_1_chunk.device)::text)
               ->  Sort
                     Sort Key: ((_hyper_1_1_chunk.device)::text) COLLATE "C"
                     ->  Seq Scan on _hyper_1_1_chunk

-- self-join on the same hypertable
:PREFIX SELECT m1.device, min(m1.time) FROM metrics m1 JOIN metrics m2 ON m1.device = m2.device GROUP BY m1.device ORDER BY m1.device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: m1.device
   ->  Merge Join
         Merge Cond: (m1.device = m2.device)
         ->  Merge Append
               Sort Key: m1.device
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk m1_1
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device
                           ->  Seq Scan on compress_hyper_2_2_chunk
               ->  Sort
                     Sort Key: m1_1.device
                     ->  Seq Scan on _hyper_1_1_chunk m1_1
         ->  Materialize
               ->  Merge Append
                     Sort Key: m2.device
                     ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk m2_1
                           ->  Sort
                                 Sort Key: compress_hyper_2_2_chunk_1.device
                                 ->  Seq Scan on compress_hyper_2_2_chunk compress_hyper_2_2_chunk_1
                     ->  Sort
                           Sort Key: m2_1.device
                           ->  Seq Scan on _hyper_1_1_chunk m2_1

\set PREFIX ''
\set ECHO errors
--- Unoptimized results
+++ Optimized results
@@ -1,6 +1,6 @@
  timescaledb.enable_columnarindexscan 
 --------------------------------------
- off
+ on
 
  device | count 
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_1_3_chunk

SET timescaledb.enable_columnarindexscan = on;
\ir :TEST_QUERY_NAME
-- 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.
-- canary for test diff
SHOW timescaledb.enable_columnarindexscan;
 timescaledb.enable_columnarindexscan 
--------------------------------------
 on

-- simple query with count without grouping
:PREFIX SELECT device, count(*) FROM metrics GROUP BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- simple query with count with grouping
:PREFIX SELECT device, count(*) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- simple query with multiple count with grouping
:PREFIX SELECT device, count(*), count(*) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- simple query with 1 max aggregate that can use optimization
:PREFIX SELECT device, max(time) FROM metrics GROUP BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- simple query with 1 min aggregate that can use optimization
:PREFIX SELECT sensor, min(time) FROM metrics GROUP BY sensor ORDER BY sensor;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.sensor
   ->  Merge Append
         Sort Key: metrics.sensor
         ->  Partial GroupAggregate
               Group Key: sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Sort
                           Sort Key: compress_hyper_2_4_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_4_chunk

-- simple query with 1 first aggregate that can use optimization
:PREFIX SELECT device, first(time, time) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- simple query with 1 last aggregate that can use optimization
:PREFIX SELECT device, last(time, time) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- explicit index columns dont prevent optimization
:PREFIX SELECT sensor, min(value) FROM metrics GROUP BY sensor ORDER BY sensor;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.sensor
   ->  Merge Append
         Sort Key: metrics.sensor
         ->  Partial GroupAggregate
               Group Key: sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Sort
                           Sort Key: compress_hyper_2_4_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_4_chunk

:PREFIX SELECT sensor, min(value), max(value) FROM metrics GROUP BY sensor ORDER BY sensor;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.sensor
   ->  Merge Append
         Sort Key: metrics.sensor
         ->  Partial GroupAggregate
               Group Key: sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Sort
                           Sort Key: compress_hyper_2_4_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_4_chunk

-- multiple aggregates on same column
:PREFIX SELECT sensor, min(time), max(time), first(time,time), last(time,time) FROM metrics GROUP BY sensor ORDER BY sensor;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.sensor
   ->  Merge Append
         Sort Key: metrics.sensor
         ->  Partial GroupAggregate
               Group Key: sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Sort
                           Sort Key: compress_hyper_2_4_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_4_chunk

-- same aggregate on multiple columns
:PREFIX SELECT sensor, min(time), min(value)  FROM metrics GROUP BY sensor ORDER BY sensor;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.sensor
   ->  Merge Append
         Sort Key: metrics.sensor
         ->  Partial GroupAggregate
               Group Key: sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Sort
                           Sort Key: compress_hyper_2_4_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_4_chunk

-- same aggregate on multiple columns but different order
:PREFIX SELECT sensor, min(value), min(time), sensor  FROM metrics GROUP BY sensor ORDER BY sensor;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.sensor
   ->  Merge Append
         Sort Key: metrics.sensor
         ->  Partial GroupAggregate
               Group Key: sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Sort
                           Sort Key: compress_hyper_2_4_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_4_chunk

-- multiple aggregates on multiple columns
:PREFIX SELECT sensor, first(time,time), last(time,time), first(value, value), last(value, value)  FROM metrics GROUP BY sensor ORDER BY sensor;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.sensor
   ->  Merge Append
         Sort Key: metrics.sensor
         ->  Partial GroupAggregate
               Group Key: sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Sort
                           Sort Key: compress_hyper_2_4_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_4_chunk

-- test multiple group by columns
:PREFIX SELECT device, sensor, max(time) FROM metrics GROUP BY device,sensor ORDER BY device,sensor;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device, metrics.sensor
   ->  Merge Append
         Sort Key: metrics.device, metrics.sensor
         ->  Partial GroupAggregate
               Group Key: device, sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device, sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- filter on segmentby allows optimization
:PREFIX SELECT device, min(time) FROM metrics WHERE device IN ('d1','d2') GROUP BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
                           Index Cond: (device = ANY ('{d1,d2}'::text[]))
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk
                           Index Cond: (device = ANY ('{d1,d2}'::text[]))

:PREFIX SELECT device, min(time) FROM metrics WHERE device =ANY(ARRAY['d1','d2']) AND sensor='B' GROUP BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
                           Index Cond: ((device = ANY ('{d1,d2}'::text[])) AND (sensor = 'B'::text))
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk
                           Index Cond: ((device = ANY ('{d1,d2}'::text[])) AND (sensor = 'B'::text))

-- HAVING on segmentby (pushed down to lower scan)
:PREFIX SELECT device, min(time) FROM metrics GROUP BY device HAVING device IN ('d1','d2');
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
                           Index Cond: (device = ANY ('{d1,d2}'::text[]))
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk
                           Index Cond: (device = ANY ('{d1,d2}'::text[]))

:PREFIX SELECT device, min(time) FROM metrics GROUP BY device,sensor HAVING device =ANY(ARRAY['d1','d2']) AND sensor='B';
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
                           Index Cond: ((device = ANY ('{d1,d2}'::text[])) AND (sensor = 'B'::text))
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk
                           Index Cond: ((device = ANY ('{d1,d2}'::text[])) AND (sensor = 'B'::text))

-- HAVING with aggregate function
:PREFIX SELECT device, min(value) FROM metrics GROUP BY device HAVING min(value) > 20;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   Filter: (min(metrics.value) > '20'::double precision)
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

:PREFIX SELECT device, max(value) FROM metrics GROUP BY device HAVING min(value) < 25;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   Filter: (min(metrics.value) < '25'::double precision)
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- HAVING
:PREFIX SELECT device, min(time) FROM metrics GROUP BY device HAVING min(time) > '2025-01-01 00:30:00 PST';
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   Filter: (min(metrics."time") > 'Wed Jan 01 00:30:00 2025 PST'::timestamp with time zone)
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- tableoid doesnt prevent optimization
:PREFIX SELECT tableoid, device, max(time) FROM metrics GROUP BY device, tableoid;
--- QUERY PLAN ---
 Finalize HashAggregate
   Group Key: metrics.device, metrics.tableoid
   ->  Append
         ->  Partial HashAggregate
               Group Key: device, tableoid
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial HashAggregate
               Group Key: device, tableoid
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Seq Scan on compress_hyper_2_4_chunk

:PREFIX SELECT device, max(time), min(tableoid) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- multiple aggregates on same column use optimization
:PREFIX SELECT device, min(time), max(time) FROM metrics GROUP BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- multiple aggregates on same column with first/last
:PREFIX SELECT device, min(time), first(time, time), last(time, time), max(time) FROM metrics GROUP BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- segmentby column at end of targetlist
:PREFIX SELECT first(time, time), last(time, time), device FROM metrics GROUP BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- multiple aggregates on different columns use optimization
:PREFIX SELECT device, min(time), max(value) FROM metrics GROUP BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- multiple aggregates on same non-time column use optimization
:PREFIX SELECT device, min(value), max(value) FROM metrics GROUP BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- multiple aggregates on multiple columns
:PREFIX SELECT device, min(time), max(time), min(value), max(value) FROM metrics GROUP BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- expression on aggregates
:PREFIX SELECT device, max(time), min(time), max(time) - min(time)  FROM metrics GROUP BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- test with sort
:PREFIX SELECT device, sensor, first(time,time), last(time,time) from metrics group by device, sensor order by 1,2;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device, metrics.sensor
   ->  Merge Append
         Sort Key: metrics.device, metrics.sensor
         ->  Partial GroupAggregate
               Group Key: device, sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device, sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- test with subquery (SubqueryScan)
:PREFIX SELECT * FROM (SELECT device, min(time), max(time) FROM metrics GROUP BY device) sub ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

:PREFIX SELECT mx, mn, device FROM (SELECT device, min(time) mn, max(time) mx FROM metrics GROUP BY device) sub ORDER BY device;
--- QUERY PLAN ---
 Subquery Scan on sub
   ->  Finalize GroupAggregate
         Group Key: metrics.device
         ->  Merge Append
               Sort Key: metrics.device
               ->  Partial GroupAggregate
                     Group Key: device
                     ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                           ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
               ->  Partial GroupAggregate
                     Group Key: device
                     ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                           ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- test with CTE (also uses SubqueryScan)
:PREFIX WITH agg_data AS (
    SELECT device, min(time) as min_time, max(time) as max_time FROM metrics GROUP BY device
)
SELECT * FROM agg_data ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- test parallel queries (not supported with ColumnarIndexScan)
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'on', false);
 set_config 
------------
 on

:PREFIX SELECT device, min(time), max(time) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'off', false);
 set_config 
------------
 off

-- test with UNION ALL (Append node)
:PREFIX SELECT device, min(time) FROM metrics WHERE device = 'd1' GROUP BY device
UNION ALL
SELECT device, min(time) FROM metrics WHERE device = 'd2' GROUP BY device
ORDER BY device;
--- QUERY PLAN ---
 Sort
   Sort Key: metrics.device
   ->  Append
         ->  Finalize GroupAggregate
               ->  Append
                     ->  Partial GroupAggregate
                           ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                                 ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
                                       Index Cond: (device = 'd1'::text)
                     ->  Partial GroupAggregate
                           ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                                 ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk
                                       Index Cond: (device = 'd1'::text)
         ->  Finalize GroupAggregate
               ->  Append
                     ->  Partial GroupAggregate
                           ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                                 ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk compress_hyper_2_2_chunk_1
                                       Index Cond: (device = 'd2'::text)
                     ->  Partial GroupAggregate
                           ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                                 ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk compress_hyper_2_4_chunk_1
                                       Index Cond: (device = 'd2'::text)

:PREFIX SELECT device, min(time), max(time) FROM metrics GROUP BY device
UNION ALL
SELECT device, max(time), min(time) FROM metrics GROUP BY device ORDER BY 1,2,3;
--- QUERY PLAN ---
 Incremental Sort
   Sort Key: metrics.device, (min(metrics."time")), (max(metrics."time"))
   Presorted Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Finalize GroupAggregate
               Group Key: metrics.device
               ->  Merge Append
                     Sort Key: metrics.device
                     ->  Partial GroupAggregate
                           Group Key: device
                           ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                                 ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
                     ->  Partial GroupAggregate
                           Group Key: device
                           ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                                 ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk
         ->  Finalize GroupAggregate
               Group Key: metrics_1.device
               ->  Merge Append
                     Sort Key: metrics_1.device
                     ->  Partial GroupAggregate
                           Group Key: device
                           ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                                 ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk compress_hyper_2_2_chunk_1
                     ->  Partial GroupAggregate
                           Group Key: device
                           ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                                 ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk compress_hyper_2_4_chunk_1

-- test WINDOW functions
:PREFIX SELECT device, max(time), lead(device) OVER (PARTITION BY max(time)) from metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Sort
   Sort Key: metrics.device
   ->  WindowAgg
         ->  Sort
               Sort Key: (max(metrics."time"))
               ->  Finalize GroupAggregate
                     Group Key: metrics.device
                     ->  Merge Append
                           Sort Key: metrics.device
                           ->  Partial GroupAggregate
                                 Group Key: device
                                 ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                                       ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
                           ->  Partial GroupAggregate
                                 Group Key: device
                                 ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                                       ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

:PREFIX SELECT device, max(time), lead(device) OVER (PARTITION BY device) from metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 WindowAgg
   ->  Finalize GroupAggregate
         Group Key: metrics.device
         ->  Merge Append
               Sort Key: metrics.device
               ->  Partial GroupAggregate
                     Group Key: device
                     ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                           ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
               ->  Partial GroupAggregate
                     Group Key: device
                     ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                           ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- currently unoptimized queries
-- count on dimension column (could be optimized but currently is not)
:PREFIX SELECT count(time) FROM metrics;
--- QUERY PLAN ---
 Finalize Aggregate
   ->  Append
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
                     ->  Seq Scan on compress_hyper_2_4_chunk

-- count with segmentby column
:PREFIX SELECT count(device) FROM metrics;
--- QUERY PLAN ---
 Finalize Aggregate
   ->  Append
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
                     ->  Seq Scan on compress_hyper_2_4_chunk

-- count with other columns
:PREFIX SELECT count(value) FROM metrics;
--- QUERY PLAN ---
 Finalize Aggregate
   ->  Append
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
                     ->  Seq Scan on compress_hyper_2_4_chunk

-- group by on non-segmentby prevents optimization
:PREFIX SELECT max(time) FROM metrics GROUP BY value;
--- QUERY PLAN ---
 Finalize HashAggregate
   Group Key: metrics.value
   ->  Append
         ->  Partial HashAggregate
               Group Key: _hyper_1_1_chunk.value
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial HashAggregate
               Group Key: _hyper_1_3_chunk.value
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
                     ->  Seq Scan on compress_hyper_2_4_chunk

:PREFIX SELECT device, max(time) FROM metrics GROUP BY device,value;
--- QUERY PLAN ---
 Finalize HashAggregate
   Group Key: metrics.device, metrics.value
   ->  Append
         ->  Partial HashAggregate
               Group Key: _hyper_1_1_chunk.device, _hyper_1_1_chunk.value
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial HashAggregate
               Group Key: _hyper_1_3_chunk.device, _hyper_1_3_chunk.value
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
                     ->  Seq Scan on compress_hyper_2_4_chunk

-- unsupported aggregates on any column
:PREFIX SELECT avg(value) FROM metrics;
--- QUERY PLAN ---
 Finalize Aggregate
   ->  Append
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
                     ->  Seq Scan on compress_hyper_2_4_chunk

:PREFIX SELECT string_agg(device, ',') FROM metrics;
--- QUERY PLAN ---
 Finalize Aggregate
   ->  Append
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
                     ->  Seq Scan on compress_hyper_2_4_chunk

-- aggregate on segmentby column does not use optimization
:PREFIX SELECT device, min(sensor) FROM metrics GROUP BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_3_chunk.device
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- aggregate on column without metadata
:PREFIX SELECT min(value2) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_3_chunk.device
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- aggregates with FILTER clause
:PREFIX SELECT count(*) FILTER(WHERE device='dev 1') FROM metrics;
--- QUERY PLAN ---
 Finalize Aggregate
   ->  Append
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
                     ->  Seq Scan on compress_hyper_2_4_chunk

:PREFIX SELECT min(value) FILTER(WHERE device='dev 1') FROM metrics;
--- QUERY PLAN ---
 Finalize Aggregate
   ->  Append
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
                     ->  Seq Scan on compress_hyper_2_4_chunk

-- aggregates with DISTINCT
:PREFIX SELECT count(DISTINCT device) FROM metrics;
--- QUERY PLAN ---
 Aggregate
   ->  Merge Append
         Sort Key: metrics.device
         ->  Custom Scan (SkipScan) on _hyper_1_1_chunk
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Custom Scan (SkipScan) on _hyper_1_3_chunk
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- aggregates with ORDER
:PREFIX SELECT min(value ORDER BY time) FROM metrics;
--- QUERY PLAN ---
 Aggregate
   ->  Sort
         Sort Key: metrics."time"
         ->  Append
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
                     ->  Seq Scan on compress_hyper_2_4_chunk

-- WHERE clause on non-segmentby column
:PREFIX SELECT count(*) FROM metrics WHERE value > 10;
--- QUERY PLAN ---
 Finalize Aggregate
   ->  Append
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     Vectorized Filter: (value > '10'::double precision)
                     ->  Seq Scan on compress_hyper_2_2_chunk
                           Filter: (_ts_meta_v2_max_value > '10'::double precision)
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
                     Vectorized Filter: (value > '10'::double precision)
                     ->  Seq Scan on compress_hyper_2_4_chunk
                           Filter: (_ts_meta_v2_max_value > '10'::double precision)

-- first/last with different arg1 and arg2
:PREFIX SELECT first(value, time) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_3_chunk.device
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

:PREFIX SELECT last(value, time) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_3_chunk.device
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- GROUPING SETS / ROLLUP / CUBE are not supported by ColumnarIndexScan
:PREFIX SELECT device, sensor, count(*) FROM metrics GROUP BY ROLLUP(device, sensor) ORDER BY device, sensor;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: metrics.device, metrics.sensor
   Group Key: metrics.device
   Group Key: ()
   ->  Merge Append
         Sort Key: metrics.device, metrics.sensor
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
               ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
               ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

:PREFIX SELECT device, sensor, count(*) FROM metrics GROUP BY CUBE(device, sensor) ORDER BY device, sensor;
--- QUERY PLAN ---
 Sort
   Sort Key: metrics.device, metrics.sensor
   ->  MixedAggregate
         Hash Key: metrics.sensor
         Group Key: metrics.device, metrics.sensor
         Group Key: metrics.device
         Group Key: ()
         ->  Merge Append
               Sort Key: metrics.device, metrics.sensor
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

:PREFIX SELECT device, sensor, count(*) FROM metrics GROUP BY GROUPING SETS ((device), (sensor), ()) ORDER BY device, sensor;
--- QUERY PLAN ---
 Sort
   Sort Key: metrics.device, metrics.sensor
   ->  MixedAggregate
         Hash Key: metrics.device
         Hash Key: metrics.sensor
         Group Key: ()
         ->  Append
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Seq Scan on compress_hyper_2_2_chunk
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
                     ->  Seq Scan on compress_hyper_2_4_chunk

-- DISTINCT on a segmentby column (Agg with no Aggrefs)
:PREFIX SELECT DISTINCT device FROM metrics ORDER BY device;
--- QUERY PLAN ---
 Unique
   ->  Merge Append
         Sort Key: metrics.device
         ->  Custom Scan (SkipScan) on _hyper_1_1_chunk
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Custom Scan (SkipScan) on _hyper_1_3_chunk
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- count(*) without GROUP BY
:PREFIX SELECT count(*) FROM metrics;
--- QUERY PLAN ---
 Finalize Aggregate
   ->  Append
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Seq Scan on compress_hyper_2_4_chunk

-- aggregate only in HAVING (not in SELECT)
:PREFIX SELECT device FROM metrics GROUP BY device HAVING min(value) > 10 ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   Filter: (min(metrics.value) > '10'::double precision)
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- GROUP BY ordinal position
:PREFIX SELECT device, min(time) FROM metrics GROUP BY 1 ORDER BY 1;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- GROUP BY () empty grouping
:PREFIX SELECT count(*) FROM metrics GROUP BY ();
--- QUERY PLAN ---
 Aggregate
   Group Key: ()
   ->  Append
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
               ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
               ->  Seq Scan on compress_hyper_2_4_chunk

-- aggregate wrapped in COALESCE
:PREFIX SELECT device, COALESCE(min(time), '2000-01-01'::timestamptz) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- aggregate above a JOIN (Agg not directly above ColumnarScan)
:PREFIX SELECT m.device, min(m.time) FROM metrics m JOIN (VALUES ('d1'), ('d2')) AS d(device) ON m.device = d.device GROUP BY m.device ORDER BY m.device;
--- QUERY PLAN ---
 Sort
   Sort Key: m.device
   ->  HashAggregate
         Group Key: m.device
         ->  Nested Loop
               ->  Values Scan on "*VALUES*"
               ->  Append
                     ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk m_1
                           ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
                                 Index Cond: (device = "*VALUES*".column1)
                     ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk m_2
                           ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk
                                 Index Cond: (device = "*VALUES*".column1)

-- ordered-set aggregate (not supported)
:PREFIX SELECT device, percentile_cont(0.5) WITHIN GROUP (ORDER BY value) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
               ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
               ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- bool_and / bool_or (not supported)
:PREFIX SELECT device, bool_and(value > 0), bool_or(value > 0) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: _hyper_1_1_chunk.device
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: _hyper_1_3_chunk.device
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- partition-wise aggregate
SET enable_partitionwise_aggregate = on;
:PREFIX SELECT device, min(time), max(time) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

SET enable_partitionwise_aggregate = off;
-- Aggref inside a SubLink in the outer tlist (inner aggregate belongs to a separate Agg)
:PREFIX SELECT device, count(*), (SELECT count(*) FROM (VALUES (1),(2),(3)) v(x) WHERE x::text < device) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk
   SubPlan 1
     ->  Aggregate
           ->  Values Scan on "*VALUES*"
                 Filter: ((column1)::text < metrics.device)

-- HAVING with non-pushable expression on grouping columns
:PREFIX SELECT device, sensor, min(time) FROM metrics GROUP BY device, sensor HAVING (device || sensor) LIKE 'd1%' ORDER BY device, sensor;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device, metrics.sensor
   ->  Merge Append
         Sort Key: metrics.device, metrics.sensor
         ->  Partial GroupAggregate
               Group Key: device, sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device, compress_hyper_2_2_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_2_chunk
                                 Filter: ((device || sensor) ~~ 'd1%'::text)
         ->  Partial GroupAggregate
               Group Key: device, sensor
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Sort
                           Sort Key: compress_hyper_2_4_chunk.device, compress_hyper_2_4_chunk.sensor
                           ->  Seq Scan on compress_hyper_2_4_chunk
                                 Filter: ((device || sensor) ~~ 'd1%'::text)

-- same Aggref referenced multiple times in tlist
:PREFIX SELECT device, min(time), date_trunc('day', min(time)) FROM metrics GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- GROUP BY column not in SELECT (appears only as resjunk in Agg tlist)
:PREFIX SELECT count(*) FROM metrics GROUP BY device ORDER BY count(*);
--- QUERY PLAN ---
 Sort
   Sort Key: (sum(compress_hyper_2_2_chunk._ts_meta_count))
   ->  Finalize GroupAggregate
         Group Key: metrics.device
         ->  Merge Append
               Sort Key: metrics.device
               ->  Partial GroupAggregate
                     Group Key: device
                     ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                           ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
               ->  Partial GroupAggregate
                     Group Key: device
                     ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                           ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- WHERE mixes segmentby and non-segmentby column
:PREFIX SELECT device, count(*) FROM metrics WHERE device = 'd1' AND value > 10 GROUP BY device ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   ->  Append
         ->  Partial GroupAggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     Vectorized Filter: (value > '10'::double precision)
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
                           Index Cond: (device = 'd1'::text)
                           Filter: (_ts_meta_v2_max_value > '10'::double precision)
         ->  Partial GroupAggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
                     Vectorized Filter: (value > '10'::double precision)
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk
                           Index Cond: (device = 'd1'::text)
                           Filter: (_ts_meta_v2_max_value > '10'::double precision)

-- WHERE with a SubPlan on a segmentby column (not pushable, must not be dropped)
:PREFIX SELECT count(*) FROM metrics WHERE device NOT IN (SELECT 'd' || g FROM generate_series(1,1) g);
--- QUERY PLAN ---
 Finalize Aggregate
   ->  Custom Scan (ChunkAppend) on metrics
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     Filter: (NOT (ANY (device = (hashed SubPlan 1).col1)))
                     ->  Seq Scan on compress_hyper_2_2_chunk
                     SubPlan 1
                       ->  Function Scan on generate_series g
         ->  Partial Aggregate
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
                     Filter: (NOT (ANY (device = (hashed SubPlan 1).col1)))
                     ->  Seq Scan on compress_hyper_2_4_chunk

:PREFIX SELECT count(*) FROM metrics WHERE device IN (SELECT 'd' || g FROM generate_series(1,2) g);
--- QUERY PLAN ---
 Aggregate
   ->  Nested Loop
         ->  Unique
               ->  Sort
                     Sort Key: (('d'::text || (g.g)::text))
                     ->  Function Scan on generate_series g
         ->  Append
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
                           Index Cond: (device = ('d'::text || (g.g)::text))
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk
                           Index Cond: (device = ('d'::text || (g.g)::text))

-- HAVING with NOT around Aggref
:PREFIX SELECT device, min(value) FROM metrics GROUP BY device HAVING NOT (min(value) > 10) ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   Filter: (min(metrics.value) <= '10'::double precision)
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- HAVING with Aggref IS NULL
:PREFIX SELECT device, min(value) FROM metrics GROUP BY device HAVING min(value) IS NULL ORDER BY device;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: metrics.device
   Filter: (min(metrics.value) IS NULL)
   ->  Merge Append
         Sort Key: metrics.device
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_1_chunk metrics
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: device
               ->  Custom Scan (ColumnarIndexScan) on _hyper_1_3_chunk metrics
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk

-- GROUP BY with explicit collation (not a bare Var)
:PREFIX SELECT device COLLATE "C", count(*) FROM metrics GROUP BY device COLLATE "C" ORDER BY 1;
--- QUERY PLAN ---
 Finalize GroupAggregate
   Group Key: ((metrics.device)::text)
   ->  Merge Append
         Sort Key: ((metrics.device)::text) COLLATE "C"
         ->  Partial GroupAggregate
               Group Key: (_hyper_1_1_chunk.device)::text
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_2_chunk.device COLLATE "C"
                           ->  Seq Scan on compress_hyper_2_2_chunk
         ->  Partial GroupAggregate
               Group Key: (_hyper_1_3_chunk.device)::text
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk
                     ->  Sort
                           Sort Key: compress_hyper_2_4_chunk.device COLLATE "C"
                           ->  Seq Scan on compress_hyper_2_4_chunk

-- self-join on the same hypertable
:PREFIX SELECT m1.device, min(m1.time) FROM metrics m1 JOIN metrics m2 ON m1.device = m2.device GROUP BY m1.device ORDER BY m1.device;
--- QUERY PLAN ---
 GroupAggregate
   Group Key: m1.device
   ->  Merge Join
         Merge Cond: (m1.device = m2.device)
         ->  Merge Append
               Sort Key: m1.device
               ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk m1_1
                     ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk
               ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk m1_2
                     ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk
         ->  Materialize
               ->  Merge Append
                     Sort Key: m2.device
                     ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk m2_1
                           ->  Index Scan using compress_hyper_2_2_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_2_chunk compress_hyper_2_2_chunk_1
                     ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk m2_2
                           ->  Index Scan using compress_hyper_2_4_chunk_device_sensor__ts_meta_v2_last_tim_idx on compress_hyper_2_4_chunk compress_hyper_2_4_chunk_1

\set PREFIX ''
\set ECHO errors
--- Unoptimized results
+++ Optimized results
@@ -1,6 +1,6 @@
  timescaledb.enable_columnarindexscan 
 --------------------------------------
- off
+ on
 
  device | count 
NOTICE:  using column "ts" as partitioning column
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_3_5_chunk

--- QUERY PLAN ---
 HashAggregate
   Group Key: (_hyper_3_5_chunk.device_id)::text
   ->  Custom Scan (ColumnarScan) on _hyper_3_5_chunk
         ->  Seq Scan on compress_hyper_4_6_chunk

 count 
-------
     1
     1

 count 
-------
     6

 count 
-------
     6

 count 
-------
     6

 count 
-------
     6