skip_scan.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.
-- need superuser to adjust statistics in load script
\c :TEST_DBNAME :ROLE_SUPERUSER
\set TEST_BASE_NAME skip_scan
SELECT format('include/%s_load.sql', :'TEST_BASE_NAME') AS "TEST_LOAD_NAME",
    format('include/%s_multi_load.sql', :'TEST_BASE_NAME') AS "TEST_MULTI_LOAD_NAME",
    format('include/%s_query.sql', :'TEST_BASE_NAME') AS "TEST_QUERY_NAME",
    format('%s/results/%s_results_unoptimized.out', :'TEST_OUTPUT_DIR', :'TEST_BASE_NAME') AS "TEST_RESULTS_UNOPTIMIZED",
    format('%s/results/%s_results_optimized.out', :'TEST_OUTPUT_DIR', :'TEST_BASE_NAME') AS "TEST_RESULTS_OPTIMIZED" \gset
SELECT format('\! diff -u --label "Unoptimized results" --label "Optimized results" %s %s', :'TEST_RESULTS_UNOPTIMIZED', :'TEST_RESULTS_OPTIMIZED') AS "DIFF_CMD" \gset
\ir :TEST_LOAD_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.
CREATE TABLE skip_scan(time int, dev int, dev_name text, val int);
-- Turn off autovacuum to not trigger new vacuums that restores the
-- adjusted statistics
ALTER TABLE skip_scan SET (autovacuum_enabled = off);
INSERT INTO skip_scan SELECT t, d, 'device_' || d::text, t*d FROM generate_series(1, 1000) t, generate_series(1, 10) d;
INSERT INTO skip_scan VALUES (NULL, 0, -1, NULL), (0, NULL, -1, NULL);
INSERT INTO skip_scan(time,dev,dev_name,val) SELECT t, NULL, NULL, NULL FROM generate_series(0, 999, 50) t;
ANALYZE skip_scan;
CREATE TABLE skip_scan_nulls(time int);
-- Turn off autovacuum to not trigger new vacuums that restores the
-- adjusted statistics
ALTER TABLE skip_scan_nulls SET (autovacuum_enabled = off);
CREATE INDEX ON skip_scan_nulls(time);
INSERT INTO skip_scan_nulls SELECT NULL FROM generate_series(1,100);
ANALYZE skip_scan_nulls;
-- create hypertable with different physical layouts in the chunks
CREATE TABLE skip_scan_ht(f1 int, f2 int, f3 int, time int NOT NULL, dev int, dev_name text, val int);
-- Turn off autovacuum to not trigger new vacuums that restores the
-- adjusted statistics
ALTER TABLE skip_scan_ht SET (autovacuum_enabled = off);
SELECT create_hypertable('skip_scan_ht', 'time', chunk_time_interval => 250, create_default_indexes => false);
     create_hypertable     
---------------------------
 (1,public,skip_scan_ht,t)

INSERT INTO skip_scan_ht(time,dev,dev_name,val) SELECT t, d, 'device_' || d::text, random() FROM generate_series(0, 249) t, generate_series(1, 10) d;
ALTER TABLE skip_scan_ht DROP COLUMN f1;
INSERT INTO skip_scan_ht(time,dev,dev_name,val) SELECT t, d, 'device_' || d::text, random() FROM generate_series(250, 499) t, generate_series(1, 10) d;
ALTER TABLE skip_scan_ht DROP COLUMN f2;
INSERT INTO skip_scan_ht(time,dev,dev_name,val) SELECT t, d, 'device_' || d::text, random() FROM generate_series(500, 749) t, generate_series(1, 10) d;
ALTER TABLE skip_scan_ht DROP COLUMN f3;
INSERT INTO skip_scan_ht(time,dev,dev_name,val) SELECT t, d, 'device_' || d::text, random() FROM generate_series(750, 999) t, generate_series(1, 10) d;
INSERT INTO skip_scan_ht(time,dev,dev_name,val) SELECT t, NULL, NULL, NULL FROM generate_series(0, 999, 50) t;
ANALYZE skip_scan_ht;
ALTER TABLE skip_scan_ht SET (timescaledb.compress,timescaledb.compress_orderby='time desc', timescaledb.compress_segmentby='dev');
-- create compressed hypertable with different physical layouts in the chunks
CREATE TABLE skip_scan_htc(f1 int, f2 int, f3 int, time int NOT NULL, dev int, dev_name text, val int);
-- Turn off autovacuum to not trigger new vacuums that restores the
-- adjusted statistics
ALTER TABLE skip_scan_htc SET (autovacuum_enabled = off);
SELECT create_hypertable('skip_scan_htc', 'time', chunk_time_interval => 250, create_default_indexes => false);
     create_hypertable      
----------------------------
 (3,public,skip_scan_htc,t)

ALTER TABLE skip_scan_htc SET (timescaledb.compress, timescaledb.compress_orderby='time desc', timescaledb.compress_segmentby='dev');
INSERT INTO skip_scan_htc(time,dev,dev_name,val) SELECT t, d, 'device_' || d::text, random() FROM generate_series(0, 249) t, generate_series(1, 10) d;
ALTER TABLE skip_scan_htc DROP COLUMN f1;
INSERT INTO skip_scan_htc(time,dev,dev_name,val) SELECT t, d, 'device_' || d::text, random() FROM generate_series(250, 499) t, generate_series(1, 10) d;
ALTER TABLE skip_scan_htc DROP COLUMN f2;
INSERT INTO skip_scan_htc(time,dev,dev_name,val) SELECT t, d, 'device_' || d::text, random() FROM generate_series(500, 749) t, generate_series(1, 10) d;
ALTER TABLE skip_scan_htc DROP COLUMN f3;
INSERT INTO skip_scan_htc(time,dev,dev_name,val) SELECT t, d, 'device_' || d::text, random() FROM generate_series(750, 999) t, generate_series(1, 10) d;
INSERT INTO skip_scan_htc(time,dev,dev_name,val) SELECT t, NULL, NULL, NULL FROM generate_series(0, 999, 50) t;
ANALYZE skip_scan_htc;
CREATE TABLE skip_scan_insert(time int, dev int, dev_name text, val int, query text);
CREATE OR REPLACE FUNCTION int_func_immutable() RETURNS int LANGUAGE SQL IMMUTABLE SECURITY DEFINER AS $$SELECT 1; $$;
CREATE OR REPLACE FUNCTION int_func_stable() RETURNS int LANGUAGE SQL STABLE SECURITY DEFINER AS $$ SELECT 2; $$;
CREATE OR REPLACE FUNCTION int_func_volatile() RETURNS int LANGUAGE SQL VOLATILE SECURITY DEFINER AS $$ SELECT 3; $$;
CREATE OR REPLACE FUNCTION inta_func_immutable() RETURNS int[] LANGUAGE SQL IMMUTABLE SECURITY DEFINER AS $$ SELECT ARRAY[1,2,3]; $$;
CREATE OR REPLACE FUNCTION inta_func_stable() RETURNS int[] LANGUAGE SQL STABLE SECURITY DEFINER AS $$ SELECT ARRAY[2,3,4]; $$;
CREATE OR REPLACE FUNCTION inta_func_volatile() RETURNS int[] LANGUAGE SQL VOLATILE SECURITY DEFINER AS $$ SELECT ARRAY[3,4,5]; $$;
-- adjust statistics so we get skipscan plans
UPDATE pg_statistic SET stadistinct=1, stanullfrac=0.5 WHERE starelid='skip_scan'::regclass;
UPDATE pg_statistic SET stadistinct=1, stanullfrac=0.5 WHERE starelid='skip_scan_nulls'::regclass;
UPDATE pg_statistic SET stadistinct=1, stanullfrac=0.5 WHERE starelid='skip_scan_ht'::regclass;
UPDATE pg_statistic SET stadistinct=1, stanullfrac=0.5 WHERE starelid IN (select inhrelid from pg_inherits where inhparent='skip_scan_ht'::regclass);
UPDATE pg_statistic SET stadistinct=1, stanullfrac=0.5 WHERE starelid='skip_scan_htc'::regclass;
UPDATE pg_statistic SET stadistinct=1, stanullfrac=0.5 WHERE starelid IN (select inhrelid from pg_inherits where inhparent='skip_scan_htc'::regclass);
-- create compressed hypertable with different physical layouts in the compressed chunks
CREATE TABLE skip_scan_htcl(f1 int, f2 int, f3 int, time int NOT NULL, dev int, dev_name text, val int);
-- Turn off autovacuum to not trigger new vacuums that restores the
-- adjusted statistics
ALTER TABLE skip_scan_htcl SET (autovacuum_enabled = off);
SELECT create_hypertable('skip_scan_htcl', 'time', chunk_time_interval => 250, create_default_indexes => false);
      create_hypertable      
-----------------------------
 (5,public,skip_scan_htcl,t)

ALTER TABLE skip_scan_htcl SET (timescaledb.compress, timescaledb.compress_orderby='time desc', timescaledb.compress_segmentby='dev');
INSERT INTO skip_scan_htcl(time,dev,dev_name,val) SELECT t, d, 'device_' || d::text, random() FROM generate_series(0, 249) t, generate_series(1, 10) d;
-- Make sure 1st compressed chunk has columns which will be dropped later, it also doesn't have NULLs
SELECT compress_chunk(ch) FROM show_chunks('skip_scan_htcl') ch;
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_5_9_chunk

ALTER TABLE skip_scan_htcl DROP COLUMN f1;
INSERT INTO skip_scan_htcl(time,dev,dev_name,val) SELECT t, d, 'device_' || d::text, random() FROM generate_series(250, 499) t, generate_series(1, 10) d;
ALTER TABLE skip_scan_htcl DROP COLUMN f2;
INSERT INTO skip_scan_htcl(time,dev,dev_name,val) SELECT t, d, 'device_' || d::text, random() FROM generate_series(500, 749) t, generate_series(1, 10) d;
ALTER TABLE skip_scan_htcl DROP COLUMN f3;
INSERT INTO skip_scan_htcl(time,dev,dev_name,val) SELECT t, d, 'device_' || d::text, random() FROM generate_series(750, 999) t, generate_series(1, 10) d;
INSERT INTO skip_scan_htcl(time,dev,dev_name,val) SELECT t, NULL, NULL, NULL FROM generate_series(0, 999, 50) t;
-- The rest of the compressed chunks do not have dropped columns
-- compressed chunks #2 and #3 have attnos out of sync with uncompressed chunks
SELECT compress_chunk(ch) FROM show_chunks('skip_scan_htcl') ch order by 1 desc limit 3;
             compress_chunk              
-----------------------------------------
 _timescaledb_internal._hyper_5_13_chunk
 _timescaledb_internal._hyper_5_12_chunk
 _timescaledb_internal._hyper_5_11_chunk

ANALYZE skip_scan_htcl;
-- adjust statistics so we get skipscan plans
UPDATE pg_statistic SET stadistinct=1, stanullfrac=0.5 WHERE starelid='skip_scan_htcl'::regclass;
UPDATE pg_statistic SET stadistinct=1, stanullfrac=0.5 WHERE starelid IN (select inhrelid from pg_inherits where inhparent='skip_scan_htcl'::regclass);
-- run tests on normal table and diff results
\set TABLE skip_scan
\set PREFIX ''
\o :TEST_RESULTS_OPTIMIZED
\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 result diff
SELECT current_setting('timescaledb.enable_skipscan') AS enable_skipscan;
-- test different index configurations
-- no index so we cant do SkipScan
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev;
-- NULLS LAST index on dev
CREATE INDEX skip_scan_idx_dev_nulls_last ON :TABLE(dev);
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev DESC;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT dev FROM :TABLE WHERE dev IS NOT NULL ORDER BY dev;
DROP INDEX skip_scan_idx_dev_nulls_last;
-- NULLS FIRST index on dev
CREATE INDEX skip_scan_idx_dev_nulls_first ON :TABLE(dev NULLS FIRST);
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev NULLS FIRST;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev NULLS FIRST;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT dev FROM :TABLE WHERE dev IS NOT NULL ORDER BY dev NULLS FIRST;
DROP INDEX skip_scan_idx_dev_nulls_first;
-- multicolumn index with dev as leading column
CREATE INDEX skip_scan_idx_dev_time_idx ON :TABLE(dev, time);
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev DESC, time DESC;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT dev FROM :TABLE WHERE dev IS NOT NULL ORDER BY dev;
DROP INDEX skip_scan_idx_dev_time_idx;
-- multicolumn index with dev as non-leading column
CREATE INDEX skip_scan_idx_time_dev_idx ON :TABLE(time, dev);
:PREFIX SELECT DISTINCT dev FROM :TABLE WHERE time = 100 ORDER BY dev;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time = 100;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT dev FROM :TABLE WHERE time = 100 AND dev IS NOT NULL ORDER BY dev;
DROP INDEX skip_scan_idx_time_dev_idx;
-- hash index is not ordered so can't use skipscan
CREATE INDEX skip_scan_idx_hash ON :TABLE USING hash(dev_name);
:PREFIX SELECT DISTINCT dev_name FROM :TABLE WHERE dev_name IN ('device_1','device_2') ORDER BY dev_name;
DROP INDEX skip_scan_idx_hash;
-- expression indexes
-- currently not supported by skipscan
CREATE INDEX skip_scan_expr_idx ON :TABLE((dev % 3));
:PREFIX SELECT DISTINCT dev%3 FROM :TABLE ORDER BY dev%3;
:PREFIX SELECT DISTINCT ON (dev%3) dev FROM :TABLE ORDER BY dev%3;
DROP INDEX skip_scan_expr_idx;
CREATE INDEX ON :TABLE(dev_name);
-- Tests for #8107: accounting for scanning most of the input due to non-index quals
-- Non-index qual, no tuples match: SeqScan preferred
:PREFIX SELECT DISTINCT dev_name FROM :TABLE WHERE val < 0 ORDER BY 1;
-- Highly selective non-index qual, have to scan many tuples to match: SeqScan preferred
:PREFIX SELECT DISTINCT dev_name FROM :TABLE WHERE time = 100 ORDER BY 1;
-- Same but a combo of non-index quals
:PREFIX SELECT DISTINCT dev_name FROM :TABLE WHERE time = 100 and dev = 1 ORDER BY 1;
-- Highly selective index qual: less tuples to scan for low-selective non-index qual, can choose SkipScan
:PREFIX SELECT DISTINCT dev_name FROM :TABLE WHERE dev_name IS NULL and dev = 1 ORDER BY 1;
CREATE INDEX ON :TABLE(dev);
-- RowCompareExpr where some args are not in the index:
-- we extract lossy "dev >=5" index condition which is strict and doesn't allow NULLs
SET timescaledb.debug_skip_scan_info TO true;
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE (dev, time) > (5,100);
psql:include/skip_scan_query.sql:74: INFO:  SkipScan used on skip_scan_dev_idx(dev NOT NULL)
RESET timescaledb.debug_skip_scan_info;
CREATE INDEX ON :TABLE(dev, time);
CREATE INDEX ON :TABLE(time,dev);
CREATE INDEX ON :TABLE(time,dev,val);
\qecho basic DISTINCT queries on :TABLE
:PREFIX SELECT DISTINCT dev, 'q1_1' FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev_name, 'q1_2' FROM :TABLE ORDER BY dev_name;
:PREFIX SELECT DISTINCT dev, 'q1_3', NULL FROM :TABLE ORDER BY dev;
\qecho stable expression in targetlist on :TABLE
:PREFIX SELECT DISTINCT dev, 'q1_4', length(md5(now()::text)) FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev_name, 'q1_5', length(md5(now()::text)) FROM :TABLE ORDER BY dev_name;
-- volatile expression in targetlist
:PREFIX SELECT DISTINCT dev, 'q1_6', length(md5(random()::text)) FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev_name, 'q1_7', length(md5(random()::text)) FROM :TABLE ORDER BY dev_name;
-- queries without skipscan because distinct is not limited to specific column
:PREFIX SELECT DISTINCT * FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT *, 'q1_9' FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev, time, 'q1_10' FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev, NULL, 'q1_11' FROM :TABLE ORDER BY dev;
-- distinct on expressions not supported
:PREFIX SELECT DISTINCT time_bucket(10,time), 'q1_12' FROM :TABLE;
:PREFIX SELECT DISTINCT length(dev_name), 'q1_13' FROM :TABLE;
:PREFIX SELECT DISTINCT 3*time, 'q1_14' FROM :TABLE;
:PREFIX SELECT DISTINCT 'Device ' || dev_name FROM :TABLE;
-- DISTINCT ON queries
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_2' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_3', NULL FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_4', length(md5(now()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_5', length(md5(random()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) *, 'q2_7' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, time, 'q2_8' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, NULL, 'q2_9' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) time, 'q2_10' FROM :TABLE ORDER by dev, time;
:PREFIX SELECT DISTINCT ON (dev) dev, tableoid::regclass, 'q2_11' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, int_func_immutable(), 'q2_12' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, int_func_stable(), 'q2_13' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, int_func_volatile(), 'q2_14' FROM :TABLE;
-- DISTINCT ON queries on TEXT column
:PREFIX SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE WHERE dev_name IS NOT NULL;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_2' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_3', NULL FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_4', length(md5(now()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_5', length(md5(random()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) * FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) *, 'q3_7' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, time, 'q3_8' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, NULL, 'q3_9' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) time, 'q3_10' FROM :TABLE ORDER by dev_name, time;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, tableoid::regclass, 'q3_11' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name::varchar) dev_name::varchar FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev, int_func_immutable(), 'q3_13' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev, int_func_stable(), 'q3_14' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev, int_func_volatile(), 'q3_15' FROM :TABLE;
\qecho DISTINCT with wholerow var
:PREFIX SELECT DISTINCT ON (dev) :TABLE FROM :TABLE;
-- should not use SkipScan since we only support SkipScan on single-column distinct
:PREFIX SELECT DISTINCT :TABLE FROM :TABLE;
\qecho LIMIT queries on :TABLE
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE LIMIT 3;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev DESC, time DESC LIMIT 3;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev, time LIMIT 3;
\qecho range queries on :TABLE
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time BETWEEN 100 AND 300;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time < 200;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time > 800;
\qecho ordered append on :TABLE
:PREFIX SELECT * FROM :TABLE ORDER BY time;
:PREFIX SELECT DISTINCT ON (time) time FROM :TABLE WHERE time BETWEEN 0 AND 5000;
\qecho SUBSELECTS on :TABLE
:PREFIX SELECT time, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a;
:PREFIX SELECT NULL, dev, NULL, 'q4_3' FROM (SELECT DISTINCT ON (dev) dev FROM :TABLE) a;
:PREFIX SELECT time, dev, NULL, 'q4_4' FROM (SELECT DISTINCT ON (dev) dev, time FROM :TABLE) a;
\qecho ORDER BY
:PREFIX SELECT time, dev, val, 'q5_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE ORDER BY dev, time) a;
:PREFIX SELECT time, dev, val, 'q5_2' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE ORDER BY dev DESC, time DESC) a;
\qecho WHERE CLAUSES
:PREFIX SELECT time, dev, val, 'q6_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > 5) a;
:PREFIX SELECT time, dev, val, 'q6_2' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE time > 5) a;
:PREFIX SELECT time, dev, val, 'q6_3' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a WHERE dev > 5;
:PREFIX SELECT time, dev, val, 'q6_4' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a WHERE time > 5;
--\qecho immutable func in WHERE clause on :TABLE
:PREFIX SELECT DISTINCT ON (dev) *, 'q6_5' FROM :TABLE WHERE dev > int_func_immutable();
--\qecho stable func in WHERE clause on :TABLE
:PREFIX SELECT DISTINCT ON (dev) *, 'q6_6' FROM :TABLE WHERE dev > int_func_stable();
--\qecho volatile func in WHERE clause on :TABLE
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > int_func_volatile();
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_immutable());
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_stable());
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_volatile());
-- RowCompareExpr
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE (dev, time) > (5,100);
-- always false expr similar to our initial skip qual
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > NULL;
-- no tuples matching
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > 20;
-- multiple constraints in WHERE clause
:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev > 5 AND time = 100;
:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev > 5 AND time > 200;
:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev >= 5 AND dev < 7 AND dev >= 2;
:PREFIX SELECT DISTINCT ON (dev) dev,time,val FROM :TABLE WHERE time > 100 AND time < 200 AND val > 10 AND val < 10000 AND dev > 2 AND dev < 7 ORDER BY dev,time;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE dev IS NULL;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE WHERE dev_name IS NULL;
-- test constants in ORDER BY
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = 1 ORDER BY dev, time DESC;
:PREFIX SELECT DISTINCT dev, time FROM :TABLE WHERE dev = 1 and time = 100 ORDER BY dev, time DESC;
:PREFIX SELECT DISTINCT dev_name::varchar FROM :TABLE  WHERE dev_name::varchar = 'device_1' ORDER BY 1;
-- test distinct PathKey with sortref = 0 in PG15 due to FALSE filter not pushed into relation (should not crash in PG15)
:PREFIX SELECT DISTINCT sq.dev FROM (SELECT dev FROM :TABLE) sq JOIN :TABLE ref ON (sq.dev = ref.dev) WHERE 1 > 2;
-- test distinct PathKey with sortref not aligning with targets sortrefs in PG15 due to Window function
:PREFIX SELECT DISTINCT
    case when false then c10 else c10 end as c0,
    max(c8) over (partition by c10 order by c6) as c1
