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