1-- This file and its contents are licensed under the Timescale License. 2-- Please see the included NOTICE for copyright information and 3-- LICENSE-TIMESCALE for a copy of the license. 4-- need superuser to modify statistics 5\c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER 6\ir include/skip_scan_load.sql 7-- This file and its contents are licensed under the Timescale License. 8-- Please see the included NOTICE for copyright information and 9-- LICENSE-TIMESCALE for a copy of the license. 10CREATE TABLE skip_scan(time int, dev int, dev_name text, val int); 11INSERT INTO skip_scan SELECT t, d, 'device_' || d::text, t*d FROM generate_series(1, 1000) t, generate_series(1, 10) d; 12INSERT INTO skip_scan VALUES (NULL, 0, -1, NULL), (0, NULL, -1, NULL); 13INSERT INTO skip_scan(time,dev,dev_name,val) SELECT t, NULL, NULL, NULL FROM generate_series(0, 999, 50) t; 14ANALYZE skip_scan; 15CREATE TABLE skip_scan_nulls(time int); 16CREATE INDEX ON skip_scan_nulls(time); 17INSERT INTO skip_scan_nulls SELECT NULL FROM generate_series(1,100); 18ANALYZE skip_scan_nulls; 19-- create hypertable with different physical layouts in the chunks 20CREATE TABLE skip_scan_ht(f1 int, f2 int, f3 int, time int NOT NULL, dev int, dev_name text, val int); 21SELECT create_hypertable('skip_scan_ht', 'time', chunk_time_interval => 250, create_default_indexes => false); 22 create_hypertable 23--------------------------- 24 (1,public,skip_scan_ht,t) 25(1 row) 26 27INSERT 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; 28ALTER TABLE skip_scan_ht DROP COLUMN f1; 29INSERT 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; 30ALTER TABLE skip_scan_ht DROP COLUMN f2; 31INSERT 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; 32ALTER TABLE skip_scan_ht DROP COLUMN f3; 33INSERT 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; 34INSERT INTO skip_scan_ht(time,dev,dev_name,val) SELECT t, NULL, NULL, NULL FROM generate_series(0, 999, 50) t; 35ANALYZE skip_scan_ht; 36ALTER TABLE skip_scan_ht SET (timescaledb.compress,timescaledb.compress_orderby='time desc', timescaledb.compress_segmentby='dev'); 37CREATE TABLE skip_scan_insert(time int, dev int, dev_name text, val int, query text); 38CREATE OR REPLACE FUNCTION int_func_immutable() RETURNS int LANGUAGE SQL IMMUTABLE SECURITY DEFINER AS $$SELECT 1; $$; 39CREATE OR REPLACE FUNCTION int_func_stable() RETURNS int LANGUAGE SQL STABLE SECURITY DEFINER AS $$ SELECT 2; $$; 40CREATE OR REPLACE FUNCTION int_func_volatile() RETURNS int LANGUAGE SQL VOLATILE SECURITY DEFINER AS $$ SELECT 3; $$; 41CREATE OR REPLACE FUNCTION inta_func_immutable() RETURNS int[] LANGUAGE SQL IMMUTABLE SECURITY DEFINER AS $$ SELECT ARRAY[1,2,3]; $$; 42CREATE OR REPLACE FUNCTION inta_func_stable() RETURNS int[] LANGUAGE SQL STABLE SECURITY DEFINER AS $$ SELECT ARRAY[2,3,4]; $$; 43CREATE OR REPLACE FUNCTION inta_func_volatile() RETURNS int[] LANGUAGE SQL VOLATILE SECURITY DEFINER AS $$ SELECT ARRAY[3,4,5]; $$; 44-- adjust statistics so we get skipscan plans 45UPDATE pg_statistic SET stadistinct=1, stanullfrac=0.5 WHERE starelid='skip_scan'::regclass; 46UPDATE pg_statistic SET stadistinct=1, stanullfrac=0.5 WHERE starelid='skip_scan_nulls'::regclass; 47UPDATE pg_statistic SET stadistinct=1, stanullfrac=0.5 WHERE starelid='skip_scan_ht'::regclass; 48UPDATE pg_statistic SET stadistinct=1, stanullfrac=0.5 WHERE starelid IN (select inhrelid from pg_inherits where inhparent='skip_scan_ht'::regclass); 49-- we want to run with analyze here so we can see counts in the nodes 50\set PREFIX 'EXPLAIN (analyze, costs off, timing off, summary off)' 51\set TABLE skip_scan 52\ir include/skip_scan_query.sql 53-- This file and its contents are licensed under the Timescale License. 54-- Please see the included NOTICE for copyright information and 55-- LICENSE-TIMESCALE for a copy of the license. 56-- canary for result diff 57SELECT current_setting('timescaledb.enable_skipscan') AS enable_skipscan; 58 enable_skipscan 59----------------- 60 on 61(1 row) 62 63-- test different index configurations 64-- no index so we cant do SkipScan 65:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev; 66 QUERY PLAN 67--------------------------------------------------------------- 68 Sort (actual rows=12 loops=1) 69 Sort Key: dev 70 Sort Method: quicksort 71 -> HashAggregate (actual rows=12 loops=1) 72 Group Key: dev 73 Batches: 1 74 -> Seq Scan on skip_scan (actual rows=10022 loops=1) 75(7 rows) 76 77-- NULLS LAST index on dev 78CREATE INDEX skip_scan_idx_dev_nulls_last ON :TABLE(dev); 79:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev; 80 QUERY PLAN 81------------------------------------------------------------------------------------------------------ 82 Unique (actual rows=12 loops=1) 83 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 84 -> Index Only Scan using skip_scan_idx_dev_nulls_last on skip_scan (actual rows=12 loops=1) 85 Index Cond: (dev > NULL::integer) 86 Heap Fetches: 12 87(5 rows) 88 89:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev DESC; 90 QUERY PLAN 91--------------------------------------------------------------------------------------------------------------- 92 Unique (actual rows=12 loops=1) 93 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 94 -> Index Only Scan Backward using skip_scan_idx_dev_nulls_last on skip_scan (actual rows=12 loops=1) 95 Index Cond: (dev < NULL::integer) 96 Heap Fetches: 12 97(5 rows) 98 99:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE; 100 QUERY PLAN 101------------------------------------------------------------------------------------------------------ 102 Unique (actual rows=12 loops=1) 103 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 104 -> Index Only Scan using skip_scan_idx_dev_nulls_last on skip_scan (actual rows=12 loops=1) 105 Index Cond: (dev > NULL::integer) 106 Heap Fetches: 12 107(5 rows) 108 109DROP INDEX skip_scan_idx_dev_nulls_last; 110-- NULLS FIRST index on dev 111CREATE INDEX skip_scan_idx_dev_nulls_first ON :TABLE(dev NULLS FIRST); 112:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev NULLS FIRST; 113 QUERY PLAN 114------------------------------------------------------------------------------------------------------- 115 Unique (actual rows=12 loops=1) 116 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 117 -> Index Only Scan using skip_scan_idx_dev_nulls_first on skip_scan (actual rows=12 loops=1) 118 Index Cond: (dev > NULL::integer) 119 Heap Fetches: 12 120(5 rows) 121 122:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev NULLS FIRST; 123 QUERY PLAN 124------------------------------------------------------------------------------------------------------- 125 Unique (actual rows=12 loops=1) 126 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 127 -> Index Only Scan using skip_scan_idx_dev_nulls_first on skip_scan (actual rows=12 loops=1) 128 Index Cond: (dev > NULL::integer) 129 Heap Fetches: 12 130(5 rows) 131 132DROP INDEX skip_scan_idx_dev_nulls_first; 133-- multicolumn index with dev as leading column 134CREATE INDEX skip_scan_idx_dev_time_idx ON :TABLE(dev, time); 135:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev; 136 QUERY PLAN 137---------------------------------------------------------------------------------------------------- 138 Unique (actual rows=12 loops=1) 139 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 140 -> Index Only Scan using skip_scan_idx_dev_time_idx on skip_scan (actual rows=12 loops=1) 141 Index Cond: (dev > NULL::integer) 142 Heap Fetches: 12 143(5 rows) 144 145:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE; 146 QUERY PLAN 147---------------------------------------------------------------------------------------------------- 148 Unique (actual rows=12 loops=1) 149 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 150 -> Index Only Scan using skip_scan_idx_dev_time_idx on skip_scan (actual rows=12 loops=1) 151 Index Cond: (dev > NULL::integer) 152 Heap Fetches: 12 153(5 rows) 154 155:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev DESC, time DESC; 156 QUERY PLAN 157------------------------------------------------------------------------------------------------------------- 158 Unique (actual rows=12 loops=1) 159 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 160 -> Index Only Scan Backward using skip_scan_idx_dev_time_idx on skip_scan (actual rows=12 loops=1) 161 Index Cond: (dev < NULL::integer) 162 Heap Fetches: 12 163(5 rows) 164 165DROP INDEX skip_scan_idx_dev_time_idx; 166-- multicolumn index with dev as non-leading column 167CREATE INDEX skip_scan_idx_time_dev_idx ON :TABLE(time, dev); 168:PREFIX SELECT DISTINCT dev FROM :TABLE WHERE time = 100 ORDER BY dev; 169 QUERY PLAN 170---------------------------------------------------------------------------------------------------- 171 Unique (actual rows=11 loops=1) 172 -> Custom Scan (SkipScan) on skip_scan (actual rows=11 loops=1) 173 -> Index Only Scan using skip_scan_idx_time_dev_idx on skip_scan (actual rows=11 loops=1) 174 Index Cond: (("time" = 100) AND (dev > NULL::integer)) 175 Heap Fetches: 11 176(5 rows) 177 178:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time = 100; 179 QUERY PLAN 180---------------------------------------------------------------------------------------------------- 181 Unique (actual rows=11 loops=1) 182 -> Custom Scan (SkipScan) on skip_scan (actual rows=11 loops=1) 183 -> Index Only Scan using skip_scan_idx_time_dev_idx on skip_scan (actual rows=11 loops=1) 184 Index Cond: (("time" = 100) AND (dev > NULL::integer)) 185 Heap Fetches: 11 186(5 rows) 187 188DROP INDEX skip_scan_idx_time_dev_idx; 189-- hash index is not ordered so can't use skipscan 190CREATE INDEX skip_scan_idx_hash ON :TABLE USING hash(dev_name); 191:PREFIX SELECT DISTINCT dev_name FROM :TABLE WHERE dev_name IN ('device_1','device_2') ORDER BY dev_name; 192 QUERY PLAN 193-------------------------------------------------------------------------------------- 194 Sort (actual rows=2 loops=1) 195 Sort Key: dev_name 196 Sort Method: quicksort 197 -> HashAggregate (actual rows=2 loops=1) 198 Group Key: dev_name 199 Batches: 1 200 -> Bitmap Heap Scan on skip_scan (actual rows=2000 loops=1) 201 Recheck Cond: (dev_name = ANY ('{device_1,device_2}'::text[])) 202 Heap Blocks: exact=13 203 -> Bitmap Index Scan on skip_scan_idx_hash (actual rows=2000 loops=1) 204 Index Cond: (dev_name = ANY ('{device_1,device_2}'::text[])) 205(11 rows) 206 207DROP INDEX skip_scan_idx_hash; 208-- expression indexes 209-- currently not supported by skipscan 210CREATE INDEX skip_scan_expr_idx ON :TABLE((dev % 3)); 211:PREFIX SELECT DISTINCT dev%3 FROM :TABLE ORDER BY dev%3; 212 QUERY PLAN 213--------------------------------------------------------------- 214 Sort (actual rows=4 loops=1) 215 Sort Key: ((dev % 3)) 216 Sort Method: quicksort 217 -> HashAggregate (actual rows=4 loops=1) 218 Group Key: (dev % 3) 219 Batches: 1 220 -> Seq Scan on skip_scan (actual rows=10022 loops=1) 221(7 rows) 222 223:PREFIX SELECT DISTINCT ON (dev%3) dev FROM :TABLE ORDER BY dev%3; 224 QUERY PLAN 225------------------------------------------------------------------------------------ 226 Unique (actual rows=4 loops=1) 227 -> Index Scan using skip_scan_expr_idx on skip_scan (actual rows=10022 loops=1) 228(2 rows) 229 230DROP INDEX skip_scan_expr_idx; 231CREATE INDEX ON :TABLE(dev_name); 232CREATE INDEX ON :TABLE(dev); 233CREATE INDEX ON :TABLE(dev, time); 234CREATE INDEX ON :TABLE(time,dev); 235CREATE INDEX ON :TABLE(time,dev,val); 236\qecho basic DISTINCT queries on :TABLE 237basic DISTINCT queries on skip_scan 238:PREFIX SELECT DISTINCT dev, 'q1_1' FROM :TABLE ORDER BY dev; 239 QUERY PLAN 240------------------------------------------------------------------------------------------------- 241 Result (actual rows=12 loops=1) 242 -> Unique (actual rows=12 loops=1) 243 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 244 -> Index Only Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 245 Index Cond: (dev > NULL::integer) 246 Heap Fetches: 12 247(6 rows) 248 249:PREFIX SELECT DISTINCT dev_name, 'q1_2' FROM :TABLE ORDER BY dev_name; 250 QUERY PLAN 251------------------------------------------------------------------------------------------------------ 252 Result (actual rows=12 loops=1) 253 -> Unique (actual rows=12 loops=1) 254 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 255 -> Index Only Scan using skip_scan_dev_name_idx on skip_scan (actual rows=12 loops=1) 256 Index Cond: (dev_name > NULL::text) 257 Heap Fetches: 12 258(6 rows) 259 260:PREFIX SELECT DISTINCT dev, 'q1_3', NULL FROM :TABLE ORDER BY dev; 261 QUERY PLAN 262------------------------------------------------------------------------------------------------- 263 Result (actual rows=12 loops=1) 264 -> Unique (actual rows=12 loops=1) 265 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 266 -> Index Only Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 267 Index Cond: (dev > NULL::integer) 268 Heap Fetches: 12 269(6 rows) 270 271\qecho stable expression in targetlist on :TABLE 272stable expression in targetlist on skip_scan 273:PREFIX SELECT DISTINCT dev, 'q1_4', length(md5(now()::text)) FROM :TABLE ORDER BY dev; 274 QUERY PLAN 275------------------------------------------------------------------------------------------------- 276 Result (actual rows=12 loops=1) 277 -> Unique (actual rows=12 loops=1) 278 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 279 -> Index Only Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 280 Index Cond: (dev > NULL::integer) 281 Heap Fetches: 12 282(6 rows) 283 284:PREFIX SELECT DISTINCT dev_name, 'q1_5', length(md5(now()::text)) FROM :TABLE ORDER BY dev_name; 285 QUERY PLAN 286------------------------------------------------------------------------------------------------------ 287 Result (actual rows=12 loops=1) 288 -> Unique (actual rows=12 loops=1) 289 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 290 -> Index Only Scan using skip_scan_dev_name_idx on skip_scan (actual rows=12 loops=1) 291 Index Cond: (dev_name > NULL::text) 292 Heap Fetches: 12 293(6 rows) 294 295-- volatile expression in targetlist 296:PREFIX SELECT DISTINCT dev, 'q1_6', length(md5(random()::text)) FROM :TABLE ORDER BY dev; 297 QUERY PLAN 298--------------------------------------------------------------- 299 Unique (actual rows=12 loops=1) 300 -> Sort (actual rows=10022 loops=1) 301 Sort Key: dev, (length(md5((random())::text))) 302 Sort Method: quicksort 303 -> Seq Scan on skip_scan (actual rows=10022 loops=1) 304(5 rows) 305 306:PREFIX SELECT DISTINCT dev_name, 'q1_7', length(md5(random()::text)) FROM :TABLE ORDER BY dev_name; 307 QUERY PLAN 308--------------------------------------------------------------- 309 Unique (actual rows=12 loops=1) 310 -> Sort (actual rows=10022 loops=1) 311 Sort Key: dev_name, (length(md5((random())::text))) 312 Sort Method: quicksort 313 -> Seq Scan on skip_scan (actual rows=10022 loops=1) 314(5 rows) 315 316-- queries without skipscan because distinct is not limited to specific column 317:PREFIX SELECT DISTINCT * FROM :TABLE ORDER BY dev; 318 QUERY PLAN 319--------------------------------------------------------------- 320 Sort (actual rows=10022 loops=1) 321 Sort Key: dev 322 Sort Method: quicksort 323 -> HashAggregate (actual rows=10022 loops=1) 324 Group Key: dev, "time", dev_name, val 325 Batches: 1 326 -> Seq Scan on skip_scan (actual rows=10022 loops=1) 327(7 rows) 328 329:PREFIX SELECT DISTINCT *, 'q1_9' FROM :TABLE ORDER BY dev; 330 QUERY PLAN 331--------------------------------------------------------------- 332 Sort (actual rows=10022 loops=1) 333 Sort Key: dev 334 Sort Method: quicksort 335 -> HashAggregate (actual rows=10022 loops=1) 336 Group Key: dev, "time", dev_name, val, 'q1_9'::text 337 Batches: 1 338 -> Seq Scan on skip_scan (actual rows=10022 loops=1) 339(7 rows) 340 341:PREFIX SELECT DISTINCT dev, time, 'q1_10' FROM :TABLE ORDER BY dev; 342 QUERY PLAN 343--------------------------------------------------------------- 344 Sort (actual rows=10021 loops=1) 345 Sort Key: dev 346 Sort Method: quicksort 347 -> HashAggregate (actual rows=10021 loops=1) 348 Group Key: dev, "time", 'q1_10'::text 349 Batches: 1 350 -> Seq Scan on skip_scan (actual rows=10022 loops=1) 351(7 rows) 352 353:PREFIX SELECT DISTINCT dev, NULL, 'q1_11' FROM :TABLE ORDER BY dev; 354 QUERY PLAN 355------------------------------------------------------------------------------------------------- 356 Result (actual rows=12 loops=1) 357 -> Unique (actual rows=12 loops=1) 358 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 359 -> Index Only Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 360 Index Cond: (dev > NULL::integer) 361 Heap Fetches: 12 362(6 rows) 363 364-- distinct on expressions not supported 365:PREFIX SELECT DISTINCT time_bucket(10,time), 'q1_12' FROM :TABLE; 366 QUERY PLAN 367--------------------------------------------------------- 368 HashAggregate (actual rows=102 loops=1) 369 Group Key: time_bucket(10, "time"), 'q1_12'::text 370 Batches: 1 371 -> Seq Scan on skip_scan (actual rows=10022 loops=1) 372(4 rows) 373 374:PREFIX SELECT DISTINCT length(dev_name), 'q1_13' FROM :TABLE; 375 QUERY PLAN 376--------------------------------------------------------- 377 HashAggregate (actual rows=4 loops=1) 378 Group Key: length(dev_name), 'q1_13'::text 379 Batches: 1 380 -> Seq Scan on skip_scan (actual rows=10022 loops=1) 381(4 rows) 382 383:PREFIX SELECT DISTINCT 3*time, 'q1_14' FROM :TABLE; 384 QUERY PLAN 385--------------------------------------------------------- 386 HashAggregate (actual rows=1002 loops=1) 387 Group Key: (3 * "time"), 'q1_14'::text 388 Batches: 1 389 -> Seq Scan on skip_scan (actual rows=10022 loops=1) 390(4 rows) 391 392:PREFIX SELECT DISTINCT 'Device ' || dev_name FROM :TABLE; 393 QUERY PLAN 394--------------------------------------------------------- 395 HashAggregate (actual rows=12 loops=1) 396 Group Key: ('Device '::text || dev_name) 397 Batches: 1 398 -> Seq Scan on skip_scan (actual rows=10022 loops=1) 399(4 rows) 400 401-- DISTINCT ON queries 402:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE; 403 QUERY PLAN 404------------------------------------------------------------------------------------------- 405 Unique (actual rows=12 loops=1) 406 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 407 -> Index Only Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 408 Index Cond: (dev > NULL::integer) 409 Heap Fetches: 12 410(5 rows) 411 412:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_2' FROM :TABLE; 413 QUERY PLAN 414------------------------------------------------------------------------------------------------- 415 Result (actual rows=12 loops=1) 416 -> Unique (actual rows=12 loops=1) 417 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 418 -> Index Only Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 419 Index Cond: (dev > NULL::integer) 420 Heap Fetches: 12 421(6 rows) 422 423:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_3', NULL FROM :TABLE; 424 QUERY PLAN 425------------------------------------------------------------------------------------------------- 426 Result (actual rows=12 loops=1) 427 -> Unique (actual rows=12 loops=1) 428 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 429 -> Index Only Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 430 Index Cond: (dev > NULL::integer) 431 Heap Fetches: 12 432(6 rows) 433 434:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_4', length(md5(now()::text)) FROM :TABLE; 435 QUERY PLAN 436------------------------------------------------------------------------------------------------- 437 Result (actual rows=12 loops=1) 438 -> Unique (actual rows=12 loops=1) 439 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 440 -> Index Only Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 441 Index Cond: (dev > NULL::integer) 442 Heap Fetches: 12 443(6 rows) 444 445:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_5', length(md5(random()::text)) FROM :TABLE; 446 QUERY PLAN 447------------------------------------------------------------------------------------------------- 448 Result (actual rows=12 loops=1) 449 -> Unique (actual rows=12 loops=1) 450 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 451 -> Index Only Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 452 Index Cond: (dev > NULL::integer) 453 Heap Fetches: 12 454(6 rows) 455 456:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE; 457 QUERY PLAN 458-------------------------------------------------------------------------------------- 459 Unique (actual rows=12 loops=1) 460 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 461 -> Index Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 462(3 rows) 463 464:PREFIX SELECT DISTINCT ON (dev) *, 'q2_7' FROM :TABLE; 465 QUERY PLAN 466-------------------------------------------------------------------------------------------- 467 Result (actual rows=12 loops=1) 468 -> Unique (actual rows=12 loops=1) 469 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 470 -> Index Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 471(4 rows) 472 473:PREFIX SELECT DISTINCT ON (dev) dev, time, 'q2_8' FROM :TABLE; 474 QUERY PLAN 475-------------------------------------------------------------------------------------------- 476 Result (actual rows=12 loops=1) 477 -> Unique (actual rows=12 loops=1) 478 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 479 -> Index Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 480(4 rows) 481 482:PREFIX SELECT DISTINCT ON (dev) dev, NULL, 'q2_9' FROM :TABLE; 483 QUERY PLAN 484------------------------------------------------------------------------------------------------- 485 Result (actual rows=12 loops=1) 486 -> Unique (actual rows=12 loops=1) 487 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 488 -> Index Only Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 489 Index Cond: (dev > NULL::integer) 490 Heap Fetches: 12 491(6 rows) 492 493:PREFIX SELECT DISTINCT ON (dev) time, 'q2_10' FROM :TABLE ORDER by dev, time; 494 QUERY PLAN 495------------------------------------------------------------------------------------------------------ 496 Result (actual rows=12 loops=1) 497 -> Unique (actual rows=12 loops=1) 498 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 499 -> Index Only Scan using skip_scan_dev_time_idx on skip_scan (actual rows=12 loops=1) 500 Index Cond: (dev > NULL::integer) 501 Heap Fetches: 12 502(6 rows) 503 504:PREFIX SELECT DISTINCT ON (dev) dev, tableoid::regclass, 'q2_11' FROM :TABLE; 505 QUERY PLAN 506-------------------------------------------------------------------------------------------- 507 Result (actual rows=12 loops=1) 508 -> Unique (actual rows=12 loops=1) 509 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 510 -> Index Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 511(4 rows) 512 513:PREFIX SELECT DISTINCT ON (dev) dev, int_func_immutable(), 'q2_12' FROM :TABLE; 514 QUERY PLAN 515------------------------------------------------------------------------------------------------- 516 Result (actual rows=12 loops=1) 517 -> Unique (actual rows=12 loops=1) 518 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 519 -> Index Only Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 520 Index Cond: (dev > NULL::integer) 521 Heap Fetches: 12 522(6 rows) 523 524:PREFIX SELECT DISTINCT ON (dev) dev, int_func_stable(), 'q2_13' FROM :TABLE; 525 QUERY PLAN 526------------------------------------------------------------------------------------------------- 527 Result (actual rows=12 loops=1) 528 -> Unique (actual rows=12 loops=1) 529 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 530 -> Index Only Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 531 Index Cond: (dev > NULL::integer) 532 Heap Fetches: 12 533(6 rows) 534 535:PREFIX SELECT DISTINCT ON (dev) dev, int_func_volatile(), 'q2_14' FROM :TABLE; 536 QUERY PLAN 537------------------------------------------------------------------------------------------------- 538 Result (actual rows=12 loops=1) 539 -> Unique (actual rows=12 loops=1) 540 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 541 -> Index Only Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 542 Index Cond: (dev > NULL::integer) 543 Heap Fetches: 12 544(6 rows) 545 546-- DISTINCT ON queries on TEXT column 547:PREFIX SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE; 548 QUERY PLAN 549------------------------------------------------------------------------------------------------ 550 Unique (actual rows=12 loops=1) 551 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 552 -> Index Only Scan using skip_scan_dev_name_idx on skip_scan (actual rows=12 loops=1) 553 Index Cond: (dev_name > NULL::text) 554 Heap Fetches: 12 555(5 rows) 556 557:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_2' FROM :TABLE; 558 QUERY PLAN 559------------------------------------------------------------------------------------------------------ 560 Result (actual rows=12 loops=1) 561 -> Unique (actual rows=12 loops=1) 562 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 563 -> Index Only Scan using skip_scan_dev_name_idx on skip_scan (actual rows=12 loops=1) 564 Index Cond: (dev_name > NULL::text) 565 Heap Fetches: 12 566(6 rows) 567 568:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_3', NULL FROM :TABLE; 569 QUERY PLAN 570------------------------------------------------------------------------------------------------------ 571 Result (actual rows=12 loops=1) 572 -> Unique (actual rows=12 loops=1) 573 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 574 -> Index Only Scan using skip_scan_dev_name_idx on skip_scan (actual rows=12 loops=1) 575 Index Cond: (dev_name > NULL::text) 576 Heap Fetches: 12 577(6 rows) 578 579:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_4', length(md5(now()::text)) FROM :TABLE; 580 QUERY PLAN 581------------------------------------------------------------------------------------------------------ 582 Result (actual rows=12 loops=1) 583 -> Unique (actual rows=12 loops=1) 584 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 585 -> Index Only Scan using skip_scan_dev_name_idx on skip_scan (actual rows=12 loops=1) 586 Index Cond: (dev_name > NULL::text) 587 Heap Fetches: 12 588(6 rows) 589 590:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_5', length(md5(random()::text)) FROM :TABLE; 591 QUERY PLAN 592------------------------------------------------------------------------------------------------------ 593 Result (actual rows=12 loops=1) 594 -> Unique (actual rows=12 loops=1) 595 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 596 -> Index Only Scan using skip_scan_dev_name_idx on skip_scan (actual rows=12 loops=1) 597 Index Cond: (dev_name > NULL::text) 598 Heap Fetches: 12 599(6 rows) 600 601:PREFIX SELECT DISTINCT ON (dev_name) * FROM :TABLE; 602 QUERY PLAN 603------------------------------------------------------------------------------------------- 604 Unique (actual rows=12 loops=1) 605 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 606 -> Index Scan using skip_scan_dev_name_idx on skip_scan (actual rows=12 loops=1) 607(3 rows) 608 609:PREFIX SELECT DISTINCT ON (dev_name) *, 'q3_7' FROM :TABLE; 610 QUERY PLAN 611------------------------------------------------------------------------------------------------- 612 Result (actual rows=12 loops=1) 613 -> Unique (actual rows=12 loops=1) 614 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 615 -> Index Scan using skip_scan_dev_name_idx on skip_scan (actual rows=12 loops=1) 616(4 rows) 617 618:PREFIX SELECT DISTINCT ON (dev_name) dev_name, time, 'q3_8' FROM :TABLE; 619 QUERY PLAN 620------------------------------------------------------------------------------------------------- 621 Result (actual rows=12 loops=1) 622 -> Unique (actual rows=12 loops=1) 623 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 624 -> Index Scan using skip_scan_dev_name_idx on skip_scan (actual rows=12 loops=1) 625(4 rows) 626 627:PREFIX SELECT DISTINCT ON (dev_name) dev_name, NULL, 'q3_9' FROM :TABLE; 628 QUERY PLAN 629------------------------------------------------------------------------------------------------------ 630 Result (actual rows=12 loops=1) 631 -> Unique (actual rows=12 loops=1) 632 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 633 -> Index Only Scan using skip_scan_dev_name_idx on skip_scan (actual rows=12 loops=1) 634 Index Cond: (dev_name > NULL::text) 635 Heap Fetches: 12 636(6 rows) 637 638:PREFIX SELECT DISTINCT ON (dev_name) time, 'q3_10' FROM :TABLE ORDER by dev_name, time; 639 QUERY PLAN 640--------------------------------------------------------------- 641 Unique (actual rows=12 loops=1) 642 -> Sort (actual rows=10022 loops=1) 643 Sort Key: dev_name, "time" 644 Sort Method: quicksort 645 -> Seq Scan on skip_scan (actual rows=10022 loops=1) 646(5 rows) 647 648:PREFIX SELECT DISTINCT ON (dev_name) dev_name, tableoid::regclass, 'q3_11' FROM :TABLE; 649 QUERY PLAN 650------------------------------------------------------------------------------------------------- 651 Result (actual rows=12 loops=1) 652 -> Unique (actual rows=12 loops=1) 653 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 654 -> Index Scan using skip_scan_dev_name_idx on skip_scan (actual rows=12 loops=1) 655(4 rows) 656 657:PREFIX SELECT DISTINCT ON (dev_name::varchar) dev_name::varchar FROM :TABLE; 658 QUERY PLAN 659------------------------------------------------------------------------------------------------------ 660 Result (actual rows=12 loops=1) 661 -> Unique (actual rows=12 loops=1) 662 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 663 -> Index Only Scan using skip_scan_dev_name_idx on skip_scan (actual rows=12 loops=1) 664 Index Cond: (dev_name > NULL::text) 665 Heap Fetches: 12 666(6 rows) 667 668:PREFIX SELECT DISTINCT ON (dev_name) dev, int_func_immutable(), 'q3_13' FROM :TABLE; 669 QUERY PLAN 670------------------------------------------------------------------------------------------------- 671 Result (actual rows=12 loops=1) 672 -> Unique (actual rows=12 loops=1) 673 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 674 -> Index Scan using skip_scan_dev_name_idx on skip_scan (actual rows=12 loops=1) 675(4 rows) 676 677:PREFIX SELECT DISTINCT ON (dev_name) dev, int_func_stable(), 'q3_14' FROM :TABLE; 678 QUERY PLAN 679------------------------------------------------------------------------------------------------- 680 Result (actual rows=12 loops=1) 681 -> Unique (actual rows=12 loops=1) 682 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 683 -> Index Scan using skip_scan_dev_name_idx on skip_scan (actual rows=12 loops=1) 684(4 rows) 685 686:PREFIX SELECT DISTINCT ON (dev_name) dev, int_func_volatile(), 'q3_15' FROM :TABLE; 687 QUERY PLAN 688------------------------------------------------------------------------------------------------- 689 Result (actual rows=12 loops=1) 690 -> Unique (actual rows=12 loops=1) 691 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 692 -> Index Scan using skip_scan_dev_name_idx on skip_scan (actual rows=12 loops=1) 693(4 rows) 694 695\qecho DISTINCT with wholerow var 696DISTINCT with wholerow var 697:PREFIX SELECT DISTINCT ON (dev) :TABLE FROM :TABLE; 698 QUERY PLAN 699-------------------------------------------------------------------------------------- 700 Unique (actual rows=12 loops=1) 701 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 702 -> Index Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 703(3 rows) 704 705-- should not use SkipScan since we only support SkipScan on single-column distinct 706:PREFIX SELECT DISTINCT :TABLE FROM :TABLE; 707 QUERY PLAN 708--------------------------------------------------------------- 709 Unique (actual rows=10022 loops=1) 710 -> Sort (actual rows=10022 loops=1) 711 Sort Key: skip_scan.* 712 Sort Method: quicksort 713 -> Seq Scan on skip_scan (actual rows=10022 loops=1) 714(5 rows) 715 716\qecho LIMIT queries on :TABLE 717LIMIT queries on skip_scan 718:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE LIMIT 3; 719 QUERY PLAN 720------------------------------------------------------------------------------------------------ 721 Limit (actual rows=3 loops=1) 722 -> Unique (actual rows=3 loops=1) 723 -> Custom Scan (SkipScan) on skip_scan (actual rows=3 loops=1) 724 -> Index Only Scan using skip_scan_dev_idx on skip_scan (actual rows=3 loops=1) 725 Index Cond: (dev > NULL::integer) 726 Heap Fetches: 3 727(6 rows) 728 729:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev DESC, time DESC LIMIT 3; 730 QUERY PLAN 731-------------------------------------------------------------------------------------------------------------- 732 Limit (actual rows=3 loops=1) 733 -> Unique (actual rows=3 loops=1) 734 -> Custom Scan (SkipScan) on skip_scan (actual rows=3 loops=1) 735 -> Index Only Scan Backward using skip_scan_dev_time_idx on skip_scan (actual rows=3 loops=1) 736 Index Cond: (dev < NULL::integer) 737 Heap Fetches: 3 738(6 rows) 739 740:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev, time LIMIT 3; 741 QUERY PLAN 742----------------------------------------------------------------------------------------------------- 743 Limit (actual rows=3 loops=1) 744 -> Unique (actual rows=3 loops=1) 745 -> Custom Scan (SkipScan) on skip_scan (actual rows=3 loops=1) 746 -> Index Only Scan using skip_scan_dev_time_idx on skip_scan (actual rows=3 loops=1) 747 Index Cond: (dev > NULL::integer) 748 Heap Fetches: 3 749(6 rows) 750 751\qecho range queries on :TABLE 752range queries on skip_scan 753:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time BETWEEN 100 AND 300; 754 QUERY PLAN 755------------------------------------------------------------------------------------------------ 756 Unique (actual rows=11 loops=1) 757 -> Custom Scan (SkipScan) on skip_scan (actual rows=11 loops=1) 758 -> Index Only Scan using skip_scan_dev_time_idx on skip_scan (actual rows=11 loops=1) 759 Index Cond: ((dev > NULL::integer) AND ("time" >= 100) AND ("time" <= 300)) 760 Heap Fetches: 11 761(5 rows) 762 763:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time < 200; 764 QUERY PLAN 765------------------------------------------------------------------------------------------------ 766 Unique (actual rows=11 loops=1) 767 -> Custom Scan (SkipScan) on skip_scan (actual rows=11 loops=1) 768 -> Index Only Scan using skip_scan_dev_time_idx on skip_scan (actual rows=11 loops=1) 769 Index Cond: ((dev > NULL::integer) AND ("time" < 200)) 770 Heap Fetches: 11 771(5 rows) 772 773:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time > 800; 774 QUERY PLAN 775------------------------------------------------------------------------------------------------ 776 Unique (actual rows=11 loops=1) 777 -> Custom Scan (SkipScan) on skip_scan (actual rows=11 loops=1) 778 -> Index Only Scan using skip_scan_dev_time_idx on skip_scan (actual rows=11 loops=1) 779 Index Cond: ((dev > NULL::integer) AND ("time" > 800)) 780 Heap Fetches: 11 781(5 rows) 782 783\qecho ordered append on :TABLE 784ordered append on skip_scan 785:PREFIX SELECT * FROM :TABLE ORDER BY time; 786 QUERY PLAN 787---------------------------------------------------------------------------------- 788 Index Scan using skip_scan_time_dev_idx on skip_scan (actual rows=10022 loops=1) 789(1 row) 790 791:PREFIX SELECT DISTINCT ON (time) time FROM :TABLE WHERE time BETWEEN 0 AND 5000; 792 QUERY PLAN 793-------------------------------------------------------------------------------------------------- 794 Unique (actual rows=1001 loops=1) 795 -> Custom Scan (SkipScan) on skip_scan (actual rows=1001 loops=1) 796 -> Index Only Scan using skip_scan_time_dev_idx on skip_scan (actual rows=1001 loops=1) 797 Index Cond: (("time" > NULL::integer) AND ("time" >= 0) AND ("time" <= 5000)) 798 Heap Fetches: 1001 799(5 rows) 800 801\qecho SUBSELECTS on :TABLE 802SUBSELECTS on skip_scan 803:PREFIX SELECT time, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a; 804 QUERY PLAN 805-------------------------------------------------------------------------------------------------- 806 Subquery Scan on a (actual rows=12 loops=1) 807 -> Result (actual rows=12 loops=1) 808 -> Unique (actual rows=12 loops=1) 809 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 810 -> Index Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 811(5 rows) 812 813:PREFIX SELECT NULL, dev, NULL, 'q4_3' FROM (SELECT DISTINCT ON (dev) dev FROM :TABLE) a; 814 QUERY PLAN 815------------------------------------------------------------------------------------------------- 816 Subquery Scan on a (actual rows=12 loops=1) 817 -> Unique (actual rows=12 loops=1) 818 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 819 -> Index Only Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 820 Index Cond: (dev > NULL::integer) 821 Heap Fetches: 12 822(6 rows) 823 824:PREFIX SELECT time, dev, NULL, 'q4_4' FROM (SELECT DISTINCT ON (dev) dev, time FROM :TABLE) a; 825 QUERY PLAN 826-------------------------------------------------------------------------------------------- 827 Subquery Scan on a (actual rows=12 loops=1) 828 -> Unique (actual rows=12 loops=1) 829 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 830 -> Index Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 831(4 rows) 832 833\qecho ORDER BY 834ORDER BY 835:PREFIX SELECT time, dev, val, 'q5_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE ORDER BY dev, time) a; 836 QUERY PLAN 837------------------------------------------------------------------------------------------------------- 838 Subquery Scan on a (actual rows=12 loops=1) 839 -> Result (actual rows=12 loops=1) 840 -> Unique (actual rows=12 loops=1) 841 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 842 -> Index Scan using skip_scan_dev_time_idx on skip_scan (actual rows=12 loops=1) 843(5 rows) 844 845:PREFIX SELECT time, dev, val, 'q5_2' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE ORDER BY dev DESC, time DESC) a; 846 QUERY PLAN 847---------------------------------------------------------------------------------------------------------------- 848 Subquery Scan on a (actual rows=12 loops=1) 849 -> Result (actual rows=12 loops=1) 850 -> Unique (actual rows=12 loops=1) 851 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 852 -> Index Scan Backward using skip_scan_dev_time_idx on skip_scan (actual rows=12 loops=1) 853(5 rows) 854 855\qecho WHERE CLAUSES 856WHERE CLAUSES 857:PREFIX SELECT time, dev, val, 'q6_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > 5) a; 858 QUERY PLAN 859------------------------------------------------------------------------------------------------- 860 Subquery Scan on a (actual rows=5 loops=1) 861 -> Result (actual rows=5 loops=1) 862 -> Unique (actual rows=5 loops=1) 863 -> Custom Scan (SkipScan) on skip_scan (actual rows=5 loops=1) 864 -> Index Scan using skip_scan_dev_idx on skip_scan (actual rows=5 loops=1) 865 Index Cond: (dev > 5) 866(6 rows) 867 868:PREFIX SELECT time, dev, val, 'q6_2' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE time > 5) a; 869 QUERY PLAN 870------------------------------------------------------------------------------------------------------- 871 Subquery Scan on a (actual rows=11 loops=1) 872 -> Result (actual rows=11 loops=1) 873 -> Unique (actual rows=11 loops=1) 874 -> Custom Scan (SkipScan) on skip_scan (actual rows=11 loops=1) 875 -> Index Scan using skip_scan_dev_time_idx on skip_scan (actual rows=11 loops=1) 876 Index Cond: ("time" > 5) 877(6 rows) 878 879:PREFIX SELECT time, dev, val, 'q6_3' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a WHERE dev > 5; 880 QUERY PLAN 881------------------------------------------------------------------------------------------------- 882 Subquery Scan on a (actual rows=5 loops=1) 883 -> Result (actual rows=5 loops=1) 884 -> Unique (actual rows=5 loops=1) 885 -> Custom Scan (SkipScan) on skip_scan (actual rows=5 loops=1) 886 -> Index Scan using skip_scan_dev_idx on skip_scan (actual rows=5 loops=1) 887 Index Cond: (dev > 5) 888(6 rows) 889 890:PREFIX SELECT time, dev, val, 'q6_4' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a WHERE time > 5; 891 QUERY PLAN 892-------------------------------------------------------------------------------------------------- 893 Subquery Scan on a (actual rows=0 loops=1) 894 Filter: (a."time" > 5) 895 Rows Removed by Filter: 12 896 -> Result (actual rows=12 loops=1) 897 -> Unique (actual rows=12 loops=1) 898 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 899 -> Index Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 900(7 rows) 901 902--\qecho immutable func in WHERE clause on :TABLE 903:PREFIX SELECT DISTINCT ON (dev) *, 'q6_5' FROM :TABLE WHERE dev > int_func_immutable(); 904 QUERY PLAN 905------------------------------------------------------------------------------------------- 906 Result (actual rows=9 loops=1) 907 -> Unique (actual rows=9 loops=1) 908 -> Custom Scan (SkipScan) on skip_scan (actual rows=9 loops=1) 909 -> Index Scan using skip_scan_dev_idx on skip_scan (actual rows=9 loops=1) 910 Index Cond: (dev > 1) 911(5 rows) 912 913--\qecho stable func in WHERE clause on :TABLE 914:PREFIX SELECT DISTINCT ON (dev) *, 'q6_6' FROM :TABLE WHERE dev > int_func_stable(); 915 QUERY PLAN 916------------------------------------------------------------------------------------------- 917 Result (actual rows=8 loops=1) 918 -> Unique (actual rows=8 loops=1) 919 -> Custom Scan (SkipScan) on skip_scan (actual rows=8 loops=1) 920 -> Index Scan using skip_scan_dev_idx on skip_scan (actual rows=8 loops=1) 921 Index Cond: (dev > int_func_stable()) 922(5 rows) 923 924--\qecho volatile func in WHERE clause on :TABLE 925:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > int_func_volatile(); 926 QUERY PLAN 927------------------------------------------------------------------------------------- 928 Unique (actual rows=7 loops=1) 929 -> Custom Scan (SkipScan) on skip_scan (actual rows=7 loops=1) 930 -> Index Scan using skip_scan_dev_idx on skip_scan (actual rows=7 loops=1) 931 Filter: (dev > int_func_volatile()) 932 Rows Removed by Filter: 3022 933(5 rows) 934 935:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_immutable()); 936 QUERY PLAN 937------------------------------------------------------------------------------------- 938 Unique (actual rows=3 loops=1) 939 -> Custom Scan (SkipScan) on skip_scan (actual rows=3 loops=1) 940 -> Index Scan using skip_scan_dev_idx on skip_scan (actual rows=3 loops=1) 941 Index Cond: (dev = ANY ('{1,2,3}'::integer[])) 942(4 rows) 943 944:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_stable()); 945 QUERY PLAN 946------------------------------------------------------------------------------------- 947 Unique (actual rows=3 loops=1) 948 -> Custom Scan (SkipScan) on skip_scan (actual rows=3 loops=1) 949 -> Index Scan using skip_scan_dev_idx on skip_scan (actual rows=3 loops=1) 950 Index Cond: (dev = ANY (inta_func_stable())) 951(4 rows) 952 953:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_volatile()); 954 QUERY PLAN 955------------------------------------------------------------------------------------- 956 Unique (actual rows=3 loops=1) 957 -> Custom Scan (SkipScan) on skip_scan (actual rows=3 loops=1) 958 -> Index Scan using skip_scan_dev_idx on skip_scan (actual rows=3 loops=1) 959 Filter: (dev = ANY (inta_func_volatile())) 960 Rows Removed by Filter: 7022 961(5 rows) 962 963-- RowCompareExpr 964:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE (dev, time) > (5,100); 965 QUERY PLAN 966------------------------------------------------------------------------------------------ 967 Unique (actual rows=6 loops=1) 968 -> Custom Scan (SkipScan) on skip_scan (actual rows=6 loops=1) 969 -> Index Scan using skip_scan_dev_time_idx on skip_scan (actual rows=6 loops=1) 970 Index Cond: (ROW(dev, "time") > ROW(5, 100)) 971(4 rows) 972 973-- always false expr similar to our initial skip qual 974:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > NULL; 975 QUERY PLAN 976---------------------------------------------- 977 Unique (actual rows=0 loops=1) 978 -> Sort (actual rows=0 loops=1) 979 Sort Key: dev 980 Sort Method: quicksort 981 -> Result (actual rows=0 loops=1) 982 One-Time Filter: false 983(6 rows) 984 985-- no tuples matching 986:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > 20; 987 QUERY PLAN 988------------------------------------------------------------------------------------- 989 Unique (actual rows=0 loops=1) 990 -> Custom Scan (SkipScan) on skip_scan (actual rows=0 loops=1) 991 -> Index Scan using skip_scan_dev_idx on skip_scan (actual rows=0 loops=1) 992 Index Cond: (dev > 20) 993(4 rows) 994 995-- multiple constraints in WHERE clause 996:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev > 5 AND time = 100; 997 QUERY PLAN 998--------------------------------------------------------------------------------------------------- 999 Unique (actual rows=5 loops=1) 1000 -> Custom Scan (SkipScan) on skip_scan (actual rows=5 loops=1) 1001 -> Index Only Scan using skip_scan_time_dev_val_idx on skip_scan (actual rows=5 loops=1) 1002 Index Cond: (("time" = 100) AND (dev > NULL::integer) AND (dev > 5)) 1003 Heap Fetches: 5 1004(5 rows) 1005 1006:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev > 5 AND time > 200; 1007 QUERY PLAN 1008------------------------------------------------------------------------------------- 1009 Unique (actual rows=5 loops=1) 1010 -> Custom Scan (SkipScan) on skip_scan (actual rows=5 loops=1) 1011 -> Index Scan using skip_scan_dev_idx on skip_scan (actual rows=5 loops=1) 1012 Index Cond: (dev > 5) 1013 Filter: ("time" > 200) 1014 Rows Removed by Filter: 1000 1015(6 rows) 1016 1017:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev >= 5 AND dev < 7 AND dev >= 2; 1018 QUERY PLAN 1019------------------------------------------------------------------------------------- 1020 Unique (actual rows=2 loops=1) 1021 -> Custom Scan (SkipScan) on skip_scan (actual rows=2 loops=1) 1022 -> Index Scan using skip_scan_dev_idx on skip_scan (actual rows=2 loops=1) 1023 Index Cond: ((dev >= 5) AND (dev < 7) AND (dev >= 2)) 1024(4 rows) 1025 1026: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; 1027 QUERY PLAN 1028------------------------------------------------------------------------------------------- 1029 Unique (actual rows=4 loops=1) 1030 -> Custom Scan (SkipScan) on skip_scan (actual rows=4 loops=1) 1031 -> Index Scan using skip_scan_dev_time_idx on skip_scan (actual rows=4 loops=1) 1032 Index Cond: ((dev > 2) AND (dev < 7) AND ("time" > 100) AND ("time" < 200)) 1033 Filter: ((val > 10) AND (val < 10000)) 1034(5 rows) 1035 1036:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE dev IS NULL; 1037 QUERY PLAN 1038------------------------------------------------------------------------------------------ 1039 Unique (actual rows=1 loops=1) 1040 -> Custom Scan (SkipScan) on skip_scan (actual rows=1 loops=1) 1041 -> Index Only Scan using skip_scan_dev_idx on skip_scan (actual rows=1 loops=1) 1042 Index Cond: ((dev > NULL::integer) AND (dev IS NULL)) 1043 Heap Fetches: 1 1044(5 rows) 1045 1046:PREFIX SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE WHERE dev_name IS NULL; 1047 QUERY PLAN 1048----------------------------------------------------------------------------------------------- 1049 Unique (actual rows=1 loops=1) 1050 -> Custom Scan (SkipScan) on skip_scan (actual rows=1 loops=1) 1051 -> Index Only Scan using skip_scan_dev_name_idx on skip_scan (actual rows=1 loops=1) 1052 Index Cond: ((dev_name > NULL::text) AND (dev_name IS NULL)) 1053 Heap Fetches: 1 1054(5 rows) 1055 1056-- test constants in ORDER BY 1057:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = 1 ORDER BY dev, time DESC; 1058 QUERY PLAN 1059--------------------------------------------------------------------------------------------------- 1060 Unique (actual rows=1 loops=1) 1061 -> Custom Scan (SkipScan) on skip_scan (actual rows=1 loops=1) 1062 -> Index Scan Backward using skip_scan_dev_time_idx on skip_scan (actual rows=1 loops=1) 1063 Index Cond: (dev = 1) 1064(4 rows) 1065 1066-- CTE 1067:PREFIX WITH devices AS ( 1068 SELECT DISTINCT ON (dev) dev FROM :TABLE 1069) 1070SELECT * FROM devices; 1071 QUERY PLAN 1072------------------------------------------------------------------------------------------- 1073 Unique (actual rows=12 loops=1) 1074 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 1075 -> Index Only Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 1076 Index Cond: (dev > NULL::integer) 1077 Heap Fetches: 12 1078(5 rows) 1079 1080:PREFIX WITH devices AS ( 1081 SELECT DISTINCT dev FROM :TABLE 1082) 1083SELECT * FROM devices ORDER BY dev; 1084 QUERY PLAN 1085------------------------------------------------------------------------------------------- 1086 Unique (actual rows=12 loops=1) 1087 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 1088 -> Index Only Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 1089 Index Cond: (dev > NULL::integer) 1090 Heap Fetches: 12 1091(5 rows) 1092 1093-- prepared statements 1094PREPARE prep AS SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE; 1095:PREFIX EXECUTE prep; 1096 QUERY PLAN 1097------------------------------------------------------------------------------------------------ 1098 Unique (actual rows=12 loops=1) 1099 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 1100 -> Index Only Scan using skip_scan_dev_name_idx on skip_scan (actual rows=12 loops=1) 1101 Index Cond: (dev_name > NULL::text) 1102 Heap Fetches: 12 1103(5 rows) 1104 1105:PREFIX EXECUTE prep; 1106 QUERY PLAN 1107------------------------------------------------------------------------------------------------ 1108 Unique (actual rows=12 loops=1) 1109 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 1110 -> Index Only Scan using skip_scan_dev_name_idx on skip_scan (actual rows=12 loops=1) 1111 Index Cond: (dev_name > NULL::text) 1112 Heap Fetches: 12 1113(5 rows) 1114 1115:PREFIX EXECUTE prep; 1116 QUERY PLAN 1117------------------------------------------------------------------------------------------------ 1118 Unique (actual rows=12 loops=1) 1119 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 1120 -> Index Only Scan using skip_scan_dev_name_idx on skip_scan (actual rows=12 loops=1) 1121 Index Cond: (dev_name > NULL::text) 1122 Heap Fetches: 12 1123(5 rows) 1124 1125DEALLOCATE prep; 1126-- ReScan tests 1127:PREFIX SELECT time, dev, val, 'q7_1' FROM (SELECT DISTINCT ON (dev) * FROM ( 1128 VALUES (1), (2)) a(v), 1129 LATERAL (SELECT * FROM :TABLE WHERE time != a.v) b) a; 1130 QUERY PLAN 1131----------------------------------------------------------------------------------------------- 1132 Subquery Scan on a (actual rows=11 loops=1) 1133 -> Unique (actual rows=11 loops=1) 1134 -> Nested Loop (actual rows=20022 loops=1) 1135 Join Filter: (skip_scan."time" <> "*VALUES*".column1) 1136 Rows Removed by Join Filter: 22 1137 -> Index Scan using skip_scan_dev_idx on skip_scan (actual rows=10022 loops=1) 1138 -> Materialize (actual rows=2 loops=10022) 1139 -> Values Scan on "*VALUES*" (actual rows=2 loops=1) 1140(8 rows) 1141 1142:PREFIX SELECT time, dev, val, 'q7_2' FROM (SELECT * FROM ( 1143 VALUES (1), (2)) a(v), 1144 LATERAL (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev != a.v) b) a; 1145 QUERY PLAN 1146-------------------------------------------------------------------------------------------------- 1147 Nested Loop (actual rows=20 loops=1) 1148 -> Values Scan on "*VALUES*" (actual rows=2 loops=1) 1149 -> Result (actual rows=10 loops=2) 1150 -> Unique (actual rows=10 loops=2) 1151 -> Custom Scan (SkipScan) on skip_scan (actual rows=10 loops=2) 1152 -> Index Scan using skip_scan_dev_idx on skip_scan (actual rows=10 loops=2) 1153 Filter: (dev <> "*VALUES*".column1) 1154 Rows Removed by Filter: 1021 1155(8 rows) 1156 1157-- RuntimeKeys 1158:PREFIX SELECT time, dev, val, 'q8_1' FROM (SELECT * FROM ( 1159 VALUES (1), (2)) a(v), 1160 LATERAL (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev >= a.v) b) c; 1161 QUERY PLAN 1162-------------------------------------------------------------------------------------------------- 1163 Nested Loop (actual rows=19 loops=1) 1164 -> Values Scan on "*VALUES*" (actual rows=2 loops=1) 1165 -> Result (actual rows=10 loops=2) 1166 -> Unique (actual rows=10 loops=2) 1167 -> Custom Scan (SkipScan) on skip_scan (actual rows=10 loops=2) 1168 -> Index Scan using skip_scan_dev_idx on skip_scan (actual rows=10 loops=2) 1169 Index Cond: (dev >= "*VALUES*".column1) 1170(7 rows) 1171 1172-- Emulate multi-column DISTINCT using multiple SkipSkans 1173:PREFIX SELECT time, dev, val, 'q9_1' FROM (SELECT b.* FROM 1174 (SELECT DISTINCT ON (dev) dev FROM :TABLE) a, 1175 LATERAL (SELECT DISTINCT ON (time) * FROM :TABLE WHERE dev = a.dev) b) c; 1176 QUERY PLAN 1177--------------------------------------------------------------------------------------------------------------------- 1178 Nested Loop (actual rows=10001 loops=1) 1179 -> Unique (actual rows=12 loops=1) 1180 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 1181 -> Index Only Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 1182 Index Cond: (dev > NULL::integer) 1183 Heap Fetches: 12 1184 -> Result (actual rows=833 loops=12) 1185 -> Unique (actual rows=833 loops=12) 1186 -> Custom Scan (SkipScan) on skip_scan skip_scan_1 (actual rows=833 loops=12) 1187 -> Index Scan using skip_scan_dev_time_idx on skip_scan skip_scan_1 (actual rows=833 loops=12) 1188 Index Cond: (dev = skip_scan.dev) 1189(11 rows) 1190 1191:PREFIX SELECT time, dev, NULL, 'q9_2' FROM (SELECT b.* FROM 1192 (SELECT DISTINCT ON (dev) dev FROM :TABLE) a, 1193 LATERAL (SELECT DISTINCT ON (time) dev, time FROM :TABLE WHERE dev = a.dev) b) c; 1194 QUERY PLAN 1195-------------------------------------------------------------------------------------------------------------------- 1196 Nested Loop (actual rows=10001 loops=1) 1197 -> Unique (actual rows=12 loops=1) 1198 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 1199 -> Index Only Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 1200 Index Cond: (dev > NULL::integer) 1201 Heap Fetches: 12 1202 -> Unique (actual rows=833 loops=12) 1203 -> Custom Scan (SkipScan) on skip_scan skip_scan_1 (actual rows=833 loops=12) 1204 -> Index Only Scan using skip_scan_dev_time_idx on skip_scan skip_scan_1 (actual rows=833 loops=12) 1205 Index Cond: ((dev = skip_scan.dev) AND ("time" > NULL::integer)) 1206 Heap Fetches: 10001 1207(11 rows) 1208 1209-- Test that the multi-column DISTINCT emulation is equivalent to a real multi-column DISTINCT 1210:PREFIX SELECT * FROM 1211 (SELECT DISTINCT ON (dev) dev FROM :TABLE) a, 1212 LATERAL (SELECT DISTINCT ON (time) dev, time FROM :TABLE WHERE dev = a.dev) b; 1213 QUERY PLAN 1214-------------------------------------------------------------------------------------------------------------------- 1215 Nested Loop (actual rows=10001 loops=1) 1216 -> Unique (actual rows=12 loops=1) 1217 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 1218 -> Index Only Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 1219 Index Cond: (dev > NULL::integer) 1220 Heap Fetches: 12 1221 -> Unique (actual rows=833 loops=12) 1222 -> Custom Scan (SkipScan) on skip_scan skip_scan_1 (actual rows=833 loops=12) 1223 -> Index Only Scan using skip_scan_dev_time_idx on skip_scan skip_scan_1 (actual rows=833 loops=12) 1224 Index Cond: ((dev = skip_scan.dev) AND ("time" > NULL::integer)) 1225 Heap Fetches: 10001 1226(11 rows) 1227 1228:PREFIX SELECT DISTINCT ON (dev, time) dev, time FROM :TABLE WHERE dev IS NOT NULL; 1229 QUERY PLAN 1230--------------------------------------------------------------------------------------------- 1231 Unique (actual rows=10001 loops=1) 1232 -> Index Only Scan using skip_scan_dev_time_idx on skip_scan (actual rows=10001 loops=1) 1233 Index Cond: (dev IS NOT NULL) 1234 Heap Fetches: 10001 1235(4 rows) 1236 1237:PREFIX SELECT DISTINCT ON (dev, time) dev, time FROM :TABLE WHERE dev IS NOT NULL 1238UNION SELECT b.* FROM 1239 (SELECT DISTINCT ON (dev) dev FROM :TABLE) a, 1240 LATERAL (SELECT DISTINCT ON (time) dev, time FROM :TABLE WHERE dev = a.dev) b; 1241 QUERY PLAN 1242-------------------------------------------------------------------------------------------------------------------------------------- 1243 Unique (actual rows=10001 loops=1) 1244 -> Sort (actual rows=20002 loops=1) 1245 Sort Key: skip_scan.dev, skip_scan."time" 1246 Sort Method: quicksort 1247 -> Append (actual rows=20002 loops=1) 1248 -> Unique (actual rows=10001 loops=1) 1249 -> Index Only Scan using skip_scan_dev_time_idx on skip_scan (actual rows=10001 loops=1) 1250 Index Cond: (dev IS NOT NULL) 1251 Heap Fetches: 10001 1252 -> Nested Loop (actual rows=10001 loops=1) 1253 -> Unique (actual rows=12 loops=1) 1254 -> Custom Scan (SkipScan) on skip_scan skip_scan_1 (actual rows=12 loops=1) 1255 -> Index Only Scan using skip_scan_dev_idx on skip_scan skip_scan_1 (actual rows=12 loops=1) 1256 Index Cond: (dev > NULL::integer) 1257 Heap Fetches: 12 1258 -> Unique (actual rows=833 loops=12) 1259 -> Custom Scan (SkipScan) on skip_scan skip_scan_2 (actual rows=833 loops=12) 1260 -> Index Only Scan using skip_scan_dev_time_idx on skip_scan skip_scan_2 (actual rows=833 loops=12) 1261 Index Cond: ((dev = skip_scan_1.dev) AND ("time" > NULL::integer)) 1262 Heap Fetches: 10001 1263(20 rows) 1264 1265-- SkipScan into INSERT 1266:PREFIX INSERT INTO skip_scan_insert(time, dev, val, query) SELECT time, dev, val, 'q10_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a; 1267 QUERY PLAN 1268-------------------------------------------------------------------------------------------------------- 1269 Insert on skip_scan_insert (actual rows=0 loops=1) 1270 -> Subquery Scan on a (actual rows=12 loops=1) 1271 -> Result (actual rows=12 loops=1) 1272 -> Unique (actual rows=12 loops=1) 1273 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 1274 -> Index Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 1275(6 rows) 1276 1277-- parallel query 1278SET force_parallel_mode TO true; 1279:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev; 1280 QUERY PLAN 1281------------------------------------------------------------------------------------------- 1282 Unique (actual rows=12 loops=1) 1283 -> Custom Scan (SkipScan) on skip_scan (actual rows=12 loops=1) 1284 -> Index Only Scan using skip_scan_dev_idx on skip_scan (actual rows=12 loops=1) 1285 Index Cond: (dev > NULL::integer) 1286 Heap Fetches: 12 1287(5 rows) 1288 1289RESET force_parallel_mode; 1290TRUNCATE skip_scan_insert; 1291-- table with only nulls 1292:PREFIX SELECT DISTINCT ON (time) time FROM skip_scan_nulls; 1293 QUERY PLAN 1294------------------------------------------------------------------------------------------------------- 1295 Unique (actual rows=1 loops=1) 1296 -> Custom Scan (SkipScan) on skip_scan_nulls (actual rows=1 loops=1) 1297 -> Index Only Scan using skip_scan_nulls_time_idx on skip_scan_nulls (actual rows=1 loops=1) 1298 Index Cond: ("time" > NULL::integer) 1299 Heap Fetches: 1 1300(5 rows) 1301 1302-- no tuples in resultset 1303:PREFIX SELECT DISTINCT ON (time) time FROM skip_scan_nulls WHERE time IS NOT NULL; 1304 QUERY PLAN 1305------------------------------------------------------------------------------------------------------- 1306 Unique (actual rows=0 loops=1) 1307 -> Custom Scan (SkipScan) on skip_scan_nulls (actual rows=0 loops=1) 1308 -> Index Only Scan using skip_scan_nulls_time_idx on skip_scan_nulls (actual rows=0 loops=1) 1309 Index Cond: (("time" > NULL::integer) AND ("time" IS NOT NULL)) 1310 Heap Fetches: 0 1311(5 rows) 1312 1313\set TABLE skip_scan_ht 1314\ir include/skip_scan_query.sql 1315-- This file and its contents are licensed under the Timescale License. 1316-- Please see the included NOTICE for copyright information and 1317-- LICENSE-TIMESCALE for a copy of the license. 1318-- canary for result diff 1319SELECT current_setting('timescaledb.enable_skipscan') AS enable_skipscan; 1320 enable_skipscan 1321----------------- 1322 on 1323(1 row) 1324 1325-- test different index configurations 1326-- no index so we cant do SkipScan 1327:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev; 1328 QUERY PLAN 1329--------------------------------------------------------------------------- 1330 Sort (actual rows=11 loops=1) 1331 Sort Key: _hyper_1_1_chunk.dev 1332 Sort Method: quicksort 1333 -> HashAggregate (actual rows=11 loops=1) 1334 Group Key: _hyper_1_1_chunk.dev 1335 Batches: 1 1336 -> Append (actual rows=10020 loops=1) 1337 -> Seq Scan on _hyper_1_1_chunk (actual rows=2505 loops=1) 1338 -> Seq Scan on _hyper_1_2_chunk (actual rows=2505 loops=1) 1339 -> Seq Scan on _hyper_1_3_chunk (actual rows=2505 loops=1) 1340 -> Seq Scan on _hyper_1_4_chunk (actual rows=2505 loops=1) 1341(11 rows) 1342 1343-- NULLS LAST index on dev 1344CREATE INDEX skip_scan_idx_dev_nulls_last ON :TABLE(dev); 1345:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev; 1346 QUERY PLAN 1347------------------------------------------------------------------------------------------------------------------------------------ 1348 Unique (actual rows=11 loops=1) 1349 -> Merge Append (actual rows=44 loops=1) 1350 Sort Key: _hyper_1_1_chunk.dev 1351 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 1352 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_idx_dev_nulls_last on _hyper_1_1_chunk (actual rows=11 loops=1) 1353 Index Cond: (dev > NULL::integer) 1354 Heap Fetches: 11 1355 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 1356 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_idx_dev_nulls_last on _hyper_1_2_chunk (actual rows=11 loops=1) 1357 Index Cond: (dev > NULL::integer) 1358 Heap Fetches: 11 1359 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 1360 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_idx_dev_nulls_last on _hyper_1_3_chunk (actual rows=11 loops=1) 1361 Index Cond: (dev > NULL::integer) 1362 Heap Fetches: 11 1363 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 1364 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_idx_dev_nulls_last on _hyper_1_4_chunk (actual rows=11 loops=1) 1365 Index Cond: (dev > NULL::integer) 1366 Heap Fetches: 11 1367(19 rows) 1368 1369:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev DESC; 1370 QUERY PLAN 1371--------------------------------------------------------------------------------------------------------------------------------------------- 1372 Unique (actual rows=11 loops=1) 1373 -> Merge Append (actual rows=44 loops=1) 1374 Sort Key: _hyper_1_1_chunk.dev DESC 1375 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 1376 -> Index Only Scan Backward using _hyper_1_1_chunk_skip_scan_idx_dev_nulls_last on _hyper_1_1_chunk (actual rows=11 loops=1) 1377 Index Cond: (dev < NULL::integer) 1378 Heap Fetches: 11 1379 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 1380 -> Index Only Scan Backward using _hyper_1_2_chunk_skip_scan_idx_dev_nulls_last on _hyper_1_2_chunk (actual rows=11 loops=1) 1381 Index Cond: (dev < NULL::integer) 1382 Heap Fetches: 11 1383 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 1384 -> Index Only Scan Backward using _hyper_1_3_chunk_skip_scan_idx_dev_nulls_last on _hyper_1_3_chunk (actual rows=11 loops=1) 1385 Index Cond: (dev < NULL::integer) 1386 Heap Fetches: 11 1387 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 1388 -> Index Only Scan Backward using _hyper_1_4_chunk_skip_scan_idx_dev_nulls_last on _hyper_1_4_chunk (actual rows=11 loops=1) 1389 Index Cond: (dev < NULL::integer) 1390 Heap Fetches: 11 1391(19 rows) 1392 1393:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE; 1394 QUERY PLAN 1395------------------------------------------------------------------------------------------------------------------------------------ 1396 Unique (actual rows=11 loops=1) 1397 -> Merge Append (actual rows=44 loops=1) 1398 Sort Key: _hyper_1_1_chunk.dev 1399 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 1400 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_idx_dev_nulls_last on _hyper_1_1_chunk (actual rows=11 loops=1) 1401 Index Cond: (dev > NULL::integer) 1402 Heap Fetches: 11 1403 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 1404 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_idx_dev_nulls_last on _hyper_1_2_chunk (actual rows=11 loops=1) 1405 Index Cond: (dev > NULL::integer) 1406 Heap Fetches: 11 1407 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 1408 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_idx_dev_nulls_last on _hyper_1_3_chunk (actual rows=11 loops=1) 1409 Index Cond: (dev > NULL::integer) 1410 Heap Fetches: 11 1411 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 1412 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_idx_dev_nulls_last on _hyper_1_4_chunk (actual rows=11 loops=1) 1413 Index Cond: (dev > NULL::integer) 1414 Heap Fetches: 11 1415(19 rows) 1416 1417DROP INDEX skip_scan_idx_dev_nulls_last; 1418-- NULLS FIRST index on dev 1419CREATE INDEX skip_scan_idx_dev_nulls_first ON :TABLE(dev NULLS FIRST); 1420:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev NULLS FIRST; 1421 QUERY PLAN 1422------------------------------------------------------------------------------------------------------------------------------------- 1423 Unique (actual rows=11 loops=1) 1424 -> Merge Append (actual rows=44 loops=1) 1425 Sort Key: _hyper_1_1_chunk.dev NULLS FIRST 1426 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 1427 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_idx_dev_nulls_first on _hyper_1_1_chunk (actual rows=11 loops=1) 1428 Index Cond: (dev > NULL::integer) 1429 Heap Fetches: 11 1430 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 1431 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_idx_dev_nulls_first on _hyper_1_2_chunk (actual rows=11 loops=1) 1432 Index Cond: (dev > NULL::integer) 1433 Heap Fetches: 11 1434 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 1435 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_idx_dev_nulls_first on _hyper_1_3_chunk (actual rows=11 loops=1) 1436 Index Cond: (dev > NULL::integer) 1437 Heap Fetches: 11 1438 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 1439 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_idx_dev_nulls_first on _hyper_1_4_chunk (actual rows=11 loops=1) 1440 Index Cond: (dev > NULL::integer) 1441 Heap Fetches: 11 1442(19 rows) 1443 1444:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev NULLS FIRST; 1445 QUERY PLAN 1446------------------------------------------------------------------------------------------------------------------------------------- 1447 Unique (actual rows=11 loops=1) 1448 -> Merge Append (actual rows=44 loops=1) 1449 Sort Key: _hyper_1_1_chunk.dev NULLS FIRST 1450 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 1451 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_idx_dev_nulls_first on _hyper_1_1_chunk (actual rows=11 loops=1) 1452 Index Cond: (dev > NULL::integer) 1453 Heap Fetches: 11 1454 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 1455 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_idx_dev_nulls_first on _hyper_1_2_chunk (actual rows=11 loops=1) 1456 Index Cond: (dev > NULL::integer) 1457 Heap Fetches: 11 1458 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 1459 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_idx_dev_nulls_first on _hyper_1_3_chunk (actual rows=11 loops=1) 1460 Index Cond: (dev > NULL::integer) 1461 Heap Fetches: 11 1462 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 1463 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_idx_dev_nulls_first on _hyper_1_4_chunk (actual rows=11 loops=1) 1464 Index Cond: (dev > NULL::integer) 1465 Heap Fetches: 11 1466(19 rows) 1467 1468DROP INDEX skip_scan_idx_dev_nulls_first; 1469-- multicolumn index with dev as leading column 1470CREATE INDEX skip_scan_idx_dev_time_idx ON :TABLE(dev, time); 1471:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev; 1472 QUERY PLAN 1473---------------------------------------------------------------------------------------------------------------------------------- 1474 Unique (actual rows=11 loops=1) 1475 -> Merge Append (actual rows=44 loops=1) 1476 Sort Key: _hyper_1_1_chunk.dev 1477 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 1478 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_idx_dev_time_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 1479 Index Cond: (dev > NULL::integer) 1480 Heap Fetches: 11 1481 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 1482 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_idx_dev_time_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 1483 Index Cond: (dev > NULL::integer) 1484 Heap Fetches: 11 1485 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 1486 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_idx_dev_time_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 1487 Index Cond: (dev > NULL::integer) 1488 Heap Fetches: 11 1489 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 1490 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_idx_dev_time_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 1491 Index Cond: (dev > NULL::integer) 1492 Heap Fetches: 11 1493(19 rows) 1494 1495:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE; 1496 QUERY PLAN 1497---------------------------------------------------------------------------------------------------------------------------------- 1498 Unique (actual rows=11 loops=1) 1499 -> Merge Append (actual rows=44 loops=1) 1500 Sort Key: _hyper_1_1_chunk.dev 1501 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 1502 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_idx_dev_time_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 1503 Index Cond: (dev > NULL::integer) 1504 Heap Fetches: 11 1505 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 1506 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_idx_dev_time_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 1507 Index Cond: (dev > NULL::integer) 1508 Heap Fetches: 11 1509 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 1510 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_idx_dev_time_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 1511 Index Cond: (dev > NULL::integer) 1512 Heap Fetches: 11 1513 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 1514 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_idx_dev_time_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 1515 Index Cond: (dev > NULL::integer) 1516 Heap Fetches: 11 1517(19 rows) 1518 1519:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev DESC, time DESC; 1520 QUERY PLAN 1521------------------------------------------------------------------------------------------------------------------------------------------- 1522 Unique (actual rows=11 loops=1) 1523 -> Merge Append (actual rows=44 loops=1) 1524 Sort Key: _hyper_1_1_chunk.dev DESC, _hyper_1_1_chunk."time" DESC 1525 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 1526 -> Index Only Scan Backward using _hyper_1_1_chunk_skip_scan_idx_dev_time_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 1527 Index Cond: (dev < NULL::integer) 1528 Heap Fetches: 11 1529 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 1530 -> Index Only Scan Backward using _hyper_1_2_chunk_skip_scan_idx_dev_time_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 1531 Index Cond: (dev < NULL::integer) 1532 Heap Fetches: 11 1533 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 1534 -> Index Only Scan Backward using _hyper_1_3_chunk_skip_scan_idx_dev_time_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 1535 Index Cond: (dev < NULL::integer) 1536 Heap Fetches: 11 1537 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 1538 -> Index Only Scan Backward using _hyper_1_4_chunk_skip_scan_idx_dev_time_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 1539 Index Cond: (dev < NULL::integer) 1540 Heap Fetches: 11 1541(19 rows) 1542 1543DROP INDEX skip_scan_idx_dev_time_idx; 1544-- multicolumn index with dev as non-leading column 1545CREATE INDEX skip_scan_idx_time_dev_idx ON :TABLE(time, dev); 1546:PREFIX SELECT DISTINCT dev FROM :TABLE WHERE time = 100 ORDER BY dev; 1547 QUERY PLAN 1548---------------------------------------------------------------------------------------------------------------------- 1549 Unique (actual rows=11 loops=1) 1550 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_idx_time_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 1551 Index Cond: ("time" = 100) 1552 Heap Fetches: 11 1553(4 rows) 1554 1555:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time = 100; 1556 QUERY PLAN 1557---------------------------------------------------------------------------------------------------------------------- 1558 Unique (actual rows=11 loops=1) 1559 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_idx_time_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 1560 Index Cond: ("time" = 100) 1561 Heap Fetches: 11 1562(4 rows) 1563 1564DROP INDEX skip_scan_idx_time_dev_idx; 1565-- hash index is not ordered so can't use skipscan 1566CREATE INDEX skip_scan_idx_hash ON :TABLE USING hash(dev_name); 1567:PREFIX SELECT DISTINCT dev_name FROM :TABLE WHERE dev_name IN ('device_1','device_2') ORDER BY dev_name; 1568 QUERY PLAN 1569------------------------------------------------------------------------------------------------------------ 1570 Sort (actual rows=2 loops=1) 1571 Sort Key: _hyper_1_1_chunk.dev_name 1572 Sort Method: quicksort 1573 -> HashAggregate (actual rows=2 loops=1) 1574 Group Key: _hyper_1_1_chunk.dev_name 1575 Batches: 1 1576 -> Append (actual rows=2000 loops=1) 1577 -> Bitmap Heap Scan on _hyper_1_1_chunk (actual rows=500 loops=1) 1578 Recheck Cond: (dev_name = ANY ('{device_1,device_2}'::text[])) 1579 Heap Blocks: exact=4 1580 -> Bitmap Index Scan on _hyper_1_1_chunk_skip_scan_idx_hash (actual rows=500 loops=1) 1581 Index Cond: (dev_name = ANY ('{device_1,device_2}'::text[])) 1582 -> Bitmap Heap Scan on _hyper_1_2_chunk (actual rows=500 loops=1) 1583 Recheck Cond: (dev_name = ANY ('{device_1,device_2}'::text[])) 1584 Heap Blocks: exact=4 1585 -> Bitmap Index Scan on _hyper_1_2_chunk_skip_scan_idx_hash (actual rows=500 loops=1) 1586 Index Cond: (dev_name = ANY ('{device_1,device_2}'::text[])) 1587 -> Bitmap Heap Scan on _hyper_1_3_chunk (actual rows=500 loops=1) 1588 Recheck Cond: (dev_name = ANY ('{device_1,device_2}'::text[])) 1589 Heap Blocks: exact=4 1590 -> Bitmap Index Scan on _hyper_1_3_chunk_skip_scan_idx_hash (actual rows=500 loops=1) 1591 Index Cond: (dev_name = ANY ('{device_1,device_2}'::text[])) 1592 -> Bitmap Heap Scan on _hyper_1_4_chunk (actual rows=500 loops=1) 1593 Recheck Cond: (dev_name = ANY ('{device_1,device_2}'::text[])) 1594 Heap Blocks: exact=4 1595 -> Bitmap Index Scan on _hyper_1_4_chunk_skip_scan_idx_hash (actual rows=500 loops=1) 1596 Index Cond: (dev_name = ANY ('{device_1,device_2}'::text[])) 1597(27 rows) 1598 1599DROP INDEX skip_scan_idx_hash; 1600-- expression indexes 1601-- currently not supported by skipscan 1602CREATE INDEX skip_scan_expr_idx ON :TABLE((dev % 3)); 1603:PREFIX SELECT DISTINCT dev%3 FROM :TABLE ORDER BY dev%3; 1604 QUERY PLAN 1605--------------------------------------------------------------------------------- 1606 Sort (actual rows=4 loops=1) 1607 Sort Key: ((_hyper_1_1_chunk.dev % 3)) 1608 Sort Method: quicksort 1609 -> HashAggregate (actual rows=4 loops=1) 1610 Group Key: (_hyper_1_1_chunk.dev % 3) 1611 Batches: 1 1612 -> Result (actual rows=10020 loops=1) 1613 -> Append (actual rows=10020 loops=1) 1614 -> Seq Scan on _hyper_1_1_chunk (actual rows=2505 loops=1) 1615 -> Seq Scan on _hyper_1_2_chunk (actual rows=2505 loops=1) 1616 -> Seq Scan on _hyper_1_3_chunk (actual rows=2505 loops=1) 1617 -> Seq Scan on _hyper_1_4_chunk (actual rows=2505 loops=1) 1618(12 rows) 1619 1620:PREFIX SELECT DISTINCT ON (dev%3) dev FROM :TABLE ORDER BY dev%3; 1621 QUERY PLAN 1622----------------------------------------------------------------------------------------------------------------- 1623 Unique (actual rows=4 loops=1) 1624 -> Merge Append (actual rows=10020 loops=1) 1625 Sort Key: ((_hyper_1_1_chunk.dev % 3)) 1626 -> Index Scan using _hyper_1_1_chunk_skip_scan_expr_idx on _hyper_1_1_chunk (actual rows=2505 loops=1) 1627 -> Index Scan using _hyper_1_2_chunk_skip_scan_expr_idx on _hyper_1_2_chunk (actual rows=2505 loops=1) 1628 -> Index Scan using _hyper_1_3_chunk_skip_scan_expr_idx on _hyper_1_3_chunk (actual rows=2505 loops=1) 1629 -> Index Scan using _hyper_1_4_chunk_skip_scan_expr_idx on _hyper_1_4_chunk (actual rows=2505 loops=1) 1630(7 rows) 1631 1632DROP INDEX skip_scan_expr_idx; 1633CREATE INDEX ON :TABLE(dev_name); 1634CREATE INDEX ON :TABLE(dev); 1635CREATE INDEX ON :TABLE(dev, time); 1636CREATE INDEX ON :TABLE(time,dev); 1637CREATE INDEX ON :TABLE(time,dev,val); 1638\qecho basic DISTINCT queries on :TABLE 1639basic DISTINCT queries on skip_scan_ht 1640:PREFIX SELECT DISTINCT dev, 'q1_1' FROM :TABLE ORDER BY dev; 1641 QUERY PLAN 1642---------------------------------------------------------------------------------------------------------------------------------- 1643 Result (actual rows=11 loops=1) 1644 -> Unique (actual rows=11 loops=1) 1645 -> Merge Append (actual rows=44 loops=1) 1646 Sort Key: _hyper_1_1_chunk.dev 1647 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 1648 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 1649 Index Cond: (dev > NULL::integer) 1650 Heap Fetches: 11 1651 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 1652 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 1653 Index Cond: (dev > NULL::integer) 1654 Heap Fetches: 11 1655 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 1656 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 1657 Index Cond: (dev > NULL::integer) 1658 Heap Fetches: 11 1659 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 1660 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 1661 Index Cond: (dev > NULL::integer) 1662 Heap Fetches: 11 1663(20 rows) 1664 1665:PREFIX SELECT DISTINCT dev_name, 'q1_2' FROM :TABLE ORDER BY dev_name; 1666 QUERY PLAN 1667--------------------------------------------------------------------------------------------------------------------------------------- 1668 Result (actual rows=11 loops=1) 1669 -> Unique (actual rows=11 loops=1) 1670 -> Merge Append (actual rows=44 loops=1) 1671 Sort Key: _hyper_1_1_chunk.dev_name 1672 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 1673 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_name_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 1674 Index Cond: (dev_name > NULL::text) 1675 Heap Fetches: 11 1676 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 1677 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_name_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 1678 Index Cond: (dev_name > NULL::text) 1679 Heap Fetches: 11 1680 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 1681 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_name_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 1682 Index Cond: (dev_name > NULL::text) 1683 Heap Fetches: 11 1684 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 1685 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_name_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 1686 Index Cond: (dev_name > NULL::text) 1687 Heap Fetches: 11 1688(20 rows) 1689 1690:PREFIX SELECT DISTINCT dev, 'q1_3', NULL FROM :TABLE ORDER BY dev; 1691 QUERY PLAN 1692---------------------------------------------------------------------------------------------------------------------------------- 1693 Result (actual rows=11 loops=1) 1694 -> Unique (actual rows=11 loops=1) 1695 -> Merge Append (actual rows=44 loops=1) 1696 Sort Key: _hyper_1_1_chunk.dev 1697 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 1698 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 1699 Index Cond: (dev > NULL::integer) 1700 Heap Fetches: 11 1701 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 1702 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 1703 Index Cond: (dev > NULL::integer) 1704 Heap Fetches: 11 1705 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 1706 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 1707 Index Cond: (dev > NULL::integer) 1708 Heap Fetches: 11 1709 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 1710 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 1711 Index Cond: (dev > NULL::integer) 1712 Heap Fetches: 11 1713(20 rows) 1714 1715\qecho stable expression in targetlist on :TABLE 1716stable expression in targetlist on skip_scan_ht 1717:PREFIX SELECT DISTINCT dev, 'q1_4', length(md5(now()::text)) FROM :TABLE ORDER BY dev; 1718 QUERY PLAN 1719---------------------------------------------------------------------------------------------------------------------------------- 1720 Result (actual rows=11 loops=1) 1721 -> Unique (actual rows=11 loops=1) 1722 -> Merge Append (actual rows=44 loops=1) 1723 Sort Key: _hyper_1_1_chunk.dev 1724 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 1725 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 1726 Index Cond: (dev > NULL::integer) 1727 Heap Fetches: 11 1728 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 1729 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 1730 Index Cond: (dev > NULL::integer) 1731 Heap Fetches: 11 1732 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 1733 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 1734 Index Cond: (dev > NULL::integer) 1735 Heap Fetches: 11 1736 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 1737 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 1738 Index Cond: (dev > NULL::integer) 1739 Heap Fetches: 11 1740(20 rows) 1741 1742:PREFIX SELECT DISTINCT dev_name, 'q1_5', length(md5(now()::text)) FROM :TABLE ORDER BY dev_name; 1743 QUERY PLAN 1744--------------------------------------------------------------------------------------------------------------------------------------- 1745 Result (actual rows=11 loops=1) 1746 -> Unique (actual rows=11 loops=1) 1747 -> Merge Append (actual rows=44 loops=1) 1748 Sort Key: _hyper_1_1_chunk.dev_name 1749 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 1750 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_name_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 1751 Index Cond: (dev_name > NULL::text) 1752 Heap Fetches: 11 1753 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 1754 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_name_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 1755 Index Cond: (dev_name > NULL::text) 1756 Heap Fetches: 11 1757 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 1758 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_name_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 1759 Index Cond: (dev_name > NULL::text) 1760 Heap Fetches: 11 1761 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 1762 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_name_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 1763 Index Cond: (dev_name > NULL::text) 1764 Heap Fetches: 11 1765(20 rows) 1766 1767-- volatile expression in targetlist 1768:PREFIX SELECT DISTINCT dev, 'q1_6', length(md5(random()::text)) FROM :TABLE ORDER BY dev; 1769 QUERY PLAN 1770--------------------------------------------------------------------------------- 1771 Unique (actual rows=11 loops=1) 1772 -> Sort (actual rows=10020 loops=1) 1773 Sort Key: _hyper_1_1_chunk.dev, (length(md5((random())::text))) 1774 Sort Method: quicksort 1775 -> Result (actual rows=10020 loops=1) 1776 -> Append (actual rows=10020 loops=1) 1777 -> Seq Scan on _hyper_1_1_chunk (actual rows=2505 loops=1) 1778 -> Seq Scan on _hyper_1_2_chunk (actual rows=2505 loops=1) 1779 -> Seq Scan on _hyper_1_3_chunk (actual rows=2505 loops=1) 1780 -> Seq Scan on _hyper_1_4_chunk (actual rows=2505 loops=1) 1781(10 rows) 1782 1783:PREFIX SELECT DISTINCT dev_name, 'q1_7', length(md5(random()::text)) FROM :TABLE ORDER BY dev_name; 1784 QUERY PLAN 1785--------------------------------------------------------------------------------- 1786 Unique (actual rows=11 loops=1) 1787 -> Sort (actual rows=10020 loops=1) 1788 Sort Key: _hyper_1_1_chunk.dev_name, (length(md5((random())::text))) 1789 Sort Method: quicksort 1790 -> Result (actual rows=10020 loops=1) 1791 -> Append (actual rows=10020 loops=1) 1792 -> Seq Scan on _hyper_1_1_chunk (actual rows=2505 loops=1) 1793 -> Seq Scan on _hyper_1_2_chunk (actual rows=2505 loops=1) 1794 -> Seq Scan on _hyper_1_3_chunk (actual rows=2505 loops=1) 1795 -> Seq Scan on _hyper_1_4_chunk (actual rows=2505 loops=1) 1796(10 rows) 1797 1798-- queries without skipscan because distinct is not limited to specific column 1799:PREFIX SELECT DISTINCT * FROM :TABLE ORDER BY dev; 1800 QUERY PLAN 1801------------------------------------------------------------------------------------------------------------------- 1802 Sort (actual rows=10020 loops=1) 1803 Sort Key: _hyper_1_1_chunk.dev 1804 Sort Method: quicksort 1805 -> HashAggregate (actual rows=10020 loops=1) 1806 Group Key: _hyper_1_1_chunk.dev, _hyper_1_1_chunk."time", _hyper_1_1_chunk.dev_name, _hyper_1_1_chunk.val 1807 Batches: 1 1808 -> Append (actual rows=10020 loops=1) 1809 -> Seq Scan on _hyper_1_1_chunk (actual rows=2505 loops=1) 1810 -> Seq Scan on _hyper_1_2_chunk (actual rows=2505 loops=1) 1811 -> Seq Scan on _hyper_1_3_chunk (actual rows=2505 loops=1) 1812 -> Seq Scan on _hyper_1_4_chunk (actual rows=2505 loops=1) 1813(11 rows) 1814 1815:PREFIX SELECT DISTINCT *, 'q1_9' FROM :TABLE ORDER BY dev; 1816 QUERY PLAN 1817--------------------------------------------------------------------------------------------------------------------------------- 1818 Sort (actual rows=10020 loops=1) 1819 Sort Key: _hyper_1_1_chunk.dev 1820 Sort Method: quicksort 1821 -> HashAggregate (actual rows=10020 loops=1) 1822 Group Key: _hyper_1_1_chunk.dev, _hyper_1_1_chunk."time", _hyper_1_1_chunk.dev_name, _hyper_1_1_chunk.val, 'q1_9'::text 1823 Batches: 1 1824 -> Result (actual rows=10020 loops=1) 1825 -> Append (actual rows=10020 loops=1) 1826 -> Seq Scan on _hyper_1_1_chunk (actual rows=2505 loops=1) 1827 -> Seq Scan on _hyper_1_2_chunk (actual rows=2505 loops=1) 1828 -> Seq Scan on _hyper_1_3_chunk (actual rows=2505 loops=1) 1829 -> Seq Scan on _hyper_1_4_chunk (actual rows=2505 loops=1) 1830(12 rows) 1831 1832:PREFIX SELECT DISTINCT dev, time, 'q1_10' FROM :TABLE ORDER BY dev; 1833 QUERY PLAN 1834--------------------------------------------------------------------------------- 1835 Sort (actual rows=10020 loops=1) 1836 Sort Key: _hyper_1_1_chunk.dev 1837 Sort Method: quicksort 1838 -> HashAggregate (actual rows=10020 loops=1) 1839 Group Key: _hyper_1_1_chunk.dev, _hyper_1_1_chunk."time", 'q1_10'::text 1840 Batches: 1 1841 -> Result (actual rows=10020 loops=1) 1842 -> Append (actual rows=10020 loops=1) 1843 -> Seq Scan on _hyper_1_1_chunk (actual rows=2505 loops=1) 1844 -> Seq Scan on _hyper_1_2_chunk (actual rows=2505 loops=1) 1845 -> Seq Scan on _hyper_1_3_chunk (actual rows=2505 loops=1) 1846 -> Seq Scan on _hyper_1_4_chunk (actual rows=2505 loops=1) 1847(12 rows) 1848 1849:PREFIX SELECT DISTINCT dev, NULL, 'q1_11' FROM :TABLE ORDER BY dev; 1850 QUERY PLAN 1851---------------------------------------------------------------------------------------------------------------------------------- 1852 Result (actual rows=11 loops=1) 1853 -> Unique (actual rows=11 loops=1) 1854 -> Merge Append (actual rows=44 loops=1) 1855 Sort Key: _hyper_1_1_chunk.dev 1856 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 1857 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 1858 Index Cond: (dev > NULL::integer) 1859 Heap Fetches: 11 1860 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 1861 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 1862 Index Cond: (dev > NULL::integer) 1863 Heap Fetches: 11 1864 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 1865 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 1866 Index Cond: (dev > NULL::integer) 1867 Heap Fetches: 11 1868 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 1869 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 1870 Index Cond: (dev > NULL::integer) 1871 Heap Fetches: 11 1872(20 rows) 1873 1874-- distinct on expressions not supported 1875:PREFIX SELECT DISTINCT time_bucket(10,time), 'q1_12' FROM :TABLE; 1876 QUERY PLAN 1877--------------------------------------------------------------------------- 1878 HashAggregate (actual rows=100 loops=1) 1879 Group Key: time_bucket(10, _hyper_1_1_chunk."time"), 'q1_12'::text 1880 Batches: 1 1881 -> Result (actual rows=10020 loops=1) 1882 -> Append (actual rows=10020 loops=1) 1883 -> Seq Scan on _hyper_1_1_chunk (actual rows=2505 loops=1) 1884 -> Seq Scan on _hyper_1_2_chunk (actual rows=2505 loops=1) 1885 -> Seq Scan on _hyper_1_3_chunk (actual rows=2505 loops=1) 1886 -> Seq Scan on _hyper_1_4_chunk (actual rows=2505 loops=1) 1887(9 rows) 1888 1889:PREFIX SELECT DISTINCT length(dev_name), 'q1_13' FROM :TABLE; 1890 QUERY PLAN 1891--------------------------------------------------------------------------- 1892 HashAggregate (actual rows=3 loops=1) 1893 Group Key: length(_hyper_1_1_chunk.dev_name), 'q1_13'::text 1894 Batches: 1 1895 -> Result (actual rows=10020 loops=1) 1896 -> Append (actual rows=10020 loops=1) 1897 -> Seq Scan on _hyper_1_1_chunk (actual rows=2505 loops=1) 1898 -> Seq Scan on _hyper_1_2_chunk (actual rows=2505 loops=1) 1899 -> Seq Scan on _hyper_1_3_chunk (actual rows=2505 loops=1) 1900 -> Seq Scan on _hyper_1_4_chunk (actual rows=2505 loops=1) 1901(9 rows) 1902 1903:PREFIX SELECT DISTINCT 3*time, 'q1_14' FROM :TABLE; 1904 QUERY PLAN 1905--------------------------------------------------------------------------- 1906 HashAggregate (actual rows=1000 loops=1) 1907 Group Key: (3 * _hyper_1_1_chunk."time"), 'q1_14'::text 1908 Batches: 1 1909 -> Result (actual rows=10020 loops=1) 1910 -> Append (actual rows=10020 loops=1) 1911 -> Seq Scan on _hyper_1_1_chunk (actual rows=2505 loops=1) 1912 -> Seq Scan on _hyper_1_2_chunk (actual rows=2505 loops=1) 1913 -> Seq Scan on _hyper_1_3_chunk (actual rows=2505 loops=1) 1914 -> Seq Scan on _hyper_1_4_chunk (actual rows=2505 loops=1) 1915(9 rows) 1916 1917:PREFIX SELECT DISTINCT 'Device ' || dev_name FROM :TABLE; 1918 QUERY PLAN 1919--------------------------------------------------------------------------- 1920 HashAggregate (actual rows=11 loops=1) 1921 Group Key: ('Device '::text || _hyper_1_1_chunk.dev_name) 1922 Batches: 1 1923 -> Result (actual rows=10020 loops=1) 1924 -> Append (actual rows=10020 loops=1) 1925 -> Seq Scan on _hyper_1_1_chunk (actual rows=2505 loops=1) 1926 -> Seq Scan on _hyper_1_2_chunk (actual rows=2505 loops=1) 1927 -> Seq Scan on _hyper_1_3_chunk (actual rows=2505 loops=1) 1928 -> Seq Scan on _hyper_1_4_chunk (actual rows=2505 loops=1) 1929(9 rows) 1930 1931-- DISTINCT ON queries 1932:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE; 1933 QUERY PLAN 1934---------------------------------------------------------------------------------------------------------------------------- 1935 Unique (actual rows=11 loops=1) 1936 -> Merge Append (actual rows=44 loops=1) 1937 Sort Key: _hyper_1_1_chunk.dev 1938 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 1939 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 1940 Index Cond: (dev > NULL::integer) 1941 Heap Fetches: 11 1942 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 1943 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 1944 Index Cond: (dev > NULL::integer) 1945 Heap Fetches: 11 1946 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 1947 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 1948 Index Cond: (dev > NULL::integer) 1949 Heap Fetches: 11 1950 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 1951 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 1952 Index Cond: (dev > NULL::integer) 1953 Heap Fetches: 11 1954(19 rows) 1955 1956:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_2' FROM :TABLE; 1957 QUERY PLAN 1958---------------------------------------------------------------------------------------------------------------------------------- 1959 Result (actual rows=11 loops=1) 1960 -> Unique (actual rows=11 loops=1) 1961 -> Merge Append (actual rows=44 loops=1) 1962 Sort Key: _hyper_1_1_chunk.dev 1963 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 1964 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 1965 Index Cond: (dev > NULL::integer) 1966 Heap Fetches: 11 1967 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 1968 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 1969 Index Cond: (dev > NULL::integer) 1970 Heap Fetches: 11 1971 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 1972 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 1973 Index Cond: (dev > NULL::integer) 1974 Heap Fetches: 11 1975 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 1976 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 1977 Index Cond: (dev > NULL::integer) 1978 Heap Fetches: 11 1979(20 rows) 1980 1981:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_3', NULL FROM :TABLE; 1982 QUERY PLAN 1983---------------------------------------------------------------------------------------------------------------------------------- 1984 Result (actual rows=11 loops=1) 1985 -> Unique (actual rows=11 loops=1) 1986 -> Merge Append (actual rows=44 loops=1) 1987 Sort Key: _hyper_1_1_chunk.dev 1988 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 1989 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 1990 Index Cond: (dev > NULL::integer) 1991 Heap Fetches: 11 1992 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 1993 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 1994 Index Cond: (dev > NULL::integer) 1995 Heap Fetches: 11 1996 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 1997 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 1998 Index Cond: (dev > NULL::integer) 1999 Heap Fetches: 11 2000 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2001 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2002 Index Cond: (dev > NULL::integer) 2003 Heap Fetches: 11 2004(20 rows) 2005 2006:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_4', length(md5(now()::text)) FROM :TABLE; 2007 QUERY PLAN 2008---------------------------------------------------------------------------------------------------------------------------------- 2009 Result (actual rows=11 loops=1) 2010 -> Unique (actual rows=11 loops=1) 2011 -> Merge Append (actual rows=44 loops=1) 2012 Sort Key: _hyper_1_1_chunk.dev 2013 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2014 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2015 Index Cond: (dev > NULL::integer) 2016 Heap Fetches: 11 2017 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2018 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2019 Index Cond: (dev > NULL::integer) 2020 Heap Fetches: 11 2021 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2022 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2023 Index Cond: (dev > NULL::integer) 2024 Heap Fetches: 11 2025 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2026 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2027 Index Cond: (dev > NULL::integer) 2028 Heap Fetches: 11 2029(20 rows) 2030 2031:PREFIX SELECT DISTINCT ON (dev) dev, 'q2_5', length(md5(random()::text)) FROM :TABLE; 2032 QUERY PLAN 2033---------------------------------------------------------------------------------------------------------------------------------- 2034 Result (actual rows=11 loops=1) 2035 -> Unique (actual rows=11 loops=1) 2036 -> Merge Append (actual rows=44 loops=1) 2037 Sort Key: _hyper_1_1_chunk.dev 2038 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2039 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2040 Index Cond: (dev > NULL::integer) 2041 Heap Fetches: 11 2042 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2043 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2044 Index Cond: (dev > NULL::integer) 2045 Heap Fetches: 11 2046 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2047 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2048 Index Cond: (dev > NULL::integer) 2049 Heap Fetches: 11 2050 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2051 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2052 Index Cond: (dev > NULL::integer) 2053 Heap Fetches: 11 2054(20 rows) 2055 2056:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE; 2057 QUERY PLAN 2058----------------------------------------------------------------------------------------------------------------------- 2059 Unique (actual rows=11 loops=1) 2060 -> Merge Append (actual rows=44 loops=1) 2061 Sort Key: _hyper_1_1_chunk.dev 2062 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2063 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2064 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2065 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2066 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2067 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2068 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2069 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2070(11 rows) 2071 2072:PREFIX SELECT DISTINCT ON (dev) *, 'q2_7' FROM :TABLE; 2073 QUERY PLAN 2074----------------------------------------------------------------------------------------------------------------------------- 2075 Result (actual rows=11 loops=1) 2076 -> Unique (actual rows=11 loops=1) 2077 -> Merge Append (actual rows=44 loops=1) 2078 Sort Key: _hyper_1_1_chunk.dev 2079 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2080 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2081 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2082 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2083 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2084 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2085 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2086 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2087(12 rows) 2088 2089:PREFIX SELECT DISTINCT ON (dev) dev, time, 'q2_8' FROM :TABLE; 2090 QUERY PLAN 2091----------------------------------------------------------------------------------------------------------------------------- 2092 Result (actual rows=11 loops=1) 2093 -> Unique (actual rows=11 loops=1) 2094 -> Merge Append (actual rows=44 loops=1) 2095 Sort Key: _hyper_1_1_chunk.dev 2096 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2097 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2098 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2099 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2100 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2101 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2102 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2103 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2104(12 rows) 2105 2106:PREFIX SELECT DISTINCT ON (dev) dev, NULL, 'q2_9' FROM :TABLE; 2107 QUERY PLAN 2108---------------------------------------------------------------------------------------------------------------------------------- 2109 Result (actual rows=11 loops=1) 2110 -> Unique (actual rows=11 loops=1) 2111 -> Merge Append (actual rows=44 loops=1) 2112 Sort Key: _hyper_1_1_chunk.dev 2113 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2114 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2115 Index Cond: (dev > NULL::integer) 2116 Heap Fetches: 11 2117 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2118 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2119 Index Cond: (dev > NULL::integer) 2120 Heap Fetches: 11 2121 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2122 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2123 Index Cond: (dev > NULL::integer) 2124 Heap Fetches: 11 2125 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2126 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2127 Index Cond: (dev > NULL::integer) 2128 Heap Fetches: 11 2129(20 rows) 2130 2131:PREFIX SELECT DISTINCT ON (dev) time, 'q2_10' FROM :TABLE ORDER by dev, time; 2132 QUERY PLAN 2133--------------------------------------------------------------------------------------------------------------------------------------- 2134 Result (actual rows=11 loops=1) 2135 -> Unique (actual rows=11 loops=1) 2136 -> Merge Append (actual rows=44 loops=1) 2137 Sort Key: _hyper_1_1_chunk.dev, _hyper_1_1_chunk."time" 2138 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2139 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_time_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2140 Index Cond: (dev > NULL::integer) 2141 Heap Fetches: 11 2142 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2143 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_time_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2144 Index Cond: (dev > NULL::integer) 2145 Heap Fetches: 11 2146 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2147 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_time_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2148 Index Cond: (dev > NULL::integer) 2149 Heap Fetches: 11 2150 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2151 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_time_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2152 Index Cond: (dev > NULL::integer) 2153 Heap Fetches: 11 2154(20 rows) 2155 2156:PREFIX SELECT DISTINCT ON (dev) dev, tableoid::regclass, 'q2_11' FROM :TABLE; 2157 QUERY PLAN 2158----------------------------------------------------------------------------------------------------------------------------- 2159 Result (actual rows=11 loops=1) 2160 -> Unique (actual rows=11 loops=1) 2161 -> Merge Append (actual rows=44 loops=1) 2162 Sort Key: _hyper_1_1_chunk.dev 2163 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2164 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2165 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2166 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2167 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2168 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2169 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2170 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2171(12 rows) 2172 2173:PREFIX SELECT DISTINCT ON (dev) dev, int_func_immutable(), 'q2_12' FROM :TABLE; 2174 QUERY PLAN 2175---------------------------------------------------------------------------------------------------------------------------------- 2176 Result (actual rows=11 loops=1) 2177 -> Unique (actual rows=11 loops=1) 2178 -> Merge Append (actual rows=44 loops=1) 2179 Sort Key: _hyper_1_1_chunk.dev 2180 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2181 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2182 Index Cond: (dev > NULL::integer) 2183 Heap Fetches: 11 2184 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2185 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2186 Index Cond: (dev > NULL::integer) 2187 Heap Fetches: 11 2188 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2189 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2190 Index Cond: (dev > NULL::integer) 2191 Heap Fetches: 11 2192 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2193 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2194 Index Cond: (dev > NULL::integer) 2195 Heap Fetches: 11 2196(20 rows) 2197 2198:PREFIX SELECT DISTINCT ON (dev) dev, int_func_stable(), 'q2_13' FROM :TABLE; 2199 QUERY PLAN 2200---------------------------------------------------------------------------------------------------------------------------------- 2201 Result (actual rows=11 loops=1) 2202 -> Unique (actual rows=11 loops=1) 2203 -> Merge Append (actual rows=44 loops=1) 2204 Sort Key: _hyper_1_1_chunk.dev 2205 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2206 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2207 Index Cond: (dev > NULL::integer) 2208 Heap Fetches: 11 2209 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2210 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2211 Index Cond: (dev > NULL::integer) 2212 Heap Fetches: 11 2213 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2214 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2215 Index Cond: (dev > NULL::integer) 2216 Heap Fetches: 11 2217 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2218 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2219 Index Cond: (dev > NULL::integer) 2220 Heap Fetches: 11 2221(20 rows) 2222 2223:PREFIX SELECT DISTINCT ON (dev) dev, int_func_volatile(), 'q2_14' FROM :TABLE; 2224 QUERY PLAN 2225---------------------------------------------------------------------------------------------------------------------------------- 2226 Result (actual rows=11 loops=1) 2227 -> Unique (actual rows=11 loops=1) 2228 -> Merge Append (actual rows=44 loops=1) 2229 Sort Key: _hyper_1_1_chunk.dev 2230 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2231 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2232 Index Cond: (dev > NULL::integer) 2233 Heap Fetches: 11 2234 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2235 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2236 Index Cond: (dev > NULL::integer) 2237 Heap Fetches: 11 2238 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2239 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2240 Index Cond: (dev > NULL::integer) 2241 Heap Fetches: 11 2242 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2243 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2244 Index Cond: (dev > NULL::integer) 2245 Heap Fetches: 11 2246(20 rows) 2247 2248-- DISTINCT ON queries on TEXT column 2249:PREFIX SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE; 2250 QUERY PLAN 2251--------------------------------------------------------------------------------------------------------------------------------- 2252 Unique (actual rows=11 loops=1) 2253 -> Merge Append (actual rows=44 loops=1) 2254 Sort Key: _hyper_1_1_chunk.dev_name 2255 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2256 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_name_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2257 Index Cond: (dev_name > NULL::text) 2258 Heap Fetches: 11 2259 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2260 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_name_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2261 Index Cond: (dev_name > NULL::text) 2262 Heap Fetches: 11 2263 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2264 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_name_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2265 Index Cond: (dev_name > NULL::text) 2266 Heap Fetches: 11 2267 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2268 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_name_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2269 Index Cond: (dev_name > NULL::text) 2270 Heap Fetches: 11 2271(19 rows) 2272 2273:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_2' FROM :TABLE; 2274 QUERY PLAN 2275--------------------------------------------------------------------------------------------------------------------------------------- 2276 Result (actual rows=11 loops=1) 2277 -> Unique (actual rows=11 loops=1) 2278 -> Merge Append (actual rows=44 loops=1) 2279 Sort Key: _hyper_1_1_chunk.dev_name 2280 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2281 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_name_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2282 Index Cond: (dev_name > NULL::text) 2283 Heap Fetches: 11 2284 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2285 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_name_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2286 Index Cond: (dev_name > NULL::text) 2287 Heap Fetches: 11 2288 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2289 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_name_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2290 Index Cond: (dev_name > NULL::text) 2291 Heap Fetches: 11 2292 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2293 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_name_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2294 Index Cond: (dev_name > NULL::text) 2295 Heap Fetches: 11 2296(20 rows) 2297 2298:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_3', NULL FROM :TABLE; 2299 QUERY PLAN 2300--------------------------------------------------------------------------------------------------------------------------------------- 2301 Result (actual rows=11 loops=1) 2302 -> Unique (actual rows=11 loops=1) 2303 -> Merge Append (actual rows=44 loops=1) 2304 Sort Key: _hyper_1_1_chunk.dev_name 2305 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2306 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_name_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2307 Index Cond: (dev_name > NULL::text) 2308 Heap Fetches: 11 2309 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2310 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_name_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2311 Index Cond: (dev_name > NULL::text) 2312 Heap Fetches: 11 2313 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2314 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_name_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2315 Index Cond: (dev_name > NULL::text) 2316 Heap Fetches: 11 2317 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2318 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_name_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2319 Index Cond: (dev_name > NULL::text) 2320 Heap Fetches: 11 2321(20 rows) 2322 2323:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_4', length(md5(now()::text)) FROM :TABLE; 2324 QUERY PLAN 2325--------------------------------------------------------------------------------------------------------------------------------------- 2326 Result (actual rows=11 loops=1) 2327 -> Unique (actual rows=11 loops=1) 2328 -> Merge Append (actual rows=44 loops=1) 2329 Sort Key: _hyper_1_1_chunk.dev_name 2330 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2331 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_name_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2332 Index Cond: (dev_name > NULL::text) 2333 Heap Fetches: 11 2334 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2335 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_name_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2336 Index Cond: (dev_name > NULL::text) 2337 Heap Fetches: 11 2338 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2339 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_name_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2340 Index Cond: (dev_name > NULL::text) 2341 Heap Fetches: 11 2342 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2343 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_name_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2344 Index Cond: (dev_name > NULL::text) 2345 Heap Fetches: 11 2346(20 rows) 2347 2348:PREFIX SELECT DISTINCT ON (dev_name) dev_name, 'q3_5', length(md5(random()::text)) FROM :TABLE; 2349 QUERY PLAN 2350--------------------------------------------------------------------------------------------------------------------------------------- 2351 Result (actual rows=11 loops=1) 2352 -> Unique (actual rows=11 loops=1) 2353 -> Merge Append (actual rows=44 loops=1) 2354 Sort Key: _hyper_1_1_chunk.dev_name 2355 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2356 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_name_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2357 Index Cond: (dev_name > NULL::text) 2358 Heap Fetches: 11 2359 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2360 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_name_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2361 Index Cond: (dev_name > NULL::text) 2362 Heap Fetches: 11 2363 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2364 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_name_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2365 Index Cond: (dev_name > NULL::text) 2366 Heap Fetches: 11 2367 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2368 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_name_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2369 Index Cond: (dev_name > NULL::text) 2370 Heap Fetches: 11 2371(20 rows) 2372 2373:PREFIX SELECT DISTINCT ON (dev_name) * FROM :TABLE; 2374 QUERY PLAN 2375---------------------------------------------------------------------------------------------------------------------------- 2376 Unique (actual rows=11 loops=1) 2377 -> Merge Append (actual rows=44 loops=1) 2378 Sort Key: _hyper_1_1_chunk.dev_name 2379 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2380 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_name_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2381 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2382 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_name_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2383 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2384 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_name_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2385 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2386 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_name_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2387(11 rows) 2388 2389:PREFIX SELECT DISTINCT ON (dev_name) *, 'q3_7' FROM :TABLE; 2390 QUERY PLAN 2391---------------------------------------------------------------------------------------------------------------------------------- 2392 Result (actual rows=11 loops=1) 2393 -> Unique (actual rows=11 loops=1) 2394 -> Merge Append (actual rows=44 loops=1) 2395 Sort Key: _hyper_1_1_chunk.dev_name 2396 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2397 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_name_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2398 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2399 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_name_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2400 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2401 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_name_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2402 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2403 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_name_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2404(12 rows) 2405 2406:PREFIX SELECT DISTINCT ON (dev_name) dev_name, time, 'q3_8' FROM :TABLE; 2407 QUERY PLAN 2408---------------------------------------------------------------------------------------------------------------------------------- 2409 Result (actual rows=11 loops=1) 2410 -> Unique (actual rows=11 loops=1) 2411 -> Merge Append (actual rows=44 loops=1) 2412 Sort Key: _hyper_1_1_chunk.dev_name 2413 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2414 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_name_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2415 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2416 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_name_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2417 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2418 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_name_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2419 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2420 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_name_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2421(12 rows) 2422 2423:PREFIX SELECT DISTINCT ON (dev_name) dev_name, NULL, 'q3_9' FROM :TABLE; 2424 QUERY PLAN 2425--------------------------------------------------------------------------------------------------------------------------------------- 2426 Result (actual rows=11 loops=1) 2427 -> Unique (actual rows=11 loops=1) 2428 -> Merge Append (actual rows=44 loops=1) 2429 Sort Key: _hyper_1_1_chunk.dev_name 2430 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2431 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_name_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2432 Index Cond: (dev_name > NULL::text) 2433 Heap Fetches: 11 2434 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2435 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_name_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2436 Index Cond: (dev_name > NULL::text) 2437 Heap Fetches: 11 2438 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2439 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_name_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2440 Index Cond: (dev_name > NULL::text) 2441 Heap Fetches: 11 2442 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2443 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_name_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2444 Index Cond: (dev_name > NULL::text) 2445 Heap Fetches: 11 2446(20 rows) 2447 2448:PREFIX SELECT DISTINCT ON (dev_name) time, 'q3_10' FROM :TABLE ORDER by dev_name, time; 2449 QUERY PLAN 2450--------------------------------------------------------------------------------- 2451 Unique (actual rows=11 loops=1) 2452 -> Sort (actual rows=10020 loops=1) 2453 Sort Key: _hyper_1_1_chunk.dev_name, _hyper_1_1_chunk."time" 2454 Sort Method: quicksort 2455 -> Result (actual rows=10020 loops=1) 2456 -> Append (actual rows=10020 loops=1) 2457 -> Seq Scan on _hyper_1_1_chunk (actual rows=2505 loops=1) 2458 -> Seq Scan on _hyper_1_2_chunk (actual rows=2505 loops=1) 2459 -> Seq Scan on _hyper_1_3_chunk (actual rows=2505 loops=1) 2460 -> Seq Scan on _hyper_1_4_chunk (actual rows=2505 loops=1) 2461(10 rows) 2462 2463:PREFIX SELECT DISTINCT ON (dev_name) dev_name, tableoid::regclass, 'q3_11' FROM :TABLE; 2464 QUERY PLAN 2465---------------------------------------------------------------------------------------------------------------------------------- 2466 Result (actual rows=11 loops=1) 2467 -> Unique (actual rows=11 loops=1) 2468 -> Merge Append (actual rows=44 loops=1) 2469 Sort Key: _hyper_1_1_chunk.dev_name 2470 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2471 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_name_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2472 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2473 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_name_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2474 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2475 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_name_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2476 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2477 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_name_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2478(12 rows) 2479 2480:PREFIX SELECT DISTINCT ON (dev_name::varchar) dev_name::varchar FROM :TABLE; 2481 QUERY PLAN 2482--------------------------------------------------------------------------------------------------------------------------------------- 2483 Result (actual rows=11 loops=1) 2484 -> Unique (actual rows=11 loops=1) 2485 -> Merge Append (actual rows=44 loops=1) 2486 Sort Key: _hyper_1_1_chunk.dev_name 2487 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2488 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_name_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2489 Index Cond: (dev_name > NULL::text) 2490 Heap Fetches: 11 2491 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2492 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_name_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2493 Index Cond: (dev_name > NULL::text) 2494 Heap Fetches: 11 2495 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2496 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_name_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2497 Index Cond: (dev_name > NULL::text) 2498 Heap Fetches: 11 2499 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2500 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_name_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2501 Index Cond: (dev_name > NULL::text) 2502 Heap Fetches: 11 2503(20 rows) 2504 2505:PREFIX SELECT DISTINCT ON (dev_name) dev, int_func_immutable(), 'q3_13' FROM :TABLE; 2506 QUERY PLAN 2507---------------------------------------------------------------------------------------------------------------------------------- 2508 Result (actual rows=11 loops=1) 2509 -> Unique (actual rows=11 loops=1) 2510 -> Merge Append (actual rows=44 loops=1) 2511 Sort Key: _hyper_1_1_chunk.dev_name 2512 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2513 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_name_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2514 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2515 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_name_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2516 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2517 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_name_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2518 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2519 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_name_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2520(12 rows) 2521 2522:PREFIX SELECT DISTINCT ON (dev_name) dev, int_func_stable(), 'q3_14' FROM :TABLE; 2523 QUERY PLAN 2524---------------------------------------------------------------------------------------------------------------------------------- 2525 Result (actual rows=11 loops=1) 2526 -> Unique (actual rows=11 loops=1) 2527 -> Merge Append (actual rows=44 loops=1) 2528 Sort Key: _hyper_1_1_chunk.dev_name 2529 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2530 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_name_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2531 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2532 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_name_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2533 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2534 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_name_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2535 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2536 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_name_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2537(12 rows) 2538 2539:PREFIX SELECT DISTINCT ON (dev_name) dev, int_func_volatile(), 'q3_15' FROM :TABLE; 2540 QUERY PLAN 2541---------------------------------------------------------------------------------------------------------------------------------- 2542 Result (actual rows=11 loops=1) 2543 -> Unique (actual rows=11 loops=1) 2544 -> Merge Append (actual rows=44 loops=1) 2545 Sort Key: _hyper_1_1_chunk.dev_name 2546 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2547 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_name_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2548 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2549 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_name_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2550 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2551 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_name_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2552 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2553 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_name_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2554(12 rows) 2555 2556\qecho DISTINCT with wholerow var 2557DISTINCT with wholerow var 2558:PREFIX SELECT DISTINCT ON (dev) :TABLE FROM :TABLE; 2559 QUERY PLAN 2560----------------------------------------------------------------------------------------------------------------------- 2561 Unique (actual rows=11 loops=1) 2562 -> Merge Append (actual rows=44 loops=1) 2563 Sort Key: _hyper_1_1_chunk.dev 2564 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2565 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2566 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2567 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2568 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2569 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2570 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2571 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2572(11 rows) 2573 2574-- should not use SkipScan since we only support SkipScan on single-column distinct 2575:PREFIX SELECT DISTINCT :TABLE FROM :TABLE; 2576 QUERY PLAN 2577--------------------------------------------------------------------------- 2578 Unique (actual rows=10020 loops=1) 2579 -> Sort (actual rows=10020 loops=1) 2580 Sort Key: ((skip_scan_ht.*)::skip_scan_ht) 2581 Sort Method: quicksort 2582 -> Append (actual rows=10020 loops=1) 2583 -> Seq Scan on _hyper_1_1_chunk (actual rows=2505 loops=1) 2584 -> Seq Scan on _hyper_1_2_chunk (actual rows=2505 loops=1) 2585 -> Seq Scan on _hyper_1_3_chunk (actual rows=2505 loops=1) 2586 -> Seq Scan on _hyper_1_4_chunk (actual rows=2505 loops=1) 2587(9 rows) 2588 2589\qecho LIMIT queries on :TABLE 2590LIMIT queries on skip_scan_ht 2591:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE LIMIT 3; 2592 QUERY PLAN 2593--------------------------------------------------------------------------------------------------------------------------------- 2594 Limit (actual rows=3 loops=1) 2595 -> Unique (actual rows=3 loops=1) 2596 -> Merge Append (actual rows=9 loops=1) 2597 Sort Key: _hyper_1_1_chunk.dev 2598 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=3 loops=1) 2599 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=3 loops=1) 2600 Index Cond: (dev > NULL::integer) 2601 Heap Fetches: 3 2602 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=3 loops=1) 2603 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=3 loops=1) 2604 Index Cond: (dev > NULL::integer) 2605 Heap Fetches: 3 2606 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=3 loops=1) 2607 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=3 loops=1) 2608 Index Cond: (dev > NULL::integer) 2609 Heap Fetches: 3 2610 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=3 loops=1) 2611 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=3 loops=1) 2612 Index Cond: (dev > NULL::integer) 2613 Heap Fetches: 3 2614(20 rows) 2615 2616:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev DESC, time DESC LIMIT 3; 2617 QUERY PLAN 2618----------------------------------------------------------------------------------------------------------------------------------------------- 2619 Limit (actual rows=3 loops=1) 2620 -> Unique (actual rows=3 loops=1) 2621 -> Merge Append (actual rows=9 loops=1) 2622 Sort Key: _hyper_1_1_chunk.dev DESC, _hyper_1_1_chunk."time" DESC 2623 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=3 loops=1) 2624 -> Index Only Scan Backward using _hyper_1_1_chunk_skip_scan_ht_dev_time_idx on _hyper_1_1_chunk (actual rows=3 loops=1) 2625 Index Cond: (dev < NULL::integer) 2626 Heap Fetches: 3 2627 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=3 loops=1) 2628 -> Index Only Scan Backward using _hyper_1_2_chunk_skip_scan_ht_dev_time_idx on _hyper_1_2_chunk (actual rows=3 loops=1) 2629 Index Cond: (dev < NULL::integer) 2630 Heap Fetches: 3 2631 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=3 loops=1) 2632 -> Index Only Scan Backward using _hyper_1_3_chunk_skip_scan_ht_dev_time_idx on _hyper_1_3_chunk (actual rows=3 loops=1) 2633 Index Cond: (dev < NULL::integer) 2634 Heap Fetches: 3 2635 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=3 loops=1) 2636 -> Index Only Scan Backward using _hyper_1_4_chunk_skip_scan_ht_dev_time_idx on _hyper_1_4_chunk (actual rows=3 loops=1) 2637 Index Cond: (dev < NULL::integer) 2638 Heap Fetches: 3 2639(20 rows) 2640 2641:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev, time LIMIT 3; 2642 QUERY PLAN 2643-------------------------------------------------------------------------------------------------------------------------------------- 2644 Limit (actual rows=3 loops=1) 2645 -> Unique (actual rows=3 loops=1) 2646 -> Merge Append (actual rows=9 loops=1) 2647 Sort Key: _hyper_1_1_chunk.dev, _hyper_1_1_chunk."time" 2648 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=3 loops=1) 2649 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_time_idx on _hyper_1_1_chunk (actual rows=3 loops=1) 2650 Index Cond: (dev > NULL::integer) 2651 Heap Fetches: 3 2652 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=3 loops=1) 2653 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_time_idx on _hyper_1_2_chunk (actual rows=3 loops=1) 2654 Index Cond: (dev > NULL::integer) 2655 Heap Fetches: 3 2656 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=3 loops=1) 2657 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_time_idx on _hyper_1_3_chunk (actual rows=3 loops=1) 2658 Index Cond: (dev > NULL::integer) 2659 Heap Fetches: 3 2660 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=3 loops=1) 2661 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_time_idx on _hyper_1_4_chunk (actual rows=3 loops=1) 2662 Index Cond: (dev > NULL::integer) 2663 Heap Fetches: 3 2664(20 rows) 2665 2666\qecho range queries on :TABLE 2667range queries on skip_scan_ht 2668:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time BETWEEN 100 AND 300; 2669 QUERY PLAN 2670--------------------------------------------------------------------------------------------------------------------------------- 2671 Unique (actual rows=11 loops=1) 2672 -> Merge Append (actual rows=22 loops=1) 2673 Sort Key: _hyper_1_1_chunk.dev 2674 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2675 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_time_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2676 Index Cond: ((dev > NULL::integer) AND ("time" >= 100) AND ("time" <= 300)) 2677 Heap Fetches: 11 2678 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2679 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_time_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2680 Index Cond: ((dev > NULL::integer) AND ("time" >= 100) AND ("time" <= 300)) 2681 Heap Fetches: 11 2682(11 rows) 2683 2684:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time < 200; 2685 QUERY PLAN 2686--------------------------------------------------------------------------------------------------------------------------- 2687 Unique (actual rows=11 loops=1) 2688 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2689 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_time_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2690 Index Cond: ((dev > NULL::integer) AND ("time" < 200)) 2691 Heap Fetches: 11 2692(5 rows) 2693 2694:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time > 800; 2695 QUERY PLAN 2696--------------------------------------------------------------------------------------------------------------------------- 2697 Unique (actual rows=11 loops=1) 2698 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2699 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_time_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2700 Index Cond: ((dev > NULL::integer) AND ("time" > 800)) 2701 Heap Fetches: 11 2702(5 rows) 2703 2704\qecho ordered append on :TABLE 2705ordered append on skip_scan_ht 2706:PREFIX SELECT * FROM :TABLE ORDER BY time; 2707 QUERY PLAN 2708------------------------------------------------------------------------------------------------------------------ 2709 Custom Scan (ChunkAppend) on skip_scan_ht (actual rows=10020 loops=1) 2710 Order: skip_scan_ht."time" 2711 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_time_dev_idx on _hyper_1_1_chunk (actual rows=2505 loops=1) 2712 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_time_dev_idx on _hyper_1_2_chunk (actual rows=2505 loops=1) 2713 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_time_dev_idx on _hyper_1_3_chunk (actual rows=2505 loops=1) 2714 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_time_dev_idx on _hyper_1_4_chunk (actual rows=2505 loops=1) 2715(6 rows) 2716 2717:PREFIX SELECT DISTINCT ON (time) time FROM :TABLE WHERE time BETWEEN 0 AND 5000; 2718 QUERY PLAN 2719---------------------------------------------------------------------------------------------------------------------------------- 2720 Unique (actual rows=1000 loops=1) 2721 -> Merge Append (actual rows=1000 loops=1) 2722 Sort Key: _hyper_1_1_chunk."time" 2723 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=250 loops=1) 2724 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_time_dev_idx on _hyper_1_1_chunk (actual rows=250 loops=1) 2725 Index Cond: (("time" > NULL::integer) AND ("time" >= 0) AND ("time" <= 5000)) 2726 Heap Fetches: 250 2727 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=250 loops=1) 2728 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_time_dev_idx on _hyper_1_4_chunk (actual rows=250 loops=1) 2729 Index Cond: (("time" > NULL::integer) AND ("time" >= 0) AND ("time" <= 5000)) 2730 Heap Fetches: 250 2731 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=250 loops=1) 2732 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_time_dev_idx on _hyper_1_2_chunk (actual rows=250 loops=1) 2733 Index Cond: (("time" > NULL::integer) AND ("time" >= 0) AND ("time" <= 5000)) 2734 Heap Fetches: 250 2735 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=250 loops=1) 2736 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_time_dev_idx on _hyper_1_3_chunk (actual rows=250 loops=1) 2737 Index Cond: (("time" > NULL::integer) AND ("time" >= 0) AND ("time" <= 5000)) 2738 Heap Fetches: 250 2739(19 rows) 2740 2741\qecho SUBSELECTS on :TABLE 2742SUBSELECTS on skip_scan_ht 2743:PREFIX SELECT time, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a; 2744 QUERY PLAN 2745----------------------------------------------------------------------------------------------------------------------------------- 2746 Subquery Scan on a (actual rows=11 loops=1) 2747 -> Result (actual rows=11 loops=1) 2748 -> Unique (actual rows=11 loops=1) 2749 -> Merge Append (actual rows=44 loops=1) 2750 Sort Key: _hyper_1_1_chunk.dev 2751 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2752 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2753 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2754 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2755 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2756 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2757 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2758 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2759(13 rows) 2760 2761:PREFIX SELECT NULL, dev, NULL, 'q4_3' FROM (SELECT DISTINCT ON (dev) dev FROM :TABLE) a; 2762 QUERY PLAN 2763---------------------------------------------------------------------------------------------------------------------------------- 2764 Subquery Scan on a (actual rows=11 loops=1) 2765 -> Unique (actual rows=11 loops=1) 2766 -> Merge Append (actual rows=44 loops=1) 2767 Sort Key: _hyper_1_1_chunk.dev 2768 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2769 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2770 Index Cond: (dev > NULL::integer) 2771 Heap Fetches: 11 2772 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2773 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2774 Index Cond: (dev > NULL::integer) 2775 Heap Fetches: 11 2776 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2777 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2778 Index Cond: (dev > NULL::integer) 2779 Heap Fetches: 11 2780 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2781 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2782 Index Cond: (dev > NULL::integer) 2783 Heap Fetches: 11 2784(20 rows) 2785 2786:PREFIX SELECT time, dev, NULL, 'q4_4' FROM (SELECT DISTINCT ON (dev) dev, time FROM :TABLE) a; 2787 QUERY PLAN 2788----------------------------------------------------------------------------------------------------------------------------- 2789 Subquery Scan on a (actual rows=11 loops=1) 2790 -> Unique (actual rows=11 loops=1) 2791 -> Merge Append (actual rows=44 loops=1) 2792 Sort Key: _hyper_1_1_chunk.dev 2793 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2794 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2795 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2796 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2797 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2798 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2799 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2800 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2801(12 rows) 2802 2803\qecho ORDER BY 2804ORDER BY 2805:PREFIX SELECT time, dev, val, 'q5_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE ORDER BY dev, time) a; 2806 QUERY PLAN 2807---------------------------------------------------------------------------------------------------------------------------------------- 2808 Subquery Scan on a (actual rows=11 loops=1) 2809 -> Result (actual rows=11 loops=1) 2810 -> Unique (actual rows=11 loops=1) 2811 -> Merge Append (actual rows=44 loops=1) 2812 Sort Key: _hyper_1_1_chunk.dev, _hyper_1_1_chunk."time" 2813 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2814 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_time_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2815 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2816 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_time_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2817 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2818 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_time_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2819 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2820 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_time_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2821(13 rows) 2822 2823:PREFIX SELECT time, dev, val, 'q5_2' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE ORDER BY dev DESC, time DESC) a; 2824 QUERY PLAN 2825------------------------------------------------------------------------------------------------------------------------------------------------- 2826 Subquery Scan on a (actual rows=11 loops=1) 2827 -> Result (actual rows=11 loops=1) 2828 -> Unique (actual rows=11 loops=1) 2829 -> Merge Append (actual rows=44 loops=1) 2830 Sort Key: _hyper_1_1_chunk.dev DESC, _hyper_1_1_chunk."time" DESC 2831 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2832 -> Index Scan Backward using _hyper_1_1_chunk_skip_scan_ht_dev_time_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2833 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2834 -> Index Scan Backward using _hyper_1_2_chunk_skip_scan_ht_dev_time_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2835 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2836 -> Index Scan Backward using _hyper_1_3_chunk_skip_scan_ht_dev_time_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2837 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2838 -> Index Scan Backward using _hyper_1_4_chunk_skip_scan_ht_dev_time_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2839(13 rows) 2840 2841\qecho WHERE CLAUSES 2842WHERE CLAUSES 2843:PREFIX SELECT time, dev, val, 'q6_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > 5) a; 2844 QUERY PLAN 2845---------------------------------------------------------------------------------------------------------------------------------- 2846 Subquery Scan on a (actual rows=5 loops=1) 2847 -> Result (actual rows=5 loops=1) 2848 -> Unique (actual rows=5 loops=1) 2849 -> Merge Append (actual rows=20 loops=1) 2850 Sort Key: _hyper_1_1_chunk.dev 2851 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=5 loops=1) 2852 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=5 loops=1) 2853 Index Cond: (dev > 5) 2854 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=5 loops=1) 2855 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=5 loops=1) 2856 Index Cond: (dev > 5) 2857 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=5 loops=1) 2858 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=5 loops=1) 2859 Index Cond: (dev > 5) 2860 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=5 loops=1) 2861 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=5 loops=1) 2862 Index Cond: (dev > 5) 2863(17 rows) 2864 2865:PREFIX SELECT time, dev, val, 'q6_2' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE time > 5) a; 2866 QUERY PLAN 2867---------------------------------------------------------------------------------------------------------------------------------------- 2868 Subquery Scan on a (actual rows=11 loops=1) 2869 -> Result (actual rows=11 loops=1) 2870 -> Unique (actual rows=11 loops=1) 2871 -> Merge Append (actual rows=44 loops=1) 2872 Sort Key: _hyper_1_1_chunk.dev 2873 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2874 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_time_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2875 Index Cond: ("time" > 5) 2876 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2877 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_time_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2878 Index Cond: ("time" > 5) 2879 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2880 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_time_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2881 Index Cond: ("time" > 5) 2882 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2883 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_time_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2884 Index Cond: ("time" > 5) 2885(17 rows) 2886 2887:PREFIX SELECT time, dev, val, 'q6_3' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a WHERE dev > 5; 2888 QUERY PLAN 2889---------------------------------------------------------------------------------------------------------------------------------- 2890 Subquery Scan on a (actual rows=5 loops=1) 2891 -> Result (actual rows=5 loops=1) 2892 -> Unique (actual rows=5 loops=1) 2893 -> Merge Append (actual rows=20 loops=1) 2894 Sort Key: _hyper_1_1_chunk.dev 2895 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=5 loops=1) 2896 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=5 loops=1) 2897 Index Cond: (dev > 5) 2898 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=5 loops=1) 2899 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=5 loops=1) 2900 Index Cond: (dev > 5) 2901 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=5 loops=1) 2902 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=5 loops=1) 2903 Index Cond: (dev > 5) 2904 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=5 loops=1) 2905 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=5 loops=1) 2906 Index Cond: (dev > 5) 2907(17 rows) 2908 2909:PREFIX SELECT time, dev, val, 'q6_4' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a WHERE time > 5; 2910 QUERY PLAN 2911----------------------------------------------------------------------------------------------------------------------------------- 2912 Subquery Scan on a (actual rows=7 loops=1) 2913 Filter: (a."time" > 5) 2914 Rows Removed by Filter: 4 2915 -> Result (actual rows=11 loops=1) 2916 -> Unique (actual rows=11 loops=1) 2917 -> Merge Append (actual rows=44 loops=1) 2918 Sort Key: _hyper_1_1_chunk.dev 2919 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 2920 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 2921 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 2922 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 2923 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 2924 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 2925 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 2926 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 2927(15 rows) 2928 2929--\qecho immutable func in WHERE clause on :TABLE 2930:PREFIX SELECT DISTINCT ON (dev) *, 'q6_5' FROM :TABLE WHERE dev > int_func_immutable(); 2931 QUERY PLAN 2932---------------------------------------------------------------------------------------------------------------------------- 2933 Result (actual rows=9 loops=1) 2934 -> Unique (actual rows=9 loops=1) 2935 -> Merge Append (actual rows=36 loops=1) 2936 Sort Key: _hyper_1_1_chunk.dev 2937 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=9 loops=1) 2938 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=9 loops=1) 2939 Index Cond: (dev > 1) 2940 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=9 loops=1) 2941 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=9 loops=1) 2942 Index Cond: (dev > 1) 2943 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=9 loops=1) 2944 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=9 loops=1) 2945 Index Cond: (dev > 1) 2946 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=9 loops=1) 2947 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=9 loops=1) 2948 Index Cond: (dev > 1) 2949(16 rows) 2950 2951--\qecho stable func in WHERE clause on :TABLE 2952:PREFIX SELECT DISTINCT ON (dev) *, 'q6_6' FROM :TABLE WHERE dev > int_func_stable(); 2953 QUERY PLAN 2954---------------------------------------------------------------------------------------------------------------------------------- 2955 Result (actual rows=8 loops=1) 2956 -> Unique (actual rows=8 loops=1) 2957 -> Custom Scan (ConstraintAwareAppend) (actual rows=32 loops=1) 2958 Hypertable: skip_scan_ht 2959 Chunks left after exclusion: 4 2960 -> Merge Append (actual rows=32 loops=1) 2961 Sort Key: _hyper_1_1_chunk.dev 2962 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=8 loops=1) 2963 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=8 loops=1) 2964 Index Cond: (dev > int_func_stable()) 2965 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=8 loops=1) 2966 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=8 loops=1) 2967 Index Cond: (dev > int_func_stable()) 2968 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=8 loops=1) 2969 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=8 loops=1) 2970 Index Cond: (dev > int_func_stable()) 2971 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=8 loops=1) 2972 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=8 loops=1) 2973 Index Cond: (dev > int_func_stable()) 2974(19 rows) 2975 2976--\qecho volatile func in WHERE clause on :TABLE 2977:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > int_func_volatile(); 2978 QUERY PLAN 2979---------------------------------------------------------------------------------------------------------------------------- 2980 Unique (actual rows=7 loops=1) 2981 -> Custom Scan (ConstraintAwareAppend) (actual rows=28 loops=1) 2982 Hypertable: skip_scan_ht 2983 Chunks left after exclusion: 4 2984 -> Merge Append (actual rows=28 loops=1) 2985 Sort Key: _hyper_1_1_chunk.dev 2986 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=7 loops=1) 2987 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=7 loops=1) 2988 Filter: (dev > int_func_volatile()) 2989 Rows Removed by Filter: 755 2990 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=7 loops=1) 2991 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=7 loops=1) 2992 Filter: (dev > int_func_volatile()) 2993 Rows Removed by Filter: 755 2994 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=7 loops=1) 2995 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=7 loops=1) 2996 Filter: (dev > int_func_volatile()) 2997 Rows Removed by Filter: 755 2998 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=7 loops=1) 2999 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=7 loops=1) 3000 Filter: (dev > int_func_volatile()) 3001 Rows Removed by Filter: 755 3002(22 rows) 3003 3004:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_immutable()); 3005 QUERY PLAN 3006---------------------------------------------------------------------------------------------------------------------- 3007 Unique (actual rows=3 loops=1) 3008 -> Merge Append (actual rows=12 loops=1) 3009 Sort Key: _hyper_1_1_chunk.dev 3010 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=3 loops=1) 3011 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=3 loops=1) 3012 Index Cond: (dev = ANY ('{1,2,3}'::integer[])) 3013 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=3 loops=1) 3014 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=3 loops=1) 3015 Index Cond: (dev = ANY ('{1,2,3}'::integer[])) 3016 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=3 loops=1) 3017 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=3 loops=1) 3018 Index Cond: (dev = ANY ('{1,2,3}'::integer[])) 3019 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=3 loops=1) 3020 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=3 loops=1) 3021 Index Cond: (dev = ANY ('{1,2,3}'::integer[])) 3022(15 rows) 3023 3024:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_stable()); 3025 QUERY PLAN 3026---------------------------------------------------------------------------------------------------------------------------- 3027 Unique (actual rows=3 loops=1) 3028 -> Custom Scan (ConstraintAwareAppend) (actual rows=12 loops=1) 3029 Hypertable: skip_scan_ht 3030 Chunks left after exclusion: 4 3031 -> Merge Append (actual rows=12 loops=1) 3032 Sort Key: _hyper_1_1_chunk.dev 3033 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=3 loops=1) 3034 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=3 loops=1) 3035 Index Cond: (dev = ANY (inta_func_stable())) 3036 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=3 loops=1) 3037 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=3 loops=1) 3038 Index Cond: (dev = ANY (inta_func_stable())) 3039 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=3 loops=1) 3040 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=3 loops=1) 3041 Index Cond: (dev = ANY (inta_func_stable())) 3042 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=3 loops=1) 3043 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=3 loops=1) 3044 Index Cond: (dev = ANY (inta_func_stable())) 3045(18 rows) 3046 3047:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = ANY(inta_func_volatile()); 3048 QUERY PLAN 3049---------------------------------------------------------------------------------------------------------------------------- 3050 Unique (actual rows=3 loops=1) 3051 -> Custom Scan (ConstraintAwareAppend) (actual rows=12 loops=1) 3052 Hypertable: skip_scan_ht 3053 Chunks left after exclusion: 4 3054 -> Merge Append (actual rows=12 loops=1) 3055 Sort Key: _hyper_1_1_chunk.dev 3056 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=3 loops=1) 3057 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=3 loops=1) 3058 Filter: (dev = ANY (inta_func_volatile())) 3059 Rows Removed by Filter: 1755 3060 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=3 loops=1) 3061 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=3 loops=1) 3062 Filter: (dev = ANY (inta_func_volatile())) 3063 Rows Removed by Filter: 1755 3064 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=3 loops=1) 3065 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=3 loops=1) 3066 Filter: (dev = ANY (inta_func_volatile())) 3067 Rows Removed by Filter: 1755 3068 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=3 loops=1) 3069 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=3 loops=1) 3070 Filter: (dev = ANY (inta_func_volatile())) 3071 Rows Removed by Filter: 1755 3072(22 rows) 3073 3074-- RowCompareExpr 3075:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE (dev, time) > (5,100); 3076 QUERY PLAN 3077--------------------------------------------------------------------------------------------------------------------------- 3078 Unique (actual rows=6 loops=1) 3079 -> Merge Append (actual rows=24 loops=1) 3080 Sort Key: _hyper_1_1_chunk.dev 3081 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=6 loops=1) 3082 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_time_idx on _hyper_1_1_chunk (actual rows=6 loops=1) 3083 Index Cond: (ROW(dev, "time") > ROW(5, 100)) 3084 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=6 loops=1) 3085 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_time_idx on _hyper_1_2_chunk (actual rows=6 loops=1) 3086 Index Cond: (ROW(dev, "time") > ROW(5, 100)) 3087 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=6 loops=1) 3088 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_time_idx on _hyper_1_3_chunk (actual rows=6 loops=1) 3089 Index Cond: (ROW(dev, "time") > ROW(5, 100)) 3090 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=6 loops=1) 3091 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_time_idx on _hyper_1_4_chunk (actual rows=6 loops=1) 3092 Index Cond: (ROW(dev, "time") > ROW(5, 100)) 3093(15 rows) 3094 3095-- always false expr similar to our initial skip qual 3096:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > NULL; 3097 QUERY PLAN 3098---------------------------------------------- 3099 Unique (actual rows=0 loops=1) 3100 -> Sort (actual rows=0 loops=1) 3101 Sort Key: dev 3102 Sort Method: quicksort 3103 -> Result (actual rows=0 loops=1) 3104 One-Time Filter: false 3105(6 rows) 3106 3107-- no tuples matching 3108:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > 20; 3109 QUERY PLAN 3110---------------------------------------------------------------------------------------------------------------------- 3111 Unique (actual rows=0 loops=1) 3112 -> Merge Append (actual rows=0 loops=1) 3113 Sort Key: _hyper_1_1_chunk.dev 3114 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=0 loops=1) 3115 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=0 loops=1) 3116 Index Cond: (dev > 20) 3117 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=0 loops=1) 3118 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=0 loops=1) 3119 Index Cond: (dev > 20) 3120 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=0 loops=1) 3121 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=0 loops=1) 3122 Index Cond: (dev > 20) 3123 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=0 loops=1) 3124 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=0 loops=1) 3125 Index Cond: (dev > 20) 3126(15 rows) 3127 3128-- multiple constraints in WHERE clause 3129:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev > 5 AND time = 100; 3130 QUERY PLAN 3131------------------------------------------------------------------------------------------------------------------------ 3132 Unique (actual rows=5 loops=1) 3133 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_time_dev_val_idx on _hyper_1_1_chunk (actual rows=5 loops=1) 3134 Index Cond: (("time" = 100) AND (dev > 5)) 3135 Heap Fetches: 5 3136(4 rows) 3137 3138:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev > 5 AND time > 200; 3139 QUERY PLAN 3140-------------------------------------------------------------------------------------------------------------------------------- 3141 Unique (actual rows=5 loops=1) 3142 -> Merge Append (actual rows=20 loops=1) 3143 Sort Key: _hyper_1_1_chunk.dev 3144 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=5 loops=1) 3145 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_time_idx on _hyper_1_1_chunk (actual rows=5 loops=1) 3146 Index Cond: ((dev > NULL::integer) AND (dev > 5) AND ("time" > 200)) 3147 Heap Fetches: 5 3148 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=5 loops=1) 3149 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=5 loops=1) 3150 Index Cond: (dev > 5) 3151 Filter: ("time" > 200) 3152 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=5 loops=1) 3153 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=5 loops=1) 3154 Index Cond: (dev > 5) 3155 Filter: ("time" > 200) 3156 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=5 loops=1) 3157 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=5 loops=1) 3158 Index Cond: (dev > 5) 3159 Filter: ("time" > 200) 3160(19 rows) 3161 3162:PREFIX SELECT DISTINCT ON (dev) dev,time FROM :TABLE WHERE dev >= 5 AND dev < 7 AND dev >= 2; 3163 QUERY PLAN 3164---------------------------------------------------------------------------------------------------------------------- 3165 Unique (actual rows=2 loops=1) 3166 -> Merge Append (actual rows=8 loops=1) 3167 Sort Key: _hyper_1_1_chunk.dev 3168 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=2 loops=1) 3169 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=2 loops=1) 3170 Index Cond: ((dev >= 5) AND (dev < 7) AND (dev >= 2)) 3171 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=2 loops=1) 3172 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=2 loops=1) 3173 Index Cond: ((dev >= 5) AND (dev < 7) AND (dev >= 2)) 3174 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=2 loops=1) 3175 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=2 loops=1) 3176 Index Cond: ((dev >= 5) AND (dev < 7) AND (dev >= 2)) 3177 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=2 loops=1) 3178 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=2 loops=1) 3179 Index Cond: ((dev >= 5) AND (dev < 7) AND (dev >= 2)) 3180(15 rows) 3181 3182: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; 3183 QUERY PLAN 3184--------------------------------------------------------------------------------------------------------------------- 3185 Unique (actual rows=0 loops=1) 3186 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=0 loops=1) 3187 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_time_idx on _hyper_1_1_chunk (actual rows=0 loops=1) 3188 Index Cond: ((dev > 2) AND (dev < 7) AND ("time" > 100) AND ("time" < 200)) 3189 Filter: ((val > 10) AND (val < 10000)) 3190 Rows Removed by Filter: 396 3191(6 rows) 3192 3193:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE dev IS NULL; 3194 QUERY PLAN 3195--------------------------------------------------------------------------------------------------------------------------- 3196 Unique (actual rows=1 loops=1) 3197 -> Merge Append (actual rows=4 loops=1) 3198 Sort Key: _hyper_1_1_chunk.dev 3199 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=1 loops=1) 3200 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=1 loops=1) 3201 Index Cond: ((dev > NULL::integer) AND (dev IS NULL)) 3202 Heap Fetches: 1 3203 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=1 loops=1) 3204 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=1 loops=1) 3205 Index Cond: ((dev > NULL::integer) AND (dev IS NULL)) 3206 Heap Fetches: 1 3207 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=1 loops=1) 3208 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=1 loops=1) 3209 Index Cond: ((dev > NULL::integer) AND (dev IS NULL)) 3210 Heap Fetches: 1 3211 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=1 loops=1) 3212 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=1 loops=1) 3213 Index Cond: ((dev > NULL::integer) AND (dev IS NULL)) 3214 Heap Fetches: 1 3215(19 rows) 3216 3217:PREFIX SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE WHERE dev_name IS NULL; 3218 QUERY PLAN 3219-------------------------------------------------------------------------------------------------------------------------------- 3220 Unique (actual rows=1 loops=1) 3221 -> Merge Append (actual rows=4 loops=1) 3222 Sort Key: _hyper_1_1_chunk.dev_name 3223 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=1 loops=1) 3224 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_name_idx on _hyper_1_1_chunk (actual rows=1 loops=1) 3225 Index Cond: ((dev_name > NULL::text) AND (dev_name IS NULL)) 3226 Heap Fetches: 1 3227 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=1 loops=1) 3228 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_name_idx on _hyper_1_2_chunk (actual rows=1 loops=1) 3229 Index Cond: ((dev_name > NULL::text) AND (dev_name IS NULL)) 3230 Heap Fetches: 1 3231 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=1 loops=1) 3232 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_name_idx on _hyper_1_3_chunk (actual rows=1 loops=1) 3233 Index Cond: ((dev_name > NULL::text) AND (dev_name IS NULL)) 3234 Heap Fetches: 1 3235 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=1 loops=1) 3236 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_name_idx on _hyper_1_4_chunk (actual rows=1 loops=1) 3237 Index Cond: ((dev_name > NULL::text) AND (dev_name IS NULL)) 3238 Heap Fetches: 1 3239(19 rows) 3240 3241-- test constants in ORDER BY 3242:PREFIX SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev = 1 ORDER BY dev, time DESC; 3243 QUERY PLAN 3244------------------------------------------------------------------------------------------------------------------------------------ 3245 Unique (actual rows=1 loops=1) 3246 -> Merge Append (actual rows=4 loops=1) 3247 Sort Key: _hyper_1_1_chunk."time" DESC 3248 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=1 loops=1) 3249 -> Index Scan Backward using _hyper_1_1_chunk_skip_scan_ht_dev_time_idx on _hyper_1_1_chunk (actual rows=1 loops=1) 3250 Index Cond: (dev = 1) 3251 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=1 loops=1) 3252 -> Index Scan Backward using _hyper_1_2_chunk_skip_scan_ht_dev_time_idx on _hyper_1_2_chunk (actual rows=1 loops=1) 3253 Index Cond: (dev = 1) 3254 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=1 loops=1) 3255 -> Index Scan Backward using _hyper_1_3_chunk_skip_scan_ht_dev_time_idx on _hyper_1_3_chunk (actual rows=1 loops=1) 3256 Index Cond: (dev = 1) 3257 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=1 loops=1) 3258 -> Index Scan Backward using _hyper_1_4_chunk_skip_scan_ht_dev_time_idx on _hyper_1_4_chunk (actual rows=1 loops=1) 3259 Index Cond: (dev = 1) 3260(15 rows) 3261 3262-- CTE 3263:PREFIX WITH devices AS ( 3264 SELECT DISTINCT ON (dev) dev FROM :TABLE 3265) 3266SELECT * FROM devices; 3267 QUERY PLAN 3268---------------------------------------------------------------------------------------------------------------------------- 3269 Unique (actual rows=11 loops=1) 3270 -> Merge Append (actual rows=44 loops=1) 3271 Sort Key: _hyper_1_1_chunk.dev 3272 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 3273 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 3274 Index Cond: (dev > NULL::integer) 3275 Heap Fetches: 11 3276 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 3277 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 3278 Index Cond: (dev > NULL::integer) 3279 Heap Fetches: 11 3280 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 3281 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 3282 Index Cond: (dev > NULL::integer) 3283 Heap Fetches: 11 3284 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 3285 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 3286 Index Cond: (dev > NULL::integer) 3287 Heap Fetches: 11 3288(19 rows) 3289 3290:PREFIX WITH devices AS ( 3291 SELECT DISTINCT dev FROM :TABLE 3292) 3293SELECT * FROM devices ORDER BY dev; 3294 QUERY PLAN 3295---------------------------------------------------------------------------------------------------------------------------- 3296 Unique (actual rows=11 loops=1) 3297 -> Merge Append (actual rows=44 loops=1) 3298 Sort Key: _hyper_1_1_chunk.dev 3299 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 3300 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 3301 Index Cond: (dev > NULL::integer) 3302 Heap Fetches: 11 3303 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 3304 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 3305 Index Cond: (dev > NULL::integer) 3306 Heap Fetches: 11 3307 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 3308 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 3309 Index Cond: (dev > NULL::integer) 3310 Heap Fetches: 11 3311 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 3312 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 3313 Index Cond: (dev > NULL::integer) 3314 Heap Fetches: 11 3315(19 rows) 3316 3317-- prepared statements 3318PREPARE prep AS SELECT DISTINCT ON (dev_name) dev_name FROM :TABLE; 3319:PREFIX EXECUTE prep; 3320 QUERY PLAN 3321--------------------------------------------------------------------------------------------------------------------------------- 3322 Unique (actual rows=11 loops=1) 3323 -> Merge Append (actual rows=44 loops=1) 3324 Sort Key: _hyper_1_1_chunk.dev_name 3325 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 3326 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_name_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 3327 Index Cond: (dev_name > NULL::text) 3328 Heap Fetches: 11 3329 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 3330 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_name_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 3331 Index Cond: (dev_name > NULL::text) 3332 Heap Fetches: 11 3333 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 3334 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_name_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 3335 Index Cond: (dev_name > NULL::text) 3336 Heap Fetches: 11 3337 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 3338 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_name_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 3339 Index Cond: (dev_name > NULL::text) 3340 Heap Fetches: 11 3341(19 rows) 3342 3343:PREFIX EXECUTE prep; 3344 QUERY PLAN 3345--------------------------------------------------------------------------------------------------------------------------------- 3346 Unique (actual rows=11 loops=1) 3347 -> Merge Append (actual rows=44 loops=1) 3348 Sort Key: _hyper_1_1_chunk.dev_name 3349 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 3350 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_name_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 3351 Index Cond: (dev_name > NULL::text) 3352 Heap Fetches: 11 3353 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 3354 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_name_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 3355 Index Cond: (dev_name > NULL::text) 3356 Heap Fetches: 11 3357 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 3358 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_name_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 3359 Index Cond: (dev_name > NULL::text) 3360 Heap Fetches: 11 3361 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 3362 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_name_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 3363 Index Cond: (dev_name > NULL::text) 3364 Heap Fetches: 11 3365(19 rows) 3366 3367:PREFIX EXECUTE prep; 3368 QUERY PLAN 3369--------------------------------------------------------------------------------------------------------------------------------- 3370 Unique (actual rows=11 loops=1) 3371 -> Merge Append (actual rows=44 loops=1) 3372 Sort Key: _hyper_1_1_chunk.dev_name 3373 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 3374 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_name_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 3375 Index Cond: (dev_name > NULL::text) 3376 Heap Fetches: 11 3377 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 3378 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_name_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 3379 Index Cond: (dev_name > NULL::text) 3380 Heap Fetches: 11 3381 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 3382 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_name_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 3383 Index Cond: (dev_name > NULL::text) 3384 Heap Fetches: 11 3385 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 3386 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_name_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 3387 Index Cond: (dev_name > NULL::text) 3388 Heap Fetches: 11 3389(19 rows) 3390 3391DEALLOCATE prep; 3392-- ReScan tests 3393:PREFIX SELECT time, dev, val, 'q7_1' FROM (SELECT DISTINCT ON (dev) * FROM ( 3394 VALUES (1), (2)) a(v), 3395 LATERAL (SELECT * FROM :TABLE WHERE time != a.v) b) a; 3396 QUERY PLAN 3397------------------------------------------------------------------------------------------------------------------------------- 3398 Subquery Scan on a (actual rows=11 loops=1) 3399 -> Unique (actual rows=11 loops=1) 3400 -> Nested Loop (actual rows=20020 loops=1) 3401 Join Filter: (_hyper_1_1_chunk."time" <> "*VALUES*".column1) 3402 Rows Removed by Join Filter: 20 3403 -> Merge Append (actual rows=10020 loops=1) 3404 Sort Key: _hyper_1_1_chunk.dev 3405 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=2505 loops=1) 3406 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=2505 loops=1) 3407 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=2505 loops=1) 3408 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=2505 loops=1) 3409 -> Materialize (actual rows=2 loops=10020) 3410 -> Values Scan on "*VALUES*" (actual rows=2 loops=1) 3411(13 rows) 3412 3413:PREFIX SELECT time, dev, val, 'q7_2' FROM (SELECT * FROM ( 3414 VALUES (1), (2)) a(v), 3415 LATERAL (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev != a.v) b) a; 3416 QUERY PLAN 3417---------------------------------------------------------------------------------------------------------------------------------- 3418 Nested Loop (actual rows=18 loops=1) 3419 -> Values Scan on "*VALUES*" (actual rows=2 loops=1) 3420 -> Result (actual rows=9 loops=2) 3421 -> Unique (actual rows=9 loops=2) 3422 -> Merge Append (actual rows=36 loops=2) 3423 Sort Key: _hyper_1_1_chunk.dev 3424 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=9 loops=2) 3425 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=9 loops=2) 3426 Filter: (dev <> "*VALUES*".column1) 3427 Rows Removed by Filter: 255 3428 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=9 loops=2) 3429 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=9 loops=2) 3430 Filter: (dev <> "*VALUES*".column1) 3431 Rows Removed by Filter: 255 3432 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=9 loops=2) 3433 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=9 loops=2) 3434 Filter: (dev <> "*VALUES*".column1) 3435 Rows Removed by Filter: 255 3436 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=9 loops=2) 3437 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=9 loops=2) 3438 Filter: (dev <> "*VALUES*".column1) 3439 Rows Removed by Filter: 255 3440(22 rows) 3441 3442-- RuntimeKeys 3443:PREFIX SELECT time, dev, val, 'q8_1' FROM (SELECT * FROM ( 3444 VALUES (1), (2)) a(v), 3445 LATERAL (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev >= a.v) b) c; 3446 QUERY PLAN 3447----------------------------------------------------------------------------------------------------------------------------------- 3448 Nested Loop (actual rows=19 loops=1) 3449 -> Values Scan on "*VALUES*" (actual rows=2 loops=1) 3450 -> Result (actual rows=10 loops=2) 3451 -> Unique (actual rows=10 loops=2) 3452 -> Merge Append (actual rows=38 loops=2) 3453 Sort Key: _hyper_1_1_chunk.dev 3454 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=10 loops=2) 3455 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=10 loops=2) 3456 Index Cond: (dev >= "*VALUES*".column1) 3457 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=10 loops=2) 3458 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=10 loops=2) 3459 Index Cond: (dev >= "*VALUES*".column1) 3460 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=10 loops=2) 3461 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=10 loops=2) 3462 Index Cond: (dev >= "*VALUES*".column1) 3463 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=10 loops=2) 3464 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=10 loops=2) 3465 Index Cond: (dev >= "*VALUES*".column1) 3466(18 rows) 3467 3468-- Emulate multi-column DISTINCT using multiple SkipSkans 3469:PREFIX SELECT time, dev, val, 'q9_1' FROM (SELECT b.* FROM 3470 (SELECT DISTINCT ON (dev) dev FROM :TABLE) a, 3471 LATERAL (SELECT DISTINCT ON (time) * FROM :TABLE WHERE dev = a.dev) b) c; 3472 QUERY PLAN 3473------------------------------------------------------------------------------------------------------------------------------------------------------------- 3474 Nested Loop (actual rows=10000 loops=1) 3475 -> Unique (actual rows=11 loops=1) 3476 -> Merge Append (actual rows=44 loops=1) 3477 Sort Key: _hyper_1_1_chunk.dev 3478 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 3479 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 3480 Index Cond: (dev > NULL::integer) 3481 Heap Fetches: 11 3482 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 3483 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 3484 Index Cond: (dev > NULL::integer) 3485 Heap Fetches: 11 3486 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 3487 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 3488 Index Cond: (dev > NULL::integer) 3489 Heap Fetches: 11 3490 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 3491 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 3492 Index Cond: (dev > NULL::integer) 3493 Heap Fetches: 11 3494 -> Result (actual rows=909 loops=11) 3495 -> Unique (actual rows=909 loops=11) 3496 -> Merge Append (actual rows=909 loops=11) 3497 Sort Key: _hyper_1_1_chunk_1."time" 3498 -> Custom Scan (SkipScan) on _hyper_1_1_chunk _hyper_1_1_chunk_1 (actual rows=227 loops=11) 3499 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_time_idx on _hyper_1_1_chunk _hyper_1_1_chunk_1 (actual rows=227 loops=11) 3500 Index Cond: (dev = _hyper_1_1_chunk.dev) 3501 -> Custom Scan (SkipScan) on _hyper_1_2_chunk _hyper_1_2_chunk_1 (actual rows=227 loops=11) 3502 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_time_idx on _hyper_1_2_chunk _hyper_1_2_chunk_1 (actual rows=227 loops=11) 3503 Index Cond: (dev = _hyper_1_1_chunk.dev) 3504 -> Custom Scan (SkipScan) on _hyper_1_3_chunk _hyper_1_3_chunk_1 (actual rows=227 loops=11) 3505 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_time_idx on _hyper_1_3_chunk _hyper_1_3_chunk_1 (actual rows=227 loops=11) 3506 Index Cond: (dev = _hyper_1_1_chunk.dev) 3507 -> Custom Scan (SkipScan) on _hyper_1_4_chunk _hyper_1_4_chunk_1 (actual rows=227 loops=11) 3508 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_time_idx on _hyper_1_4_chunk _hyper_1_4_chunk_1 (actual rows=227 loops=11) 3509 Index Cond: (dev = _hyper_1_1_chunk.dev) 3510(36 rows) 3511 3512:PREFIX SELECT time, dev, NULL, 'q9_2' FROM (SELECT b.* FROM 3513 (SELECT DISTINCT ON (dev) dev FROM :TABLE) a, 3514 LATERAL (SELECT DISTINCT ON (time) dev, time FROM :TABLE WHERE dev = a.dev) b) c; 3515 QUERY PLAN 3516------------------------------------------------------------------------------------------------------------------------------------------------------------ 3517 Nested Loop (actual rows=10000 loops=1) 3518 -> Unique (actual rows=11 loops=1) 3519 -> Merge Append (actual rows=44 loops=1) 3520 Sort Key: _hyper_1_1_chunk.dev 3521 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 3522 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 3523 Index Cond: (dev > NULL::integer) 3524 Heap Fetches: 11 3525 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 3526 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 3527 Index Cond: (dev > NULL::integer) 3528 Heap Fetches: 11 3529 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 3530 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 3531 Index Cond: (dev > NULL::integer) 3532 Heap Fetches: 11 3533 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 3534 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 3535 Index Cond: (dev > NULL::integer) 3536 Heap Fetches: 11 3537 -> Unique (actual rows=909 loops=11) 3538 -> Merge Append (actual rows=909 loops=11) 3539 Sort Key: _hyper_1_1_chunk_1."time" 3540 -> Custom Scan (SkipScan) on _hyper_1_1_chunk _hyper_1_1_chunk_1 (actual rows=227 loops=11) 3541 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_time_idx on _hyper_1_1_chunk _hyper_1_1_chunk_1 (actual rows=227 loops=11) 3542 Index Cond: ((dev = _hyper_1_1_chunk.dev) AND ("time" > NULL::integer)) 3543 Heap Fetches: 2500 3544 -> Custom Scan (SkipScan) on _hyper_1_2_chunk _hyper_1_2_chunk_1 (actual rows=227 loops=11) 3545 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_time_idx on _hyper_1_2_chunk _hyper_1_2_chunk_1 (actual rows=227 loops=11) 3546 Index Cond: ((dev = _hyper_1_1_chunk.dev) AND ("time" > NULL::integer)) 3547 Heap Fetches: 2500 3548 -> Custom Scan (SkipScan) on _hyper_1_3_chunk _hyper_1_3_chunk_1 (actual rows=227 loops=11) 3549 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_time_idx on _hyper_1_3_chunk _hyper_1_3_chunk_1 (actual rows=227 loops=11) 3550 Index Cond: ((dev = _hyper_1_1_chunk.dev) AND ("time" > NULL::integer)) 3551 Heap Fetches: 2500 3552 -> Custom Scan (SkipScan) on _hyper_1_4_chunk _hyper_1_4_chunk_1 (actual rows=227 loops=11) 3553 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_time_idx on _hyper_1_4_chunk _hyper_1_4_chunk_1 (actual rows=227 loops=11) 3554 Index Cond: ((dev = _hyper_1_1_chunk.dev) AND ("time" > NULL::integer)) 3555 Heap Fetches: 2500 3556(39 rows) 3557 3558-- Test that the multi-column DISTINCT emulation is equivalent to a real multi-column DISTINCT 3559:PREFIX SELECT * FROM 3560 (SELECT DISTINCT ON (dev) dev FROM :TABLE) a, 3561 LATERAL (SELECT DISTINCT ON (time) dev, time FROM :TABLE WHERE dev = a.dev) b; 3562 QUERY PLAN 3563------------------------------------------------------------------------------------------------------------------------------------------------------------ 3564 Nested Loop (actual rows=10000 loops=1) 3565 -> Unique (actual rows=11 loops=1) 3566 -> Merge Append (actual rows=44 loops=1) 3567 Sort Key: _hyper_1_1_chunk.dev 3568 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 3569 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 3570 Index Cond: (dev > NULL::integer) 3571 Heap Fetches: 11 3572 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 3573 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 3574 Index Cond: (dev > NULL::integer) 3575 Heap Fetches: 11 3576 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 3577 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 3578 Index Cond: (dev > NULL::integer) 3579 Heap Fetches: 11 3580 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 3581 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 3582 Index Cond: (dev > NULL::integer) 3583 Heap Fetches: 11 3584 -> Unique (actual rows=909 loops=11) 3585 -> Merge Append (actual rows=909 loops=11) 3586 Sort Key: _hyper_1_1_chunk_1."time" 3587 -> Custom Scan (SkipScan) on _hyper_1_1_chunk _hyper_1_1_chunk_1 (actual rows=227 loops=11) 3588 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_time_idx on _hyper_1_1_chunk _hyper_1_1_chunk_1 (actual rows=227 loops=11) 3589 Index Cond: ((dev = _hyper_1_1_chunk.dev) AND ("time" > NULL::integer)) 3590 Heap Fetches: 2500 3591 -> Custom Scan (SkipScan) on _hyper_1_2_chunk _hyper_1_2_chunk_1 (actual rows=227 loops=11) 3592 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_time_idx on _hyper_1_2_chunk _hyper_1_2_chunk_1 (actual rows=227 loops=11) 3593 Index Cond: ((dev = _hyper_1_1_chunk.dev) AND ("time" > NULL::integer)) 3594 Heap Fetches: 2500 3595 -> Custom Scan (SkipScan) on _hyper_1_3_chunk _hyper_1_3_chunk_1 (actual rows=227 loops=11) 3596 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_time_idx on _hyper_1_3_chunk _hyper_1_3_chunk_1 (actual rows=227 loops=11) 3597 Index Cond: ((dev = _hyper_1_1_chunk.dev) AND ("time" > NULL::integer)) 3598 Heap Fetches: 2500 3599 -> Custom Scan (SkipScan) on _hyper_1_4_chunk _hyper_1_4_chunk_1 (actual rows=227 loops=11) 3600 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_time_idx on _hyper_1_4_chunk _hyper_1_4_chunk_1 (actual rows=227 loops=11) 3601 Index Cond: ((dev = _hyper_1_1_chunk.dev) AND ("time" > NULL::integer)) 3602 Heap Fetches: 2500 3603(39 rows) 3604 3605:PREFIX SELECT DISTINCT ON (dev, time) dev, time FROM :TABLE WHERE dev IS NOT NULL; 3606 QUERY PLAN 3607----------------------------------------------------------------------------------------------------------------------------- 3608 Unique (actual rows=10000 loops=1) 3609 -> Merge Append (actual rows=10000 loops=1) 3610 Sort Key: _hyper_1_1_chunk.dev, _hyper_1_1_chunk."time" 3611 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_time_idx on _hyper_1_1_chunk (actual rows=2500 loops=1) 3612 Index Cond: (dev IS NOT NULL) 3613 Heap Fetches: 2500 3614 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_time_idx on _hyper_1_2_chunk (actual rows=2500 loops=1) 3615 Index Cond: (dev IS NOT NULL) 3616 Heap Fetches: 2500 3617 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_time_idx on _hyper_1_3_chunk (actual rows=2500 loops=1) 3618 Index Cond: (dev IS NOT NULL) 3619 Heap Fetches: 2500 3620 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_time_idx on _hyper_1_4_chunk (actual rows=2500 loops=1) 3621 Index Cond: (dev IS NOT NULL) 3622 Heap Fetches: 2500 3623(15 rows) 3624 3625:PREFIX SELECT DISTINCT ON (dev, time) dev, time FROM :TABLE WHERE dev IS NOT NULL 3626UNION SELECT b.* FROM 3627 (SELECT DISTINCT ON (dev) dev FROM :TABLE) a, 3628 LATERAL (SELECT DISTINCT ON (time) dev, time FROM :TABLE WHERE dev = a.dev) b; 3629 QUERY PLAN 3630------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 3631 Unique (actual rows=10000 loops=1) 3632 -> Sort (actual rows=20000 loops=1) 3633 Sort Key: _hyper_1_1_chunk.dev, _hyper_1_1_chunk."time" 3634 Sort Method: quicksort 3635 -> Append (actual rows=20000 loops=1) 3636 -> Unique (actual rows=10000 loops=1) 3637 -> Merge Append (actual rows=10000 loops=1) 3638 Sort Key: _hyper_1_1_chunk.dev, _hyper_1_1_chunk."time" 3639 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_time_idx on _hyper_1_1_chunk (actual rows=2500 loops=1) 3640 Index Cond: (dev IS NOT NULL) 3641 Heap Fetches: 2500 3642 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_time_idx on _hyper_1_2_chunk (actual rows=2500 loops=1) 3643 Index Cond: (dev IS NOT NULL) 3644 Heap Fetches: 2500 3645 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_time_idx on _hyper_1_3_chunk (actual rows=2500 loops=1) 3646 Index Cond: (dev IS NOT NULL) 3647 Heap Fetches: 2500 3648 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_time_idx on _hyper_1_4_chunk (actual rows=2500 loops=1) 3649 Index Cond: (dev IS NOT NULL) 3650 Heap Fetches: 2500 3651 -> Nested Loop (actual rows=10000 loops=1) 3652 -> Unique (actual rows=11 loops=1) 3653 -> Merge Append (actual rows=44 loops=1) 3654 Sort Key: _hyper_1_1_chunk_1.dev 3655 -> Custom Scan (SkipScan) on _hyper_1_1_chunk _hyper_1_1_chunk_1 (actual rows=11 loops=1) 3656 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk _hyper_1_1_chunk_1 (actual rows=11 loops=1) 3657 Index Cond: (dev > NULL::integer) 3658 Heap Fetches: 11 3659 -> Custom Scan (SkipScan) on _hyper_1_2_chunk _hyper_1_2_chunk_1 (actual rows=11 loops=1) 3660 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk _hyper_1_2_chunk_1 (actual rows=11 loops=1) 3661 Index Cond: (dev > NULL::integer) 3662 Heap Fetches: 11 3663 -> Custom Scan (SkipScan) on _hyper_1_3_chunk _hyper_1_3_chunk_1 (actual rows=11 loops=1) 3664 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk _hyper_1_3_chunk_1 (actual rows=11 loops=1) 3665 Index Cond: (dev > NULL::integer) 3666 Heap Fetches: 11 3667 -> Custom Scan (SkipScan) on _hyper_1_4_chunk _hyper_1_4_chunk_1 (actual rows=11 loops=1) 3668 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk _hyper_1_4_chunk_1 (actual rows=11 loops=1) 3669 Index Cond: (dev > NULL::integer) 3670 Heap Fetches: 11 3671 -> Unique (actual rows=909 loops=11) 3672 -> Merge Append (actual rows=909 loops=11) 3673 Sort Key: _hyper_1_1_chunk_2."time" 3674 -> Custom Scan (SkipScan) on _hyper_1_1_chunk _hyper_1_1_chunk_2 (actual rows=227 loops=11) 3675 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_time_idx on _hyper_1_1_chunk _hyper_1_1_chunk_2 (actual rows=227 loops=11) 3676 Index Cond: ((dev = _hyper_1_1_chunk_1.dev) AND ("time" > NULL::integer)) 3677 Heap Fetches: 2500 3678 -> Custom Scan (SkipScan) on _hyper_1_2_chunk _hyper_1_2_chunk_2 (actual rows=227 loops=11) 3679 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_time_idx on _hyper_1_2_chunk _hyper_1_2_chunk_2 (actual rows=227 loops=11) 3680 Index Cond: ((dev = _hyper_1_1_chunk_1.dev) AND ("time" > NULL::integer)) 3681 Heap Fetches: 2500 3682 -> Custom Scan (SkipScan) on _hyper_1_3_chunk _hyper_1_3_chunk_2 (actual rows=227 loops=11) 3683 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_time_idx on _hyper_1_3_chunk _hyper_1_3_chunk_2 (actual rows=227 loops=11) 3684 Index Cond: ((dev = _hyper_1_1_chunk_1.dev) AND ("time" > NULL::integer)) 3685 Heap Fetches: 2500 3686 -> Custom Scan (SkipScan) on _hyper_1_4_chunk _hyper_1_4_chunk_2 (actual rows=227 loops=11) 3687 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_time_idx on _hyper_1_4_chunk _hyper_1_4_chunk_2 (actual rows=227 loops=11) 3688 Index Cond: ((dev = _hyper_1_1_chunk_1.dev) AND ("time" > NULL::integer)) 3689 Heap Fetches: 2500 3690(59 rows) 3691 3692-- SkipScan into INSERT 3693:PREFIX INSERT INTO skip_scan_insert(time, dev, val, query) SELECT time, dev, val, 'q10_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a; 3694 QUERY PLAN 3695----------------------------------------------------------------------------------------------------------------------------------------- 3696 Insert on skip_scan_insert (actual rows=0 loops=1) 3697 -> Subquery Scan on a (actual rows=11 loops=1) 3698 -> Result (actual rows=11 loops=1) 3699 -> Unique (actual rows=11 loops=1) 3700 -> Merge Append (actual rows=44 loops=1) 3701 Sort Key: _hyper_1_1_chunk.dev 3702 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 3703 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 3704 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 3705 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 3706 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 3707 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 3708 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 3709 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 3710(14 rows) 3711 3712-- parallel query 3713SET force_parallel_mode TO true; 3714:PREFIX SELECT DISTINCT dev FROM :TABLE ORDER BY dev; 3715 QUERY PLAN 3716---------------------------------------------------------------------------------------------------------------------------- 3717 Unique (actual rows=11 loops=1) 3718 -> Merge Append (actual rows=44 loops=1) 3719 Sort Key: _hyper_1_1_chunk.dev 3720 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 3721 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx on _hyper_1_1_chunk (actual rows=11 loops=1) 3722 Index Cond: (dev > NULL::integer) 3723 Heap Fetches: 11 3724 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 3725 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx on _hyper_1_2_chunk (actual rows=11 loops=1) 3726 Index Cond: (dev > NULL::integer) 3727 Heap Fetches: 11 3728 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 3729 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx on _hyper_1_3_chunk (actual rows=11 loops=1) 3730 Index Cond: (dev > NULL::integer) 3731 Heap Fetches: 11 3732 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 3733 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx on _hyper_1_4_chunk (actual rows=11 loops=1) 3734 Index Cond: (dev > NULL::integer) 3735 Heap Fetches: 11 3736(19 rows) 3737 3738RESET force_parallel_mode; 3739TRUNCATE skip_scan_insert; 3740-- table with only nulls 3741:PREFIX SELECT DISTINCT ON (time) time FROM skip_scan_nulls; 3742 QUERY PLAN 3743------------------------------------------------------------------------------------------------------- 3744 Unique (actual rows=1 loops=1) 3745 -> Custom Scan (SkipScan) on skip_scan_nulls (actual rows=1 loops=1) 3746 -> Index Only Scan using skip_scan_nulls_time_idx on skip_scan_nulls (actual rows=1 loops=1) 3747 Index Cond: ("time" > NULL::integer) 3748 Heap Fetches: 1 3749(5 rows) 3750 3751-- no tuples in resultset 3752:PREFIX SELECT DISTINCT ON (time) time FROM skip_scan_nulls WHERE time IS NOT NULL; 3753 QUERY PLAN 3754------------------------------------------------------------------------------------------------------- 3755 Unique (actual rows=0 loops=1) 3756 -> Custom Scan (SkipScan) on skip_scan_nulls (actual rows=0 loops=1) 3757 -> Index Only Scan using skip_scan_nulls_time_idx on skip_scan_nulls (actual rows=0 loops=1) 3758 Index Cond: (("time" > NULL::integer) AND ("time" IS NOT NULL)) 3759 Heap Fetches: 0 3760(5 rows) 3761 3762\ir include/skip_scan_query_ht.sql 3763-- This file and its contents are licensed under the Timescale License. 3764-- Please see the included NOTICE for copyright information and 3765-- LICENSE-TIMESCALE for a copy of the license. 3766CREATE INDEX ON :TABLE(dev); 3767CREATE INDEX ON :TABLE(time); 3768-- SkipScan with ordered append 3769:PREFIX SELECT DISTINCT ON (time) time FROM :TABLE ORDER BY time; 3770 QUERY PLAN 3771------------------------------------------------------------------------------------------------------------------------------ 3772 Unique (actual rows=1000 loops=1) 3773 -> Custom Scan (ChunkAppend) on skip_scan_ht (actual rows=1000 loops=1) 3774 Order: skip_scan_ht."time" 3775 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=250 loops=1) 3776 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_time_idx on _hyper_1_1_chunk (actual rows=250 loops=1) 3777 Index Cond: ("time" > NULL::integer) 3778 Heap Fetches: 250 3779 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=250 loops=1) 3780 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_time_idx on _hyper_1_2_chunk (actual rows=250 loops=1) 3781 Index Cond: ("time" > NULL::integer) 3782 Heap Fetches: 250 3783 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=250 loops=1) 3784 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_time_idx on _hyper_1_3_chunk (actual rows=250 loops=1) 3785 Index Cond: ("time" > NULL::integer) 3786 Heap Fetches: 250 3787 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=250 loops=1) 3788 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_time_idx on _hyper_1_4_chunk (actual rows=250 loops=1) 3789 Index Cond: ("time" > NULL::integer) 3790 Heap Fetches: 250 3791(19 rows) 3792 3793--baseline query with skipscan 3794:PREFIX SELECT DISTINCT ON (dev) dev, dev_name FROM :TABLE; 3795 QUERY PLAN 3796------------------------------------------------------------------------------------------------------------------------ 3797 Unique (actual rows=11 loops=1) 3798 -> Merge Append (actual rows=44 loops=1) 3799 Sort Key: _hyper_1_1_chunk.dev 3800 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 3801 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx1 on _hyper_1_1_chunk (actual rows=11 loops=1) 3802 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 3803 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx1 on _hyper_1_2_chunk (actual rows=11 loops=1) 3804 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 3805 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx1 on _hyper_1_3_chunk (actual rows=11 loops=1) 3806 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 3807 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx1 on _hyper_1_4_chunk (actual rows=11 loops=1) 3808(11 rows) 3809 3810-- compression doesnt prevent skipscan 3811SELECT compress_chunk('_timescaledb_internal._hyper_1_1_chunk'); 3812 compress_chunk 3813---------------------------------------- 3814 _timescaledb_internal._hyper_1_1_chunk 3815(1 row) 3816 3817:PREFIX SELECT DISTINCT ON (dev) dev, dev_name FROM :TABLE; 3818 QUERY PLAN 3819--------------------------------------------------------------------------------------------------------------------------------------------------------- 3820 Unique (actual rows=11 loops=1) 3821 -> Merge Append (actual rows=2538 loops=1) 3822 Sort Key: _hyper_1_1_chunk.dev 3823 -> Custom Scan (DecompressChunk) on _hyper_1_1_chunk (actual rows=2505 loops=1) 3824 -> Index Scan using compress_hyper_2_5_chunk__compressed_hypertable_2_dev__ts_meta_ on compress_hyper_2_5_chunk (actual rows=11 loops=1) 3825 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 3826 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx1 on _hyper_1_2_chunk (actual rows=11 loops=1) 3827 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 3828 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx1 on _hyper_1_3_chunk (actual rows=11 loops=1) 3829 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 3830 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx1 on _hyper_1_4_chunk (actual rows=11 loops=1) 3831(11 rows) 3832 3833SELECT decompress_chunk('_timescaledb_internal._hyper_1_1_chunk'); 3834 decompress_chunk 3835---------------------------------------- 3836 _timescaledb_internal._hyper_1_1_chunk 3837(1 row) 3838 3839--baseline query with skipscan 3840:PREFIX SELECT DISTINCT ON (dev) dev, dev_name FROM :TABLE; 3841 QUERY PLAN 3842------------------------------------------------------------------------------------------------------------------------ 3843 Unique (actual rows=11 loops=1) 3844 -> Merge Append (actual rows=44 loops=1) 3845 Sort Key: _hyper_1_1_chunk.dev 3846 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 3847 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx1 on _hyper_1_1_chunk (actual rows=11 loops=1) 3848 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 3849 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx1 on _hyper_1_2_chunk (actual rows=11 loops=1) 3850 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 3851 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx1 on _hyper_1_3_chunk (actual rows=11 loops=1) 3852 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 3853 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx1 on _hyper_1_4_chunk (actual rows=11 loops=1) 3854(11 rows) 3855 3856-- partial indexes don't prevent skipscan 3857DROP INDEX _timescaledb_internal._hyper_1_1_chunk_skip_scan_ht_dev_idx; 3858:PREFIX SELECT DISTINCT ON (dev) dev, dev_name FROM :TABLE; 3859 QUERY PLAN 3860------------------------------------------------------------------------------------------------------------------------ 3861 Unique (actual rows=11 loops=1) 3862 -> Merge Append (actual rows=44 loops=1) 3863 Sort Key: _hyper_1_1_chunk.dev 3864 -> Custom Scan (SkipScan) on _hyper_1_1_chunk (actual rows=11 loops=1) 3865 -> Index Scan using _hyper_1_1_chunk_skip_scan_ht_dev_idx1 on _hyper_1_1_chunk (actual rows=11 loops=1) 3866 -> Custom Scan (SkipScan) on _hyper_1_2_chunk (actual rows=11 loops=1) 3867 -> Index Scan using _hyper_1_2_chunk_skip_scan_ht_dev_idx1 on _hyper_1_2_chunk (actual rows=11 loops=1) 3868 -> Custom Scan (SkipScan) on _hyper_1_3_chunk (actual rows=11 loops=1) 3869 -> Index Scan using _hyper_1_3_chunk_skip_scan_ht_dev_idx1 on _hyper_1_3_chunk (actual rows=11 loops=1) 3870 -> Custom Scan (SkipScan) on _hyper_1_4_chunk (actual rows=11 loops=1) 3871 -> Index Scan using _hyper_1_4_chunk_skip_scan_ht_dev_idx1 on _hyper_1_4_chunk (actual rows=11 loops=1) 3872(11 rows) 3873 3874-- IndexPath without pathkeys doesnt use SkipScan 3875EXPLAIN (costs off, timing off, summary off) SELECT DISTINCT 1 FROM pg_rewrite; 3876 QUERY PLAN 3877------------------------------------------------------------------------- 3878 Unique 3879 -> Index Only Scan using pg_rewrite_rel_rulename_index on pg_rewrite 3880(2 rows) 3881 3882-- try one query with EXPLAIN only for coverage 3883EXPLAIN (costs off, timing off, summary off) SELECT DISTINCT ON (dev_name) dev_name FROM skip_scan; 3884 QUERY PLAN 3885----------------------------------------------------------------------- 3886 Unique 3887 -> Custom Scan (SkipScan) on skip_scan 3888 -> Index Only Scan using skip_scan_dev_name_idx on skip_scan 3889 Index Cond: (dev_name > NULL::text) 3890(4 rows) 3891 3892EXPLAIN (costs off, timing off, summary off) SELECT DISTINCT ON (dev_name) dev_name FROM skip_scan_ht; 3893 QUERY PLAN 3894-------------------------------------------------------------------------------------------------------- 3895 Unique 3896 -> Merge Append 3897 Sort Key: _hyper_1_1_chunk.dev_name 3898 -> Custom Scan (SkipScan) on _hyper_1_1_chunk 3899 -> Index Only Scan using _hyper_1_1_chunk_skip_scan_ht_dev_name_idx on _hyper_1_1_chunk 3900 Index Cond: (dev_name > NULL::text) 3901 -> Custom Scan (SkipScan) on _hyper_1_2_chunk 3902 -> Index Only Scan using _hyper_1_2_chunk_skip_scan_ht_dev_name_idx on _hyper_1_2_chunk 3903 Index Cond: (dev_name > NULL::text) 3904 -> Custom Scan (SkipScan) on _hyper_1_3_chunk 3905 -> Index Only Scan using _hyper_1_3_chunk_skip_scan_ht_dev_name_idx on _hyper_1_3_chunk 3906 Index Cond: (dev_name > NULL::text) 3907 -> Custom Scan (SkipScan) on _hyper_1_4_chunk 3908 -> Index Only Scan using _hyper_1_4_chunk_skip_scan_ht_dev_name_idx on _hyper_1_4_chunk 3909 Index Cond: (dev_name > NULL::text) 3910(15 rows) 3911 3912-- #3629 skipscan with constant skipscan column in where clause 3913CREATE TABLE i3629(a int, time timestamptz NOT NULL); 3914SELECT table_name FROM create_hypertable('i3629', 'time'); 3915 table_name 3916------------ 3917 i3629 3918(1 row) 3919 3920INSERT INTO i3629 SELECT i, '2020-04-01'::date-10-i from generate_series(1,20) i; 3921EXPLAIN (SUMMARY OFF, COSTS OFF) SELECT DISTINCT ON (a) * FROM i3629 WHERE a in (2) ORDER BY a ASC, time DESC; 3922 QUERY PLAN 3923------------------------------------------------ 3924 Unique 3925 -> Sort 3926 Sort Key: _hyper_3_6_chunk."time" DESC 3927 -> Append 3928 -> Seq Scan on _hyper_3_6_chunk 3929 Filter: (a = 2) 3930 -> Seq Scan on _hyper_3_7_chunk 3931 Filter: (a = 2) 3932 -> Seq Scan on _hyper_3_8_chunk 3933 Filter: (a = 2) 3934 -> Seq Scan on _hyper_3_9_chunk 3935 Filter: (a = 2) 3936(12 rows) 3937 3938SELECT DISTINCT ON (a) * FROM i3629 WHERE a in (2) ORDER BY a ASC, time DESC; 3939 a | time 3940---+------------------------------ 3941 2 | Fri Mar 20 00:00:00 2020 PDT 3942(1 row) 3943 3944-- #3720 skipscan not being used on varchar column 3945CREATE TABLE i3720(time timestamptz not null,data varchar); 3946SELECT table_name FROM create_hypertable('i3720','time'); 3947 table_name 3948------------ 3949 i3720 3950(1 row) 3951 3952INSERT INTO i3720 3953SELECT time, (array['Yes', 'No', 'Maybe'])[floor(random() * 3 + 1)] 3954FROM generate_series('2000-01-01'::timestamptz,'2000-01-03'::timestamptz, '10 minute'::interval) AS g1(time); 3955CREATE INDEX ON i3720(data, time); 3956ANALYZE i3720; 3957:PREFIX SELECT DISTINCT ON(data) * FROM i3720; 3958 QUERY PLAN 3959---------------------------------------------------------------------------------------------------------------------- 3960 Unique (actual rows=3 loops=1) 3961 -> Custom Scan (SkipScan) on _hyper_4_10_chunk (actual rows=3 loops=1) 3962 -> Index Only Scan using _hyper_4_10_chunk_i3720_data_time_idx on _hyper_4_10_chunk (actual rows=3 loops=1) 3963 Index Cond: (data > NULL::text) 3964 Heap Fetches: 3 3965(5 rows) 3966 3967