FROM (SELECT time as c6, val as c8, dev as c10 FROM :TABLE WHERE false) subq WHERE false;
-- test multiple distincts with all but one pinned: #7998
:PREFIX SELECT DISTINCT dev, dev FROM :TABLE ORDER BY 1;
:PREFIX SELECT DISTINCT dev, time FROM :TABLE WHERE time = 100 ORDER BY 1;
:PREFIX SELECT DISTINCT dev, time, val FROM :TABLE WHERE time = 100 and val = 100 ORDER BY 1;
:PREFIX SELECT DISTINCT ON (dev, time) * FROM :TABLE WHERE time = 100 ORDER BY dev;
:PREFIX SELECT DISTINCT ON (dev, time) dev, time FROM :TABLE WHERE dev = 1 AND time < 20 ORDER BY dev, time;
-- CTE
:PREFIX WITH devices AS (
	SELECT DISTINCT ON (dev) dev FROM :TABLE
)
SELECT * FROM devices;
:PREFIX WITH devices AS (
	SELECT DISTINCT dev FROM :TABLE
)
SELECT * FROM devices ORDER BY dev;
-- prepared statements
PREPARE prep AS SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
DEALLOCATE prep;
-- ReScan tests
:PREFIX SELECT time, dev, val, 'q7_1' FROM (SELECT DISTINCT ON (dev) * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT * FROM :TABLE WHERE time != a.v) b) a;
:PREFIX SELECT time, dev, val, 'q7_2' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev != a.v) b) a;
-- RuntimeKeys
:PREFIX SELECT time, dev, val, 'q8_1' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev >= a.v) b) c;
-- Emulate multi-column DISTINCT using multiple SkipSkans
:PREFIX SELECT time, dev, val, 'q9_1' FROM (SELECT b.* FROM
    (SELECT DISTINCT ON (dev) dev FROM :TABLE) a,
    LATERAL (SELECT DISTINCT ON (time) * FROM :TABLE WHERE dev = a.dev) b) c;
:PREFIX SELECT time, dev, NULL, 'q9_2' FROM (SELECT b.* FROM
    (SELECT DISTINCT ON (dev) dev FROM :TABLE) a,
    LATERAL (SELECT DISTINCT ON (time) dev, time FROM :TABLE WHERE dev = a.dev) b) c;
-- Test that the multi-column DISTINCT emulation is equivalent to a real multi-column DISTINCT
:PREFIX SELECT * FROM
   (SELECT DISTINCT ON (dev) dev FROM :TABLE) a,
   LATERAL (SELECT DISTINCT ON (time) dev, time FROM :TABLE WHERE dev = a.dev) b;
:PREFIX SELECT DISTINCT ON (dev, time) dev, time FROM :TABLE WHERE coalesce(dev, -1) >= 0;
:PREFIX SELECT DISTINCT ON (dev, time) dev, time FROM :TABLE WHERE coalesce(dev, -1) >= 0
UNION SELECT b.* FROM
   (SELECT DISTINCT ON (dev) dev FROM :TABLE) a,
   LATERAL (SELECT DISTINCT ON (time) dev, time FROM :TABLE WHERE dev = a.dev) b;
-- SkipScan into INSERT
:PREFIX INSERT INTO skip_scan_insert(time, dev, val, query) SELECT time, dev, val, 'q10_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a;
-- parallel query
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'on', false);
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev;
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'off', false);
TRUNCATE skip_scan_insert;
-- table with only nulls
:PREFIX SELECT DISTINCT ON (time) time FROM skip_scan_nulls;
-- no tuples in resultset
:PREFIX SELECT DISTINCT ON (time) time FROM skip_scan_nulls WHERE time IS NOT NULL;
-- Test for SDC issue #2976
-- We should use index "btree ("time", dev, dev_name)" where "dev_name" index key is #3 but we have 2 index quals including SkipScan qual
-- We should not drop qual on "dev_name"
CREATE INDEX skip_scan_idx_time_dev_dname ON :TABLE(time,dev,dev_name);
:PREFIX SELECT DISTINCT time FROM :TABLE WHERE dev_name IS NULL ORDER BY 1;
DROP INDEX skip_scan_idx_time_dev_dname;
-- "dev_name" is not a key column: it's not in the index quals and should be a filter
CREATE INDEX skip_scan_idx_time_dev_dname ON :TABLE(time,dev) INCLUDE(dev_name);
:PREFIX SELECT DISTINCT time FROM :TABLE WHERE dev_name IS NULL ORDER BY 1;
DROP INDEX skip_scan_idx_time_dev_dname;
\o
SET timescaledb.enable_skipscan TO false;
\o :TEST_RESULTS_UNOPTIMIZED
\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 result diff
SELECT current_setting('timescaledb.enable_skipscan') AS enable_skipscan;
-- test different index configurations
-- no index so we cant do SkipScan
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev;
-- NULLS LAST index on dev
CREATE INDEX skip_scan_idx_dev_nulls_last ON :TABLE(dev);
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev DESC;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT dev FROM :TABLE WHERE dev IS NOT NULL ORDER BY dev;
DROP INDEX skip_scan_idx_dev_nulls_last;
-- NULLS FIRST index on dev
CREATE INDEX skip_scan_idx_dev_nulls_first ON :TABLE(dev NULLS FIRST);
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev NULLS FIRST;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev NULLS FIRST;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT dev FROM :TABLE WHERE dev IS NOT NULL ORDER BY dev NULLS FIRST;
DROP INDEX skip_scan_idx_dev_nulls_first;
-- multicolumn index with dev as leading column
CREATE INDEX skip_scan_idx_dev_time_idx ON :TABLE(dev, time);
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev DESC, time DESC;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT dev FROM :TABLE WHERE dev IS NOT NULL ORDER BY dev;
DROP INDEX skip_scan_idx_dev_time_idx;
-- multicolumn index with dev as non-leading column
CREATE INDEX skip_scan_idx_time_dev_idx ON :TABLE(time, dev);
:PREFIX SELECT DISTINCT dev FROM :TABLE WHERE time = 100 ORDER BY dev;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time = 100;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT dev FROM :TABLE WHERE time = 100 AND dev IS NOT NULL ORDER BY dev;
DROP INDEX skip_scan_idx_time_dev_idx;
-- hash index is not ordered so can't use skipscan
CREATE INDEX skip_scan_idx_hash ON :TABLE USING hash(dev_name);
:PREFIX SELECT DISTINCT dev_name FROM :TABLE WHERE dev_name IN ('device_1','device_2') ORDER BY dev_name;
DROP INDEX skip_scan_idx_hash;
-- expression indexes
-- currently not supported by skipscan
CREATE INDEX skip_scan_expr_idx ON :TABLE((dev % 3));
:PREFIX SELECT DISTINCT dev%3 FROM :TABLE ORDER BY dev%3;
:PREFIX SELECT DISTINCT ON (dev%3) dev FROM :TABLE ORDER BY dev%3;
DROP INDEX skip_scan_expr_idx;
CREATE INDEX ON :TABLE(dev_name);
-- Tests for #8107: accounting for scanning most of the input due to non-index quals
-- Non-index qual, no tuples match: SeqScan preferred
:PREFIX SELECT DISTINCT dev_name FROM :TABLE WHERE val < 0 ORDER BY 1;
-- Highly selective non-index qual, have to scan many tuples to match: SeqScan preferred
:PREFIX SELECT DISTINCT dev_name FROM :TABLE WHERE time = 100 ORDER BY 1;
-- Same but a combo of non-index quals
:PREFIX SELECT DISTINCT dev_name FROM :TABLE WHERE time = 100 and dev = 1 ORDER BY 1;
-- Highly selective index qual: less tuples to scan for low-selective non-index qual, can choose SkipScan
:PREFIX SELECT DISTINCT dev_name FROM :TABLE WHERE dev_name IS NULL and dev = 1 ORDER BY 1;
CREATE INDEX ON :TABLE(dev);
-- RowCompareExpr where some args are not in the index:
-- we extract lossy "dev >=5" index condition which is strict and doesn't allow NULLs
SET timescaledb.debug_skip_scan_info TO true;
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE (dev, time) > (5,100);
RESET timescaledb.debug_skip_scan_info;
CREATE INDEX ON :TABLE(dev, time);
CREATE INDEX ON :TABLE(time,dev);
CREATE INDEX ON :TABLE(time,dev,val);
\qecho basic DISTINCT queries on :TABLE
:PREFIX SELECT DISTINCT dev, 'q1_1' FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev_name, 'q1_2' FROM :TABLE ORDER BY dev_name;
:PREFIX SELECT DISTINCT dev, 'q1_3', NULL FROM :TABLE ORDER BY dev;
\qecho stable expression in targetlist on :TABLE
:PREFIX SELECT DISTINCT dev, 'q1_4', length(md5(now()::text)) FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev_name, 'q1_5', length(md5(now()::text)) FROM :TABLE ORDER BY dev_name;
-- volatile expression in targetlist
:PREFIX SELECT DISTINCT dev, 'q1_6', length(md5(random()::text)) FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev_name, 'q1_7', length(md5(random()::text)) FROM :TABLE ORDER BY dev_name;
-- queries without skipscan because distinct is not limited to specific column
:PREFIX SELECT DISTINCT * FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT *, 'q1_9' FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev, time, 'q1_10' FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev, NULL, 'q1_11' FROM :TABLE ORDER BY dev;
-- distinct on expressions not supported
:PREFIX SELECT DISTINCT time_bucket(10,time), 'q1_12' FROM :TABLE;
:PREFIX SELECT DISTINCT length(dev_name), 'q1_13' FROM :TABLE;
:PREFIX SELECT DISTINCT 3*time, 'q1_14' FROM :TABLE;
:PREFIX SELECT DISTINCT 'Device ' || dev_name FROM :TABLE;
-- DISTINCT ON queries
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_2' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_3', NULL FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_4', length(md5(now()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_5', length(md5(random()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) *, 'q2_7' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, time, 'q2_8' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, NULL, 'q2_9' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) time, 'q2_10' FROM :TABLE ORDER by dev, time;
:PREFIX SELECT DISTINCT ON (dev) dev, tableoid::regclass, 'q2_11' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, int_func_immutable(), 'q2_12' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, int_func_stable(), 'q2_13' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, int_func_volatile(), 'q2_14' FROM :TABLE;
-- DISTINCT ON queries on TEXT column
:PREFIX SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE WHERE dev_name IS NOT NULL;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_2' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_3', NULL FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_4', length(md5(now()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_5', length(md5(random()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) * FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) *, 'q3_7' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, time, 'q3_8' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, NULL, 'q3_9' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) time, 'q3_10' FROM :TABLE ORDER by dev_name, time;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, tableoid::regclass, 'q3_11' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name::varchar) dev_name::varchar FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev, int_func_immutable(), 'q3_13' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev, int_func_stable(), 'q3_14' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev, int_func_volatile(), 'q3_15' FROM :TABLE;
\qecho DISTINCT with wholerow var
:PREFIX SELECT DISTINCT ON (dev) :TABLE FROM :TABLE;
-- should not use SkipScan since we only support SkipScan on single-column distinct
:PREFIX SELECT DISTINCT :TABLE FROM :TABLE;
\qecho LIMIT queries on :TABLE
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE LIMIT 3;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev DESC, time DESC LIMIT 3;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev, time LIMIT 3;
\qecho range queries on :TABLE
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time BETWEEN 100 AND 300;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time < 200;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time > 800;
\qecho ordered append on :TABLE
:PREFIX SELECT * FROM :TABLE ORDER BY time;
:PREFIX SELECT DISTINCT ON (time) time FROM :TABLE WHERE time BETWEEN 0 AND 5000;
\qecho SUBSELECTS on :TABLE
:PREFIX SELECT time, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a;
:PREFIX SELECT NULL, dev, NULL, 'q4_3' FROM (SELECT DISTINCT ON (dev) dev FROM :TABLE) a;
:PREFIX SELECT time, dev, NULL, 'q4_4' FROM (SELECT DISTINCT ON (dev) dev, time FROM :TABLE) a;
\qecho ORDER BY
:PREFIX SELECT time, dev, val, 'q5_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE ORDER BY dev, time) a;
:PREFIX SELECT time, dev, val, 'q5_2' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE ORDER BY dev DESC, time DESC) a;
\qecho WHERE CLAUSES
:PREFIX SELECT time, dev, val, 'q6_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > 5) a;
:PREFIX SELECT time, dev, val, 'q6_2' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE time > 5) a;
:PREFIX SELECT time, dev, val, 'q6_3' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a WHERE dev > 5;
:PREFIX SELECT time, dev, val, 'q6_4' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a WHERE time > 5;
--\qecho immutable func in WHERE clause on :TABLE
:PREFIX SELECT DISTINCT ON (dev) *, 'q6_5' FROM :TABLE WHERE dev > int_func_immutable();
--\qecho stable func in WHERE clause on :TABLE
:PREFIX SELECT DISTINCT ON (dev) *, 'q6_6' FROM :TABLE WHERE dev > int_func_stable();
--\qecho volatile func in WHERE clause on :TABLE
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > int_func_volatile();
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_immutable());
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_stable());
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_volatile());
-- RowCompareExpr
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE (dev, time) > (5,100);
-- always false expr similar to our initial skip qual
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > NULL;
-- no tuples matching
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > 20;
-- multiple constraints in WHERE clause
:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev > 5 AND time = 100;
:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev > 5 AND time > 200;
:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev >= 5 AND dev < 7 AND dev >= 2;
:PREFIX SELECT DISTINCT ON (dev) dev,time,val FROM :TABLE WHERE time > 100 AND time < 200 AND val > 10 AND val < 10000 AND dev > 2 AND dev < 7 ORDER BY dev,time;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE dev IS NULL;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE WHERE dev_name IS NULL;
-- test constants in ORDER BY
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = 1 ORDER BY dev, time DESC;
:PREFIX SELECT DISTINCT dev, time FROM :TABLE WHERE dev = 1 and time = 100 ORDER BY dev, time DESC;
:PREFIX SELECT DISTINCT dev_name::varchar FROM :TABLE  WHERE dev_name::varchar = 'device_1' ORDER BY 1;
-- test distinct PathKey with sortref = 0 in PG15 due to FALSE filter not pushed into relation (should not crash in PG15)
:PREFIX SELECT DISTINCT sq.dev FROM (SELECT dev FROM :TABLE) sq JOIN :TABLE ref ON (sq.dev = ref.dev) WHERE 1 > 2;
-- test distinct PathKey with sortref not aligning with targets sortrefs in PG15 due to Window function
:PREFIX SELECT DISTINCT
    case when false then c10 else c10 end as c0,
    max(c8) over (partition by c10 order by c6) as c1
FROM (SELECT time as c6, val as c8, dev as c10 FROM :TABLE WHERE false) subq WHERE false;
-- test multiple distincts with all but one pinned: #7998
:PREFIX SELECT DISTINCT dev, dev FROM :TABLE ORDER BY 1;
:PREFIX SELECT DISTINCT dev, time FROM :TABLE WHERE time = 100 ORDER BY 1;
:PREFIX SELECT DISTINCT dev, time, val FROM :TABLE WHERE time = 100 and val = 100 ORDER BY 1;
:PREFIX SELECT DISTINCT ON (dev, time) * FROM :TABLE WHERE time = 100 ORDER BY dev;
:PREFIX SELECT DISTINCT ON (dev, time) dev, time FROM :TABLE WHERE dev = 1 AND time < 20 ORDER BY dev, time;
-- CTE
:PREFIX WITH devices AS (
	SELECT DISTINCT ON (dev) dev FROM :TABLE
)
SELECT * FROM devices;
:PREFIX WITH devices AS (
	SELECT DISTINCT dev FROM :TABLE
)
SELECT * FROM devices ORDER BY dev;
-- prepared statements
PREPARE prep AS SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
DEALLOCATE prep;
-- ReScan tests
:PREFIX SELECT time, dev, val, 'q7_1' FROM (SELECT DISTINCT ON (dev) * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT * FROM :TABLE WHERE time != a.v) b) a;
:PREFIX SELECT time, dev, val, 'q7_2' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev != a.v) b) a;
-- RuntimeKeys
:PREFIX SELECT time, dev, val, 'q8_1' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev >= a.v) b) c;
-- Emulate multi-column DISTINCT using multiple SkipSkans
:PREFIX SELECT time, dev, val, 'q9_1' FROM (SELECT b.* FROM
    (SELECT DISTINCT ON (dev) dev FROM :TABLE) a,
    LATERAL (SELECT DISTINCT ON (time) * FROM :TABLE WHERE dev = a.dev) b) c;
:PREFIX SELECT time, dev, NULL, 'q9_2' FROM (SELECT b.* FROM
    (SELECT DISTINCT ON (dev) dev FROM :TABLE) a,
    LATERAL (SELECT DISTINCT ON (time) dev, time FROM :TABLE WHERE dev = a.dev) b) c;
-- Test that the multi-column DISTINCT emulation is equivalent to a real multi-column DISTINCT
:PREFIX SELECT * FROM
   (SELECT DISTINCT ON (dev) dev FROM :TABLE) a,
   LATERAL (SELECT DISTINCT ON (time) dev, time FROM :TABLE WHERE dev = a.dev) b;
:PREFIX SELECT DISTINCT ON (dev, time) dev, time FROM :TABLE WHERE coalesce(dev, -1) >= 0;
:PREFIX SELECT DISTINCT ON (dev, time) dev, time FROM :TABLE WHERE coalesce(dev, -1) >= 0
UNION SELECT b.* FROM
   (SELECT DISTINCT ON (dev) dev FROM :TABLE) a,
   LATERAL (SELECT DISTINCT ON (time) dev, time FROM :TABLE WHERE dev = a.dev) b;
-- SkipScan into INSERT
:PREFIX INSERT INTO skip_scan_insert(time, dev, val, query) SELECT time, dev, val, 'q10_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a;
-- parallel query
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'on', false);
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev;
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'off', false);
TRUNCATE skip_scan_insert;
-- table with only nulls
:PREFIX SELECT DISTINCT ON (time) time FROM skip_scan_nulls;
-- no tuples in resultset
:PREFIX SELECT DISTINCT ON (time) time FROM skip_scan_nulls WHERE time IS NOT NULL;
-- Test for SDC issue #2976
-- We should use index "btree ("time", dev, dev_name)" where "dev_name" index key is #3 but we have 2 index quals including SkipScan qual
-- We should not drop qual on "dev_name"
CREATE INDEX skip_scan_idx_time_dev_dname ON :TABLE(time,dev,dev_name);
:PREFIX SELECT DISTINCT time FROM :TABLE WHERE dev_name IS NULL ORDER BY 1;
DROP INDEX skip_scan_idx_time_dev_dname;
-- "dev_name" is not a key column: it's not in the index quals and should be a filter
CREATE INDEX skip_scan_idx_time_dev_dname ON :TABLE(time,dev) INCLUDE(dev_name);
:PREFIX SELECT DISTINCT time FROM :TABLE WHERE dev_name IS NULL ORDER BY 1;
DROP INDEX skip_scan_idx_time_dev_dname;
\o
RESET timescaledb.enable_skipscan;
-- compare SkipScan results on normal table
:DIFF_CMD
--- Unoptimized results
+++ Optimized results
@@ -1,6 +1,6 @@
  enable_skipscan 
 -----------------
- off
+ on
 
  dev 
-- run tests on hypertable and diff results
\set TABLE skip_scan_ht
\set PREFIX ''
\o :TEST_RESULTS_OPTIMIZED
\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 result diff
SELECT current_setting('timescaledb.enable_skipscan') AS enable_skipscan;
-- test different index configurations
-- no index so we cant do SkipScan
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev;
-- NULLS LAST index on dev
CREATE INDEX skip_scan_idx_dev_nulls_last ON :TABLE(dev);
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev DESC;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT dev FROM :TABLE WHERE dev IS NOT NULL ORDER BY dev;
DROP INDEX skip_scan_idx_dev_nulls_last;
-- NULLS FIRST index on dev
CREATE INDEX skip_scan_idx_dev_nulls_first ON :TABLE(dev NULLS FIRST);
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev NULLS FIRST;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev NULLS FIRST;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT dev FROM :TABLE WHERE dev IS NOT NULL ORDER BY dev NULLS FIRST;
DROP INDEX skip_scan_idx_dev_nulls_first;
-- multicolumn index with dev as leading column
CREATE INDEX skip_scan_idx_dev_time_idx ON :TABLE(dev, time);
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev DESC, time DESC;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT dev FROM :TABLE WHERE dev IS NOT NULL ORDER BY dev;
DROP INDEX skip_scan_idx_dev_time_idx;
-- multicolumn index with dev as non-leading column
CREATE INDEX skip_scan_idx_time_dev_idx ON :TABLE(time, dev);
:PREFIX SELECT DISTINCT dev FROM :TABLE WHERE time = 100 ORDER BY dev;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time = 100;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT dev FROM :TABLE WHERE time = 100 AND dev IS NOT NULL ORDER BY dev;
DROP INDEX skip_scan_idx_time_dev_idx;
-- hash index is not ordered so can't use skipscan
CREATE INDEX skip_scan_idx_hash ON :TABLE USING hash(dev_name);
:PREFIX SELECT DISTINCT dev_name FROM :TABLE WHERE dev_name IN ('device_1','device_2') ORDER BY dev_name;
DROP INDEX skip_scan_idx_hash;
-- expression indexes
-- currently not supported by skipscan
CREATE INDEX skip_scan_expr_idx ON :TABLE((dev % 3));
:PREFIX SELECT DISTINCT dev%3 FROM :TABLE ORDER BY dev%3;
:PREFIX SELECT DISTINCT ON (dev%3) dev FROM :TABLE ORDER BY dev%3;
DROP INDEX skip_scan_expr_idx;
CREATE INDEX ON :TABLE(dev_name);
-- Tests for #8107: accounting for scanning most of the input due to non-index quals
-- Non-index qual, no tuples match: SeqScan preferred
:PREFIX SELECT DISTINCT dev_name FROM :TABLE WHERE val < 0 ORDER BY 1;
-- Highly selective non-index qual, have to scan many tuples to match: SeqScan preferred
:PREFIX SELECT DISTINCT dev_name FROM :TABLE WHERE time = 100 ORDER BY 1;
-- Same but a combo of non-index quals
:PREFIX SELECT DISTINCT dev_name FROM :TABLE WHERE time = 100 and dev = 1 ORDER BY 1;
-- Highly selective index qual: less tuples to scan for low-selective non-index qual, can choose SkipScan
:PREFIX SELECT DISTINCT dev_name FROM :TABLE WHERE dev_name IS NULL and dev = 1 ORDER BY 1;
CREATE INDEX ON :TABLE(dev);
-- RowCompareExpr where some args are not in the index:
-- we extract lossy "dev >=5" index condition which is strict and doesn't allow NULLs
SET timescaledb.debug_skip_scan_info TO true;
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE (dev, time) > (5,100);
psql:include/skip_scan_query.sql:74: INFO:  SkipScan used on _hyper_1_1_chunk_skip_scan_ht_dev_idx(dev NOT NULL)
psql:include/skip_scan_query.sql:74: INFO:  SkipScan used on _hyper_1_2_chunk_skip_scan_ht_dev_idx(dev NOT NULL)
psql:include/skip_scan_query.sql:74: INFO:  SkipScan used on _hyper_1_3_chunk_skip_scan_ht_dev_idx(dev NOT NULL)
psql:include/skip_scan_query.sql:74: INFO:  SkipScan used on _hyper_1_4_chunk_skip_scan_ht_dev_idx(dev NOT NULL)
RESET timescaledb.debug_skip_scan_info;
CREATE INDEX ON :TABLE(dev, time);
CREATE INDEX ON :TABLE(time,dev);
CREATE INDEX ON :TABLE(time,dev,val);
\qecho basic DISTINCT queries on :TABLE
:PREFIX SELECT DISTINCT dev, 'q1_1' FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev_name, 'q1_2' FROM :TABLE ORDER BY dev_name;
:PREFIX SELECT DISTINCT dev, 'q1_3', NULL FROM :TABLE ORDER BY dev;
\qecho stable expression in targetlist on :TABLE
:PREFIX SELECT DISTINCT dev, 'q1_4', length(md5(now()::text)) FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev_name, 'q1_5', length(md5(now()::text)) FROM :TABLE ORDER BY dev_name;
-- volatile expression in targetlist
:PREFIX SELECT DISTINCT dev, 'q1_6', length(md5(random()::text)) FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev_name, 'q1_7', length(md5(random()::text)) FROM :TABLE ORDER BY dev_name;
-- queries without skipscan because distinct is not limited to specific column
:PREFIX SELECT DISTINCT * FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT *, 'q1_9' FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev, time, 'q1_10' FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev, NULL, 'q1_11' FROM :TABLE ORDER BY dev;
-- distinct on expressions not supported
:PREFIX SELECT DISTINCT time_bucket(10,time), 'q1_12' FROM :TABLE;
:PREFIX SELECT DISTINCT length(dev_name), 'q1_13' FROM :TABLE;
:PREFIX SELECT DISTINCT 3*time, 'q1_14' FROM :TABLE;
:PREFIX SELECT DISTINCT 'Device ' || dev_name FROM :TABLE;
-- DISTINCT ON queries
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_2' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_3', NULL FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_4', length(md5(now()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_5', length(md5(random()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) *, 'q2_7' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, time, 'q2_8' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, NULL, 'q2_9' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) time, 'q2_10' FROM :TABLE ORDER by dev, time;
:PREFIX SELECT DISTINCT ON (dev) dev, tableoid::regclass, 'q2_11' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, int_func_immutable(), 'q2_12' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, int_func_stable(), 'q2_13' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, int_func_volatile(), 'q2_14' FROM :TABLE;
-- DISTINCT ON queries on TEXT column
:PREFIX SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE WHERE dev_name IS NOT NULL;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_2' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_3', NULL FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_4', length(md5(now()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_5', length(md5(random()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) * FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) *, 'q3_7' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, time, 'q3_8' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, NULL, 'q3_9' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) time, 'q3_10' FROM :TABLE ORDER by dev_name, time;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, tableoid::regclass, 'q3_11' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name::varchar) dev_name::varchar FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev, int_func_immutable(), 'q3_13' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev, int_func_stable(), 'q3_14' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev, int_func_volatile(), 'q3_15' FROM :TABLE;
\qecho DISTINCT with wholerow var
:PREFIX SELECT DISTINCT ON (dev) :TABLE FROM :TABLE;
-- should not use SkipScan since we only support SkipScan on single-column distinct
:PREFIX SELECT DISTINCT :TABLE FROM :TABLE;
\qecho LIMIT queries on :TABLE
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE LIMIT 3;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev DESC, time DESC LIMIT 3;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev, time LIMIT 3;
\qecho range queries on :TABLE
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time BETWEEN 100 AND 300;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time < 200;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time > 800;
\qecho ordered append on :TABLE
:PREFIX SELECT * FROM :TABLE ORDER BY time;
:PREFIX SELECT DISTINCT ON (time) time FROM :TABLE WHERE time BETWEEN 0 AND 5000;
\qecho SUBSELECTS on :TABLE
:PREFIX SELECT time, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a;
:PREFIX SELECT NULL, dev, NULL, 'q4_3' FROM (SELECT DISTINCT ON (dev) dev FROM :TABLE) a;
:PREFIX SELECT time, dev, NULL, 'q4_4' FROM (SELECT DISTINCT ON (dev) dev, time FROM :TABLE) a;
\qecho ORDER BY
:PREFIX SELECT time, dev, val, 'q5_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE ORDER BY dev, time) a;
:PREFIX SELECT time, dev, val, 'q5_2' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE ORDER BY dev DESC, time DESC) a;
\qecho WHERE CLAUSES
:PREFIX SELECT time, dev, val, 'q6_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > 5) a;
:PREFIX SELECT time, dev, val, 'q6_2' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE time > 5) a;
:PREFIX SELECT time, dev, val, 'q6_3' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a WHERE dev > 5;
:PREFIX SELECT time, dev, val, 'q6_4' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a WHERE time > 5;
--\qecho immutable func in WHERE clause on :TABLE
:PREFIX SELECT DISTINCT ON (dev) *, 'q6_5' FROM :TABLE WHERE dev > int_func_immutable();
--\qecho stable func in WHERE clause on :TABLE
:PREFIX SELECT DISTINCT ON (dev) *, 'q6_6' FROM :TABLE WHERE dev > int_func_stable();
--\qecho volatile func in WHERE clause on :TABLE
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > int_func_volatile();
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_immutable());
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_stable());
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_volatile());
-- RowCompareExpr
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE (dev, time) > (5,100);
-- always false expr similar to our initial skip qual
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > NULL;
-- no tuples matching
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > 20;
-- multiple constraints in WHERE clause
:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev > 5 AND time = 100;
:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev > 5 AND time > 200;
:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev >= 5 AND dev < 7 AND dev >= 2;
:PREFIX SELECT DISTINCT ON (dev) dev,time,val FROM :TABLE WHERE time > 100 AND time < 200 AND val > 10 AND val < 10000 AND dev > 2 AND dev < 7 ORDER BY dev,time;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE dev IS NULL;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE WHERE dev_name IS NULL;
-- test constants in ORDER BY
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = 1 ORDER BY dev, time DESC;
:PREFIX SELECT DISTINCT dev, time FROM :TABLE WHERE dev = 1 and time = 100 ORDER BY dev, time DESC;
:PREFIX SELECT DISTINCT dev_name::varchar FROM :TABLE  WHERE dev_name::varchar = 'device_1' ORDER BY 1;
-- test distinct PathKey with sortref = 0 in PG15 due to FALSE filter not pushed into relation (should not crash in PG15)
:PREFIX SELECT DISTINCT sq.dev FROM (SELECT dev FROM :TABLE) sq JOIN :TABLE ref ON (sq.dev = ref.dev) WHERE 1 > 2;
-- test distinct PathKey with sortref not aligning with targets sortrefs in PG15 due to Window function
:PREFIX SELECT DISTINCT
    case when false then c10 else c10 end as c0,
    max(c8) over (partition by c10 order by c6) as c1
FROM (SELECT time as c6, val as c8, dev as c10 FROM :TABLE WHERE false) subq WHERE false;
-- test multiple distincts with all but one pinned: #7998
:PREFIX SELECT DISTINCT dev, dev FROM :TABLE ORDER BY 1;
:PREFIX SELECT DISTINCT dev, time FROM :TABLE WHERE time = 100 ORDER BY 1;
:PREFIX SELECT DISTINCT dev, time, val FROM :TABLE WHERE time = 100 and val = 100 ORDER BY 1;
:PREFIX SELECT DISTINCT ON (dev, time) * FROM :TABLE WHERE time = 100 ORDER BY dev;
:PREFIX SELECT DISTINCT ON (dev, time) dev, time FROM :TABLE WHERE dev = 1 AND time < 20 ORDER BY dev, time;
-- CTE
:PREFIX WITH devices AS (
	SELECT DISTINCT ON (dev) dev FROM :TABLE
)
SELECT * FROM devices;
:PREFIX WITH devices AS (
	SELECT DISTINCT dev FROM :TABLE
)
SELECT * FROM devices ORDER BY dev;
-- prepared statements
PREPARE prep AS SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
DEALLOCATE prep;
-- ReScan tests
:PREFIX SELECT time, dev, val, 'q7_1' FROM (SELECT DISTINCT ON (dev) * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT * FROM :TABLE WHERE time != a.v) b) a;
:PREFIX SELECT time, dev, val, 'q7_2' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev != a.v) b) a;
-- RuntimeKeys
:PREFIX SELECT time, dev, val, 'q8_1' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev >= a.v) b) c;
-- Emulate multi-column DISTINCT using multiple SkipSkans
:PREFIX SELECT time, dev, val, 'q9_1' FROM (SELECT b.* FROM
    (SELECT DISTINCT ON (dev) dev FROM :TABLE) a,
    LATERAL (SELECT DISTINCT ON (time) * FROM :TABLE WHERE dev = a.dev) b) c;
:PREFIX SELECT time, dev, NULL, 'q9_2' FROM (SELECT b.* FROM
    (SELECT DISTINCT ON (dev) dev FROM :TABLE) a,
    LATERAL (SELECT DISTINCT ON (time) dev, time FROM :TABLE WHERE dev = a.dev) b) c;
-- Test that the multi-column DISTINCT emulation is equivalent to a real multi-column DISTINCT
:PREFIX SELECT * FROM
   (SELECT DISTINCT ON (dev) dev FROM :TABLE) a,
   LATERAL (SELECT DISTINCT ON (time) dev, time FROM :TABLE WHERE dev = a.dev) b;
:PREFIX SELECT DISTINCT ON (dev, time) dev, time FROM :TABLE WHERE coalesce(dev, -1) >= 0;
:PREFIX SELECT DISTINCT ON (dev, time) dev, time FROM :TABLE WHERE coalesce(dev, -1) >= 0
UNION SELECT b.* FROM
   (SELECT DISTINCT ON (dev) dev FROM :TABLE) a,
   LATERAL (SELECT DISTINCT ON (time) dev, time FROM :TABLE WHERE dev = a.dev) b;
-- SkipScan into INSERT
:PREFIX INSERT INTO skip_scan_insert(time, dev, val, query) SELECT time, dev, val, 'q10_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a;
-- parallel query
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'on', false);
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev;
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'off', false);
TRUNCATE skip_scan_insert;
-- table with only nulls
:PREFIX SELECT DISTINCT ON (time) time FROM skip_scan_nulls;
-- no tuples in resultset
:PREFIX SELECT DISTINCT ON (time) time FROM skip_scan_nulls WHERE time IS NOT NULL;
-- Test for SDC issue #2976
-- We should use index "btree ("time", dev, dev_name)" where "dev_name" index key is #3 but we have 2 index quals including SkipScan qual
-- We should not drop qual on "dev_name"
CREATE INDEX skip_scan_idx_time_dev_dname ON :TABLE(time,dev,dev_name);
:PREFIX SELECT DISTINCT time FROM :TABLE WHERE dev_name IS NULL ORDER BY 1;
DROP INDEX skip_scan_idx_time_dev_dname;
-- "dev_name" is not a key column: it's not in the index quals and should be a filter
CREATE INDEX skip_scan_idx_time_dev_dname ON :TABLE(time,dev) INCLUDE(dev_name);
:PREFIX SELECT DISTINCT time FROM :TABLE WHERE dev_name IS NULL ORDER BY 1;
DROP INDEX skip_scan_idx_time_dev_dname;
\o
SET timescaledb.enable_skipscan TO false;
\o :TEST_RESULTS_UNOPTIMIZED
\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 result diff
SELECT current_setting('timescaledb.enable_skipscan') AS enable_skipscan;
-- test different index configurations
-- no index so we cant do SkipScan
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev;
-- NULLS LAST index on dev
CREATE INDEX skip_scan_idx_dev_nulls_last ON :TABLE(dev);
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev DESC;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT dev FROM :TABLE WHERE dev IS NOT NULL ORDER BY dev;
DROP INDEX skip_scan_idx_dev_nulls_last;
-- NULLS FIRST index on dev
CREATE INDEX skip_scan_idx_dev_nulls_first ON :TABLE(dev NULLS FIRST);
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev NULLS FIRST;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev NULLS FIRST;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT dev FROM :TABLE WHERE dev IS NOT NULL ORDER BY dev NULLS FIRST;
DROP INDEX skip_scan_idx_dev_nulls_first;
-- multicolumn index with dev as leading column
CREATE INDEX skip_scan_idx_dev_time_idx ON :TABLE(dev, time);
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev DESC, time DESC;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT dev FROM :TABLE WHERE dev IS NOT NULL ORDER BY dev;
DROP INDEX skip_scan_idx_dev_time_idx;
-- multicolumn index with dev as non-leading column
CREATE INDEX skip_scan_idx_time_dev_idx ON :TABLE(time, dev);
:PREFIX SELECT DISTINCT dev FROM :TABLE WHERE time = 100 ORDER BY dev;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time = 100;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT dev FROM :TABLE WHERE time = 100 AND dev IS NOT NULL ORDER BY dev;
DROP INDEX skip_scan_idx_time_dev_idx;
-- hash index is not ordered so can't use skipscan
CREATE INDEX skip_scan_idx_hash ON :TABLE USING hash(dev_name);
:PREFIX SELECT DISTINCT dev_name FROM :TABLE WHERE dev_name IN ('device_1','device_2') ORDER BY dev_name;
DROP INDEX skip_scan_idx_hash;
-- expression indexes
-- currently not supported by skipscan
CREATE INDEX skip_scan_expr_idx ON :TABLE((dev % 3));
:PREFIX SELECT DISTINCT dev%3 FROM :TABLE ORDER BY dev%3;
:PREFIX SELECT DISTINCT ON (dev%3) dev FROM :TABLE ORDER BY dev%3;
DROP INDEX skip_scan_expr_idx;
CREATE INDEX ON :TABLE(dev_name);
-- Tests for #8107: accounting for scanning most of the input due to non-index quals
-- Non-index qual, no tuples match: SeqScan preferred
:PREFIX SELECT DISTINCT dev_name FROM :TABLE WHERE val < 0 ORDER BY 1;
-- Highly selective non-index qual, have to scan many tuples to match: SeqScan preferred
:PREFIX SELECT DISTINCT dev_name FROM :TABLE WHERE time = 100 ORDER BY 1;
-- Same but a combo of non-index quals
:PREFIX SELECT DISTINCT dev_name FROM :TABLE WHERE time = 100 and dev = 1 ORDER BY 1;
-- Highly selective index qual: less tuples to scan for low-selective non-index qual, can choose SkipScan
:PREFIX SELECT DISTINCT dev_name FROM :TABLE WHERE dev_name IS NULL and dev = 1 ORDER BY 1;
CREATE INDEX ON :TABLE(dev);
-- RowCompareExpr where some args are not in the index:
-- we extract lossy "dev >=5" index condition which is strict and doesn't allow NULLs
SET timescaledb.debug_skip_scan_info TO true;
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE (dev, time) > (5,100);
RESET timescaledb.debug_skip_scan_info;
CREATE INDEX ON :TABLE(dev, time);
CREATE INDEX ON :TABLE(time,dev);
CREATE INDEX ON :TABLE(time,dev,val);
\qecho basic DISTINCT queries on :TABLE
:PREFIX SELECT DISTINCT dev, 'q1_1' FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev_name, 'q1_2' FROM :TABLE ORDER BY dev_name;
:PREFIX SELECT DISTINCT dev, 'q1_3', NULL FROM :TABLE ORDER BY dev;
\qecho stable expression in targetlist on :TABLE
:PREFIX SELECT DISTINCT dev, 'q1_4', length(md5(now()::text)) FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev_name, 'q1_5', length(md5(now()::text)) FROM :TABLE ORDER BY dev_name;
-- volatile expression in targetlist
:PREFIX SELECT DISTINCT dev, 'q1_6', length(md5(random()::text)) FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev_name, 'q1_7', length(md5(random()::text)) FROM :TABLE ORDER BY dev_name;
-- queries without skipscan because distinct is not limited to specific column
:PREFIX SELECT DISTINCT * FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT *, 'q1_9' FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev, time, 'q1_10' FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev, NULL, 'q1_11' FROM :TABLE ORDER BY dev;
-- distinct on expressions not supported
:PREFIX SELECT DISTINCT time_bucket(10,time), 'q1_12' FROM :TABLE;
:PREFIX SELECT DISTINCT length(dev_name), 'q1_13' FROM :TABLE;
:PREFIX SELECT DISTINCT 3*time, 'q1_14' FROM :TABLE;
:PREFIX SELECT DISTINCT 'Device ' || dev_name FROM :TABLE;
-- DISTINCT ON queries
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_2' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_3', NULL FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_4', length(md5(now()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_5', length(md5(random()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) *, 'q2_7' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, time, 'q2_8' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, NULL, 'q2_9' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) time, 'q2_10' FROM :TABLE ORDER by dev, time;
:PREFIX SELECT DISTINCT ON (dev) dev, tableoid::regclass, 'q2_11' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, int_func_immutable(), 'q2_12' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, int_func_stable(), 'q2_13' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, int_func_volatile(), 'q2_14' FROM :TABLE;
-- DISTINCT ON queries on TEXT column
:PREFIX SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE WHERE dev_name IS NOT NULL;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_2' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_3', NULL FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_4', length(md5(now()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_5', length(md5(random()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) * FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) *, 'q3_7' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, time, 'q3_8' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, NULL, 'q3_9' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) time, 'q3_10' FROM :TABLE ORDER by dev_name, time;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, tableoid::regclass, 'q3_11' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name::varchar) dev_name::varchar FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev, int_func_immutable(), 'q3_13' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev, int_func_stable(), 'q3_14' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev, int_func_volatile(), 'q3_15' FROM :TABLE;
\qecho DISTINCT with wholerow var
:PREFIX SELECT DISTINCT ON (dev) :TABLE FROM :TABLE;
-- should not use SkipScan since we only support SkipScan on single-column distinct
:PREFIX SELECT DISTINCT :TABLE FROM :TABLE;
\qecho LIMIT queries on :TABLE
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE LIMIT 3;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev DESC, time DESC LIMIT 3;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev, time LIMIT 3;
\qecho range queries on :TABLE
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time BETWEEN 100 AND 300;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time < 200;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time > 800;
\qecho ordered append on :TABLE
:PREFIX SELECT * FROM :TABLE ORDER BY time;
:PREFIX SELECT DISTINCT ON (time) time FROM :TABLE WHERE time BETWEEN 0 AND 5000;
\qecho SUBSELECTS on :TABLE
:PREFIX SELECT time, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a;
:PREFIX SELECT NULL, dev, NULL, 'q4_3' FROM (SELECT DISTINCT ON (dev) dev FROM :TABLE) a;
:PREFIX SELECT time, dev, NULL, 'q4_4' FROM (SELECT DISTINCT ON (dev) dev, time FROM :TABLE) a;
\qecho ORDER BY
:PREFIX SELECT time, dev, val, 'q5_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE ORDER BY dev, time) a;
:PREFIX SELECT time, dev, val, 'q5_2' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE ORDER BY dev DESC, time DESC) a;
\qecho WHERE CLAUSES
:PREFIX SELECT time, dev, val, 'q6_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > 5) a;
:PREFIX SELECT time, dev, val, 'q6_2' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE time > 5) a;
:PREFIX SELECT time, dev, val, 'q6_3' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a WHERE dev > 5;
:PREFIX SELECT time, dev, val, 'q6_4' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a WHERE time > 5;
--\qecho immutable func in WHERE clause on :TABLE
:PREFIX SELECT DISTINCT ON (dev) *, 'q6_5' FROM :TABLE WHERE dev > int_func_immutable();
--\qecho stable func in WHERE clause on :TABLE
:PREFIX SELECT DISTINCT ON (dev) *, 'q6_6' FROM :TABLE WHERE dev > int_func_stable();
--\qecho volatile func in WHERE clause on :TABLE
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > int_func_volatile();
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_immutable());
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_stable());
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_volatile());
-- RowCompareExpr
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE (dev, time) > (5,100);
-- always false expr similar to our initial skip qual
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > NULL;
-- no tuples matching
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > 20;
-- multiple constraints in WHERE clause
:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev > 5 AND time = 100;
:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev > 5 AND time > 200;
:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev >= 5 AND dev < 7 AND dev >= 2;
:PREFIX SELECT DISTINCT ON (dev) dev,time,val FROM :TABLE WHERE time > 100 AND time < 200 AND val > 10 AND val < 10000 AND dev > 2 AND dev < 7 ORDER BY dev,time;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE dev IS NULL;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE WHERE dev_name IS NULL;
-- test constants in ORDER BY
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = 1 ORDER BY dev, time DESC;
:PREFIX SELECT DISTINCT dev, time FROM :TABLE WHERE dev = 1 and time = 100 ORDER BY dev, time DESC;
:PREFIX SELECT DISTINCT dev_name::varchar FROM :TABLE  WHERE dev_name::varchar = 'device_1' ORDER BY 1;
-- test distinct PathKey with sortref = 0 in PG15 due to FALSE filter not pushed into relation (should not crash in PG15)
:PREFIX SELECT DISTINCT sq.dev FROM (SELECT dev FROM :TABLE) sq JOIN :TABLE ref ON (sq.dev = ref.dev) WHERE 1 > 2;
-- test distinct PathKey with sortref not aligning with targets sortrefs in PG15 due to Window function
:PREFIX SELECT DISTINCT
    case when false then c10 else c10 end as c0,
    max(c8) over (partition by c10 order by c6) as c1
FROM (SELECT time as c6, val as c8, dev as c10 FROM :TABLE WHERE false) subq WHERE false;
-- test multiple distincts with all but one pinned: #7998
:PREFIX SELECT DISTINCT dev, dev FROM :TABLE ORDER BY 1;
:PREFIX SELECT DISTINCT dev, time FROM :TABLE WHERE time = 100 ORDER BY 1;
:PREFIX SELECT DISTINCT dev, time, val FROM :TABLE WHERE time = 100 and val = 100 ORDER BY 1;
:PREFIX SELECT DISTINCT ON (dev, time) * FROM :TABLE WHERE time = 100 ORDER BY dev;
:PREFIX SELECT DISTINCT ON (dev, time) dev, time FROM :TABLE WHERE dev = 1 AND time < 20 ORDER BY dev, time;
-- CTE
:PREFIX WITH devices AS (
	SELECT DISTINCT ON (dev) dev FROM :TABLE
)
SELECT * FROM devices;
:PREFIX WITH devices AS (
	SELECT DISTINCT dev FROM :TABLE
)
SELECT * FROM devices ORDER BY dev;
-- prepared statements
PREPARE prep AS SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
DEALLOCATE prep;
-- ReScan tests
:PREFIX SELECT time, dev, val, 'q7_1' FROM (SELECT DISTINCT ON (dev) * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT * FROM :TABLE WHERE time != a.v) b) a;
:PREFIX SELECT time, dev, val, 'q7_2' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev != a.v) b) a;
-- RuntimeKeys
:PREFIX SELECT time, dev, val, 'q8_1' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev >= a.v) b) c;
-- Emulate multi-column DISTINCT using multiple SkipSkans
:PREFIX SELECT time, dev, val, 'q9_1' FROM (SELECT b.* FROM
    (SELECT DISTINCT ON (dev) dev FROM :TABLE) a,
    LATERAL (SELECT DISTINCT ON (time) * FROM :TABLE WHERE dev = a.dev) b) c;
:PREFIX SELECT time, dev, NULL, 'q9_2' FROM (SELECT b.* FROM
    (SELECT DISTINCT ON (dev) dev FROM :TABLE) a,
    LATERAL (SELECT DISTINCT ON (time) dev, time FROM :TABLE WHERE dev = a.dev) b) c;
-- Test that the multi-column DISTINCT emulation is equivalent to a real multi-column DISTINCT
:PREFIX SELECT * FROM
   (SELECT DISTINCT ON (dev) dev FROM :TABLE) a,
   LATERAL (SELECT DISTINCT ON (time) dev, time FROM :TABLE WHERE dev = a.dev) b;
:PREFIX SELECT DISTINCT ON (dev, time) dev, time FROM :TABLE WHERE coalesce(dev, -1) >= 0;
:PREFIX SELECT DISTINCT ON (dev, time) dev, time FROM :TABLE WHERE coalesce(dev, -1) >= 0
UNION SELECT b.* FROM
   (SELECT DISTINCT ON (dev) dev FROM :TABLE) a,
   LATERAL (SELECT DISTINCT ON (time) dev, time FROM :TABLE WHERE dev = a.dev) b;
-- SkipScan into INSERT
:PREFIX INSERT INTO skip_scan_insert(time, dev, val, query) SELECT time, dev, val, 'q10_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a;
-- parallel query
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'on', false);
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev;
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'off', false);
TRUNCATE skip_scan_insert;
-- table with only nulls
:PREFIX SELECT DISTINCT ON (time) time FROM skip_scan_nulls;
-- no tuples in resultset
:PREFIX SELECT DISTINCT ON (time) time FROM skip_scan_nulls WHERE time IS NOT NULL;
-- Test for SDC issue #2976
-- We should use index "btree ("time", dev, dev_name)" where "dev_name" index key is #3 but we have 2 index quals including SkipScan qual
-- We should not drop qual on "dev_name"
CREATE INDEX skip_scan_idx_time_dev_dname ON :TABLE(time,dev,dev_name);
:PREFIX SELECT DISTINCT time FROM :TABLE WHERE dev_name IS NULL ORDER BY 1;
DROP INDEX skip_scan_idx_time_dev_dname;
-- "dev_name" is not a key column: it's not in the index quals and should be a filter
CREATE INDEX skip_scan_idx_time_dev_dname ON :TABLE(time,dev) INCLUDE(dev_name);
:PREFIX SELECT DISTINCT time FROM :TABLE WHERE dev_name IS NULL ORDER BY 1;
DROP INDEX skip_scan_idx_time_dev_dname;
\o
RESET timescaledb.enable_skipscan;
-- compare SkipScan results on hypertable
:DIFF_CMD
--- Unoptimized results
+++ Optimized results
@@ -1,6 +1,6 @@
  enable_skipscan 
 -----------------
- off
+ on
 
  dev 
-- run tests on compressed hypertable with different compression settings and diff results
SELECT format('include/%s_comp_query.sql', :'TEST_BASE_NAME') AS "TEST_QUERY_NAME" \gset
\set TABLE skip_scan_htc
\set PREFIX ''
\o :TEST_RESULTS_OPTIMIZED
\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 result diff
SELECT current_setting('timescaledb.enable_compressed_skipscan') AS enable_compressed_skipscan;
-- To avoid ambiguity in test EXPLAIN outputs due to mixing of chunk plans with no SkipScan
SET max_parallel_workers_per_gather = 0;
-- test different compression configurations
-- compressed index on "segmentby='dev'" has default "dev ASC, NULLS LAST" sort order
ALTER TABLE skip_scan_htc SET (timescaledb.compress, timescaledb.compress_orderby='time desc', timescaledb.compress_segmentby='dev');
psql:include/skip_scan_comp_query.sql:14: NOTICE:  updated compression settings will only apply to future compressions
SELECT compress_chunk(ch) FROM show_chunks('skip_scan_htc') ch;
-- SkipScan used when sort order on distinct column "dev" matches "segmentby" sort order
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev DESC;
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev DESC NULLS FIRST;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT dev FROM :TABLE WHERE dev IS NOT NULL ORDER BY dev;
:PREFIX SELECT DISTINCT dev FROM :TABLE WHERE dev IS NOT NULL ORDER BY dev DESC NULLS FIRST;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE;
-- NULLS FIRST doesn't match segmentby NULL direction
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev NULLS FIRST;
-- multicolumn sort with dev as leading column matching (segmentby dev, order by time DESC) compression index
:PREFIX SELECT DISTINCT ON (dev) dev, time FROM :TABLE ORDER BY dev, time DESC;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT ON (dev) dev, time FROM :TABLE WHERE dev IS NOT NULL ORDER BY dev, time DESC;
:PREFIX SELECT DISTINCT ON (dev) dev, time FROM :TABLE ORDER BY dev DESC, time;
-- multicolumn sort not matching compression index
:PREFIX SELECT DISTINCT ON (dev) dev, time FROM :TABLE ORDER BY dev DESC, time DESC;
-- same result when we use compressed IndexPath with pathkeys not matching DecompressChunk path required path keys
SET enable_seqscan TO false;
:PREFIX SELECT DISTINCT ON (dev) dev, time FROM :TABLE ORDER BY dev DESC, time DESC;
RESET enable_seqscan;
-- Distinct column not a segmentby column: should be no SkipScan
:PREFIX SELECT DISTINCT time FROM :TABLE WHERE dev = 1 ORDER BY time DESC;
-- multicolumn sort with dev as non-leading column and with leading column pinned
-- we are able to apply SkipScan here after resolving #7998
:PREFIX SELECT DISTINCT time, dev FROM :TABLE WHERE time = 100 ORDER BY time, dev;
-- multicolumn "segmentby = 'dev, dev_name'"
SELECT decompress_chunk(ch) FROM show_chunks('skip_scan_htc') ch;
ALTER TABLE skip_scan_htc SET (timescaledb.compress, timescaledb.compress_orderby='time desc', timescaledb.compress_segmentby='dev,dev_name');
psql:include/skip_scan_comp_query.sql:51: NOTICE:  updated compression settings will only apply to future compressions
SELECT compress_chunk(ch) FROM show_chunks('skip_scan_htc') ch;
-- dev is leading column
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, dev_name FROM :TABLE ORDER BY dev, dev_name;
:PREFIX SELECT DISTINCT ON (dev) dev, dev_name FROM :TABLE ORDER BY dev DESC, dev_name DESC;
-- query sort doesn't match "segmentby" sort
:PREFIX SELECT DISTINCT ON (dev) dev, dev_name FROM :TABLE ORDER BY dev, dev_name DESC;
-- dev_name is not a leading column
:PREFIX SELECT DISTINCT dev_name FROM :TABLE WHERE dev = 1 ORDER BY dev_name;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT dev_name FROM :TABLE WHERE dev = 1 AND dev_name IS NOT NULL ORDER BY dev_name;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE WHERE dev = 1;
-- Basic tests for "segmentby = 'dev'"
-----------------------------------------
SELECT decompress_chunk(ch) FROM show_chunks('skip_scan_htc') ch;
ALTER TABLE skip_scan_htc SET (timescaledb.compress, timescaledb.compress_orderby='time desc', timescaledb.compress_segmentby='dev');
psql:include/skip_scan_comp_query.sql:71: NOTICE:  updated compression settings will only apply to future compressions
SELECT compress_chunk(ch) FROM show_chunks('skip_scan_htc') ch;
\qecho basic DISTINCT queries on :TABLE
:PREFIX SELECT DISTINCT dev, 'q1_1' FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev, 'q1_3', NULL FROM :TABLE ORDER BY dev;
\qecho stable expression in targetlist on :TABLE
:PREFIX SELECT DISTINCT dev, 'q1_4', length(md5(now()::text)) FROM :TABLE ORDER BY dev;
-- volatile expression in targetlist counts as extra distinct column, no SkipScan
:PREFIX SELECT DISTINCT dev, 'q1_6', length(md5(random()::text)) FROM :TABLE ORDER BY dev;
-- queries without skipscan because distinct is not limited to specific column
:PREFIX SELECT DISTINCT * FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT *, 'q1_9' FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev, time, 'q1_10' FROM :TABLE ORDER BY dev;
-- use SkipScan as only one non-const distinct column
:PREFIX SELECT DISTINCT dev, NULL, 'q1_11' FROM :TABLE ORDER BY dev;
-- distinct on expressions not supported
:PREFIX SELECT DISTINCT dev + 1, 'q1_13' FROM :TABLE;
-- DISTINCT ON queries
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE dev_name IS NOT NULL;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_2' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_3', NULL FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_4', length(md5(now()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_5', length(md5(random()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) *, 'q2_7' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, time, 'q2_8' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, NULL, 'q2_9' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) time, 'q2_10' FROM :TABLE ORDER by dev, time DESC;
:PREFIX SELECT DISTINCT ON (dev) dev, tableoid::regclass, 'q2_11' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, int_func_immutable(), 'q2_12' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, int_func_stable(), 'q2_13' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, int_func_volatile(), 'q2_14' FROM :TABLE;
\qecho DISTINCT with wholerow var
:PREFIX SELECT DISTINCT ON (dev) :TABLE FROM :TABLE;
-- should not use SkipScan since we only support SkipScan on single-column distinct
:PREFIX SELECT DISTINCT :TABLE FROM :TABLE;
\qecho LIMIT queries on :TABLE
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE LIMIT 3;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev DESC, time LIMIT 3;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev, time DESC LIMIT 3;
\qecho range queries on :TABLE
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time BETWEEN 100 AND 300;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time < 200;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time > 800;
-- Basic tests for text index "segmentby = 'dev_name'"
------------------------------------------------------
SELECT decompress_chunk(ch) FROM show_chunks('skip_scan_htc') ch;
ALTER TABLE skip_scan_htc SET (timescaledb.compress, timescaledb.compress_orderby='time desc', timescaledb.compress_segmentby='dev_name');
psql:include/skip_scan_comp_query.sql:132: NOTICE:  updated compression settings will only apply to future compressions
SELECT compress_chunk(ch) FROM show_chunks('skip_scan_htc') ch;
:PREFIX SELECT DISTINCT dev_name, 'q1_2' FROM :TABLE ORDER BY dev_name;
\qecho stable expression in targetlist on :TABLE
:PREFIX SELECT DISTINCT dev_name, 'q1_5', length(md5(now()::text)) FROM :TABLE ORDER BY dev_name;
-- volatile expression in targetlist counts as extra distinct column, no SkipScan
:PREFIX SELECT DISTINCT dev_name, 'q1_7', length(md5(random()::text)) FROM :TABLE ORDER BY dev_name;
-- distinct on expressions not supported
:PREFIX SELECT DISTINCT 'Device ' || dev_name FROM :TABLE;
-- DISTINCT ON queries on TEXT column
:PREFIX SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE WHERE dev_name IS NOT NULL;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_2' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_3', NULL FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_4', length(md5(now()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_5', length(md5(random()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) * FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) *, 'q3_7' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, time, 'q3_8' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, NULL, 'q3_9' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) time, 'q3_10' FROM :TABLE ORDER by dev_name, time DESC;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, tableoid::regclass, 'q3_11' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name::varchar) dev_name::varchar FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev, int_func_immutable(), 'q3_13' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev, int_func_stable(), 'q3_14' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev, int_func_volatile(), 'q3_15' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE WHERE dev_name IS NULL;
-- Various tests for "segmentby = 'dev'"
---------------------------------------
SELECT decompress_chunk(ch) FROM show_chunks('skip_scan_htc') ch;
ALTER TABLE skip_scan_htc SET (timescaledb.compress, timescaledb.compress_orderby='time', timescaledb.compress_segmentby='dev');
psql:include/skip_scan_comp_query.sql:168: NOTICE:  updated compression settings will only apply to future compressions
SELECT compress_chunk(ch) FROM show_chunks('skip_scan_htc') ch;
\qecho SUBSELECTS on :TABLE
:PREFIX SELECT time, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a;
:PREFIX SELECT NULL, dev, NULL, 'q4_3' FROM (SELECT DISTINCT ON (dev) dev FROM :TABLE) a;
:PREFIX SELECT time, dev, NULL, 'q4_4' FROM (SELECT DISTINCT ON (dev) dev, time FROM :TABLE) a;
\qecho ORDER BY
:PREFIX SELECT time, dev, val, 'q5_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE ORDER BY dev, time) a;
:PREFIX SELECT time, dev, val, 'q5_2' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE ORDER BY dev DESC, time DESC) a;
\qecho WHERE CLAUSES
:PREFIX SELECT time, dev, val, 'q6_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > 5) a;
:PREFIX SELECT time, dev, val, 'q6_2' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE time > 5) a;
:PREFIX SELECT time, dev, val, 'q6_3' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a WHERE dev > 5;
:PREFIX SELECT time, dev, val, 'q6_4' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a WHERE time > 5;
--\qecho immutable func in WHERE clause on :TABLE
:PREFIX SELECT DISTINCT ON (dev) *, 'q6_5' FROM :TABLE WHERE dev > int_func_immutable();
--\qecho stable func in WHERE clause on :TABLE
:PREFIX SELECT DISTINCT ON (dev) *, 'q6_6' FROM :TABLE WHERE dev > int_func_stable();
--\qecho volatile func in WHERE clause on :TABLE
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > int_func_volatile();
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_immutable());
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_stable());
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_volatile());
-- RowCompareExpr
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE (dev, time) > (5,100);
-- always false expr similar to our initial skip qual
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > NULL;
-- no tuples matching
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > 20;
-- multiple constraints in WHERE clause
:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev > 5 AND time = 100;
:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev > 5 AND time > 200;
:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev >= 5 AND dev < 7 AND dev >= 2;
:PREFIX SELECT DISTINCT ON (dev) dev,time,val FROM :TABLE WHERE time > 100 AND time < 200 AND val > 10 AND val < 10000 AND dev > 2 AND dev < 7 ORDER BY dev,time;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE dev IS NULL;
-- test constants in ORDER BY
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = 1 ORDER BY dev, time DESC;
-- CTE
:PREFIX WITH devices AS (
	SELECT DISTINCT ON (dev) dev FROM :TABLE
)
SELECT * FROM devices;
:PREFIX WITH devices AS (
	SELECT DISTINCT dev FROM :TABLE
)
SELECT * FROM devices ORDER BY dev;
-- prepared statements
PREPARE prep AS SELECT DISTINCT ON (dev) dev FROM :TABLE;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
DEALLOCATE prep;
-- ReScan tests
-- no SkipScan as distinct is over subquery
:PREFIX SELECT time, dev, val, 'q7_1' FROM (SELECT DISTINCT ON (dev) * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT * FROM :TABLE WHERE time != a.v) b) a;
-- have SkipScan as Distinct is over index
:PREFIX SELECT time, dev, val, 'q7_2' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev != a.v) b) a;
-- RuntimeKeys
:PREFIX SELECT time, dev, val, 'q8_1' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev >= a.v) b) c;
-- Emulate multi-column DISTINCT using multiple SkipSkans
-- "segmentby = 'dev, val'"
---------------------------------------
SELECT decompress_chunk(ch) FROM show_chunks('skip_scan_htc') ch;
ALTER TABLE skip_scan_htc SET (timescaledb.compress, timescaledb.compress_orderby='time', timescaledb.compress_segmentby='dev, val');
psql:include/skip_scan_comp_query.sql:249: NOTICE:  updated compression settings will only apply to future compressions
SELECT compress_chunk(ch) FROM show_chunks('skip_scan_htc') ch;
:PREFIX SELECT time, dev, val, 'q9_1' FROM (SELECT b.* FROM
    (SELECT DISTINCT ON (dev) dev FROM :TABLE) a,
    LATERAL (SELECT DISTINCT ON (val) * FROM :TABLE WHERE dev = a.dev) b) c;
:PREFIX SELECT val, dev, NULL, 'q9_2' FROM (SELECT b.* FROM
    (SELECT DISTINCT ON (dev) dev FROM :TABLE) a,
    LATERAL (SELECT DISTINCT ON (val) dev, val FROM :TABLE WHERE dev = a.dev) b) c;
-- Test that the multi-column DISTINCT emulation is equivalent to a real multi-column DISTINCT
:PREFIX SELECT * FROM
   (SELECT DISTINCT ON (dev) dev FROM :TABLE) a,
   LATERAL (SELECT DISTINCT ON (val) dev, val FROM :TABLE WHERE dev = a.dev) b;
-- no SkipScan: 2 distinct columns
:PREFIX SELECT DISTINCT ON (dev, val) dev, val FROM :TABLE WHERE dev IS NOT NULL;
:PREFIX SELECT DISTINCT ON (dev, val) dev, val FROM :TABLE WHERE dev IS NOT NULL
UNION SELECT b.* FROM
   (SELECT DISTINCT ON (dev) dev FROM :TABLE) a,
   LATERAL (SELECT DISTINCT ON (val) dev, val FROM :TABLE WHERE dev = a.dev) b;
-- SkipScan into INSERT
:PREFIX INSERT INTO skip_scan_insert(time, dev, val, query) SELECT time, dev, val, 'q10_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a;
-- parallel query
RESET max_parallel_workers_per_gather;
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'on', false);
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev;
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'off', false);
TRUNCATE skip_scan_insert;
-- table with only nulls
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE where val IS NULL;
-- no tuples in resultset
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE val IS NULL AND dev > 100;
SELECT decompress_chunk(ch) FROM show_chunks('skip_scan_htc') ch;
\o
SET timescaledb.enable_compressed_skipscan TO false;
\o :TEST_RESULTS_UNOPTIMIZED
\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 result diff
SELECT current_setting('timescaledb.enable_compressed_skipscan') AS enable_compressed_skipscan;
-- To avoid ambiguity in test EXPLAIN outputs due to mixing of chunk plans with no SkipScan
SET max_parallel_workers_per_gather = 0;
-- test different compression configurations
-- compressed index on "segmentby='dev'" has default "dev ASC, NULLS LAST" sort order
ALTER TABLE skip_scan_htc SET (timescaledb.compress, timescaledb.compress_orderby='time desc', timescaledb.compress_segmentby='dev');
psql:include/skip_scan_comp_query.sql:14: NOTICE:  updated compression settings will only apply to future compressions
SELECT compress_chunk(ch) FROM show_chunks('skip_scan_htc') ch;
-- SkipScan used when sort order on distinct column "dev" matches "segmentby" sort order
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev DESC;
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev DESC NULLS FIRST;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT dev FROM :TABLE WHERE dev IS NOT NULL ORDER BY dev;
:PREFIX SELECT DISTINCT dev FROM :TABLE WHERE dev IS NOT NULL ORDER BY dev DESC NULLS FIRST;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE;
-- NULLS FIRST doesn't match segmentby NULL direction
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev NULLS FIRST;
-- multicolumn sort with dev as leading column matching (segmentby dev, order by time DESC) compression index
:PREFIX SELECT DISTINCT ON (dev) dev, time FROM :TABLE ORDER BY dev, time DESC;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT ON (dev) dev, time FROM :TABLE WHERE dev IS NOT NULL ORDER BY dev, time DESC;
:PREFIX SELECT DISTINCT ON (dev) dev, time FROM :TABLE ORDER BY dev DESC, time;
-- multicolumn sort not matching compression index
:PREFIX SELECT DISTINCT ON (dev) dev, time FROM :TABLE ORDER BY dev DESC, time DESC;
-- same result when we use compressed IndexPath with pathkeys not matching DecompressChunk path required path keys
SET enable_seqscan TO false;
:PREFIX SELECT DISTINCT ON (dev) dev, time FROM :TABLE ORDER BY dev DESC, time DESC;
RESET enable_seqscan;
-- Distinct column not a segmentby column: should be no SkipScan
:PREFIX SELECT DISTINCT time FROM :TABLE WHERE dev = 1 ORDER BY time DESC;
-- multicolumn sort with dev as non-leading column and with leading column pinned
-- we are able to apply SkipScan here after resolving #7998
:PREFIX SELECT DISTINCT time, dev FROM :TABLE WHERE time = 100 ORDER BY time, dev;
-- multicolumn "segmentby = 'dev, dev_name'"
SELECT decompress_chunk(ch) FROM show_chunks('skip_scan_htc') ch;
ALTER TABLE skip_scan_htc SET (timescaledb.compress, timescaledb.compress_orderby='time desc', timescaledb.compress_segmentby='dev,dev_name');
psql:include/skip_scan_comp_query.sql:51: NOTICE:  updated compression settings will only apply to future compressions
SELECT compress_chunk(ch) FROM show_chunks('skip_scan_htc') ch;
-- dev is leading column
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, dev_name FROM :TABLE ORDER BY dev, dev_name;
:PREFIX SELECT DISTINCT ON (dev) dev, dev_name FROM :TABLE ORDER BY dev DESC, dev_name DESC;
-- query sort doesn't match "segmentby" sort
:PREFIX SELECT DISTINCT ON (dev) dev, dev_name FROM :TABLE ORDER BY dev, dev_name DESC;
-- dev_name is not a leading column
:PREFIX SELECT DISTINCT dev_name FROM :TABLE WHERE dev = 1 ORDER BY dev_name;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT dev_name FROM :TABLE WHERE dev = 1 AND dev_name IS NOT NULL ORDER BY dev_name;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE WHERE dev = 1;
-- Basic tests for "segmentby = 'dev'"
-----------------------------------------
SELECT decompress_chunk(ch) FROM show_chunks('skip_scan_htc') ch;
ALTER TABLE skip_scan_htc SET (timescaledb.compress, timescaledb.compress_orderby='time desc', timescaledb.compress_segmentby='dev');
psql:include/skip_scan_comp_query.sql:71: NOTICE:  updated compression settings will only apply to future compressions
SELECT compress_chunk(ch) FROM show_chunks('skip_scan_htc') ch;
\qecho basic DISTINCT queries on :TABLE
:PREFIX SELECT DISTINCT dev, 'q1_1' FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev, 'q1_3', NULL FROM :TABLE ORDER BY dev;
\qecho stable expression in targetlist on :TABLE
:PREFIX SELECT DISTINCT dev, 'q1_4', length(md5(now()::text)) FROM :TABLE ORDER BY dev;
-- volatile expression in targetlist counts as extra distinct column, no SkipScan
:PREFIX SELECT DISTINCT dev, 'q1_6', length(md5(random()::text)) FROM :TABLE ORDER BY dev;
-- queries without skipscan because distinct is not limited to specific column
:PREFIX SELECT DISTINCT * FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT *, 'q1_9' FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev, time, 'q1_10' FROM :TABLE ORDER BY dev;
-- use SkipScan as only one non-const distinct column
:PREFIX SELECT DISTINCT dev, NULL, 'q1_11' FROM :TABLE ORDER BY dev;
-- distinct on expressions not supported
:PREFIX SELECT DISTINCT dev + 1, 'q1_13' FROM :TABLE;
-- DISTINCT ON queries
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE dev_name IS NOT NULL;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_2' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_3', NULL FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_4', length(md5(now()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_5', length(md5(random()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) *, 'q2_7' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, time, 'q2_8' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, NULL, 'q2_9' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) time, 'q2_10' FROM :TABLE ORDER by dev, time DESC;
:PREFIX SELECT DISTINCT ON (dev) dev, tableoid::regclass, 'q2_11' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, int_func_immutable(), 'q2_12' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, int_func_stable(), 'q2_13' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, int_func_volatile(), 'q2_14' FROM :TABLE;
\qecho DISTINCT with wholerow var
:PREFIX SELECT DISTINCT ON (dev) :TABLE FROM :TABLE;
-- should not use SkipScan since we only support SkipScan on single-column distinct
:PREFIX SELECT DISTINCT :TABLE FROM :TABLE;
\qecho LIMIT queries on :TABLE
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE LIMIT 3;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev DESC, time LIMIT 3;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev, time DESC LIMIT 3;
\qecho range queries on :TABLE
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time BETWEEN 100 AND 300;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time < 200;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time > 800;
-- Basic tests for text index "segmentby = 'dev_name'"
------------------------------------------------------
SELECT decompress_chunk(ch) FROM show_chunks('skip_scan_htc') ch;
ALTER TABLE skip_scan_htc SET (timescaledb.compress, timescaledb.compress_orderby='time desc', timescaledb.compress_segmentby='dev_name');
psql:include/skip_scan_comp_query.sql:132: NOTICE:  updated compression settings will only apply to future compressions
SELECT compress_chunk(ch) FROM show_chunks('skip_scan_htc') ch;
:PREFIX SELECT DISTINCT dev_name, 'q1_2' FROM :TABLE ORDER BY dev_name;
\qecho stable expression in targetlist on :TABLE
:PREFIX SELECT DISTINCT dev_name, 'q1_5', length(md5(now()::text)) FROM :TABLE ORDER BY dev_name;
-- volatile expression in targetlist counts as extra distinct column, no SkipScan
:PREFIX SELECT DISTINCT dev_name, 'q1_7', length(md5(random()::text)) FROM :TABLE ORDER BY dev_name;
-- distinct on expressions not supported
:PREFIX SELECT DISTINCT 'Device ' || dev_name FROM :TABLE;
-- DISTINCT ON queries on TEXT column
:PREFIX SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE;
-- Test not-NULL mode
:PREFIX SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE WHERE dev_name IS NOT NULL;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_2' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_3', NULL FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_4', length(md5(now()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_5', length(md5(random()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) * FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) *, 'q3_7' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, time, 'q3_8' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, NULL, 'q3_9' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) time, 'q3_10' FROM :TABLE ORDER by dev_name, time DESC;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name, tableoid::regclass, 'q3_11' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name::varchar) dev_name::varchar FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev, int_func_immutable(), 'q3_13' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev, int_func_stable(), 'q3_14' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev, int_func_volatile(), 'q3_15' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE WHERE dev_name IS NULL;
-- Various tests for "segmentby = 'dev'"
---------------------------------------
SELECT decompress_chunk(ch) FROM show_chunks('skip_scan_htc') ch;
ALTER TABLE skip_scan_htc SET (timescaledb.compress, timescaledb.compress_orderby='time', timescaledb.compress_segmentby='dev');
psql:include/skip_scan_comp_query.sql:168: NOTICE:  updated compression settings will only apply to future compressions
SELECT compress_chunk(ch) FROM show_chunks('skip_scan_htc') ch;
\qecho SUBSELECTS on :TABLE
:PREFIX SELECT time, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a;
:PREFIX SELECT NULL, dev, NULL, 'q4_3' FROM (SELECT DISTINCT ON (dev) dev FROM :TABLE) a;
:PREFIX SELECT time, dev, NULL, 'q4_4' FROM (SELECT DISTINCT ON (dev) dev, time FROM :TABLE) a;
\qecho ORDER BY
:PREFIX SELECT time, dev, val, 'q5_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE ORDER BY dev, time) a;
:PREFIX SELECT time, dev, val, 'q5_2' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE ORDER BY dev DESC, time DESC) a;
\qecho WHERE CLAUSES
:PREFIX SELECT time, dev, val, 'q6_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > 5) a;
:PREFIX SELECT time, dev, val, 'q6_2' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE time > 5) a;
:PREFIX SELECT time, dev, val, 'q6_3' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a WHERE dev > 5;
:PREFIX SELECT time, dev, val, 'q6_4' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a WHERE time > 5;
--\qecho immutable func in WHERE clause on :TABLE
:PREFIX SELECT DISTINCT ON (dev) *, 'q6_5' FROM :TABLE WHERE dev > int_func_immutable();
--\qecho stable func in WHERE clause on :TABLE
:PREFIX SELECT DISTINCT ON (dev) *, 'q6_6' FROM :TABLE WHERE dev > int_func_stable();
--\qecho volatile func in WHERE clause on :TABLE
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > int_func_volatile();
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_immutable());
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_stable());
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_volatile());
-- RowCompareExpr
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE (dev, time) > (5,100);
-- always false expr similar to our initial skip qual
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > NULL;
-- no tuples matching
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > 20;
-- multiple constraints in WHERE clause
:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev > 5 AND time = 100;
:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev > 5 AND time > 200;
:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev >= 5 AND dev < 7 AND dev >= 2;
:PREFIX SELECT DISTINCT ON (dev) dev,time,val FROM :TABLE WHERE time > 100 AND time < 200 AND val > 10 AND val < 10000 AND dev > 2 AND dev < 7 ORDER BY dev,time;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE dev IS NULL;
-- test constants in ORDER BY
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = 1 ORDER BY dev, time DESC;
-- CTE
:PREFIX WITH devices AS (
	SELECT DISTINCT ON (dev) dev FROM :TABLE
)
SELECT * FROM devices;
:PREFIX WITH devices AS (
	SELECT DISTINCT dev FROM :TABLE
)
SELECT * FROM devices ORDER BY dev;
-- prepared statements
PREPARE prep AS SELECT DISTINCT ON (dev) dev FROM :TABLE;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
DEALLOCATE prep;
-- ReScan tests
-- no SkipScan as distinct is over subquery
:PREFIX SELECT time, dev, val, 'q7_1' FROM (SELECT DISTINCT ON (dev) * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT * FROM :TABLE WHERE time != a.v) b) a;
-- have SkipScan as Distinct is over index
:PREFIX SELECT time, dev, val, 'q7_2' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev != a.v) b) a;
-- RuntimeKeys
:PREFIX SELECT time, dev, val, 'q8_1' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev >= a.v) b) c;
-- Emulate multi-column DISTINCT using multiple SkipSkans
-- "segmentby = 'dev, val'"
---------------------------------------
SELECT decompress_chunk(ch) FROM show_chunks('skip_scan_htc') ch;
ALTER TABLE skip_scan_htc SET (timescaledb.compress, timescaledb.compress_orderby='time', timescaledb.compress_segmentby='dev, val');
psql:include/skip_scan_comp_query.sql:249: NOTICE:  updated compression settings will only apply to future compressions
SELECT compress_chunk(ch) FROM show_chunks('skip_scan_htc') ch;
:PREFIX SELECT time, dev, val, 'q9_1' FROM (SELECT b.* FROM
    (SELECT DISTINCT ON (dev) dev FROM :TABLE) a,
    LATERAL (SELECT DISTINCT ON (val) * FROM :TABLE WHERE dev = a.dev) b) c;
:PREFIX SELECT val, dev, NULL, 'q9_2' FROM (SELECT b.* FROM
    (SELECT DISTINCT ON (dev) dev FROM :TABLE) a,
    LATERAL (SELECT DISTINCT ON (val) dev, val FROM :TABLE WHERE dev = a.dev) b) c;
-- Test that the multi-column DISTINCT emulation is equivalent to a real multi-column DISTINCT
:PREFIX SELECT * FROM
   (SELECT DISTINCT ON (dev) dev FROM :TABLE) a,
   LATERAL (SELECT DISTINCT ON (val) dev, val FROM :TABLE WHERE dev = a.dev) b;
-- no SkipScan: 2 distinct columns
:PREFIX SELECT DISTINCT ON (dev, val) dev, val FROM :TABLE WHERE dev IS NOT NULL;
:PREFIX SELECT DISTINCT ON (dev, val) dev, val FROM :TABLE WHERE dev IS NOT NULL
UNION SELECT b.* FROM
   (SELECT DISTINCT ON (dev) dev FROM :TABLE) a,
   LATERAL (SELECT DISTINCT ON (val) dev, val FROM :TABLE WHERE dev = a.dev) b;
-- SkipScan into INSERT
:PREFIX INSERT INTO skip_scan_insert(time, dev, val, query) SELECT time, dev, val, 'q10_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a;
-- parallel query
RESET max_parallel_workers_per_gather;
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'on', false);
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev;
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'off', false);
TRUNCATE skip_scan_insert;
-- table with only nulls
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE where val IS NULL;
-- no tuples in resultset
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE val IS NULL AND dev > 100;
SELECT decompress_chunk(ch) FROM show_chunks('skip_scan_htc') ch;
\o
RESET timescaledb.enable_compressed_skipscan;
-- compare SkipScan results on compressed hypertable
:DIFF_CMD
--- Unoptimized results
+++ Optimized results
@@ -1,6 +1,6 @@
  enable_compressed_skipscan 
 ----------------------------
- off
+ on
 
              compress_chunk             
-- run tests on compressed hypertable with different layouts of compressed chunks
SELECT format('include/%s_load_comp_query.sql', :'TEST_BASE_NAME') AS "TEST_QUERY_NAME" \gset
\set TABLE skip_scan_htcl
\set PREFIX ''
\o :TEST_RESULTS_OPTIMIZED
\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 result diff
SELECT current_setting('timescaledb.enable_compressed_skipscan') AS enable_compressed_skipscan;
-- To avoid ambiguity in test EXPLAIN outputs due to mixing of chunk plans with no SkipScan
SET max_parallel_workers_per_gather = 0;
-- Run SkipScan queries on the provided compression layout
-- compressed index on "segmentby='dev'" has default "dev ASC, NULLS LAST" sort order
-- SkipScan used when sort order on distinct column "dev" matches "segmentby" sort order
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev DESC;
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev DESC NULLS FIRST;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE;
-- multicolumn sort with dev as leading column matching (segmentby dev, order by time DESC) compression index
:PREFIX SELECT DISTINCT ON (dev) dev, time FROM :TABLE ORDER BY dev, time DESC;
:PREFIX SELECT DISTINCT ON (dev) dev, time FROM :TABLE ORDER BY dev DESC, time;
\qecho stable expression in targetlist on :TABLE
:PREFIX SELECT DISTINCT dev, 'q1_4', length(md5(now()::text)) FROM :TABLE ORDER BY dev;
-- DISTINCT ON queries
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_2' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_3', NULL FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_4', length(md5(now()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_5', length(md5(random()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) *, 'q2_7' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, time, 'q2_8' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, NULL, 'q2_9' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) time, 'q2_10' FROM :TABLE ORDER by dev, time DESC;
:PREFIX SELECT DISTINCT ON (dev) dev, tableoid::regclass, 'q2_11' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, int_func_immutable(), 'q2_12' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, int_func_stable(), 'q2_13' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, int_func_volatile(), 'q2_14' FROM :TABLE;
\qecho DISTINCT with wholerow var
:PREFIX SELECT DISTINCT ON (dev) :TABLE FROM :TABLE;
\qecho LIMIT queries on :TABLE
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE LIMIT 3;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev DESC, time LIMIT 3;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev, time DESC LIMIT 3;
\qecho range queries on :TABLE
set enable_seqscan to off;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time BETWEEN 100 AND 300;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time < 200;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time > 800;
reset enable_seqscan;
-- Various tests for "segmentby = 'dev'"
---------------------------------------
\qecho SUBSELECTS on :TABLE
:PREFIX SELECT time, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a;
:PREFIX SELECT NULL, dev, NULL, 'q4_3' FROM (SELECT DISTINCT ON (dev) dev FROM :TABLE) a;
:PREFIX SELECT time, dev, NULL, 'q4_4' FROM (SELECT DISTINCT ON (dev) dev, time FROM :TABLE) a;
\qecho ORDER BY
:PREFIX SELECT time, dev, val, 'q5_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE ORDER BY dev, time DESC) a;
:PREFIX SELECT time, dev, val, 'q5_2' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE ORDER BY dev DESC, time) a;
\qecho WHERE CLAUSES
:PREFIX SELECT time, dev, val, 'q6_2' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE time > 5) a;
:PREFIX SELECT time, dev, val, 'q6_4' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a WHERE time > 5;
-- Force not choosing SeqScan under DecompressChunks before SkipScan can evaluate it,
-- as cost of scanning and sorting a small dataset can be less than scanning an index, including startup cost.
-- After the fix in #8056 we take startup costs into account,
-- but even startup costs for scanning and sorting can be smaller for seqscan if we also have a filter on the index columns.
-- So for below queries with filters on "dev" we still need to disable SeqScan to choose IndexScan+SkipScan.
SET enable_seqscan = false;
:PREFIX SELECT time, dev, val, 'q6_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > 5) a;
:PREFIX SELECT time, dev, val, 'q6_3' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a WHERE dev > 5;
--\qecho immutable func in WHERE clause on :TABLE
:PREFIX SELECT DISTINCT ON (dev) *, 'q6_5' FROM :TABLE WHERE dev > int_func_immutable();
--\qecho stable func in WHERE clause on :TABLE
:PREFIX SELECT DISTINCT ON (dev) *, 'q6_6' FROM :TABLE WHERE dev > int_func_stable();
--\qecho volatile func in WHERE clause on :TABLE
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > int_func_volatile();
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_immutable());
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_stable());
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_volatile());
-- RowCompareExpr
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE (dev, time) > (5,100);
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > NULL;
-- no tuples matching
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > 20;
-- multiple constraints in WHERE clause
:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev > 5 AND time = 100;
:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev > 5 AND time > 200;
:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev >= 5 AND dev < 7 AND dev >= 2;
:PREFIX SELECT DISTINCT ON (dev) dev,time,val FROM :TABLE WHERE time > 100 AND time < 200 AND val > 10 AND val < 10000 AND dev > 2 AND dev < 7 ORDER BY dev,time DESC;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE dev IS NULL;
-- test constants in ORDER BY
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = 1 ORDER BY dev, time DESC;
-- CTE
:PREFIX WITH devices AS (
	SELECT DISTINCT ON (dev) dev FROM :TABLE
)
SELECT * FROM devices;
:PREFIX WITH devices AS (
	SELECT DISTINCT dev FROM :TABLE
)
SELECT * FROM devices ORDER BY dev;
-- prepared statements
PREPARE prep AS SELECT DISTINCT ON (dev) dev FROM :TABLE;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
DEALLOCATE prep;
-- ReScan tests
-- have SkipScan as Distinct is over index
:PREFIX SELECT time, dev, val, 'q7_2' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev != a.v) b) a;
-- RuntimeKeys
:PREFIX SELECT time, dev, val, 'q8_1' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev >= a.v) b) c;
RESET max_parallel_workers_per_gather;
RESET enable_seqscan;
\o
SET timescaledb.enable_compressed_skipscan TO false;
\o :TEST_RESULTS_UNOPTIMIZED
\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 result diff
SELECT current_setting('timescaledb.enable_compressed_skipscan') AS enable_compressed_skipscan;
-- To avoid ambiguity in test EXPLAIN outputs due to mixing of chunk plans with no SkipScan
SET max_parallel_workers_per_gather = 0;
-- Run SkipScan queries on the provided compression layout
-- compressed index on "segmentby='dev'" has default "dev ASC, NULLS LAST" sort order
-- SkipScan used when sort order on distinct column "dev" matches "segmentby" sort order
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev DESC;
:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev DESC NULLS FIRST;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE;
-- multicolumn sort with dev as leading column matching (segmentby dev, order by time DESC) compression index
:PREFIX SELECT DISTINCT ON (dev) dev, time FROM :TABLE ORDER BY dev, time DESC;
:PREFIX SELECT DISTINCT ON (dev) dev, time FROM :TABLE ORDER BY dev DESC, time;
\qecho stable expression in targetlist on :TABLE
:PREFIX SELECT DISTINCT dev, 'q1_4', length(md5(now()::text)) FROM :TABLE ORDER BY dev;
-- DISTINCT ON queries
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_2' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_3', NULL FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_4', length(md5(now()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_5', length(md5(random()::text)) FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) *, 'q2_7' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, time, 'q2_8' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, NULL, 'q2_9' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) time, 'q2_10' FROM :TABLE ORDER by dev, time DESC;
:PREFIX SELECT DISTINCT ON (dev) dev, tableoid::regclass, 'q2_11' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, int_func_immutable(), 'q2_12' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, int_func_stable(), 'q2_13' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev) dev, int_func_volatile(), 'q2_14' FROM :TABLE;
\qecho DISTINCT with wholerow var
:PREFIX SELECT DISTINCT ON (dev) :TABLE FROM :TABLE;
\qecho LIMIT queries on :TABLE
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE LIMIT 3;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev DESC, time LIMIT 3;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev, time DESC LIMIT 3;
\qecho range queries on :TABLE
set enable_seqscan to off;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time BETWEEN 100 AND 300;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time < 200;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time > 800;
reset enable_seqscan;
-- Various tests for "segmentby = 'dev'"
---------------------------------------
\qecho SUBSELECTS on :TABLE
:PREFIX SELECT time, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a;
:PREFIX SELECT NULL, dev, NULL, 'q4_3' FROM (SELECT DISTINCT ON (dev) dev FROM :TABLE) a;
:PREFIX SELECT time, dev, NULL, 'q4_4' FROM (SELECT DISTINCT ON (dev) dev, time FROM :TABLE) a;
\qecho ORDER BY
:PREFIX SELECT time, dev, val, 'q5_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE ORDER BY dev, time DESC) a;
:PREFIX SELECT time, dev, val, 'q5_2' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE ORDER BY dev DESC, time) a;
\qecho WHERE CLAUSES
:PREFIX SELECT time, dev, val, 'q6_2' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE time > 5) a;
:PREFIX SELECT time, dev, val, 'q6_4' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a WHERE time > 5;
-- Force not choosing SeqScan under DecompressChunks before SkipScan can evaluate it,
-- as cost of scanning and sorting a small dataset can be less than scanning an index, including startup cost.
-- After the fix in #8056 we take startup costs into account,
-- but even startup costs for scanning and sorting can be smaller for seqscan if we also have a filter on the index columns.
-- So for below queries with filters on "dev" we still need to disable SeqScan to choose IndexScan+SkipScan.
SET enable_seqscan = false;
:PREFIX SELECT time, dev, val, 'q6_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > 5) a;
:PREFIX SELECT time, dev, val, 'q6_3' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a WHERE dev > 5;
--\qecho immutable func in WHERE clause on :TABLE
:PREFIX SELECT DISTINCT ON (dev) *, 'q6_5' FROM :TABLE WHERE dev > int_func_immutable();
--\qecho stable func in WHERE clause on :TABLE
:PREFIX SELECT DISTINCT ON (dev) *, 'q6_6' FROM :TABLE WHERE dev > int_func_stable();
--\qecho volatile func in WHERE clause on :TABLE
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > int_func_volatile();
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_immutable());
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_stable());
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_volatile());
-- RowCompareExpr
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE (dev, time) > (5,100);
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > NULL;
-- no tuples matching
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > 20;
-- multiple constraints in WHERE clause
:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev > 5 AND time = 100;
:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev > 5 AND time > 200;
:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev >= 5 AND dev < 7 AND dev >= 2;
:PREFIX SELECT DISTINCT ON (dev) dev,time,val FROM :TABLE WHERE time > 100 AND time < 200 AND val > 10 AND val < 10000 AND dev > 2 AND dev < 7 ORDER BY dev,time DESC;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE dev IS NULL;
-- test constants in ORDER BY
:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = 1 ORDER BY dev, time DESC;
-- CTE
:PREFIX WITH devices AS (
	SELECT DISTINCT ON (dev) dev FROM :TABLE
)
SELECT * FROM devices;
:PREFIX WITH devices AS (
	SELECT DISTINCT dev FROM :TABLE
)
SELECT * FROM devices ORDER BY dev;
-- prepared statements
PREPARE prep AS SELECT DISTINCT ON (dev) dev FROM :TABLE;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
DEALLOCATE prep;
-- ReScan tests
-- have SkipScan as Distinct is over index
:PREFIX SELECT time, dev, val, 'q7_2' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev != a.v) b) a;
-- RuntimeKeys
:PREFIX SELECT time, dev, val, 'q8_1' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev >= a.v) b) c;
RESET max_parallel_workers_per_gather;
RESET enable_seqscan;
\o
RESET timescaledb.enable_compressed_skipscan;
-- compare SkipScan results on compressed hypertable
:DIFF_CMD
--- Unoptimized results
+++ Optimized results
@@ -1,6 +1,6 @@
  enable_compressed_skipscan 
 ----------------------------
- off
+ on
 
  dev 
-- run tests on compressed hypertable with different layouts of compressed chunks
SELECT format('include/%s_multi_query.sql', :'TEST_BASE_NAME') AS "TEST_QUERY_NAME" \gset
-- run multikey SkipScan tests
\ir :TEST_MULTI_LOAD_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.
-- Create tables with 4 not-null index columns (ASC, DESC, ASC, ASC) to test various multikey SkipScan scenarios
-- Not-null scenarios are tested separately in skip_scan_notnull.sql, here we are testing multi-keys specifically
CREATE TABLE mskip_scan(time int not null, status int not null, region text not null, dev int not null, dev_name text not null, val int);
-- Status: (1,2), region: (reg1..reg4), dev: (1..5), dev_name same as dev, 4000 timestamps divided into 4 chunks tied to status, 80K rows total
-- We want to test both (1 distinct, N distinct) and (N distinct, 1 distinct) scenarios,
-- (status,*) where time<100 will do for the former, (dev, dev_name) will do for the latter
INSERT INTO mskip_scan SELECT t, 1, 'reg_' || r::text, d, 'device_' || d::text, t*d FROM generate_series(0, 999) t, generate_series(1, 4) r, generate_series(1, 5) d;
INSERT INTO mskip_scan SELECT t, 1, 'reg_' || r::text, d, 'device_' || d::text, t*d FROM generate_series(1000, 1999) t, generate_series(1, 4) r, generate_series(1, 5) d;
INSERT INTO mskip_scan SELECT t, 1, 'reg_' || r::text, d, 'device_' || d::text, t*d FROM generate_series(2000, 2999) t, generate_series(1, 4) r, generate_series(1, 5) d;
INSERT INTO mskip_scan SELECT t, 1, 'reg_' || r::text, d, 'device_' || d::text, t*d FROM generate_series(3000, 3999) t, generate_series(1, 4) r, generate_series(1, 5) d;
CREATE INDEX on mskip_scan(status, region, dev, dev_name);
-- To test reverse column orders
CREATE INDEX on mskip_scan(status, region DESC, dev);
ANALYZE mskip_scan;
-- Same with a hypertable
CREATE TABLE mskip_scan_ht(time int not null, status int not null, region text not null, dev int not null, dev_name text not null, val int);
SELECT create_hypertable('mskip_scan_ht', 'time', chunk_time_interval => 1000, create_default_indexes => false);
     create_hypertable      
----------------------------
 (7,public,mskip_scan_ht,t)

INSERT INTO mskip_scan_ht SELECT t, 1, 'reg_' || r::text, d, 'device_' || d::text, t*d FROM generate_series(0, 999) t, generate_series(1, 4) r, generate_series(1, 5) d;
INSERT INTO mskip_scan_ht SELECT t, 1, 'reg_' || r::text, d, 'device_' || d::text, t*d FROM generate_series(1000, 1999) t, generate_series(1, 4) r, generate_series(1, 5) d;
INSERT INTO mskip_scan_ht SELECT t, 1, 'reg_' || r::text, d, 'device_' || d::text, t*d FROM generate_series(2000, 2999) t, generate_series(1, 4) r, generate_series(1, 5) d;
INSERT INTO mskip_scan_ht SELECT t, 1, 'reg_' || r::text, d, 'device_' || d::text, t*d FROM generate_series(3000, 3999) t, generate_series(1, 4) r, generate_series(1, 5) d;
CREATE INDEX on mskip_scan_ht(status, region, dev, dev_name);
-- To test reverse column orders
CREATE INDEX on mskip_scan_ht(status, region DESC, dev);
ANALYZE mskip_scan_ht;
-- Same with columnar
CREATE TABLE mskip_scan_htc(time int not null, status int not null, region text not null, dev int not null, dev_name text not null, val int);
SELECT create_hypertable('mskip_scan_htc', 'time', chunk_time_interval => 1000, create_default_indexes => false);
      create_hypertable      
-----------------------------
 (8,public,mskip_scan_htc,t)

-- Cannot have reverse orders on segmented columns in columnar index, so it won't be tested for columnar table
ALTER TABLE mskip_scan_htc SET (timescaledb.compress, timescaledb.compress_orderby='time desc', timescaledb.compress_segmentby='status,region,dev,dev_name');
INSERT INTO mskip_scan_htc SELECT t, 1, 'reg_' || r::text, d, 'device_' || d::text, t*d FROM generate_series(0, 999) t, generate_series(1, 4) r, generate_series(1, 5) d;
INSERT INTO mskip_scan_htc SELECT t, 1, 'reg_' || r::text, d, 'device_' || d::text, t*d FROM generate_series(1000, 1999) t, generate_series(1, 4) r, generate_series(1, 5) d;
INSERT INTO mskip_scan_htc SELECT t, 1, 'reg_' || r::text, d, 'device_' || d::text, t*d FROM generate_series(2000, 2999) t, generate_series(1, 4) r, generate_series(1, 5) d;
INSERT INTO mskip_scan_htc SELECT t, 1, 'reg_' || r::text, d, 'device_' || d::text, t*d FROM generate_series(3000, 3999) t, generate_series(1, 4) r, generate_series(1, 5) d;
SELECT compress_chunk(ch) FROM show_chunks('mskip_scan_htc') ch;
             compress_chunk              
-----------------------------------------
 _timescaledb_internal._hyper_8_69_chunk
 _timescaledb_internal._hyper_8_70_chunk
 _timescaledb_internal._hyper_8_71_chunk
 _timescaledb_internal._hyper_8_72_chunk

ANALYZE mskip_scan_htc;
alter table mskip_scan set (autovacuum_enabled = off);
alter table mskip_scan_ht set (autovacuum_enabled = off);
alter table mskip_scan_htc set (autovacuum_enabled = off);
\set PREFIX ''
-- make sure multikey SkipScan results are correct
\set TABLE mskip_scan
\o :TEST_RESULTS_OPTIMIZED
\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 result diff
SELECT current_setting('timescaledb.enable_multikey_skipscan') AS enable_multikey_skipscan;
-- multicolumn index with uniform order
:PREFIX SELECT DISTINCT ON(status, region, dev, dev_name) * FROM :TABLE ORDER BY status, region, dev, dev_name;
:PREFIX SELECT DISTINCT ON(region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region DESC, dev DESC;
-- index with reverse column orders (applied to non-columnstore ony)
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE ORDER BY status, region DESC, dev;
:PREFIX SELECT DISTINCT ON(region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region, dev DESC;
set enable_seqscan=0;
-- (1,N) distinct scenario
:PREFIX SELECT DISTINCT status, dev FROM :TABLE WHERE time < 100 AND region = 'reg_1' ORDER BY 1,2;
-- (N,1) distinct scenario
:PREFIX SELECT DISTINCT dev, dev_name FROM :TABLE WHERE status = 1 AND region = 'reg_1' ORDER BY 1,2;
-- SELECT list order of distinct columns shouldn't matter if OBY matches index order
:PREFIX SELECT DISTINCT dev, status, dev_name FROM :TABLE WHERE region = 'reg_2' ORDER BY status, dev, dev_name;
reset enable_seqscan;
-- Basic queries
:PREFIX SELECT DISTINCT region, dev, 'q1_3', NULL FROM :TABLE WHERE status = 1 ORDER BY region, dev;
-- DISTINCT ON queries
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, tableoid::regclass, 'q2_11' FROM :TABLE WHERE status = 1;
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_immutable(), 'q2_12' FROM :TABLE WHERE status = 1;
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_stable(), 'q2_13' FROM :TABLE WHERE status = 1;
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_volatile(), 'q2_14' FROM :TABLE WHERE status = 1;
\qecho ordered append on :TABLE
:PREFIX SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1 AND time BETWEEN 0 AND 5000;
\qecho SUBSELECTS on :TABLE
:PREFIX SELECT time, region, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (region, dev) * FROM :TABLE WHERE status = 1) a;
:PREFIX SELECT NULL, region, dev, NULL, 'q4_3' FROM (SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1) a;
\qecho ORDER BY
:PREFIX SELECT time, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region, dev) a;
-- RowCompareExpr
:PREFIX SELECT DISTINCT ON (status, dev) * FROM :TABLE WHERE region = 'reg_1' and (status, dev) > (1,2);
-- always false expr similar to our initial skip qual
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev > NULL and status > NULL and region > NULL;
-- no tuples matching
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev > 20;
-- test constants in ORDER BY
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE status = 1 ORDER BY status, region;
-- test distinct PathKey with sortref = 0 in PG15 due to FALSE filter not pushed into relation (should not crash in PG15)
:PREFIX SELECT DISTINCT sq.dev FROM (SELECT status, region, dev FROM :TABLE) sq JOIN :TABLE ref
ON (sq.dev = ref.dev) AND (sq.status = ref.status) AND (sq.region = ref.region) WHERE 1 > 2;
-- CTE
:PREFIX WITH devices AS (
	SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1
)
SELECT * FROM devices;
:PREFIX WITH devices AS (
	SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1
)
SELECT * FROM devices ORDER BY region, dev;
-- prepared statements
PREPARE prep AS SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name FROM :TABLE WHERE status = 1;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
DEALLOCATE prep;
-- ReScan tests
:PREFIX SELECT time, status, region, dev, val, 'q7_1' FROM (SELECT DISTINCT ON (status, region, dev) * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT * FROM :TABLE WHERE time != a.v) b) a;
set enable_seqscan=0;
:PREFIX SELECT time, status, region, dev, val, 'q7_2' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev != a.v) b) a;
-- RuntimeKeys
:PREFIX SELECT time, status, region, dev, val, 'q8_1' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev >= a.v) b) c;
reset enable_seqscan;
-- parallel query
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'on', false);
:PREFIX SELECT DISTINCT status, region, dev FROM :TABLE ORDER BY status, region, dev;
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'off', false);
\o
SET timescaledb.enable_multikey_skipscan TO false;
\o :TEST_RESULTS_UNOPTIMIZED
\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 result diff
SELECT current_setting('timescaledb.enable_multikey_skipscan') AS enable_multikey_skipscan;
-- multicolumn index with uniform order
:PREFIX SELECT DISTINCT ON(status, region, dev, dev_name) * FROM :TABLE ORDER BY status, region, dev, dev_name;
:PREFIX SELECT DISTINCT ON(region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region DESC, dev DESC;
-- index with reverse column orders (applied to non-columnstore ony)
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE ORDER BY status, region DESC, dev;
:PREFIX SELECT DISTINCT ON(region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region, dev DESC;
set enable_seqscan=0;
-- (1,N) distinct scenario
:PREFIX SELECT DISTINCT status, dev FROM :TABLE WHERE time < 100 AND region = 'reg_1' ORDER BY 1,2;
-- (N,1) distinct scenario
:PREFIX SELECT DISTINCT dev, dev_name FROM :TABLE WHERE status = 1 AND region = 'reg_1' ORDER BY 1,2;
-- SELECT list order of distinct columns shouldn't matter if OBY matches index order
:PREFIX SELECT DISTINCT dev, status, dev_name FROM :TABLE WHERE region = 'reg_2' ORDER BY status, dev, dev_name;
reset enable_seqscan;
-- Basic queries
:PREFIX SELECT DISTINCT region, dev, 'q1_3', NULL FROM :TABLE WHERE status = 1 ORDER BY region, dev;
-- DISTINCT ON queries
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, tableoid::regclass, 'q2_11' FROM :TABLE WHERE status = 1;
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_immutable(), 'q2_12' FROM :TABLE WHERE status = 1;
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_stable(), 'q2_13' FROM :TABLE WHERE status = 1;
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_volatile(), 'q2_14' FROM :TABLE WHERE status = 1;
\qecho ordered append on :TABLE
:PREFIX SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1 AND time BETWEEN 0 AND 5000;
\qecho SUBSELECTS on :TABLE
:PREFIX SELECT time, region, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (region, dev) * FROM :TABLE WHERE status = 1) a;
:PREFIX SELECT NULL, region, dev, NULL, 'q4_3' FROM (SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1) a;
\qecho ORDER BY
:PREFIX SELECT time, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region, dev) a;
-- RowCompareExpr
:PREFIX SELECT DISTINCT ON (status, dev) * FROM :TABLE WHERE region = 'reg_1' and (status, dev) > (1,2);
-- always false expr similar to our initial skip qual
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev > NULL and status > NULL and region > NULL;
-- no tuples matching
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev > 20;
-- test constants in ORDER BY
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE status = 1 ORDER BY status, region;
-- test distinct PathKey with sortref = 0 in PG15 due to FALSE filter not pushed into relation (should not crash in PG15)
:PREFIX SELECT DISTINCT sq.dev FROM (SELECT status, region, dev FROM :TABLE) sq JOIN :TABLE ref
ON (sq.dev = ref.dev) AND (sq.status = ref.status) AND (sq.region = ref.region) WHERE 1 > 2;
-- CTE
:PREFIX WITH devices AS (
	SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1
)
SELECT * FROM devices;
:PREFIX WITH devices AS (
	SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1
)
SELECT * FROM devices ORDER BY region, dev;
-- prepared statements
PREPARE prep AS SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name FROM :TABLE WHERE status = 1;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
DEALLOCATE prep;
-- ReScan tests
:PREFIX SELECT time, status, region, dev, val, 'q7_1' FROM (SELECT DISTINCT ON (status, region, dev) * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT * FROM :TABLE WHERE time != a.v) b) a;
set enable_seqscan=0;
:PREFIX SELECT time, status, region, dev, val, 'q7_2' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev != a.v) b) a;
-- RuntimeKeys
:PREFIX SELECT time, status, region, dev, val, 'q8_1' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev >= a.v) b) c;
reset enable_seqscan;
-- parallel query
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'on', false);
:PREFIX SELECT DISTINCT status, region, dev FROM :TABLE ORDER BY status, region, dev;
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'off', false);
\o
RESET timescaledb.enable_multikey_skipscan;
-- compare SkipScan results on table
:DIFF_CMD
--- Unoptimized results
+++ Optimized results
@@ -1,6 +1,6 @@
  enable_multikey_skipscan 
 --------------------------
- off
+ on
 
  time | status | region | dev | dev_name | val 
\set TABLE mskip_scan_ht
\o :TEST_RESULTS_OPTIMIZED
\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 result diff
SELECT current_setting('timescaledb.enable_multikey_skipscan') AS enable_multikey_skipscan;
-- multicolumn index with uniform order
:PREFIX SELECT DISTINCT ON(status, region, dev, dev_name) * FROM :TABLE ORDER BY status, region, dev, dev_name;
:PREFIX SELECT DISTINCT ON(region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region DESC, dev DESC;
-- index with reverse column orders (applied to non-columnstore ony)
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE ORDER BY status, region DESC, dev;
:PREFIX SELECT DISTINCT ON(region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region, dev DESC;
set enable_seqscan=0;
-- (1,N) distinct scenario
:PREFIX SELECT DISTINCT status, dev FROM :TABLE WHERE time < 100 AND region = 'reg_1' ORDER BY 1,2;
-- (N,1) distinct scenario
:PREFIX SELECT DISTINCT dev, dev_name FROM :TABLE WHERE status = 1 AND region = 'reg_1' ORDER BY 1,2;
-- SELECT list order of distinct columns shouldn't matter if OBY matches index order
:PREFIX SELECT DISTINCT dev, status, dev_name FROM :TABLE WHERE region = 'reg_2' ORDER BY status, dev, dev_name;
reset enable_seqscan;
-- Basic queries
:PREFIX SELECT DISTINCT region, dev, 'q1_3', NULL FROM :TABLE WHERE status = 1 ORDER BY region, dev;
-- DISTINCT ON queries
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, tableoid::regclass, 'q2_11' FROM :TABLE WHERE status = 1;
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_immutable(), 'q2_12' FROM :TABLE WHERE status = 1;
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_stable(), 'q2_13' FROM :TABLE WHERE status = 1;
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_volatile(), 'q2_14' FROM :TABLE WHERE status = 1;
\qecho ordered append on :TABLE
:PREFIX SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1 AND time BETWEEN 0 AND 5000;
\qecho SUBSELECTS on :TABLE
:PREFIX SELECT time, region, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (region, dev) * FROM :TABLE WHERE status = 1) a;
:PREFIX SELECT NULL, region, dev, NULL, 'q4_3' FROM (SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1) a;
\qecho ORDER BY
:PREFIX SELECT time, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region, dev) a;
-- RowCompareExpr
:PREFIX SELECT DISTINCT ON (status, dev) * FROM :TABLE WHERE region = 'reg_1' and (status, dev) > (1,2);
-- always false expr similar to our initial skip qual
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev > NULL and status > NULL and region > NULL;
-- no tuples matching
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev > 20;
-- test constants in ORDER BY
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE status = 1 ORDER BY status, region;
-- test distinct PathKey with sortref = 0 in PG15 due to FALSE filter not pushed into relation (should not crash in PG15)
:PREFIX SELECT DISTINCT sq.dev FROM (SELECT status, region, dev FROM :TABLE) sq JOIN :TABLE ref
ON (sq.dev = ref.dev) AND (sq.status = ref.status) AND (sq.region = ref.region) WHERE 1 > 2;
-- CTE
:PREFIX WITH devices AS (
	SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1
)
SELECT * FROM devices;
:PREFIX WITH devices AS (
	SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1
)
SELECT * FROM devices ORDER BY region, dev;
-- prepared statements
PREPARE prep AS SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name FROM :TABLE WHERE status = 1;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
DEALLOCATE prep;
-- ReScan tests
:PREFIX SELECT time, status, region, dev, val, 'q7_1' FROM (SELECT DISTINCT ON (status, region, dev) * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT * FROM :TABLE WHERE time != a.v) b) a;
set enable_seqscan=0;
:PREFIX SELECT time, status, region, dev, val, 'q7_2' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev != a.v) b) a;
-- RuntimeKeys
:PREFIX SELECT time, status, region, dev, val, 'q8_1' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev >= a.v) b) c;
reset enable_seqscan;
-- parallel query
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'on', false);
:PREFIX SELECT DISTINCT status, region, dev FROM :TABLE ORDER BY status, region, dev;
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'off', false);
\o
SET timescaledb.enable_multikey_skipscan TO false;
\o :TEST_RESULTS_UNOPTIMIZED
\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 result diff
SELECT current_setting('timescaledb.enable_multikey_skipscan') AS enable_multikey_skipscan;
-- multicolumn index with uniform order
:PREFIX SELECT DISTINCT ON(status, region, dev, dev_name) * FROM :TABLE ORDER BY status, region, dev, dev_name;
:PREFIX SELECT DISTINCT ON(region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region DESC, dev DESC;
-- index with reverse column orders (applied to non-columnstore ony)
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE ORDER BY status, region DESC, dev;
:PREFIX SELECT DISTINCT ON(region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region, dev DESC;
set enable_seqscan=0;
-- (1,N) distinct scenario
:PREFIX SELECT DISTINCT status, dev FROM :TABLE WHERE time < 100 AND region = 'reg_1' ORDER BY 1,2;
-- (N,1) distinct scenario
:PREFIX SELECT DISTINCT dev, dev_name FROM :TABLE WHERE status = 1 AND region = 'reg_1' ORDER BY 1,2;
-- SELECT list order of distinct columns shouldn't matter if OBY matches index order
:PREFIX SELECT DISTINCT dev, status, dev_name FROM :TABLE WHERE region = 'reg_2' ORDER BY status, dev, dev_name;
reset enable_seqscan;
-- Basic queries
:PREFIX SELECT DISTINCT region, dev, 'q1_3', NULL FROM :TABLE WHERE status = 1 ORDER BY region, dev;
-- DISTINCT ON queries
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, tableoid::regclass, 'q2_11' FROM :TABLE WHERE status = 1;
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_immutable(), 'q2_12' FROM :TABLE WHERE status = 1;
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_stable(), 'q2_13' FROM :TABLE WHERE status = 1;
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_volatile(), 'q2_14' FROM :TABLE WHERE status = 1;
\qecho ordered append on :TABLE
:PREFIX SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1 AND time BETWEEN 0 AND 5000;
\qecho SUBSELECTS on :TABLE
:PREFIX SELECT time, region, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (region, dev) * FROM :TABLE WHERE status = 1) a;
:PREFIX SELECT NULL, region, dev, NULL, 'q4_3' FROM (SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1) a;
\qecho ORDER BY
:PREFIX SELECT time, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region, dev) a;
-- RowCompareExpr
:PREFIX SELECT DISTINCT ON (status, dev) * FROM :TABLE WHERE region = 'reg_1' and (status, dev) > (1,2);
-- always false expr similar to our initial skip qual
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev > NULL and status > NULL and region > NULL;
-- no tuples matching
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev > 20;
-- test constants in ORDER BY
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE status = 1 ORDER BY status, region;
-- test distinct PathKey with sortref = 0 in PG15 due to FALSE filter not pushed into relation (should not crash in PG15)
:PREFIX SELECT DISTINCT sq.dev FROM (SELECT status, region, dev FROM :TABLE) sq JOIN :TABLE ref
ON (sq.dev = ref.dev) AND (sq.status = ref.status) AND (sq.region = ref.region) WHERE 1 > 2;
-- CTE
:PREFIX WITH devices AS (
	SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1
)
SELECT * FROM devices;
:PREFIX WITH devices AS (
	SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1
)
SELECT * FROM devices ORDER BY region, dev;
-- prepared statements
PREPARE prep AS SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name FROM :TABLE WHERE status = 1;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
DEALLOCATE prep;
-- ReScan tests
:PREFIX SELECT time, status, region, dev, val, 'q7_1' FROM (SELECT DISTINCT ON (status, region, dev) * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT * FROM :TABLE WHERE time != a.v) b) a;
set enable_seqscan=0;
:PREFIX SELECT time, status, region, dev, val, 'q7_2' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev != a.v) b) a;
-- RuntimeKeys
:PREFIX SELECT time, status, region, dev, val, 'q8_1' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev >= a.v) b) c;
reset enable_seqscan;
-- parallel query
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'on', false);
:PREFIX SELECT DISTINCT status, region, dev FROM :TABLE ORDER BY status, region, dev;
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'off', false);
\o
RESET timescaledb.enable_multikey_skipscan;
-- compare SkipScan results on hypertable
:DIFF_CMD
--- Unoptimized results
+++ Optimized results
@@ -1,6 +1,6 @@
  enable_multikey_skipscan 
 --------------------------
- off
+ on
 
  time | status | region | dev | dev_name |  val  
\set TABLE mskip_scan_htc
\o :TEST_RESULTS_OPTIMIZED
\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 result diff
SELECT current_setting('timescaledb.enable_multikey_skipscan') AS enable_multikey_skipscan;
-- multicolumn index with uniform order
:PREFIX SELECT DISTINCT ON(status, region, dev, dev_name) * FROM :TABLE ORDER BY status, region, dev, dev_name;
:PREFIX SELECT DISTINCT ON(region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region DESC, dev DESC;
-- index with reverse column orders (applied to non-columnstore ony)
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE ORDER BY status, region DESC, dev;
:PREFIX SELECT DISTINCT ON(region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region, dev DESC;
set enable_seqscan=0;
-- (1,N) distinct scenario
:PREFIX SELECT DISTINCT status, dev FROM :TABLE WHERE time < 100 AND region = 'reg_1' ORDER BY 1,2;
-- (N,1) distinct scenario
:PREFIX SELECT DISTINCT dev, dev_name FROM :TABLE WHERE status = 1 AND region = 'reg_1' ORDER BY 1,2;
-- SELECT list order of distinct columns shouldn't matter if OBY matches index order
:PREFIX SELECT DISTINCT dev, status, dev_name FROM :TABLE WHERE region = 'reg_2' ORDER BY status, dev, dev_name;
reset enable_seqscan;
-- Basic queries
:PREFIX SELECT DISTINCT region, dev, 'q1_3', NULL FROM :TABLE WHERE status = 1 ORDER BY region, dev;
-- DISTINCT ON queries
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, tableoid::regclass, 'q2_11' FROM :TABLE WHERE status = 1;
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_immutable(), 'q2_12' FROM :TABLE WHERE status = 1;
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_stable(), 'q2_13' FROM :TABLE WHERE status = 1;
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_volatile(), 'q2_14' FROM :TABLE WHERE status = 1;
\qecho ordered append on :TABLE
:PREFIX SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1 AND time BETWEEN 0 AND 5000;
\qecho SUBSELECTS on :TABLE
:PREFIX SELECT time, region, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (region, dev) * FROM :TABLE WHERE status = 1) a;
:PREFIX SELECT NULL, region, dev, NULL, 'q4_3' FROM (SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1) a;
\qecho ORDER BY
:PREFIX SELECT time, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region, dev) a;
-- RowCompareExpr
:PREFIX SELECT DISTINCT ON (status, dev) * FROM :TABLE WHERE region = 'reg_1' and (status, dev) > (1,2);
-- always false expr similar to our initial skip qual
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev > NULL and status > NULL and region > NULL;
-- no tuples matching
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev > 20;
-- test constants in ORDER BY
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE status = 1 ORDER BY status, region;
-- test distinct PathKey with sortref = 0 in PG15 due to FALSE filter not pushed into relation (should not crash in PG15)
:PREFIX SELECT DISTINCT sq.dev FROM (SELECT status, region, dev FROM :TABLE) sq JOIN :TABLE ref
ON (sq.dev = ref.dev) AND (sq.status = ref.status) AND (sq.region = ref.region) WHERE 1 > 2;
-- CTE
:PREFIX WITH devices AS (
	SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1
)
SELECT * FROM devices;
:PREFIX WITH devices AS (
	SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1
)
SELECT * FROM devices ORDER BY region, dev;
-- prepared statements
PREPARE prep AS SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name FROM :TABLE WHERE status = 1;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
DEALLOCATE prep;
-- ReScan tests
:PREFIX SELECT time, status, region, dev, val, 'q7_1' FROM (SELECT DISTINCT ON (status, region, dev) * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT * FROM :TABLE WHERE time != a.v) b) a;
set enable_seqscan=0;
:PREFIX SELECT time, status, region, dev, val, 'q7_2' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev != a.v) b) a;
-- RuntimeKeys
:PREFIX SELECT time, status, region, dev, val, 'q8_1' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev >= a.v) b) c;
reset enable_seqscan;
-- parallel query
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'on', false);
:PREFIX SELECT DISTINCT status, region, dev FROM :TABLE ORDER BY status, region, dev;
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'off', false);
\o
SET timescaledb.enable_multikey_skipscan TO false;
\o :TEST_RESULTS_UNOPTIMIZED
\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 result diff
SELECT current_setting('timescaledb.enable_multikey_skipscan') AS enable_multikey_skipscan;
-- multicolumn index with uniform order
:PREFIX SELECT DISTINCT ON(status, region, dev, dev_name) * FROM :TABLE ORDER BY status, region, dev, dev_name;
:PREFIX SELECT DISTINCT ON(region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region DESC, dev DESC;
-- index with reverse column orders (applied to non-columnstore ony)
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE ORDER BY status, region DESC, dev;
:PREFIX SELECT DISTINCT ON(region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region, dev DESC;
set enable_seqscan=0;
-- (1,N) distinct scenario
:PREFIX SELECT DISTINCT status, dev FROM :TABLE WHERE time < 100 AND region = 'reg_1' ORDER BY 1,2;
-- (N,1) distinct scenario
:PREFIX SELECT DISTINCT dev, dev_name FROM :TABLE WHERE status = 1 AND region = 'reg_1' ORDER BY 1,2;
-- SELECT list order of distinct columns shouldn't matter if OBY matches index order
:PREFIX SELECT DISTINCT dev, status, dev_name FROM :TABLE WHERE region = 'reg_2' ORDER BY status, dev, dev_name;
reset enable_seqscan;
-- Basic queries
:PREFIX SELECT DISTINCT region, dev, 'q1_3', NULL FROM :TABLE WHERE status = 1 ORDER BY region, dev;
-- DISTINCT ON queries
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, tableoid::regclass, 'q2_11' FROM :TABLE WHERE status = 1;
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_immutable(), 'q2_12' FROM :TABLE WHERE status = 1;
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_stable(), 'q2_13' FROM :TABLE WHERE status = 1;
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_volatile(), 'q2_14' FROM :TABLE WHERE status = 1;
\qecho ordered append on :TABLE
:PREFIX SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1 AND time BETWEEN 0 AND 5000;
\qecho SUBSELECTS on :TABLE
:PREFIX SELECT time, region, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (region, dev) * FROM :TABLE WHERE status = 1) a;
:PREFIX SELECT NULL, region, dev, NULL, 'q4_3' FROM (SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1) a;
\qecho ORDER BY
:PREFIX SELECT time, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region, dev) a;
-- RowCompareExpr
:PREFIX SELECT DISTINCT ON (status, dev) * FROM :TABLE WHERE region = 'reg_1' and (status, dev) > (1,2);
-- always false expr similar to our initial skip qual
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev > NULL and status > NULL and region > NULL;
-- no tuples matching
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev > 20;
-- test constants in ORDER BY
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE status = 1 ORDER BY status, region;
-- test distinct PathKey with sortref = 0 in PG15 due to FALSE filter not pushed into relation (should not crash in PG15)
:PREFIX SELECT DISTINCT sq.dev FROM (SELECT status, region, dev FROM :TABLE) sq JOIN :TABLE ref
ON (sq.dev = ref.dev) AND (sq.status = ref.status) AND (sq.region = ref.region) WHERE 1 > 2;
-- CTE
:PREFIX WITH devices AS (
	SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1
)
SELECT * FROM devices;
:PREFIX WITH devices AS (
	SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1
)
SELECT * FROM devices ORDER BY region, dev;
-- prepared statements
PREPARE prep AS SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name FROM :TABLE WHERE status = 1;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
DEALLOCATE prep;
-- ReScan tests
:PREFIX SELECT time, status, region, dev, val, 'q7_1' FROM (SELECT DISTINCT ON (status, region, dev) * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT * FROM :TABLE WHERE time != a.v) b) a;
set enable_seqscan=0;
:PREFIX SELECT time, status, region, dev, val, 'q7_2' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev != a.v) b) a;
-- RuntimeKeys
:PREFIX SELECT time, status, region, dev, val, 'q8_1' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev >= a.v) b) c;
reset enable_seqscan;
-- parallel query
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'on', false);
:PREFIX SELECT DISTINCT status, region, dev FROM :TABLE ORDER BY status, region, dev;
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'off', false);
\o
RESET timescaledb.enable_multikey_skipscan;
-- compare SkipScan results on compressed hypertable
:DIFF_CMD
--- Unoptimized results
+++ Optimized results
@@ -1,6 +1,6 @@
  enable_multikey_skipscan 
 --------------------------
- off
+ on
 
  time | status | region | dev | dev_name |  val  
-- make sure multikey SkipScan is applied correctly
SET timescaledb.debug_skip_scan_info TO true;
\set TABLE mskip_scan
\o :TEST_RESULTS_OPTIMIZED
\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 result diff
SELECT current_setting('timescaledb.enable_multikey_skipscan') AS enable_multikey_skipscan;
-- multicolumn index with uniform order
:PREFIX SELECT DISTINCT ON(status, region, dev, dev_name) * FROM :TABLE ORDER BY status, region, dev, dev_name;
psql:include/skip_scan_multi_query.sql:9: INFO:  SkipScan used on mskip_scan_status_region_dev_dev_name_idx(status NOT NULL, region NOT NULL, dev NOT NULL, dev_name NOT NULL)
:PREFIX SELECT DISTINCT ON(region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region DESC, dev DESC;
psql:include/skip_scan_multi_query.sql:10: INFO:  SkipScan used on mskip_scan_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
-- index with reverse column orders (applied to non-columnstore ony)
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE ORDER BY status, region DESC, dev;
psql:include/skip_scan_multi_query.sql:13: INFO:  SkipScan used on mskip_scan_status_region_dev_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
:PREFIX SELECT DISTINCT ON(region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region, dev DESC;
psql:include/skip_scan_multi_query.sql:14: INFO:  SkipScan used on mskip_scan_status_region_dev_idx(region NOT NULL, dev NOT NULL)
set enable_seqscan=0;
-- (1,N) distinct scenario
:PREFIX SELECT DISTINCT status, dev FROM :TABLE WHERE time < 100 AND region = 'reg_1' ORDER BY 1,2;
psql:include/skip_scan_multi_query.sql:18: INFO:  SkipScan used on mskip_scan_status_region_dev_idx(status NOT NULL, dev NOT NULL)
-- (N,1) distinct scenario
:PREFIX SELECT DISTINCT dev, dev_name FROM :TABLE WHERE status = 1 AND region = 'reg_1' ORDER BY 1,2;
psql:include/skip_scan_multi_query.sql:21: INFO:  SkipScan used on mskip_scan_status_region_dev_dev_name_idx(dev NOT NULL, dev_name NOT NULL)
-- SELECT list order of distinct columns shouldn't matter if OBY matches index order
:PREFIX SELECT DISTINCT dev, status, dev_name FROM :TABLE WHERE region = 'reg_2' ORDER BY status, dev, dev_name;
psql:include/skip_scan_multi_query.sql:24: INFO:  SkipScan used on mskip_scan_status_region_dev_dev_name_idx(status NOT NULL, dev NOT NULL, dev_name NOT NULL)
reset enable_seqscan;
-- Basic queries
:PREFIX SELECT DISTINCT region, dev, 'q1_3', NULL FROM :TABLE WHERE status = 1 ORDER BY region, dev;
psql:include/skip_scan_multi_query.sql:28: INFO:  SkipScan used on mskip_scan_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
-- DISTINCT ON queries
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, tableoid::regclass, 'q2_11' FROM :TABLE WHERE status = 1;
psql:include/skip_scan_multi_query.sql:31: INFO:  SkipScan used on mskip_scan_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_immutable(), 'q2_12' FROM :TABLE WHERE status = 1;
psql:include/skip_scan_multi_query.sql:32: INFO:  SkipScan used on mskip_scan_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_stable(), 'q2_13' FROM :TABLE WHERE status = 1;
psql:include/skip_scan_multi_query.sql:33: INFO:  SkipScan used on mskip_scan_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_volatile(), 'q2_14' FROM :TABLE WHERE status = 1;
psql:include/skip_scan_multi_query.sql:34: INFO:  SkipScan used on mskip_scan_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
\qecho ordered append on :TABLE
:PREFIX SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1 AND time BETWEEN 0 AND 5000;
psql:include/skip_scan_multi_query.sql:37: INFO:  SkipScan used on mskip_scan_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
\qecho SUBSELECTS on :TABLE
:PREFIX SELECT time, region, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (region, dev) * FROM :TABLE WHERE status = 1) a;
psql:include/skip_scan_multi_query.sql:40: INFO:  SkipScan used on mskip_scan_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
:PREFIX SELECT NULL, region, dev, NULL, 'q4_3' FROM (SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1) a;
psql:include/skip_scan_multi_query.sql:41: INFO:  SkipScan used on mskip_scan_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
\qecho ORDER BY
:PREFIX SELECT time, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region, dev) a;
psql:include/skip_scan_multi_query.sql:44: INFO:  SkipScan used on mskip_scan_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
-- RowCompareExpr
:PREFIX SELECT DISTINCT ON (status, dev) * FROM :TABLE WHERE region = 'reg_1' and (status, dev) > (1,2);
psql:include/skip_scan_multi_query.sql:47: INFO:  SkipScan used on mskip_scan_status_region_dev_idx(status NOT NULL, dev NOT NULL)
-- always false expr similar to our initial skip qual
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev > NULL and status > NULL and region > NULL;
-- no tuples matching
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev > 20;
psql:include/skip_scan_multi_query.sql:51: INFO:  SkipScan used on mskip_scan_status_region_dev_dev_name_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
-- test constants in ORDER BY
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE status = 1 ORDER BY status, region;
psql:include/skip_scan_multi_query.sql:54: INFO:  SkipScan used on mskip_scan_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
-- test distinct PathKey with sortref = 0 in PG15 due to FALSE filter not pushed into relation (should not crash in PG15)
:PREFIX SELECT DISTINCT sq.dev FROM (SELECT status, region, dev FROM :TABLE) sq JOIN :TABLE ref
ON (sq.dev = ref.dev) AND (sq.status = ref.status) AND (sq.region = ref.region) WHERE 1 > 2;
-- CTE
:PREFIX WITH devices AS (
	SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1
)
SELECT * FROM devices;
psql:include/skip_scan_multi_query.sql:64: INFO:  SkipScan used on mskip_scan_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
:PREFIX WITH devices AS (
	SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1
)
SELECT * FROM devices ORDER BY region, dev;
psql:include/skip_scan_multi_query.sql:69: INFO:  SkipScan used on mskip_scan_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
-- prepared statements
PREPARE prep AS SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name FROM :TABLE WHERE status = 1;
:PREFIX EXECUTE prep;
psql:include/skip_scan_multi_query.sql:73: INFO:  SkipScan used on mskip_scan_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
DEALLOCATE prep;
-- ReScan tests
:PREFIX SELECT time, status, region, dev, val, 'q7_1' FROM (SELECT DISTINCT ON (status, region, dev) * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT * FROM :TABLE WHERE time != a.v) b) a;
set enable_seqscan=0;
:PREFIX SELECT time, status, region, dev, val, 'q7_2' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev != a.v) b) a;
psql:include/skip_scan_multi_query.sql:86: INFO:  SkipScan used on mskip_scan_status_region_dev_dev_name_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
-- RuntimeKeys
:PREFIX SELECT time, status, region, dev, val, 'q8_1' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev >= a.v) b) c;
psql:include/skip_scan_multi_query.sql:91: INFO:  SkipScan used on mskip_scan_status_region_dev_dev_name_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
reset enable_seqscan;
-- parallel query
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'on', false);
:PREFIX SELECT DISTINCT status, region, dev FROM :TABLE ORDER BY status, region, dev;
psql:include/skip_scan_multi_query.sql:96: INFO:  SkipScan used on mskip_scan_status_region_dev_dev_name_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'off', false);
\o
\set TABLE mskip_scan_ht
\o :TEST_RESULTS_OPTIMIZED
\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 result diff
SELECT current_setting('timescaledb.enable_multikey_skipscan') AS enable_multikey_skipscan;
-- multicolumn index with uniform order
:PREFIX SELECT DISTINCT ON(status, region, dev, dev_name) * FROM :TABLE ORDER BY status, region, dev, dev_name;
psql:include/skip_scan_multi_query.sql:9: INFO:  SkipScan used on _hyper_7_65_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(status NOT NULL, region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:9: INFO:  SkipScan used on _hyper_7_66_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(status NOT NULL, region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:9: INFO:  SkipScan used on _hyper_7_67_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(status NOT NULL, region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:9: INFO:  SkipScan used on _hyper_7_68_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(status NOT NULL, region NOT NULL, dev NOT NULL, dev_name NOT NULL)
:PREFIX SELECT DISTINCT ON(region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region DESC, dev DESC;
psql:include/skip_scan_multi_query.sql:10: INFO:  SkipScan used on _hyper_7_65_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:10: INFO:  SkipScan used on _hyper_7_66_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:10: INFO:  SkipScan used on _hyper_7_67_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:10: INFO:  SkipScan used on _hyper_7_68_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
-- index with reverse column orders (applied to non-columnstore ony)
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE ORDER BY status, region DESC, dev;
psql:include/skip_scan_multi_query.sql:13: INFO:  SkipScan used on _hyper_7_65_chunk_mskip_scan_ht_status_region_dev_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:13: INFO:  SkipScan used on _hyper_7_66_chunk_mskip_scan_ht_status_region_dev_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:13: INFO:  SkipScan used on _hyper_7_67_chunk_mskip_scan_ht_status_region_dev_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:13: INFO:  SkipScan used on _hyper_7_68_chunk_mskip_scan_ht_status_region_dev_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
:PREFIX SELECT DISTINCT ON(region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region, dev DESC;
psql:include/skip_scan_multi_query.sql:14: INFO:  SkipScan used on _hyper_7_65_chunk_mskip_scan_ht_status_region_dev_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:14: INFO:  SkipScan used on _hyper_7_66_chunk_mskip_scan_ht_status_region_dev_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:14: INFO:  SkipScan used on _hyper_7_67_chunk_mskip_scan_ht_status_region_dev_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:14: INFO:  SkipScan used on _hyper_7_68_chunk_mskip_scan_ht_status_region_dev_idx(region NOT NULL, dev NOT NULL)
set enable_seqscan=0;
-- (1,N) distinct scenario
:PREFIX SELECT DISTINCT status, dev FROM :TABLE WHERE time < 100 AND region = 'reg_1' ORDER BY 1,2;
psql:include/skip_scan_multi_query.sql:18: INFO:  SkipScan used on _hyper_7_65_chunk_mskip_scan_ht_status_region_dev_idx(status NOT NULL, dev NOT NULL)
-- (N,1) distinct scenario
:PREFIX SELECT DISTINCT dev, dev_name FROM :TABLE WHERE status = 1 AND region = 'reg_1' ORDER BY 1,2;
psql:include/skip_scan_multi_query.sql:21: INFO:  SkipScan used on _hyper_7_65_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:21: INFO:  SkipScan used on _hyper_7_66_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:21: INFO:  SkipScan used on _hyper_7_67_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:21: INFO:  SkipScan used on _hyper_7_68_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(dev NOT NULL, dev_name NOT NULL)
-- SELECT list order of distinct columns shouldn't matter if OBY matches index order
:PREFIX SELECT DISTINCT dev, status, dev_name FROM :TABLE WHERE region = 'reg_2' ORDER BY status, dev, dev_name;
psql:include/skip_scan_multi_query.sql:24: INFO:  SkipScan used on _hyper_7_65_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(status NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:24: INFO:  SkipScan used on _hyper_7_66_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(status NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:24: INFO:  SkipScan used on _hyper_7_67_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(status NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:24: INFO:  SkipScan used on _hyper_7_68_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(status NOT NULL, dev NOT NULL, dev_name NOT NULL)
reset enable_seqscan;
-- Basic queries
:PREFIX SELECT DISTINCT region, dev, 'q1_3', NULL FROM :TABLE WHERE status = 1 ORDER BY region, dev;
psql:include/skip_scan_multi_query.sql:28: INFO:  SkipScan used on _hyper_7_65_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:28: INFO:  SkipScan used on _hyper_7_66_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:28: INFO:  SkipScan used on _hyper_7_67_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:28: INFO:  SkipScan used on _hyper_7_68_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
-- DISTINCT ON queries
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, tableoid::regclass, 'q2_11' FROM :TABLE WHERE status = 1;
psql:include/skip_scan_multi_query.sql:31: INFO:  SkipScan used on _hyper_7_65_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:31: INFO:  SkipScan used on _hyper_7_66_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:31: INFO:  SkipScan used on _hyper_7_67_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:31: INFO:  SkipScan used on _hyper_7_68_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_immutable(), 'q2_12' FROM :TABLE WHERE status = 1;
psql:include/skip_scan_multi_query.sql:32: INFO:  SkipScan used on _hyper_7_65_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:32: INFO:  SkipScan used on _hyper_7_66_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:32: INFO:  SkipScan used on _hyper_7_67_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:32: INFO:  SkipScan used on _hyper_7_68_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_stable(), 'q2_13' FROM :TABLE WHERE status = 1;
psql:include/skip_scan_multi_query.sql:33: INFO:  SkipScan used on _hyper_7_65_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:33: INFO:  SkipScan used on _hyper_7_66_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:33: INFO:  SkipScan used on _hyper_7_67_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:33: INFO:  SkipScan used on _hyper_7_68_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_volatile(), 'q2_14' FROM :TABLE WHERE status = 1;
psql:include/skip_scan_multi_query.sql:34: INFO:  SkipScan used on _hyper_7_65_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:34: INFO:  SkipScan used on _hyper_7_66_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:34: INFO:  SkipScan used on _hyper_7_67_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:34: INFO:  SkipScan used on _hyper_7_68_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
\qecho ordered append on :TABLE
:PREFIX SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1 AND time BETWEEN 0 AND 5000;
psql:include/skip_scan_multi_query.sql:37: INFO:  SkipScan used on _hyper_7_65_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:37: INFO:  SkipScan used on _hyper_7_66_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:37: INFO:  SkipScan used on _hyper_7_67_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:37: INFO:  SkipScan used on _hyper_7_68_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
\qecho SUBSELECTS on :TABLE
:PREFIX SELECT time, region, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (region, dev) * FROM :TABLE WHERE status = 1) a;
psql:include/skip_scan_multi_query.sql:40: INFO:  SkipScan used on _hyper_7_65_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:40: INFO:  SkipScan used on _hyper_7_66_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:40: INFO:  SkipScan used on _hyper_7_67_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:40: INFO:  SkipScan used on _hyper_7_68_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
:PREFIX SELECT NULL, region, dev, NULL, 'q4_3' FROM (SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1) a;
psql:include/skip_scan_multi_query.sql:41: INFO:  SkipScan used on _hyper_7_65_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:41: INFO:  SkipScan used on _hyper_7_66_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:41: INFO:  SkipScan used on _hyper_7_67_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:41: INFO:  SkipScan used on _hyper_7_68_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
\qecho ORDER BY
:PREFIX SELECT time, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region, dev) a;
psql:include/skip_scan_multi_query.sql:44: INFO:  SkipScan used on _hyper_7_65_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:44: INFO:  SkipScan used on _hyper_7_66_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:44: INFO:  SkipScan used on _hyper_7_67_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:44: INFO:  SkipScan used on _hyper_7_68_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
-- RowCompareExpr
:PREFIX SELECT DISTINCT ON (status, dev) * FROM :TABLE WHERE region = 'reg_1' and (status, dev) > (1,2);
psql:include/skip_scan_multi_query.sql:47: INFO:  SkipScan used on _hyper_7_65_chunk_mskip_scan_ht_status_region_dev_idx(status NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:47: INFO:  SkipScan used on _hyper_7_66_chunk_mskip_scan_ht_status_region_dev_idx(status NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:47: INFO:  SkipScan used on _hyper_7_67_chunk_mskip_scan_ht_status_region_dev_idx(status NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:47: INFO:  SkipScan used on _hyper_7_68_chunk_mskip_scan_ht_status_region_dev_idx(status NOT NULL, dev NOT NULL)
-- always false expr similar to our initial skip qual
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev > NULL and status > NULL and region > NULL;
-- no tuples matching
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev > 20;
psql:include/skip_scan_multi_query.sql:51: INFO:  SkipScan used on _hyper_7_65_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:51: INFO:  SkipScan used on _hyper_7_66_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:51: INFO:  SkipScan used on _hyper_7_67_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:51: INFO:  SkipScan used on _hyper_7_68_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
-- test constants in ORDER BY
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE status = 1 ORDER BY status, region;
psql:include/skip_scan_multi_query.sql:54: INFO:  SkipScan used on _hyper_7_65_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:54: INFO:  SkipScan used on _hyper_7_66_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:54: INFO:  SkipScan used on _hyper_7_67_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:54: INFO:  SkipScan used on _hyper_7_68_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
-- test distinct PathKey with sortref = 0 in PG15 due to FALSE filter not pushed into relation (should not crash in PG15)
:PREFIX SELECT DISTINCT sq.dev FROM (SELECT status, region, dev FROM :TABLE) sq JOIN :TABLE ref
ON (sq.dev = ref.dev) AND (sq.status = ref.status) AND (sq.region = ref.region) WHERE 1 > 2;
-- CTE
:PREFIX WITH devices AS (
	SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1
)
SELECT * FROM devices;
psql:include/skip_scan_multi_query.sql:64: INFO:  SkipScan used on _hyper_7_65_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:64: INFO:  SkipScan used on _hyper_7_66_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:64: INFO:  SkipScan used on _hyper_7_67_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:64: INFO:  SkipScan used on _hyper_7_68_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
:PREFIX WITH devices AS (
	SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1
)
SELECT * FROM devices ORDER BY region, dev;
psql:include/skip_scan_multi_query.sql:69: INFO:  SkipScan used on _hyper_7_65_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:69: INFO:  SkipScan used on _hyper_7_66_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:69: INFO:  SkipScan used on _hyper_7_67_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:69: INFO:  SkipScan used on _hyper_7_68_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL)
-- prepared statements
PREPARE prep AS SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name FROM :TABLE WHERE status = 1;
:PREFIX EXECUTE prep;
psql:include/skip_scan_multi_query.sql:73: INFO:  SkipScan used on _hyper_7_65_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:73: INFO:  SkipScan used on _hyper_7_66_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:73: INFO:  SkipScan used on _hyper_7_67_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:73: INFO:  SkipScan used on _hyper_7_68_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
DEALLOCATE prep;
-- ReScan tests
:PREFIX SELECT time, status, region, dev, val, 'q7_1' FROM (SELECT DISTINCT ON (status, region, dev) * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT * FROM :TABLE WHERE time != a.v) b) a;
set enable_seqscan=0;
:PREFIX SELECT time, status, region, dev, val, 'q7_2' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev != a.v) b) a;
psql:include/skip_scan_multi_query.sql:86: INFO:  SkipScan used on _hyper_7_65_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:86: INFO:  SkipScan used on _hyper_7_66_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:86: INFO:  SkipScan used on _hyper_7_67_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:86: INFO:  SkipScan used on _hyper_7_68_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
-- RuntimeKeys
:PREFIX SELECT time, status, region, dev, val, 'q8_1' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev >= a.v) b) c;
psql:include/skip_scan_multi_query.sql:91: INFO:  SkipScan used on _hyper_7_65_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:91: INFO:  SkipScan used on _hyper_7_66_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:91: INFO:  SkipScan used on _hyper_7_67_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:91: INFO:  SkipScan used on _hyper_7_68_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
reset enable_seqscan;
-- parallel query
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'on', false);
:PREFIX SELECT DISTINCT status, region, dev FROM :TABLE ORDER BY status, region, dev;
psql:include/skip_scan_multi_query.sql:96: INFO:  SkipScan used on _hyper_7_65_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:96: INFO:  SkipScan used on _hyper_7_66_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:96: INFO:  SkipScan used on _hyper_7_67_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:96: INFO:  SkipScan used on _hyper_7_68_chunk_mskip_scan_ht_status_region_dev_dev_name_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'off', false);
\o
\set TABLE mskip_scan_htc
\o :TEST_RESULTS_OPTIMIZED
\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 result diff
SELECT current_setting('timescaledb.enable_multikey_skipscan') AS enable_multikey_skipscan;
-- multicolumn index with uniform order
:PREFIX SELECT DISTINCT ON(status, region, dev, dev_name) * FROM :TABLE ORDER BY status, region, dev, dev_name;
psql:include/skip_scan_multi_query.sql:9: INFO:  SkipScan used on compress_hyper_9_73_chunk_status_region_dev_dev_name__ts_me_idx(status NOT NULL, region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:9: INFO:  SkipScan used on compress_hyper_9_74_chunk_status_region_dev_dev_name__ts_me_idx(status NOT NULL, region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:9: INFO:  SkipScan used on compress_hyper_9_75_chunk_status_region_dev_dev_name__ts_me_idx(status NOT NULL, region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:9: INFO:  SkipScan used on compress_hyper_9_76_chunk_status_region_dev_dev_name__ts_me_idx(status NOT NULL, region NOT NULL, dev NOT NULL, dev_name NOT NULL)
:PREFIX SELECT DISTINCT ON(region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region DESC, dev DESC;
psql:include/skip_scan_multi_query.sql:10: INFO:  SkipScan used on compress_hyper_9_73_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:10: INFO:  SkipScan used on compress_hyper_9_74_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:10: INFO:  SkipScan used on compress_hyper_9_75_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:10: INFO:  SkipScan used on compress_hyper_9_76_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
-- index with reverse column orders (applied to non-columnstore ony)
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE ORDER BY status, region DESC, dev;
:PREFIX SELECT DISTINCT ON(region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region, dev DESC;
set enable_seqscan=0;
-- (1,N) distinct scenario
:PREFIX SELECT DISTINCT status, dev FROM :TABLE WHERE time < 100 AND region = 'reg_1' ORDER BY 1,2;
psql:include/skip_scan_multi_query.sql:18: INFO:  SkipScan used on compress_hyper_9_73_chunk_status_region_dev_dev_name__ts_me_idx(status NOT NULL, dev NOT NULL)
-- (N,1) distinct scenario
:PREFIX SELECT DISTINCT dev, dev_name FROM :TABLE WHERE status = 1 AND region = 'reg_1' ORDER BY 1,2;
psql:include/skip_scan_multi_query.sql:21: INFO:  SkipScan used on compress_hyper_9_73_chunk_status_region_dev_dev_name__ts_me_idx(dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:21: INFO:  SkipScan used on compress_hyper_9_74_chunk_status_region_dev_dev_name__ts_me_idx(dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:21: INFO:  SkipScan used on compress_hyper_9_75_chunk_status_region_dev_dev_name__ts_me_idx(dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:21: INFO:  SkipScan used on compress_hyper_9_76_chunk_status_region_dev_dev_name__ts_me_idx(dev NOT NULL, dev_name NOT NULL)
-- SELECT list order of distinct columns shouldn't matter if OBY matches index order
:PREFIX SELECT DISTINCT dev, status, dev_name FROM :TABLE WHERE region = 'reg_2' ORDER BY status, dev, dev_name;
psql:include/skip_scan_multi_query.sql:24: INFO:  SkipScan used on compress_hyper_9_73_chunk_status_region_dev_dev_name__ts_me_idx(status NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:24: INFO:  SkipScan used on compress_hyper_9_74_chunk_status_region_dev_dev_name__ts_me_idx(status NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:24: INFO:  SkipScan used on compress_hyper_9_75_chunk_status_region_dev_dev_name__ts_me_idx(status NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:24: INFO:  SkipScan used on compress_hyper_9_76_chunk_status_region_dev_dev_name__ts_me_idx(status NOT NULL, dev NOT NULL, dev_name NOT NULL)
reset enable_seqscan;
-- Basic queries
:PREFIX SELECT DISTINCT region, dev, 'q1_3', NULL FROM :TABLE WHERE status = 1 ORDER BY region, dev;
psql:include/skip_scan_multi_query.sql:28: INFO:  SkipScan used on compress_hyper_9_73_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:28: INFO:  SkipScan used on compress_hyper_9_74_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:28: INFO:  SkipScan used on compress_hyper_9_75_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:28: INFO:  SkipScan used on compress_hyper_9_76_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
-- DISTINCT ON queries
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, tableoid::regclass, 'q2_11' FROM :TABLE WHERE status = 1;
psql:include/skip_scan_multi_query.sql:31: INFO:  SkipScan used on compress_hyper_9_73_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:31: INFO:  SkipScan used on compress_hyper_9_74_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:31: INFO:  SkipScan used on compress_hyper_9_75_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:31: INFO:  SkipScan used on compress_hyper_9_76_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_immutable(), 'q2_12' FROM :TABLE WHERE status = 1;
psql:include/skip_scan_multi_query.sql:32: INFO:  SkipScan used on compress_hyper_9_73_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:32: INFO:  SkipScan used on compress_hyper_9_74_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:32: INFO:  SkipScan used on compress_hyper_9_75_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:32: INFO:  SkipScan used on compress_hyper_9_76_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_stable(), 'q2_13' FROM :TABLE WHERE status = 1;
psql:include/skip_scan_multi_query.sql:33: INFO:  SkipScan used on compress_hyper_9_73_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:33: INFO:  SkipScan used on compress_hyper_9_74_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:33: INFO:  SkipScan used on compress_hyper_9_75_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:33: INFO:  SkipScan used on compress_hyper_9_76_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
:PREFIX SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name, int_func_volatile(), 'q2_14' FROM :TABLE WHERE status = 1;
psql:include/skip_scan_multi_query.sql:34: INFO:  SkipScan used on compress_hyper_9_73_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:34: INFO:  SkipScan used on compress_hyper_9_74_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:34: INFO:  SkipScan used on compress_hyper_9_75_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:34: INFO:  SkipScan used on compress_hyper_9_76_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
\qecho ordered append on :TABLE
:PREFIX SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1 AND time BETWEEN 0 AND 5000;
psql:include/skip_scan_multi_query.sql:37: INFO:  SkipScan used on compress_hyper_9_73_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:37: INFO:  SkipScan used on compress_hyper_9_74_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:37: INFO:  SkipScan used on compress_hyper_9_75_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:37: INFO:  SkipScan used on compress_hyper_9_76_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
\qecho SUBSELECTS on :TABLE
:PREFIX SELECT time, region, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (region, dev) * FROM :TABLE WHERE status = 1) a;
psql:include/skip_scan_multi_query.sql:40: INFO:  SkipScan used on compress_hyper_9_73_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:40: INFO:  SkipScan used on compress_hyper_9_74_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:40: INFO:  SkipScan used on compress_hyper_9_75_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:40: INFO:  SkipScan used on compress_hyper_9_76_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
:PREFIX SELECT NULL, region, dev, NULL, 'q4_3' FROM (SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1) a;
psql:include/skip_scan_multi_query.sql:41: INFO:  SkipScan used on compress_hyper_9_73_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:41: INFO:  SkipScan used on compress_hyper_9_74_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:41: INFO:  SkipScan used on compress_hyper_9_75_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:41: INFO:  SkipScan used on compress_hyper_9_76_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
\qecho ORDER BY
:PREFIX SELECT time, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (region, dev) * FROM :TABLE WHERE status = 1 ORDER BY region, dev) a;
psql:include/skip_scan_multi_query.sql:44: INFO:  SkipScan used on compress_hyper_9_73_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:44: INFO:  SkipScan used on compress_hyper_9_74_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:44: INFO:  SkipScan used on compress_hyper_9_75_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:44: INFO:  SkipScan used on compress_hyper_9_76_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
-- RowCompareExpr
:PREFIX SELECT DISTINCT ON (status, dev) * FROM :TABLE WHERE region = 'reg_1' and (status, dev) > (1,2);
psql:include/skip_scan_multi_query.sql:47: INFO:  SkipScan used on compress_hyper_9_73_chunk_status_region_dev_dev_name__ts_me_idx(status NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:47: INFO:  SkipScan used on compress_hyper_9_74_chunk_status_region_dev_dev_name__ts_me_idx(status NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:47: INFO:  SkipScan used on compress_hyper_9_75_chunk_status_region_dev_dev_name__ts_me_idx(status NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:47: INFO:  SkipScan used on compress_hyper_9_76_chunk_status_region_dev_dev_name__ts_me_idx(status NOT NULL, dev NOT NULL)
-- always false expr similar to our initial skip qual
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev > NULL and status > NULL and region > NULL;
-- no tuples matching
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev > 20;
-- test constants in ORDER BY
:PREFIX SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE status = 1 ORDER BY status, region;
psql:include/skip_scan_multi_query.sql:54: INFO:  SkipScan used on compress_hyper_9_73_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:54: INFO:  SkipScan used on compress_hyper_9_74_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:54: INFO:  SkipScan used on compress_hyper_9_75_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:54: INFO:  SkipScan used on compress_hyper_9_76_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
-- test distinct PathKey with sortref = 0 in PG15 due to FALSE filter not pushed into relation (should not crash in PG15)
:PREFIX SELECT DISTINCT sq.dev FROM (SELECT status, region, dev FROM :TABLE) sq JOIN :TABLE ref
ON (sq.dev = ref.dev) AND (sq.status = ref.status) AND (sq.region = ref.region) WHERE 1 > 2;
-- CTE
:PREFIX WITH devices AS (
	SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1
)
SELECT * FROM devices;
psql:include/skip_scan_multi_query.sql:64: INFO:  SkipScan used on compress_hyper_9_73_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:64: INFO:  SkipScan used on compress_hyper_9_74_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:64: INFO:  SkipScan used on compress_hyper_9_75_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:64: INFO:  SkipScan used on compress_hyper_9_76_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
:PREFIX WITH devices AS (
	SELECT DISTINCT ON (region, dev) region, dev FROM :TABLE WHERE status = 1
)
SELECT * FROM devices ORDER BY region, dev;
psql:include/skip_scan_multi_query.sql:69: INFO:  SkipScan used on compress_hyper_9_73_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:69: INFO:  SkipScan used on compress_hyper_9_74_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:69: INFO:  SkipScan used on compress_hyper_9_75_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:69: INFO:  SkipScan used on compress_hyper_9_76_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL)
-- prepared statements
PREPARE prep AS SELECT DISTINCT ON (region, dev, dev_name) region, dev, dev_name FROM :TABLE WHERE status = 1;
:PREFIX EXECUTE prep;
psql:include/skip_scan_multi_query.sql:73: INFO:  SkipScan used on compress_hyper_9_73_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:73: INFO:  SkipScan used on compress_hyper_9_74_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:73: INFO:  SkipScan used on compress_hyper_9_75_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
psql:include/skip_scan_multi_query.sql:73: INFO:  SkipScan used on compress_hyper_9_76_chunk_status_region_dev_dev_name__ts_me_idx(region NOT NULL, dev NOT NULL, dev_name NOT NULL)
:PREFIX EXECUTE prep;
:PREFIX EXECUTE prep;
DEALLOCATE prep;
-- ReScan tests
:PREFIX SELECT time, status, region, dev, val, 'q7_1' FROM (SELECT DISTINCT ON (status, region, dev) * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT * FROM :TABLE WHERE time != a.v) b) a;
set enable_seqscan=0;
:PREFIX SELECT time, status, region, dev, val, 'q7_2' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev != a.v) b) a;
psql:include/skip_scan_multi_query.sql:86: INFO:  SkipScan used on compress_hyper_9_73_chunk_status_region_dev_dev_name__ts_me_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:86: INFO:  SkipScan used on compress_hyper_9_74_chunk_status_region_dev_dev_name__ts_me_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:86: INFO:  SkipScan used on compress_hyper_9_75_chunk_status_region_dev_dev_name__ts_me_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:86: INFO:  SkipScan used on compress_hyper_9_76_chunk_status_region_dev_dev_name__ts_me_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
-- RuntimeKeys
:PREFIX SELECT time, status, region, dev, val, 'q8_1' FROM (SELECT * FROM (
    VALUES (1), (2)) a(v),
    LATERAL (SELECT DISTINCT ON (status, region, dev) * FROM :TABLE WHERE dev >= a.v) b) c;
psql:include/skip_scan_multi_query.sql:91: INFO:  SkipScan used on compress_hyper_9_73_chunk_status_region_dev_dev_name__ts_me_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:91: INFO:  SkipScan used on compress_hyper_9_74_chunk_status_region_dev_dev_name__ts_me_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:91: INFO:  SkipScan used on compress_hyper_9_75_chunk_status_region_dev_dev_name__ts_me_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:91: INFO:  SkipScan used on compress_hyper_9_76_chunk_status_region_dev_dev_name__ts_me_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
reset enable_seqscan;
-- parallel query
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'on', false);
:PREFIX SELECT DISTINCT status, region, dev FROM :TABLE ORDER BY status, region, dev;
psql:include/skip_scan_multi_query.sql:96: INFO:  SkipScan used on compress_hyper_9_73_chunk_status_region_dev_dev_name__ts_me_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:96: INFO:  SkipScan used on compress_hyper_9_74_chunk_status_region_dev_dev_name__ts_me_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:96: INFO:  SkipScan used on compress_hyper_9_75_chunk_status_region_dev_dev_name__ts_me_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
psql:include/skip_scan_multi_query.sql:96: INFO:  SkipScan used on compress_hyper_9_76_chunk_status_region_dev_dev_name__ts_me_idx(status NOT NULL, region NOT NULL, dev NOT NULL)
SELECT set_config(CASE WHEN current_setting('server_version_num')::int < 160000 THEN 'force_parallel_mode' ELSE 'debug_parallel_query' END,'off', false);
\o
RESET timescaledb.debug_skip_scan_info;