1-- This file and its contents are licensed under the Apache License 2.0. 2-- Please see the included NOTICE for copyright information and 3-- LICENSE-APACHE for a copy of the license. 4\set PREFIX 'EXPLAIN (costs off) ' 5\ir include/plan_expand_hypertable_load.sql 6-- This file and its contents are licensed under the Apache License 2.0. 7-- Please see the included NOTICE for copyright information and 8-- LICENSE-APACHE for a copy of the license. 9--single time dimension 10CREATE TABLE hyper ("time_broken" bigint NOT NULL, "value" integer); 11ALTER TABLE hyper 12DROP COLUMN time_broken, 13ADD COLUMN time BIGINT; 14SELECT create_hypertable('hyper', 'time', chunk_time_interval => 10); 15psql:include/plan_expand_hypertable_load.sql:12: NOTICE: adding not-null constraint to column "time" 16 create_hypertable 17-------------------- 18 (1,public,hyper,t) 19(1 row) 20 21INSERT INTO hyper SELECT g, g FROM generate_series(0,1000) g; 22--insert a point with INT_MAX_64 23INSERT INTO hyper (time, value) SELECT 9223372036854775807::bigint, 0; 24--time and space 25CREATE TABLE hyper_w_space ("time_broken" bigint NOT NULL, "device_id" text, "value" integer); 26ALTER TABLE hyper_w_space 27DROP COLUMN time_broken, 28ADD COLUMN time BIGINT; 29SELECT create_hypertable('hyper_w_space', 'time', 'device_id', 4, chunk_time_interval => 10); 30psql:include/plan_expand_hypertable_load.sql:26: NOTICE: adding not-null constraint to column "time" 31 create_hypertable 32---------------------------- 33 (2,public,hyper_w_space,t) 34(1 row) 35 36INSERT INTO hyper_w_space (time, device_id, value) SELECT g, 'dev' || g, g FROM generate_series(0,30) g; 37CREATE VIEW hyper_w_space_view AS (SELECT * FROM hyper_w_space); 38--with timestamp and space 39CREATE TABLE tag (id serial PRIMARY KEY, name text); 40CREATE TABLE hyper_ts ("time_broken" timestamptz NOT NULL, "device_id" text, tag_id INT REFERENCES tag(id), "value" integer); 41ALTER TABLE hyper_ts 42DROP COLUMN time_broken, 43ADD COLUMN time TIMESTAMPTZ; 44SELECT create_hypertable('hyper_ts', 'time', 'device_id', 2, chunk_time_interval => '10 seconds'::interval); 45psql:include/plan_expand_hypertable_load.sql:41: NOTICE: adding not-null constraint to column "time" 46 create_hypertable 47----------------------- 48 (3,public,hyper_ts,t) 49(1 row) 50 51INSERT INTO tag(name) SELECT 'tag'||g FROM generate_series(0,10) g; 52INSERT INTO hyper_ts (time, device_id, tag_id, value) SELECT to_timestamp(g), 'dev' || g, (random() /10)+1, g FROM generate_series(0,30) g; 53--one in the future 54INSERT INTO hyper_ts (time, device_id, tag_id, value) VALUES ('2100-01-01 02:03:04 PST', 'dev101', 1, 0); 55--time partitioning function 56CREATE OR REPLACE FUNCTION unix_to_timestamp(unixtime float8) 57 RETURNS TIMESTAMPTZ LANGUAGE SQL IMMUTABLE PARALLEL SAFE STRICT AS 58$BODY$ 59 SELECT to_timestamp(unixtime); 60$BODY$; 61CREATE TABLE hyper_timefunc ("time" float8 NOT NULL, "device_id" text, "value" integer); 62SELECT create_hypertable('hyper_timefunc', 'time', 'device_id', 4, chunk_time_interval => 10, time_partitioning_func => 'unix_to_timestamp'); 63psql:include/plan_expand_hypertable_load.sql:57: WARNING: unexpected interval: smaller than one second 64 create_hypertable 65----------------------------- 66 (4,public,hyper_timefunc,t) 67(1 row) 68 69INSERT INTO hyper_timefunc (time, device_id, value) SELECT g, 'dev' || g, g FROM generate_series(0,30) g; 70CREATE TABLE metrics_timestamp(time timestamp); 71SELECT create_hypertable('metrics_timestamp','time'); 72psql:include/plan_expand_hypertable_load.sql:62: NOTICE: adding not-null constraint to column "time" 73 create_hypertable 74-------------------------------- 75 (5,public,metrics_timestamp,t) 76(1 row) 77 78INSERT INTO metrics_timestamp SELECT generate_series('2000-01-01'::timestamp,'2000-02-01'::timestamp,'1d'::interval); 79CREATE TABLE metrics_timestamptz(time timestamptz, device_id int); 80SELECT create_hypertable('metrics_timestamptz','time'); 81psql:include/plan_expand_hypertable_load.sql:66: NOTICE: adding not-null constraint to column "time" 82 create_hypertable 83---------------------------------- 84 (6,public,metrics_timestamptz,t) 85(1 row) 86 87INSERT INTO metrics_timestamptz SELECT generate_series('2000-01-01'::timestamptz,'2000-02-01'::timestamptz,'1d'::interval), 1; 88INSERT INTO metrics_timestamptz SELECT generate_series('2000-01-01'::timestamptz,'2000-02-01'::timestamptz,'1d'::interval), 2; 89INSERT INTO metrics_timestamptz SELECT generate_series('2000-01-01'::timestamptz,'2000-02-01'::timestamptz,'1d'::interval), 3; 90--create a second table to test joins with 91CREATE TABLE metrics_timestamptz_2 (LIKE metrics_timestamptz); 92SELECT create_hypertable('metrics_timestamptz_2','time'); 93 create_hypertable 94------------------------------------ 95 (7,public,metrics_timestamptz_2,t) 96(1 row) 97 98INSERT INTO metrics_timestamptz_2 99SELECT * FROM metrics_timestamptz; 100INSERT INTO metrics_timestamptz_2 VALUES ('2000-12-01'::timestamptz, 3); 101CREATE TABLE metrics_date(time date); 102SELECT create_hypertable('metrics_date','time'); 103psql:include/plan_expand_hypertable_load.sql:79: NOTICE: adding not-null constraint to column "time" 104 create_hypertable 105--------------------------- 106 (8,public,metrics_date,t) 107(1 row) 108 109INSERT INTO metrics_date SELECT generate_series('2000-01-01'::date,'2000-02-01'::date,'1d'::interval); 110ANALYZE hyper; 111ANALYZE hyper_w_space; 112ANALYZE tag; 113ANALYZE hyper_ts; 114ANALYZE hyper_timefunc; 115-- create normal table for JOIN tests 116CREATE TABLE regular_timestamptz(time timestamptz); 117INSERT INTO regular_timestamptz SELECT generate_series('2000-01-01'::timestamptz,'2000-02-01'::timestamptz,'1d'::interval); 118\ir include/plan_expand_hypertable_query.sql 119-- This file and its contents are licensed under the Apache License 2.0. 120-- Please see the included NOTICE for copyright information and 121-- LICENSE-APACHE for a copy of the license. 122--we want to see how our logic excludes chunks 123--and not how much work constraint_exclusion does 124SET constraint_exclusion = 'off'; 125\qecho test upper bounds 126test upper bounds 127:PREFIX SELECT * FROM hyper WHERE time < 10 ORDER BY value; 128 QUERY PLAN 129------------------------------------ 130 Sort 131 Sort Key: _hyper_1_1_chunk.value 132 -> Seq Scan on _hyper_1_1_chunk 133 Filter: ("time" < 10) 134(4 rows) 135 136:PREFIX SELECT * FROM hyper WHERE time < 11 ORDER BY value; 137 QUERY PLAN 138------------------------------------------ 139 Sort 140 Sort Key: _hyper_1_1_chunk.value 141 -> Append 142 -> Seq Scan on _hyper_1_1_chunk 143 Filter: ("time" < 11) 144 -> Seq Scan on _hyper_1_2_chunk 145 Filter: ("time" < 11) 146(7 rows) 147 148:PREFIX SELECT * FROM hyper WHERE time = 10 ORDER BY value; 149 QUERY PLAN 150------------------------------------ 151 Sort 152 Sort Key: _hyper_1_2_chunk.value 153 -> Seq Scan on _hyper_1_2_chunk 154 Filter: ("time" = 10) 155(4 rows) 156 157:PREFIX SELECT * FROM hyper WHERE 10 >= time ORDER BY value; 158 QUERY PLAN 159------------------------------------------ 160 Sort 161 Sort Key: _hyper_1_1_chunk.value 162 -> Append 163 -> Seq Scan on _hyper_1_1_chunk 164 Filter: (10 >= "time") 165 -> Seq Scan on _hyper_1_2_chunk 166 Filter: (10 >= "time") 167(7 rows) 168 169\qecho test lower bounds 170test lower bounds 171:PREFIX SELECT * FROM hyper WHERE time >= 10 and time < 20 ORDER BY value; 172 QUERY PLAN 173---------------------------------------------------- 174 Sort 175 Sort Key: _hyper_1_2_chunk.value 176 -> Seq Scan on _hyper_1_2_chunk 177 Filter: (("time" >= 10) AND ("time" < 20)) 178(4 rows) 179 180:PREFIX SELECT * FROM hyper WHERE 10 < time and 20 >= time ORDER BY value; 181 QUERY PLAN 182---------------------------------------------------------- 183 Sort 184 Sort Key: _hyper_1_2_chunk.value 185 -> Append 186 -> Seq Scan on _hyper_1_2_chunk 187 Filter: ((10 < "time") AND (20 >= "time")) 188 -> Seq Scan on _hyper_1_3_chunk 189 Filter: ((10 < "time") AND (20 >= "time")) 190(7 rows) 191 192:PREFIX SELECT * FROM hyper WHERE time >= 9 and time < 20 ORDER BY value; 193 QUERY PLAN 194--------------------------------------------------------- 195 Sort 196 Sort Key: _hyper_1_1_chunk.value 197 -> Append 198 -> Seq Scan on _hyper_1_1_chunk 199 Filter: (("time" >= 9) AND ("time" < 20)) 200 -> Seq Scan on _hyper_1_2_chunk 201 Filter: (("time" >= 9) AND ("time" < 20)) 202(7 rows) 203 204:PREFIX SELECT * FROM hyper WHERE time > 9 and time < 20 ORDER BY value; 205 QUERY PLAN 206-------------------------------------------------- 207 Sort 208 Sort Key: _hyper_1_2_chunk.value 209 -> Seq Scan on _hyper_1_2_chunk 210 Filter: (("time" > 9) AND ("time" < 20)) 211(4 rows) 212 213\qecho test empty result 214test empty result 215:PREFIX SELECT * FROM hyper WHERE time < 0; 216 QUERY PLAN 217-------------------------- 218 Result 219 One-Time Filter: false 220(2 rows) 221 222\qecho test expression evaluation 223test expression evaluation 224:PREFIX SELECT * FROM hyper WHERE time < (5*2)::smallint; 225 QUERY PLAN 226------------------------------------- 227 Seq Scan on _hyper_1_1_chunk 228 Filter: ("time" < '10'::smallint) 229(2 rows) 230 231\qecho test logic at INT64_MAX 232test logic at INT64_MAX 233:PREFIX SELECT * FROM hyper WHERE time = 9223372036854775807::bigint ORDER BY value; 234 QUERY PLAN 235---------------------------------------------------------- 236 Sort 237 Sort Key: _hyper_1_102_chunk.value 238 -> Seq Scan on _hyper_1_102_chunk 239 Filter: ("time" = '9223372036854775807'::bigint) 240(4 rows) 241 242:PREFIX SELECT * FROM hyper WHERE time = 9223372036854775806::bigint ORDER BY value; 243 QUERY PLAN 244---------------------------------------------------------- 245 Sort 246 Sort Key: _hyper_1_102_chunk.value 247 -> Seq Scan on _hyper_1_102_chunk 248 Filter: ("time" = '9223372036854775806'::bigint) 249(4 rows) 250 251:PREFIX SELECT * FROM hyper WHERE time >= 9223372036854775807::bigint ORDER BY value; 252 QUERY PLAN 253----------------------------------------------------------- 254 Sort 255 Sort Key: _hyper_1_102_chunk.value 256 -> Seq Scan on _hyper_1_102_chunk 257 Filter: ("time" >= '9223372036854775807'::bigint) 258(4 rows) 259 260:PREFIX SELECT * FROM hyper WHERE time > 9223372036854775807::bigint ORDER BY value; 261 QUERY PLAN 262-------------------------------- 263 Sort 264 Sort Key: value 265 -> Result 266 One-Time Filter: false 267(4 rows) 268 269:PREFIX SELECT * FROM hyper WHERE time > 9223372036854775806::bigint ORDER BY value; 270 QUERY PLAN 271---------------------------------------------------------- 272 Sort 273 Sort Key: _hyper_1_102_chunk.value 274 -> Seq Scan on _hyper_1_102_chunk 275 Filter: ("time" > '9223372036854775806'::bigint) 276(4 rows) 277 278\qecho cte 279cte 280:PREFIX WITH cte AS( 281 SELECT * FROM hyper WHERE time < 10 282) 283SELECT * FROM cte ORDER BY value; 284 QUERY PLAN 285------------------------------------ 286 Sort 287 Sort Key: _hyper_1_1_chunk.value 288 -> Seq Scan on _hyper_1_1_chunk 289 Filter: ("time" < 10) 290(4 rows) 291 292\qecho subquery 293subquery 294:PREFIX SELECT 0 = ANY (SELECT value FROM hyper WHERE time < 10); 295 QUERY PLAN 296-------------------------------------- 297 Result 298 SubPlan 1 299 -> Seq Scan on _hyper_1_1_chunk 300 Filter: ("time" < 10) 301(4 rows) 302 303\qecho no space constraint 304no space constraint 305:PREFIX SELECT * FROM hyper_w_space WHERE time < 10 ORDER BY value; 306 QUERY PLAN 307-------------------------------------------- 308 Sort 309 Sort Key: _hyper_2_106_chunk.value 310 -> Append 311 -> Seq Scan on _hyper_2_106_chunk 312 Filter: ("time" < 10) 313 -> Seq Scan on _hyper_2_104_chunk 314 Filter: ("time" < 10) 315 -> Seq Scan on _hyper_2_105_chunk 316 Filter: ("time" < 10) 317 -> Seq Scan on _hyper_2_103_chunk 318 Filter: ("time" < 10) 319(11 rows) 320 321\qecho valid space constraint 322valid space constraint 323:PREFIX SELECT * FROM hyper_w_space WHERE time < 10 and device_id = 'dev5' ORDER BY value; 324 QUERY PLAN 325---------------------------------------------------------------- 326 Sort 327 Sort Key: _hyper_2_106_chunk.value 328 -> Seq Scan on _hyper_2_106_chunk 329 Filter: (("time" < 10) AND (device_id = 'dev5'::text)) 330(4 rows) 331 332:PREFIX SELECT * FROM hyper_w_space WHERE time < 10 and 'dev5' = device_id ORDER BY value; 333 QUERY PLAN 334---------------------------------------------------------------- 335 Sort 336 Sort Key: _hyper_2_106_chunk.value 337 -> Seq Scan on _hyper_2_106_chunk 338 Filter: (("time" < 10) AND ('dev5'::text = device_id)) 339(4 rows) 340 341:PREFIX SELECT * FROM hyper_w_space WHERE time < 10 and 'dev'||(2+3) = device_id ORDER BY value; 342 QUERY PLAN 343---------------------------------------------------------------- 344 Sort 345 Sort Key: _hyper_2_106_chunk.value 346 -> Seq Scan on _hyper_2_106_chunk 347 Filter: (("time" < 10) AND ('dev5'::text = device_id)) 348(4 rows) 349 350\qecho only space constraint 351only space constraint 352:PREFIX SELECT * FROM hyper_w_space WHERE 'dev5' = device_id ORDER BY value; 353 QUERY PLAN 354-------------------------------------------------- 355 Sort 356 Sort Key: _hyper_2_106_chunk.value 357 -> Append 358 -> Seq Scan on _hyper_2_106_chunk 359 Filter: ('dev5'::text = device_id) 360 -> Seq Scan on _hyper_2_111_chunk 361 Filter: ('dev5'::text = device_id) 362 -> Seq Scan on _hyper_2_109_chunk 363 Filter: ('dev5'::text = device_id) 364(9 rows) 365 366\qecho unhandled space constraint 367unhandled space constraint 368:PREFIX SELECT * FROM hyper_w_space WHERE time < 10 and device_id > 'dev5' ORDER BY value; 369 QUERY PLAN 370---------------------------------------------------------------------- 371 Sort 372 Sort Key: _hyper_2_106_chunk.value 373 -> Append 374 -> Seq Scan on _hyper_2_106_chunk 375 Filter: (("time" < 10) AND (device_id > 'dev5'::text)) 376 -> Seq Scan on _hyper_2_104_chunk 377 Filter: (("time" < 10) AND (device_id > 'dev5'::text)) 378 -> Seq Scan on _hyper_2_105_chunk 379 Filter: (("time" < 10) AND (device_id > 'dev5'::text)) 380 -> Seq Scan on _hyper_2_103_chunk 381 Filter: (("time" < 10) AND (device_id > 'dev5'::text)) 382(11 rows) 383 384\qecho use of OR - does not filter chunks 385use of OR - does not filter chunks 386:PREFIX SELECT * FROM hyper_w_space WHERE time < 10 AND (device_id = 'dev5' or device_id = 'dev6') ORDER BY value; 387 QUERY PLAN 388------------------------------------------------------------------------------------------------------ 389 Sort 390 Sort Key: _hyper_2_106_chunk.value 391 -> Append 392 -> Seq Scan on _hyper_2_106_chunk 393 Filter: (("time" < 10) AND ((device_id = 'dev5'::text) OR (device_id = 'dev6'::text))) 394 -> Seq Scan on _hyper_2_104_chunk 395 Filter: (("time" < 10) AND ((device_id = 'dev5'::text) OR (device_id = 'dev6'::text))) 396 -> Seq Scan on _hyper_2_105_chunk 397 Filter: (("time" < 10) AND ((device_id = 'dev5'::text) OR (device_id = 'dev6'::text))) 398 -> Seq Scan on _hyper_2_103_chunk 399 Filter: (("time" < 10) AND ((device_id = 'dev5'::text) OR (device_id = 'dev6'::text))) 400(11 rows) 401 402\qecho cte 403cte 404:PREFIX WITH cte AS( 405 SELECT * FROM hyper_w_space WHERE time < 10 and device_id = 'dev5' 406) 407SELECT * FROM cte ORDER BY value; 408 QUERY PLAN 409---------------------------------------------------------------- 410 Sort 411 Sort Key: _hyper_2_106_chunk.value 412 -> Seq Scan on _hyper_2_106_chunk 413 Filter: (("time" < 10) AND (device_id = 'dev5'::text)) 414(4 rows) 415 416\qecho subquery 417subquery 418:PREFIX SELECT 0 = ANY (SELECT value FROM hyper_w_space WHERE time < 10 and device_id = 'dev5'); 419 QUERY PLAN 420------------------------------------------------------------------ 421 Result 422 SubPlan 1 423 -> Seq Scan on _hyper_2_106_chunk 424 Filter: (("time" < 10) AND (device_id = 'dev5'::text)) 425(4 rows) 426 427\qecho view 428view 429:PREFIX SELECT * FROM hyper_w_space_view WHERE time < 10 and device_id = 'dev5' ORDER BY value; 430 QUERY PLAN 431---------------------------------------------------------------- 432 Sort 433 Sort Key: _hyper_2_106_chunk.value 434 -> Seq Scan on _hyper_2_106_chunk 435 Filter: (("time" < 10) AND (device_id = 'dev5'::text)) 436(4 rows) 437 438\qecho IN statement - simple 439IN statement - simple 440:PREFIX SELECT * FROM hyper_w_space WHERE time < 10 AND device_id IN ('dev5') ORDER BY value; 441 QUERY PLAN 442---------------------------------------------------------------- 443 Sort 444 Sort Key: _hyper_2_106_chunk.value 445 -> Seq Scan on _hyper_2_106_chunk 446 Filter: (("time" < 10) AND (device_id = 'dev5'::text)) 447(4 rows) 448 449\qecho IN statement - two chunks 450IN statement - two chunks 451:PREFIX SELECT * FROM hyper_w_space WHERE time < 10 AND device_id IN ('dev5','dev6') ORDER BY value; 452 QUERY PLAN 453------------------------------------------------------------------------------------- 454 Sort 455 Sort Key: _hyper_2_106_chunk.value 456 -> Append 457 -> Seq Scan on _hyper_2_106_chunk 458 Filter: (("time" < 10) AND (device_id = ANY ('{dev5,dev6}'::text[]))) 459 -> Seq Scan on _hyper_2_105_chunk 460 Filter: (("time" < 10) AND (device_id = ANY ('{dev5,dev6}'::text[]))) 461(7 rows) 462 463\qecho IN statement - one chunk 464IN statement - one chunk 465:PREFIX SELECT * FROM hyper_w_space WHERE time < 10 AND device_id IN ('dev4','dev5') ORDER BY value; 466 QUERY PLAN 467------------------------------------------------------------------------------- 468 Sort 469 Sort Key: _hyper_2_106_chunk.value 470 -> Seq Scan on _hyper_2_106_chunk 471 Filter: (("time" < 10) AND (device_id = ANY ('{dev4,dev5}'::text[]))) 472(4 rows) 473 474\qecho NOT IN - does not filter chunks 475NOT IN - does not filter chunks 476:PREFIX SELECT * FROM hyper_w_space WHERE time < 10 AND device_id NOT IN ('dev5','dev6') ORDER BY value; 477 QUERY PLAN 478-------------------------------------------------------------------------------------- 479 Sort 480 Sort Key: _hyper_2_106_chunk.value 481 -> Append 482 -> Seq Scan on _hyper_2_106_chunk 483 Filter: (("time" < 10) AND (device_id <> ALL ('{dev5,dev6}'::text[]))) 484 -> Seq Scan on _hyper_2_104_chunk 485 Filter: (("time" < 10) AND (device_id <> ALL ('{dev5,dev6}'::text[]))) 486 -> Seq Scan on _hyper_2_105_chunk 487 Filter: (("time" < 10) AND (device_id <> ALL ('{dev5,dev6}'::text[]))) 488 -> Seq Scan on _hyper_2_103_chunk 489 Filter: (("time" < 10) AND (device_id <> ALL ('{dev5,dev6}'::text[]))) 490(11 rows) 491 492\qecho IN statement with subquery - does not filter chunks 493IN statement with subquery - does not filter chunks 494:PREFIX SELECT * FROM hyper_w_space WHERE time < 10 AND device_id IN (SELECT 'dev5'::text) ORDER BY value; 495 QUERY PLAN 496---------------------------------------------------------------- 497 Sort 498 Sort Key: _hyper_2_106_chunk.value 499 -> Seq Scan on _hyper_2_106_chunk 500 Filter: (("time" < 10) AND (device_id = 'dev5'::text)) 501(4 rows) 502 503\qecho ANY 504ANY 505:PREFIX SELECT * FROM hyper_w_space WHERE time < 10 AND device_id = ANY(ARRAY['dev5','dev6']) ORDER BY value; 506 QUERY PLAN 507------------------------------------------------------------------------------------- 508 Sort 509 Sort Key: _hyper_2_106_chunk.value 510 -> Append 511 -> Seq Scan on _hyper_2_106_chunk 512 Filter: (("time" < 10) AND (device_id = ANY ('{dev5,dev6}'::text[]))) 513 -> Seq Scan on _hyper_2_105_chunk 514 Filter: (("time" < 10) AND (device_id = ANY ('{dev5,dev6}'::text[]))) 515(7 rows) 516 517\qecho ANY with intersection 518ANY with intersection 519:PREFIX SELECT * FROM hyper_w_space WHERE time < 10 AND device_id = ANY(ARRAY['dev5','dev6']) AND device_id = ANY(ARRAY['dev6','dev7']) ORDER BY value; 520 QUERY PLAN 521----------------------------------------------------------------------------------------------------------------------------- 522 Sort 523 Sort Key: _hyper_2_105_chunk.value 524 -> Seq Scan on _hyper_2_105_chunk 525 Filter: (("time" < 10) AND (device_id = ANY ('{dev5,dev6}'::text[])) AND (device_id = ANY ('{dev6,dev7}'::text[]))) 526(4 rows) 527 528\qecho ANY without intersection shouldnt scan any chunks 529ANY without intersection shouldnt scan any chunks 530:PREFIX SELECT * FROM hyper_w_space WHERE time < 10 AND device_id = ANY(ARRAY['dev5','dev6']) AND device_id = ANY(ARRAY['dev8','dev9']) ORDER BY value; 531 QUERY PLAN 532-------------------------------- 533 Sort 534 Sort Key: value 535 -> Result 536 One-Time Filter: false 537(4 rows) 538 539\qecho ANY/IN/ALL only works for equals operator 540ANY/IN/ALL only works for equals operator 541:PREFIX SELECT * FROM hyper_w_space WHERE device_id < ANY(ARRAY['dev5','dev6']) ORDER BY value; 542 QUERY PLAN 543----------------------------------------------------------------- 544 Sort 545 Sort Key: _hyper_2_103_chunk.value 546 -> Append 547 -> Seq Scan on _hyper_2_103_chunk 548 Filter: (device_id < ANY ('{dev5,dev6}'::text[])) 549 -> Seq Scan on _hyper_2_104_chunk 550 Filter: (device_id < ANY ('{dev5,dev6}'::text[])) 551 -> Seq Scan on _hyper_2_105_chunk 552 Filter: (device_id < ANY ('{dev5,dev6}'::text[])) 553 -> Seq Scan on _hyper_2_106_chunk 554 Filter: (device_id < ANY ('{dev5,dev6}'::text[])) 555 -> Seq Scan on _hyper_2_107_chunk 556 Filter: (device_id < ANY ('{dev5,dev6}'::text[])) 557 -> Seq Scan on _hyper_2_108_chunk 558 Filter: (device_id < ANY ('{dev5,dev6}'::text[])) 559 -> Seq Scan on _hyper_2_109_chunk 560 Filter: (device_id < ANY ('{dev5,dev6}'::text[])) 561 -> Seq Scan on _hyper_2_110_chunk 562 Filter: (device_id < ANY ('{dev5,dev6}'::text[])) 563 -> Seq Scan on _hyper_2_111_chunk 564 Filter: (device_id < ANY ('{dev5,dev6}'::text[])) 565 -> Seq Scan on _hyper_2_112_chunk 566 Filter: (device_id < ANY ('{dev5,dev6}'::text[])) 567 -> Seq Scan on _hyper_2_113_chunk 568 Filter: (device_id < ANY ('{dev5,dev6}'::text[])) 569 -> Seq Scan on _hyper_2_114_chunk 570 Filter: (device_id < ANY ('{dev5,dev6}'::text[])) 571 -> Seq Scan on _hyper_2_115_chunk 572 Filter: (device_id < ANY ('{dev5,dev6}'::text[])) 573(29 rows) 574 575\qecho ALL with equals and different values shouldnt scan any chunks 576ALL with equals and different values shouldnt scan any chunks 577:PREFIX SELECT * FROM hyper_w_space WHERE device_id = ALL(ARRAY['dev5','dev6']) ORDER BY value; 578 QUERY PLAN 579-------------------------------- 580 Sort 581 Sort Key: value 582 -> Result 583 One-Time Filter: false 584(4 rows) 585 586\qecho Multi AND 587Multi AND 588:PREFIX SELECT * FROM hyper_w_space WHERE time < 10 AND time < 100 ORDER BY value; 589 QUERY PLAN 590---------------------------------------------------------- 591 Sort 592 Sort Key: _hyper_2_106_chunk.value 593 -> Append 594 -> Seq Scan on _hyper_2_106_chunk 595 Filter: (("time" < 10) AND ("time" < 100)) 596 -> Seq Scan on _hyper_2_104_chunk 597 Filter: (("time" < 10) AND ("time" < 100)) 598 -> Seq Scan on _hyper_2_105_chunk 599 Filter: (("time" < 10) AND ("time" < 100)) 600 -> Seq Scan on _hyper_2_103_chunk 601 Filter: (("time" < 10) AND ("time" < 100)) 602(11 rows) 603 604\qecho Time dimension doesnt filter chunks when using IN/ANY with multiple arguments 605Time dimension doesnt filter chunks when using IN/ANY with multiple arguments 606:PREFIX SELECT * FROM hyper_w_space WHERE time < ANY(ARRAY[1,2]) ORDER BY value; 607 QUERY PLAN 608----------------------------------------------------------- 609 Sort 610 Sort Key: _hyper_2_103_chunk.value 611 -> Append 612 -> Seq Scan on _hyper_2_103_chunk 613 Filter: ("time" < ANY ('{1,2}'::integer[])) 614 -> Seq Scan on _hyper_2_104_chunk 615 Filter: ("time" < ANY ('{1,2}'::integer[])) 616 -> Seq Scan on _hyper_2_105_chunk 617 Filter: ("time" < ANY ('{1,2}'::integer[])) 618 -> Seq Scan on _hyper_2_106_chunk 619 Filter: ("time" < ANY ('{1,2}'::integer[])) 620 -> Seq Scan on _hyper_2_107_chunk 621 Filter: ("time" < ANY ('{1,2}'::integer[])) 622 -> Seq Scan on _hyper_2_108_chunk 623 Filter: ("time" < ANY ('{1,2}'::integer[])) 624 -> Seq Scan on _hyper_2_109_chunk 625 Filter: ("time" < ANY ('{1,2}'::integer[])) 626 -> Seq Scan on _hyper_2_110_chunk 627 Filter: ("time" < ANY ('{1,2}'::integer[])) 628 -> Seq Scan on _hyper_2_111_chunk 629 Filter: ("time" < ANY ('{1,2}'::integer[])) 630 -> Seq Scan on _hyper_2_112_chunk 631 Filter: ("time" < ANY ('{1,2}'::integer[])) 632 -> Seq Scan on _hyper_2_113_chunk 633 Filter: ("time" < ANY ('{1,2}'::integer[])) 634 -> Seq Scan on _hyper_2_114_chunk 635 Filter: ("time" < ANY ('{1,2}'::integer[])) 636 -> Seq Scan on _hyper_2_115_chunk 637 Filter: ("time" < ANY ('{1,2}'::integer[])) 638(29 rows) 639 640\qecho Time dimension chunk filtering works for ANY with single argument 641Time dimension chunk filtering works for ANY with single argument 642:PREFIX SELECT * FROM hyper_w_space WHERE time < ANY(ARRAY[1]) ORDER BY value; 643 QUERY PLAN 644--------------------------------------------------------- 645 Sort 646 Sort Key: _hyper_2_106_chunk.value 647 -> Append 648 -> Seq Scan on _hyper_2_106_chunk 649 Filter: ("time" < ANY ('{1}'::integer[])) 650 -> Seq Scan on _hyper_2_104_chunk 651 Filter: ("time" < ANY ('{1}'::integer[])) 652 -> Seq Scan on _hyper_2_105_chunk 653 Filter: ("time" < ANY ('{1}'::integer[])) 654 -> Seq Scan on _hyper_2_103_chunk 655 Filter: ("time" < ANY ('{1}'::integer[])) 656(11 rows) 657 658\qecho Time dimension chunk filtering works for ALL with single argument 659Time dimension chunk filtering works for ALL with single argument 660:PREFIX SELECT * FROM hyper_w_space WHERE time < ALL(ARRAY[1]) ORDER BY value; 661 QUERY PLAN 662--------------------------------------------------------- 663 Sort 664 Sort Key: _hyper_2_106_chunk.value 665 -> Append 666 -> Seq Scan on _hyper_2_106_chunk 667 Filter: ("time" < ALL ('{1}'::integer[])) 668 -> Seq Scan on _hyper_2_104_chunk 669 Filter: ("time" < ALL ('{1}'::integer[])) 670 -> Seq Scan on _hyper_2_105_chunk 671 Filter: ("time" < ALL ('{1}'::integer[])) 672 -> Seq Scan on _hyper_2_103_chunk 673 Filter: ("time" < ALL ('{1}'::integer[])) 674(11 rows) 675 676\qecho Time dimension chunk filtering works for ALL with multiple arguments 677Time dimension chunk filtering works for ALL with multiple arguments 678:PREFIX SELECT * FROM hyper_w_space WHERE time < ALL(ARRAY[1,10,20,30]) ORDER BY value; 679 QUERY PLAN 680------------------------------------------------------------------ 681 Sort 682 Sort Key: _hyper_2_106_chunk.value 683 -> Append 684 -> Seq Scan on _hyper_2_106_chunk 685 Filter: ("time" < ALL ('{1,10,20,30}'::integer[])) 686 -> Seq Scan on _hyper_2_104_chunk 687 Filter: ("time" < ALL ('{1,10,20,30}'::integer[])) 688 -> Seq Scan on _hyper_2_105_chunk 689 Filter: ("time" < ALL ('{1,10,20,30}'::integer[])) 690 -> Seq Scan on _hyper_2_103_chunk 691 Filter: ("time" < ALL ('{1,10,20,30}'::integer[])) 692(11 rows) 693 694\qecho AND intersection using IN and EQUALS 695AND intersection using IN and EQUALS 696:PREFIX SELECT * FROM hyper_w_space WHERE device_id IN ('dev1','dev2') AND device_id = 'dev1' ORDER BY value; 697 QUERY PLAN 698-------------------------------------------------------------------------------------------------- 699 Sort 700 Sort Key: _hyper_2_103_chunk.value 701 -> Append 702 -> Seq Scan on _hyper_2_103_chunk 703 Filter: ((device_id = ANY ('{dev1,dev2}'::text[])) AND (device_id = 'dev1'::text)) 704 -> Seq Scan on _hyper_2_110_chunk 705 Filter: ((device_id = ANY ('{dev1,dev2}'::text[])) AND (device_id = 'dev1'::text)) 706 -> Seq Scan on _hyper_2_114_chunk 707 Filter: ((device_id = ANY ('{dev1,dev2}'::text[])) AND (device_id = 'dev1'::text)) 708(9 rows) 709 710\qecho AND with no intersection using IN and EQUALS 711AND with no intersection using IN and EQUALS 712:PREFIX SELECT * FROM hyper_w_space WHERE device_id IN ('dev1','dev2') AND device_id = 'dev3' ORDER BY value; 713 QUERY PLAN 714-------------------------------- 715 Sort 716 Sort Key: value 717 -> Result 718 One-Time Filter: false 719(4 rows) 720 721\qecho timestamps 722timestamps 723\qecho these should work since they are immutable functions 724these should work since they are immutable functions 725:PREFIX SELECT * FROM hyper_ts WHERE time < 'Wed Dec 31 16:00:10 1969 PST'::timestamptz ORDER BY value; 726 QUERY PLAN 727------------------------------------------------------------------------------------------- 728 Sort 729 Sort Key: _hyper_3_117_chunk.value 730 -> Append 731 -> Seq Scan on _hyper_3_117_chunk 732 Filter: ("time" < 'Wed Dec 31 16:00:10 1969 PST'::timestamp with time zone) 733 -> Seq Scan on _hyper_3_116_chunk 734 Filter: ("time" < 'Wed Dec 31 16:00:10 1969 PST'::timestamp with time zone) 735(7 rows) 736 737:PREFIX SELECT * FROM hyper_ts WHERE time < to_timestamp(10) ORDER BY value; 738 QUERY PLAN 739------------------------------------------------------------------------------------------- 740 Sort 741 Sort Key: _hyper_3_117_chunk.value 742 -> Append 743 -> Seq Scan on _hyper_3_117_chunk 744 Filter: ("time" < 'Wed Dec 31 16:00:10 1969 PST'::timestamp with time zone) 745 -> Seq Scan on _hyper_3_116_chunk 746 Filter: ("time" < 'Wed Dec 31 16:00:10 1969 PST'::timestamp with time zone) 747(7 rows) 748 749:PREFIX SELECT * FROM hyper_ts WHERE time < 'Wed Dec 31 16:00:10 1969'::timestamp AT TIME ZONE 'PST' ORDER BY value; 750 QUERY PLAN 751------------------------------------------------------------------------------------------- 752 Sort 753 Sort Key: _hyper_3_117_chunk.value 754 -> Append 755 -> Seq Scan on _hyper_3_117_chunk 756 Filter: ("time" < 'Wed Dec 31 16:00:10 1969 PST'::timestamp with time zone) 757 -> Seq Scan on _hyper_3_116_chunk 758 Filter: ("time" < 'Wed Dec 31 16:00:10 1969 PST'::timestamp with time zone) 759(7 rows) 760 761:PREFIX SELECT * FROM hyper_ts WHERE time < to_timestamp(10) and device_id = 'dev1' ORDER BY value; 762 QUERY PLAN 763---------------------------------------------------------------------------------------------------------------------- 764 Sort 765 Sort Key: _hyper_3_116_chunk.value 766 -> Seq Scan on _hyper_3_116_chunk 767 Filter: (("time" < 'Wed Dec 31 16:00:10 1969 PST'::timestamp with time zone) AND (device_id = 'dev1'::text)) 768(4 rows) 769 770\qecho these should not work since uses stable functions; 771these should not work since uses stable functions; 772:PREFIX SELECT * FROM hyper_ts WHERE time < 'Wed Dec 31 16:00:10 1969'::timestamp ORDER BY value; 773 QUERY PLAN 774------------------------------------------------------------------------------------------ 775 Sort 776 Sort Key: hyper_ts.value 777 -> Custom Scan (ChunkAppend) on hyper_ts 778 Chunks excluded during startup: 6 779 -> Seq Scan on _hyper_3_116_chunk 780 Filter: ("time" < 'Wed Dec 31 16:00:10 1969'::timestamp without time zone) 781 -> Seq Scan on _hyper_3_117_chunk 782 Filter: ("time" < 'Wed Dec 31 16:00:10 1969'::timestamp without time zone) 783(8 rows) 784 785:PREFIX SELECT * FROM hyper_ts WHERE time < ('Wed Dec 31 16:00:10 1969'::timestamp::timestamptz) ORDER BY value; 786 QUERY PLAN 787---------------------------------------------------------------------------------------------------------------------- 788 Sort 789 Sort Key: hyper_ts.value 790 -> Custom Scan (ChunkAppend) on hyper_ts 791 Chunks excluded during startup: 6 792 -> Seq Scan on _hyper_3_116_chunk 793 Filter: ("time" < ('Wed Dec 31 16:00:10 1969'::timestamp without time zone)::timestamp with time zone) 794 -> Seq Scan on _hyper_3_117_chunk 795 Filter: ("time" < ('Wed Dec 31 16:00:10 1969'::timestamp without time zone)::timestamp with time zone) 796(8 rows) 797 798:PREFIX SELECT * FROM hyper_ts WHERE NOW() < time ORDER BY value; 799 QUERY PLAN 800--------------------------------------------- 801 Sort 802 Sort Key: hyper_ts.value 803 -> Custom Scan (ChunkAppend) on hyper_ts 804 Chunks excluded during startup: 7 805 -> Seq Scan on _hyper_3_123_chunk 806 Filter: (now() < "time") 807(6 rows) 808 809\qecho joins 810joins 811:PREFIX SELECT * FROM hyper_ts WHERE tag_id IN (SELECT id FROM tag WHERE tag.id=1) and time < to_timestamp(10) and device_id = 'dev1' ORDER BY value; 812 QUERY PLAN 813--------------------------------------------------------------------------------------------------------------------------------------------- 814 Sort 815 Sort Key: _hyper_3_116_chunk.value 816 -> Nested Loop Semi Join 817 -> Seq Scan on _hyper_3_116_chunk 818 Filter: (("time" < 'Wed Dec 31 16:00:10 1969 PST'::timestamp with time zone) AND (device_id = 'dev1'::text) AND (tag_id = 1)) 819 -> Seq Scan on tag 820 Filter: (id = 1) 821(7 rows) 822 823:PREFIX SELECT * FROM hyper_ts WHERE tag_id IN (SELECT id FROM tag WHERE tag.id=1) or (time < to_timestamp(10) and device_id = 'dev1') ORDER BY value; 824 QUERY PLAN 825---------------------------------------------------------------------------------------------------------------------------------------------------- 826 Sort 827 Sort Key: hyper_ts.value 828 -> Custom Scan (ChunkAppend) on hyper_ts 829 -> Seq Scan on _hyper_3_116_chunk 830 Filter: ((hashed SubPlan 1) OR (("time" < 'Wed Dec 31 16:00:10 1969 PST'::timestamp with time zone) AND (device_id = 'dev1'::text))) 831 SubPlan 1 832 -> Seq Scan on tag 833 Filter: (id = 1) 834 -> Seq Scan on _hyper_3_117_chunk 835 Filter: ((hashed SubPlan 1) OR (("time" < 'Wed Dec 31 16:00:10 1969 PST'::timestamp with time zone) AND (device_id = 'dev1'::text))) 836 -> Seq Scan on _hyper_3_118_chunk 837 Filter: ((hashed SubPlan 1) OR (("time" < 'Wed Dec 31 16:00:10 1969 PST'::timestamp with time zone) AND (device_id = 'dev1'::text))) 838 -> Seq Scan on _hyper_3_119_chunk 839 Filter: ((hashed SubPlan 1) OR (("time" < 'Wed Dec 31 16:00:10 1969 PST'::timestamp with time zone) AND (device_id = 'dev1'::text))) 840 -> Seq Scan on _hyper_3_120_chunk 841 Filter: ((hashed SubPlan 1) OR (("time" < 'Wed Dec 31 16:00:10 1969 PST'::timestamp with time zone) AND (device_id = 'dev1'::text))) 842 -> Seq Scan on _hyper_3_121_chunk 843 Filter: ((hashed SubPlan 1) OR (("time" < 'Wed Dec 31 16:00:10 1969 PST'::timestamp with time zone) AND (device_id = 'dev1'::text))) 844 -> Seq Scan on _hyper_3_122_chunk 845 Filter: ((hashed SubPlan 1) OR (("time" < 'Wed Dec 31 16:00:10 1969 PST'::timestamp with time zone) AND (device_id = 'dev1'::text))) 846 -> Seq Scan on _hyper_3_123_chunk 847 Filter: ((hashed SubPlan 1) OR (("time" < 'Wed Dec 31 16:00:10 1969 PST'::timestamp with time zone) AND (device_id = 'dev1'::text))) 848(22 rows) 849 850:PREFIX SELECT * FROM hyper_ts WHERE tag_id IN (SELECT id FROM tag WHERE tag.name='tag1') and time < to_timestamp(10) and device_id = 'dev1' ORDER BY value; 851 QUERY PLAN 852---------------------------------------------------------------------------------------------------------------------------- 853 Sort 854 Sort Key: _hyper_3_116_chunk.value 855 -> Nested Loop 856 Join Filter: (_hyper_3_116_chunk.tag_id = tag.id) 857 -> Seq Scan on _hyper_3_116_chunk 858 Filter: (("time" < 'Wed Dec 31 16:00:10 1969 PST'::timestamp with time zone) AND (device_id = 'dev1'::text)) 859 -> Seq Scan on tag 860 Filter: (name = 'tag1'::text) 861(8 rows) 862 863:PREFIX SELECT * FROM hyper_ts JOIN tag on (hyper_ts.tag_id = tag.id ) WHERE time < to_timestamp(10) and device_id = 'dev1' ORDER BY value; 864 QUERY PLAN 865---------------------------------------------------------------------------------------------------------------------------------- 866 Sort 867 Sort Key: _hyper_3_116_chunk.value 868 -> Merge Join 869 Merge Cond: (tag.id = _hyper_3_116_chunk.tag_id) 870 -> Index Scan using tag_pkey on tag 871 -> Sort 872 Sort Key: _hyper_3_116_chunk.tag_id 873 -> Seq Scan on _hyper_3_116_chunk 874 Filter: (("time" < 'Wed Dec 31 16:00:10 1969 PST'::timestamp with time zone) AND (device_id = 'dev1'::text)) 875(9 rows) 876 877:PREFIX SELECT * FROM hyper_ts JOIN tag on (hyper_ts.tag_id = tag.id ) WHERE tag.name = 'tag1' and time < to_timestamp(10) and device_id = 'dev1' ORDER BY value; 878 QUERY PLAN 879---------------------------------------------------------------------------------------------------------------------------- 880 Sort 881 Sort Key: _hyper_3_116_chunk.value 882 -> Nested Loop 883 Join Filter: (_hyper_3_116_chunk.tag_id = tag.id) 884 -> Seq Scan on _hyper_3_116_chunk 885 Filter: (("time" < 'Wed Dec 31 16:00:10 1969 PST'::timestamp with time zone) AND (device_id = 'dev1'::text)) 886 -> Seq Scan on tag 887 Filter: (name = 'tag1'::text) 888(8 rows) 889 890\qecho test constraint exclusion for constraints in ON clause of JOINs 891test constraint exclusion for constraints in ON clause of JOINs 892\qecho should exclude chunks on m1 and propagate qual to m2 because of INNER JOIN 893should exclude chunks on m1 and propagate qual to m2 because of INNER JOIN 894:PREFIX SELECT m1.time,m2.time FROM metrics_timestamptz m1 INNER JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time AND m1.time < '2000-01-10' ORDER BY m1.time; 895 QUERY PLAN 896------------------------------------------------------------------------------------------------------------------------------- 897 Merge Join 898 Merge Cond: (m1."time" = m2."time") 899 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 900 Order: m1."time" 901 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 902 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 903 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 904 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 905 -> Materialize 906 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 907 Order: m2."time" 908 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 909 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 910 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 911 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 912(15 rows) 913 914\qecho should exclude chunks on m2 and propagate qual to m1 because of INNER JOIN 915should exclude chunks on m2 and propagate qual to m1 because of INNER JOIN 916:PREFIX SELECT m1.time,m2.time FROM metrics_timestamptz m1 INNER JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time AND m2.time < '2000-01-10' ORDER BY m1.time; 917 QUERY PLAN 918------------------------------------------------------------------------------------------------------------------------------- 919 Merge Join 920 Merge Cond: (m1."time" = m2."time") 921 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 922 Order: m1."time" 923 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 924 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 925 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 926 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 927 -> Materialize 928 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 929 Order: m2."time" 930 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 931 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 932 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 933 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 934(15 rows) 935 936\qecho must not exclude on m1 937must not exclude on m1 938:PREFIX SELECT m1.time,m2.time FROM metrics_timestamptz m1 LEFT JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time AND m1.time < '2000-01-10' ORDER BY m1.time; 939 QUERY PLAN 940------------------------------------------------------------------------------------------------------------------------------- 941 Merge Left Join 942 Merge Cond: (m1."time" = m2."time") 943 Join Filter: (m1."time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 944 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 945 Order: m1."time" 946 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 947 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 948 -> Index Only Scan Backward using _hyper_6_162_chunk_metrics_timestamptz_time_idx on _hyper_6_162_chunk m1_3 949 -> Index Only Scan Backward using _hyper_6_163_chunk_metrics_timestamptz_time_idx on _hyper_6_163_chunk m1_4 950 -> Index Only Scan Backward using _hyper_6_164_chunk_metrics_timestamptz_time_idx on _hyper_6_164_chunk m1_5 951 -> Materialize 952 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 953 Order: m2."time" 954 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 955 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 956 -> Index Only Scan Backward using _hyper_7_167_chunk_metrics_timestamptz_2_time_idx on _hyper_7_167_chunk m2_3 957 -> Index Only Scan Backward using _hyper_7_168_chunk_metrics_timestamptz_2_time_idx on _hyper_7_168_chunk m2_4 958 -> Index Only Scan Backward using _hyper_7_169_chunk_metrics_timestamptz_2_time_idx on _hyper_7_169_chunk m2_5 959 -> Index Only Scan Backward using _hyper_7_170_chunk_metrics_timestamptz_2_time_idx on _hyper_7_170_chunk m2_6 960(19 rows) 961 962\qecho should exclude chunks on m2 963should exclude chunks on m2 964:PREFIX SELECT m1.time,m2.time FROM metrics_timestamptz m1 LEFT JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time AND m2.time < '2000-01-10' ORDER BY m1.time; 965 QUERY PLAN 966------------------------------------------------------------------------------------------------------------------------------- 967 Merge Left Join 968 Merge Cond: (m1."time" = m2."time") 969 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 970 Order: m1."time" 971 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 972 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 973 -> Index Only Scan Backward using _hyper_6_162_chunk_metrics_timestamptz_time_idx on _hyper_6_162_chunk m1_3 974 -> Index Only Scan Backward using _hyper_6_163_chunk_metrics_timestamptz_time_idx on _hyper_6_163_chunk m1_4 975 -> Index Only Scan Backward using _hyper_6_164_chunk_metrics_timestamptz_time_idx on _hyper_6_164_chunk m1_5 976 -> Materialize 977 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 978 Order: m2."time" 979 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 980 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 981 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 982 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 983 -> Index Only Scan Backward using _hyper_7_167_chunk_metrics_timestamptz_2_time_idx on _hyper_7_167_chunk m2_3 984 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 985 -> Index Only Scan Backward using _hyper_7_168_chunk_metrics_timestamptz_2_time_idx on _hyper_7_168_chunk m2_4 986 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 987 -> Index Only Scan Backward using _hyper_7_169_chunk_metrics_timestamptz_2_time_idx on _hyper_7_169_chunk m2_5 988 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 989 -> Index Only Scan Backward using _hyper_7_170_chunk_metrics_timestamptz_2_time_idx on _hyper_7_170_chunk m2_6 990 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 991(24 rows) 992 993\qecho should exclude chunks on m1 994should exclude chunks on m1 995:PREFIX SELECT m1.time,m2.time FROM metrics_timestamptz m1 RIGHT JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time AND m1.time < '2000-01-10' ORDER BY m1.time; 996 QUERY PLAN 997------------------------------------------------------------------------------------------------------------------------------------- 998 Sort 999 Sort Key: m1."time" 1000 -> Merge Right Join 1001 Merge Cond: (m1."time" = m2."time") 1002 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 1003 Order: m1."time" 1004 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 1005 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 1006 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 1007 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 1008 -> Index Only Scan Backward using _hyper_6_162_chunk_metrics_timestamptz_time_idx on _hyper_6_162_chunk m1_3 1009 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 1010 -> Index Only Scan Backward using _hyper_6_163_chunk_metrics_timestamptz_time_idx on _hyper_6_163_chunk m1_4 1011 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 1012 -> Index Only Scan Backward using _hyper_6_164_chunk_metrics_timestamptz_time_idx on _hyper_6_164_chunk m1_5 1013 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 1014 -> Materialize 1015 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 1016 Order: m2."time" 1017 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 1018 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 1019 -> Index Only Scan Backward using _hyper_7_167_chunk_metrics_timestamptz_2_time_idx on _hyper_7_167_chunk m2_3 1020 -> Index Only Scan Backward using _hyper_7_168_chunk_metrics_timestamptz_2_time_idx on _hyper_7_168_chunk m2_4 1021 -> Index Only Scan Backward using _hyper_7_169_chunk_metrics_timestamptz_2_time_idx on _hyper_7_169_chunk m2_5 1022 -> Index Only Scan Backward using _hyper_7_170_chunk_metrics_timestamptz_2_time_idx on _hyper_7_170_chunk m2_6 1023(25 rows) 1024 1025\qecho must not exclude chunks on m2 1026must not exclude chunks on m2 1027:PREFIX SELECT m1.time,m2.time FROM metrics_timestamptz m1 RIGHT JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time AND m2.time < '2000-01-10' ORDER BY m1.time; 1028 QUERY PLAN 1029----------------------------------------------------------------------------------------------------------------------------------- 1030 Sort 1031 Sort Key: m1."time" 1032 -> Merge Left Join 1033 Merge Cond: (m2."time" = m1."time") 1034 Join Filter: (m2."time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 1035 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 1036 Order: m2."time" 1037 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 1038 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 1039 -> Index Only Scan Backward using _hyper_7_167_chunk_metrics_timestamptz_2_time_idx on _hyper_7_167_chunk m2_3 1040 -> Index Only Scan Backward using _hyper_7_168_chunk_metrics_timestamptz_2_time_idx on _hyper_7_168_chunk m2_4 1041 -> Index Only Scan Backward using _hyper_7_169_chunk_metrics_timestamptz_2_time_idx on _hyper_7_169_chunk m2_5 1042 -> Index Only Scan Backward using _hyper_7_170_chunk_metrics_timestamptz_2_time_idx on _hyper_7_170_chunk m2_6 1043 -> Materialize 1044 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 1045 Order: m1."time" 1046 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 1047 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 1048 -> Index Only Scan Backward using _hyper_6_162_chunk_metrics_timestamptz_time_idx on _hyper_6_162_chunk m1_3 1049 -> Index Only Scan Backward using _hyper_6_163_chunk_metrics_timestamptz_time_idx on _hyper_6_163_chunk m1_4 1050 -> Index Only Scan Backward using _hyper_6_164_chunk_metrics_timestamptz_time_idx on _hyper_6_164_chunk m1_5 1051(21 rows) 1052 1053\qecho time_bucket exclusion 1054time_bucket exclusion 1055:PREFIX SELECT * FROM hyper WHERE time_bucket(10, time) < 10::bigint ORDER BY time; 1056 QUERY PLAN 1057-------------------------------------------------------------------------------------------------------- 1058 Sort 1059 Sort Key: _hyper_1_1_chunk."time" 1060 -> Append 1061 -> Seq Scan on _hyper_1_1_chunk 1062 Filter: (("time" < '20'::bigint) AND (time_bucket('10'::bigint, "time") < '10'::bigint)) 1063 -> Seq Scan on _hyper_1_2_chunk 1064 Filter: (("time" < '20'::bigint) AND (time_bucket('10'::bigint, "time") < '10'::bigint)) 1065(7 rows) 1066 1067:PREFIX SELECT * FROM hyper WHERE time_bucket(10, time) < 11::bigint ORDER BY time; 1068 QUERY PLAN 1069-------------------------------------------------------------------------------------------------------- 1070 Sort 1071 Sort Key: _hyper_1_1_chunk."time" 1072 -> Append 1073 -> Seq Scan on _hyper_1_1_chunk 1074 Filter: (("time" < '21'::bigint) AND (time_bucket('10'::bigint, "time") < '11'::bigint)) 1075 -> Seq Scan on _hyper_1_2_chunk 1076 Filter: (("time" < '21'::bigint) AND (time_bucket('10'::bigint, "time") < '11'::bigint)) 1077 -> Seq Scan on _hyper_1_3_chunk 1078 Filter: (("time" < '21'::bigint) AND (time_bucket('10'::bigint, "time") < '11'::bigint)) 1079(9 rows) 1080 1081:PREFIX SELECT * FROM hyper WHERE time_bucket(10, time) <= 10::bigint ORDER BY time; 1082 QUERY PLAN 1083---------------------------------------------------------------------------------------------------------- 1084 Sort 1085 Sort Key: _hyper_1_1_chunk."time" 1086 -> Append 1087 -> Seq Scan on _hyper_1_1_chunk 1088 Filter: (("time" <= '20'::bigint) AND (time_bucket('10'::bigint, "time") <= '10'::bigint)) 1089 -> Seq Scan on _hyper_1_2_chunk 1090 Filter: (("time" <= '20'::bigint) AND (time_bucket('10'::bigint, "time") <= '10'::bigint)) 1091 -> Seq Scan on _hyper_1_3_chunk 1092 Filter: (("time" <= '20'::bigint) AND (time_bucket('10'::bigint, "time") <= '10'::bigint)) 1093(9 rows) 1094 1095:PREFIX SELECT * FROM hyper WHERE 10::bigint > time_bucket(10, time) ORDER BY time; 1096 QUERY PLAN 1097-------------------------------------------------------------------------------------------------------- 1098 Sort 1099 Sort Key: _hyper_1_1_chunk."time" 1100 -> Append 1101 -> Seq Scan on _hyper_1_1_chunk 1102 Filter: (("time" < '20'::bigint) AND ('10'::bigint > time_bucket('10'::bigint, "time"))) 1103 -> Seq Scan on _hyper_1_2_chunk 1104 Filter: (("time" < '20'::bigint) AND ('10'::bigint > time_bucket('10'::bigint, "time"))) 1105(7 rows) 1106 1107:PREFIX SELECT * FROM hyper WHERE 11::bigint > time_bucket(10, time) ORDER BY time; 1108 QUERY PLAN 1109-------------------------------------------------------------------------------------------------------- 1110 Sort 1111 Sort Key: _hyper_1_1_chunk."time" 1112 -> Append 1113 -> Seq Scan on _hyper_1_1_chunk 1114 Filter: (("time" < '21'::bigint) AND ('11'::bigint > time_bucket('10'::bigint, "time"))) 1115 -> Seq Scan on _hyper_1_2_chunk 1116 Filter: (("time" < '21'::bigint) AND ('11'::bigint > time_bucket('10'::bigint, "time"))) 1117 -> Seq Scan on _hyper_1_3_chunk 1118 Filter: (("time" < '21'::bigint) AND ('11'::bigint > time_bucket('10'::bigint, "time"))) 1119(9 rows) 1120 1121\qecho test overflow behaviour of time_bucket exclusion 1122test overflow behaviour of time_bucket exclusion 1123:PREFIX SELECT * FROM hyper WHERE time > 950 AND time_bucket(10, time) < '9223372036854775807'::bigint ORDER BY time; 1124 QUERY PLAN 1125---------------------------------------------------------------------------------------------------------------- 1126 Sort 1127 Sort Key: _hyper_1_96_chunk."time" 1128 -> Append 1129 -> Seq Scan on _hyper_1_96_chunk 1130 Filter: (("time" > 950) AND (time_bucket('10'::bigint, "time") < '9223372036854775807'::bigint)) 1131 -> Seq Scan on _hyper_1_97_chunk 1132 Filter: (("time" > 950) AND (time_bucket('10'::bigint, "time") < '9223372036854775807'::bigint)) 1133 -> Seq Scan on _hyper_1_98_chunk 1134 Filter: (("time" > 950) AND (time_bucket('10'::bigint, "time") < '9223372036854775807'::bigint)) 1135 -> Seq Scan on _hyper_1_99_chunk 1136 Filter: (("time" > 950) AND (time_bucket('10'::bigint, "time") < '9223372036854775807'::bigint)) 1137 -> Seq Scan on _hyper_1_100_chunk 1138 Filter: (("time" > 950) AND (time_bucket('10'::bigint, "time") < '9223372036854775807'::bigint)) 1139 -> Seq Scan on _hyper_1_101_chunk 1140 Filter: (("time" > 950) AND (time_bucket('10'::bigint, "time") < '9223372036854775807'::bigint)) 1141 -> Seq Scan on _hyper_1_102_chunk 1142 Filter: (("time" > 950) AND (time_bucket('10'::bigint, "time") < '9223372036854775807'::bigint)) 1143(17 rows) 1144 1145\qecho test timestamp upper boundary 1146test timestamp upper boundary 1147\qecho there should be no transformation if we are out of the supported (TimescaleDB-specific) range 1148there should be no transformation if we are out of the supported (TimescaleDB-specific) range 1149:PREFIX SELECT * FROM metrics_timestamp WHERE time_bucket('1d',time) < '294276-01-01'::timestamp ORDER BY time; 1150 QUERY PLAN 1151------------------------------------------------------------------------------------------------------------------------ 1152 Custom Scan (ChunkAppend) on metrics_timestamp 1153 Order: metrics_timestamp."time" 1154 -> Index Only Scan Backward using _hyper_5_155_chunk_metrics_timestamp_time_idx on _hyper_5_155_chunk 1155 Filter: (time_bucket('@ 1 day'::interval, "time") < 'Sat Jan 01 00:00:00 294276'::timestamp without time zone) 1156 -> Index Only Scan Backward using _hyper_5_156_chunk_metrics_timestamp_time_idx on _hyper_5_156_chunk 1157 Filter: (time_bucket('@ 1 day'::interval, "time") < 'Sat Jan 01 00:00:00 294276'::timestamp without time zone) 1158 -> Index Only Scan Backward using _hyper_5_157_chunk_metrics_timestamp_time_idx on _hyper_5_157_chunk 1159 Filter: (time_bucket('@ 1 day'::interval, "time") < 'Sat Jan 01 00:00:00 294276'::timestamp without time zone) 1160 -> Index Only Scan Backward using _hyper_5_158_chunk_metrics_timestamp_time_idx on _hyper_5_158_chunk 1161 Filter: (time_bucket('@ 1 day'::interval, "time") < 'Sat Jan 01 00:00:00 294276'::timestamp without time zone) 1162 -> Index Only Scan Backward using _hyper_5_159_chunk_metrics_timestamp_time_idx on _hyper_5_159_chunk 1163 Filter: (time_bucket('@ 1 day'::interval, "time") < 'Sat Jan 01 00:00:00 294276'::timestamp without time zone) 1164(12 rows) 1165 1166\qecho transformation would be out of range 1167transformation would be out of range 1168:PREFIX SELECT * FROM metrics_timestamp WHERE time_bucket('1000d',time) < '294276-01-01'::timestamp ORDER BY time; 1169 QUERY PLAN 1170---------------------------------------------------------------------------------------------------------------------------- 1171 Custom Scan (ChunkAppend) on metrics_timestamp 1172 Order: metrics_timestamp."time" 1173 -> Index Only Scan Backward using _hyper_5_155_chunk_metrics_timestamp_time_idx on _hyper_5_155_chunk 1174 Filter: (time_bucket('@ 1000 days'::interval, "time") < 'Sat Jan 01 00:00:00 294276'::timestamp without time zone) 1175 -> Index Only Scan Backward using _hyper_5_156_chunk_metrics_timestamp_time_idx on _hyper_5_156_chunk 1176 Filter: (time_bucket('@ 1000 days'::interval, "time") < 'Sat Jan 01 00:00:00 294276'::timestamp without time zone) 1177 -> Index Only Scan Backward using _hyper_5_157_chunk_metrics_timestamp_time_idx on _hyper_5_157_chunk 1178 Filter: (time_bucket('@ 1000 days'::interval, "time") < 'Sat Jan 01 00:00:00 294276'::timestamp without time zone) 1179 -> Index Only Scan Backward using _hyper_5_158_chunk_metrics_timestamp_time_idx on _hyper_5_158_chunk 1180 Filter: (time_bucket('@ 1000 days'::interval, "time") < 'Sat Jan 01 00:00:00 294276'::timestamp without time zone) 1181 -> Index Only Scan Backward using _hyper_5_159_chunk_metrics_timestamp_time_idx on _hyper_5_159_chunk 1182 Filter: (time_bucket('@ 1000 days'::interval, "time") < 'Sat Jan 01 00:00:00 294276'::timestamp without time zone) 1183(12 rows) 1184 1185\qecho test timestamptz upper boundary 1186test timestamptz upper boundary 1187\qecho there should be no transformation if we are out of the supported (TimescaleDB-specific) range 1188there should be no transformation if we are out of the supported (TimescaleDB-specific) range 1189:PREFIX SELECT time FROM metrics_timestamptz WHERE time_bucket('1d',time) < '294276-01-01'::timestamptz ORDER BY time; 1190 QUERY PLAN 1191------------------------------------------------------------------------------------------------------------------------- 1192 Custom Scan (ChunkAppend) on metrics_timestamptz 1193 Order: metrics_timestamptz."time" 1194 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk 1195 Filter: (time_bucket('@ 1 day'::interval, "time") < 'Sat Jan 01 00:00:00 294276 PST'::timestamp with time zone) 1196 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk 1197 Filter: (time_bucket('@ 1 day'::interval, "time") < 'Sat Jan 01 00:00:00 294276 PST'::timestamp with time zone) 1198 -> Index Only Scan Backward using _hyper_6_162_chunk_metrics_timestamptz_time_idx on _hyper_6_162_chunk 1199 Filter: (time_bucket('@ 1 day'::interval, "time") < 'Sat Jan 01 00:00:00 294276 PST'::timestamp with time zone) 1200 -> Index Only Scan Backward using _hyper_6_163_chunk_metrics_timestamptz_time_idx on _hyper_6_163_chunk 1201 Filter: (time_bucket('@ 1 day'::interval, "time") < 'Sat Jan 01 00:00:00 294276 PST'::timestamp with time zone) 1202 -> Index Only Scan Backward using _hyper_6_164_chunk_metrics_timestamptz_time_idx on _hyper_6_164_chunk 1203 Filter: (time_bucket('@ 1 day'::interval, "time") < 'Sat Jan 01 00:00:00 294276 PST'::timestamp with time zone) 1204(12 rows) 1205 1206\qecho transformation would be out of range 1207transformation would be out of range 1208:PREFIX SELECT time FROM metrics_timestamptz WHERE time_bucket('1000d',time) < '294276-01-01'::timestamptz ORDER BY time; 1209 QUERY PLAN 1210----------------------------------------------------------------------------------------------------------------------------- 1211 Custom Scan (ChunkAppend) on metrics_timestamptz 1212 Order: metrics_timestamptz."time" 1213 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk 1214 Filter: (time_bucket('@ 1000 days'::interval, "time") < 'Sat Jan 01 00:00:00 294276 PST'::timestamp with time zone) 1215 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk 1216 Filter: (time_bucket('@ 1000 days'::interval, "time") < 'Sat Jan 01 00:00:00 294276 PST'::timestamp with time zone) 1217 -> Index Only Scan Backward using _hyper_6_162_chunk_metrics_timestamptz_time_idx on _hyper_6_162_chunk 1218 Filter: (time_bucket('@ 1000 days'::interval, "time") < 'Sat Jan 01 00:00:00 294276 PST'::timestamp with time zone) 1219 -> Index Only Scan Backward using _hyper_6_163_chunk_metrics_timestamptz_time_idx on _hyper_6_163_chunk 1220 Filter: (time_bucket('@ 1000 days'::interval, "time") < 'Sat Jan 01 00:00:00 294276 PST'::timestamp with time zone) 1221 -> Index Only Scan Backward using _hyper_6_164_chunk_metrics_timestamptz_time_idx on _hyper_6_164_chunk 1222 Filter: (time_bucket('@ 1000 days'::interval, "time") < 'Sat Jan 01 00:00:00 294276 PST'::timestamp with time zone) 1223(12 rows) 1224 1225:PREFIX SELECT * FROM hyper WHERE time_bucket(10, time) > 10 AND time_bucket(10, time) < 100 ORDER BY time; 1226 QUERY PLAN 1227--------------------------------------------------------------------------------------------------------------------------------------------------------------- 1228 Sort 1229 Sort Key: _hyper_1_2_chunk."time" 1230 -> Append 1231 -> Seq Scan on _hyper_1_2_chunk 1232 Filter: (("time" > 10) AND ("time" < '110'::bigint) AND (time_bucket('10'::bigint, "time") > 10) AND (time_bucket('10'::bigint, "time") < 100)) 1233 -> Seq Scan on _hyper_1_3_chunk 1234 Filter: (("time" > 10) AND ("time" < '110'::bigint) AND (time_bucket('10'::bigint, "time") > 10) AND (time_bucket('10'::bigint, "time") < 100)) 1235 -> Seq Scan on _hyper_1_4_chunk 1236 Filter: (("time" > 10) AND ("time" < '110'::bigint) AND (time_bucket('10'::bigint, "time") > 10) AND (time_bucket('10'::bigint, "time") < 100)) 1237 -> Seq Scan on _hyper_1_5_chunk 1238 Filter: (("time" > 10) AND ("time" < '110'::bigint) AND (time_bucket('10'::bigint, "time") > 10) AND (time_bucket('10'::bigint, "time") < 100)) 1239 -> Seq Scan on _hyper_1_6_chunk 1240 Filter: (("time" > 10) AND ("time" < '110'::bigint) AND (time_bucket('10'::bigint, "time") > 10) AND (time_bucket('10'::bigint, "time") < 100)) 1241 -> Seq Scan on _hyper_1_7_chunk 1242 Filter: (("time" > 10) AND ("time" < '110'::bigint) AND (time_bucket('10'::bigint, "time") > 10) AND (time_bucket('10'::bigint, "time") < 100)) 1243 -> Seq Scan on _hyper_1_8_chunk 1244 Filter: (("time" > 10) AND ("time" < '110'::bigint) AND (time_bucket('10'::bigint, "time") > 10) AND (time_bucket('10'::bigint, "time") < 100)) 1245 -> Seq Scan on _hyper_1_9_chunk 1246 Filter: (("time" > 10) AND ("time" < '110'::bigint) AND (time_bucket('10'::bigint, "time") > 10) AND (time_bucket('10'::bigint, "time") < 100)) 1247 -> Seq Scan on _hyper_1_10_chunk 1248 Filter: (("time" > 10) AND ("time" < '110'::bigint) AND (time_bucket('10'::bigint, "time") > 10) AND (time_bucket('10'::bigint, "time") < 100)) 1249 -> Seq Scan on _hyper_1_11_chunk 1250 Filter: (("time" > 10) AND ("time" < '110'::bigint) AND (time_bucket('10'::bigint, "time") > 10) AND (time_bucket('10'::bigint, "time") < 100)) 1251(23 rows) 1252 1253:PREFIX SELECT * FROM hyper WHERE time_bucket(10, time) > 10 AND time_bucket(10, time) < 20 ORDER BY time; 1254 QUERY PLAN 1255------------------------------------------------------------------------------------------------------------------------------------------------------------- 1256 Sort 1257 Sort Key: _hyper_1_2_chunk."time" 1258 -> Append 1259 -> Seq Scan on _hyper_1_2_chunk 1260 Filter: (("time" > 10) AND ("time" < '30'::bigint) AND (time_bucket('10'::bigint, "time") > 10) AND (time_bucket('10'::bigint, "time") < 20)) 1261 -> Seq Scan on _hyper_1_3_chunk 1262 Filter: (("time" > 10) AND ("time" < '30'::bigint) AND (time_bucket('10'::bigint, "time") > 10) AND (time_bucket('10'::bigint, "time") < 20)) 1263(7 rows) 1264 1265:PREFIX SELECT * FROM hyper WHERE time_bucket(1, time) > 11 AND time_bucket(1, time) < 19 ORDER BY time; 1266 QUERY PLAN 1267----------------------------------------------------------------------------------------------------------------------------------------------------- 1268 Sort 1269 Sort Key: _hyper_1_2_chunk."time" 1270 -> Seq Scan on _hyper_1_2_chunk 1271 Filter: (("time" > 11) AND ("time" < '20'::bigint) AND (time_bucket('1'::bigint, "time") > 11) AND (time_bucket('1'::bigint, "time") < 19)) 1272(4 rows) 1273 1274:PREFIX SELECT * FROM hyper WHERE 10 < time_bucket(10, time) AND 20 > time_bucket(10,time) ORDER BY time; 1275 QUERY PLAN 1276------------------------------------------------------------------------------------------------------------------------------------------------------------- 1277 Sort 1278 Sort Key: _hyper_1_2_chunk."time" 1279 -> Append 1280 -> Seq Scan on _hyper_1_2_chunk 1281 Filter: (("time" > 10) AND ("time" < '30'::bigint) AND (10 < time_bucket('10'::bigint, "time")) AND (20 > time_bucket('10'::bigint, "time"))) 1282 -> Seq Scan on _hyper_1_3_chunk 1283 Filter: (("time" > 10) AND ("time" < '30'::bigint) AND (10 < time_bucket('10'::bigint, "time")) AND (20 > time_bucket('10'::bigint, "time"))) 1284(7 rows) 1285 1286\qecho time_bucket exclusion with date 1287time_bucket exclusion with date 1288:PREFIX SELECT * FROM metrics_date WHERE time_bucket('1d',time) < '2000-01-03' ORDER BY time; 1289 QUERY PLAN 1290----------------------------------------------------------------------------------------------- 1291 Index Only Scan Backward using _hyper_8_171_chunk_metrics_date_time_idx on _hyper_8_171_chunk 1292 Index Cond: ("time" < '01-04-2000'::date) 1293 Filter: (time_bucket('@ 1 day'::interval, "time") < '01-03-2000'::date) 1294(3 rows) 1295 1296:PREFIX SELECT * FROM metrics_date WHERE time_bucket('1d',time) >= '2000-01-03' AND time_bucket('1d',time) <= '2000-01-10' ORDER BY time; 1297 QUERY PLAN 1298--------------------------------------------------------------------------------------------------------------------------------------------------------- 1299 Custom Scan (ChunkAppend) on metrics_date 1300 Order: metrics_date."time" 1301 -> Index Only Scan Backward using _hyper_8_171_chunk_metrics_date_time_idx on _hyper_8_171_chunk 1302 Index Cond: (("time" >= '01-03-2000'::date) AND ("time" <= '01-11-2000'::date)) 1303 Filter: ((time_bucket('@ 1 day'::interval, "time") >= '01-03-2000'::date) AND (time_bucket('@ 1 day'::interval, "time") <= '01-10-2000'::date)) 1304 -> Index Only Scan Backward using _hyper_8_172_chunk_metrics_date_time_idx on _hyper_8_172_chunk 1305 Index Cond: (("time" >= '01-03-2000'::date) AND ("time" <= '01-11-2000'::date)) 1306 Filter: ((time_bucket('@ 1 day'::interval, "time") >= '01-03-2000'::date) AND (time_bucket('@ 1 day'::interval, "time") <= '01-10-2000'::date)) 1307(8 rows) 1308 1309\qecho time_bucket exclusion with timestamp 1310time_bucket exclusion with timestamp 1311:PREFIX SELECT * FROM metrics_timestamp WHERE time_bucket('1d',time) < '2000-01-03' ORDER BY time; 1312 QUERY PLAN 1313---------------------------------------------------------------------------------------------------------------- 1314 Index Only Scan Backward using _hyper_5_155_chunk_metrics_timestamp_time_idx on _hyper_5_155_chunk 1315 Index Cond: ("time" < 'Tue Jan 04 00:00:00 2000'::timestamp without time zone) 1316 Filter: (time_bucket('@ 1 day'::interval, "time") < 'Mon Jan 03 00:00:00 2000'::timestamp without time zone) 1317(3 rows) 1318 1319:PREFIX SELECT * FROM metrics_timestamp WHERE time_bucket('1d',time) >= '2000-01-03' AND time_bucket('1d',time) <= '2000-01-10' ORDER BY time; 1320 QUERY PLAN 1321----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1322 Custom Scan (ChunkAppend) on metrics_timestamp 1323 Order: metrics_timestamp."time" 1324 -> Index Only Scan Backward using _hyper_5_155_chunk_metrics_timestamp_time_idx on _hyper_5_155_chunk 1325 Index Cond: (("time" >= 'Mon Jan 03 00:00:00 2000'::timestamp without time zone) AND ("time" <= 'Tue Jan 11 00:00:00 2000'::timestamp without time zone)) 1326 Filter: ((time_bucket('@ 1 day'::interval, "time") >= 'Mon Jan 03 00:00:00 2000'::timestamp without time zone) AND (time_bucket('@ 1 day'::interval, "time") <= 'Mon Jan 10 00:00:00 2000'::timestamp without time zone)) 1327 -> Index Only Scan Backward using _hyper_5_156_chunk_metrics_timestamp_time_idx on _hyper_5_156_chunk 1328 Index Cond: (("time" >= 'Mon Jan 03 00:00:00 2000'::timestamp without time zone) AND ("time" <= 'Tue Jan 11 00:00:00 2000'::timestamp without time zone)) 1329 Filter: ((time_bucket('@ 1 day'::interval, "time") >= 'Mon Jan 03 00:00:00 2000'::timestamp without time zone) AND (time_bucket('@ 1 day'::interval, "time") <= 'Mon Jan 10 00:00:00 2000'::timestamp without time zone)) 1330(8 rows) 1331 1332\qecho time_bucket exclusion with timestamptz 1333time_bucket exclusion with timestamptz 1334:PREFIX SELECT time FROM metrics_timestamptz WHERE time_bucket('6h',time) < '2000-01-03' ORDER BY time; 1335 QUERY PLAN 1336------------------------------------------------------------------------------------------------------------------- 1337 Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk 1338 Index Cond: ("time" < 'Mon Jan 03 06:00:00 2000 PST'::timestamp with time zone) 1339 Filter: (time_bucket('@ 6 hours'::interval, "time") < 'Mon Jan 03 00:00:00 2000 PST'::timestamp with time zone) 1340(3 rows) 1341 1342:PREFIX SELECT time FROM metrics_timestamptz WHERE time_bucket('6h',time) >= '2000-01-03' AND time_bucket('6h',time) <= '2000-01-10' ORDER BY time; 1343 QUERY PLAN 1344----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1345 Custom Scan (ChunkAppend) on metrics_timestamptz 1346 Order: metrics_timestamptz."time" 1347 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk 1348 Index Cond: (("time" >= 'Mon Jan 03 00:00:00 2000 PST'::timestamp with time zone) AND ("time" <= 'Mon Jan 10 06:00:00 2000 PST'::timestamp with time zone)) 1349 Filter: ((time_bucket('@ 6 hours'::interval, "time") >= 'Mon Jan 03 00:00:00 2000 PST'::timestamp with time zone) AND (time_bucket('@ 6 hours'::interval, "time") <= 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1350 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk 1351 Index Cond: (("time" >= 'Mon Jan 03 00:00:00 2000 PST'::timestamp with time zone) AND ("time" <= 'Mon Jan 10 06:00:00 2000 PST'::timestamp with time zone)) 1352 Filter: ((time_bucket('@ 6 hours'::interval, "time") >= 'Mon Jan 03 00:00:00 2000 PST'::timestamp with time zone) AND (time_bucket('@ 6 hours'::interval, "time") <= 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1353(8 rows) 1354 1355\qecho time_bucket exclusion with timestamptz and day interval 1356time_bucket exclusion with timestamptz and day interval 1357:PREFIX SELECT time FROM metrics_timestamptz WHERE time_bucket('1d',time) < '2000-01-03' ORDER BY time; 1358 QUERY PLAN 1359----------------------------------------------------------------------------------------------------------------- 1360 Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk 1361 Index Cond: ("time" < 'Tue Jan 04 00:00:00 2000 PST'::timestamp with time zone) 1362 Filter: (time_bucket('@ 1 day'::interval, "time") < 'Mon Jan 03 00:00:00 2000 PST'::timestamp with time zone) 1363(3 rows) 1364 1365:PREFIX SELECT time FROM metrics_timestamptz WHERE time_bucket('1d',time) >= '2000-01-03' AND time_bucket('1d',time) <= '2000-01-10' ORDER BY time; 1366 QUERY PLAN 1367------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1368 Custom Scan (ChunkAppend) on metrics_timestamptz 1369 Order: metrics_timestamptz."time" 1370 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk 1371 Index Cond: (("time" >= 'Mon Jan 03 00:00:00 2000 PST'::timestamp with time zone) AND ("time" <= 'Tue Jan 11 00:00:00 2000 PST'::timestamp with time zone)) 1372 Filter: ((time_bucket('@ 1 day'::interval, "time") >= 'Mon Jan 03 00:00:00 2000 PST'::timestamp with time zone) AND (time_bucket('@ 1 day'::interval, "time") <= 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1373 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk 1374 Index Cond: (("time" >= 'Mon Jan 03 00:00:00 2000 PST'::timestamp with time zone) AND ("time" <= 'Tue Jan 11 00:00:00 2000 PST'::timestamp with time zone)) 1375 Filter: ((time_bucket('@ 1 day'::interval, "time") >= 'Mon Jan 03 00:00:00 2000 PST'::timestamp with time zone) AND (time_bucket('@ 1 day'::interval, "time") <= 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1376(8 rows) 1377 1378:PREFIX SELECT time FROM metrics_timestamptz WHERE time_bucket('1d',time) >= '2000-01-03' AND time_bucket('7d',time) <= '2000-01-10' ORDER BY time; 1379 QUERY PLAN 1380-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1381 Custom Scan (ChunkAppend) on metrics_timestamptz 1382 Order: metrics_timestamptz."time" 1383 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk 1384 Index Cond: (("time" >= 'Mon Jan 03 00:00:00 2000 PST'::timestamp with time zone) AND ("time" <= 'Mon Jan 17 00:00:00 2000 PST'::timestamp with time zone)) 1385 Filter: ((time_bucket('@ 1 day'::interval, "time") >= 'Mon Jan 03 00:00:00 2000 PST'::timestamp with time zone) AND (time_bucket('@ 7 days'::interval, "time") <= 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1386 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk 1387 Index Cond: (("time" >= 'Mon Jan 03 00:00:00 2000 PST'::timestamp with time zone) AND ("time" <= 'Mon Jan 17 00:00:00 2000 PST'::timestamp with time zone)) 1388 Filter: ((time_bucket('@ 1 day'::interval, "time") >= 'Mon Jan 03 00:00:00 2000 PST'::timestamp with time zone) AND (time_bucket('@ 7 days'::interval, "time") <= 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1389 -> Index Only Scan Backward using _hyper_6_162_chunk_metrics_timestamptz_time_idx on _hyper_6_162_chunk 1390 Index Cond: (("time" >= 'Mon Jan 03 00:00:00 2000 PST'::timestamp with time zone) AND ("time" <= 'Mon Jan 17 00:00:00 2000 PST'::timestamp with time zone)) 1391 Filter: ((time_bucket('@ 1 day'::interval, "time") >= 'Mon Jan 03 00:00:00 2000 PST'::timestamp with time zone) AND (time_bucket('@ 7 days'::interval, "time") <= 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1392(11 rows) 1393 1394\qecho no transformation 1395no transformation 1396:PREFIX SELECT * FROM hyper WHERE time_bucket(10 + floor(random())::int, time) > 10 AND time_bucket(10 + floor(random())::int, time) < 100 AND time < 150 ORDER BY time; 1397 QUERY PLAN 1398------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1399 Sort 1400 Sort Key: hyper."time" 1401 -> Custom Scan (ChunkAppend) on hyper 1402 Chunks excluded during startup: 0 1403 -> Seq Scan on _hyper_1_1_chunk 1404 Filter: (("time" < 150) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") > 10) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") < 100)) 1405 -> Seq Scan on _hyper_1_2_chunk 1406 Filter: (("time" < 150) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") > 10) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") < 100)) 1407 -> Seq Scan on _hyper_1_3_chunk 1408 Filter: (("time" < 150) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") > 10) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") < 100)) 1409 -> Seq Scan on _hyper_1_4_chunk 1410 Filter: (("time" < 150) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") > 10) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") < 100)) 1411 -> Seq Scan on _hyper_1_5_chunk 1412 Filter: (("time" < 150) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") > 10) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") < 100)) 1413 -> Seq Scan on _hyper_1_6_chunk 1414 Filter: (("time" < 150) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") > 10) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") < 100)) 1415 -> Seq Scan on _hyper_1_7_chunk 1416 Filter: (("time" < 150) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") > 10) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") < 100)) 1417 -> Seq Scan on _hyper_1_8_chunk 1418 Filter: (("time" < 150) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") > 10) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") < 100)) 1419 -> Seq Scan on _hyper_1_9_chunk 1420 Filter: (("time" < 150) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") > 10) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") < 100)) 1421 -> Seq Scan on _hyper_1_10_chunk 1422 Filter: (("time" < 150) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") > 10) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") < 100)) 1423 -> Seq Scan on _hyper_1_11_chunk 1424 Filter: (("time" < 150) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") > 10) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") < 100)) 1425 -> Seq Scan on _hyper_1_12_chunk 1426 Filter: (("time" < 150) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") > 10) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") < 100)) 1427 -> Seq Scan on _hyper_1_13_chunk 1428 Filter: (("time" < 150) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") > 10) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") < 100)) 1429 -> Seq Scan on _hyper_1_14_chunk 1430 Filter: (("time" < 150) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") > 10) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") < 100)) 1431 -> Seq Scan on _hyper_1_15_chunk 1432 Filter: (("time" < 150) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") > 10) AND (time_bucket(((10 + (floor(random()))::integer))::bigint, "time") < 100)) 1433(34 rows) 1434 1435\qecho exclude chunks based on time column with partitioning function. This 1436exclude chunks based on time column with partitioning function. This 1437\qecho transparently applies the time partitioning function on the time 1438transparently applies the time partitioning function on the time 1439\qecho value to be able to exclude chunks (similar to a closed dimension). 1440value to be able to exclude chunks (similar to a closed dimension). 1441:PREFIX SELECT * FROM hyper_timefunc WHERE time < 4 ORDER BY value; 1442 QUERY PLAN 1443-------------------------------------------------------- 1444 Sort 1445 Sort Key: _hyper_4_125_chunk.value 1446 -> Append 1447 -> Seq Scan on _hyper_4_125_chunk 1448 Filter: ("time" < '4'::double precision) 1449 -> Seq Scan on _hyper_4_126_chunk 1450 Filter: ("time" < '4'::double precision) 1451 -> Seq Scan on _hyper_4_127_chunk 1452 Filter: ("time" < '4'::double precision) 1453 -> Seq Scan on _hyper_4_124_chunk 1454 Filter: ("time" < '4'::double precision) 1455(11 rows) 1456 1457\qecho excluding based on time expression is currently unoptimized 1458excluding based on time expression is currently unoptimized 1459:PREFIX SELECT * FROM hyper_timefunc WHERE unix_to_timestamp(time) < 'Wed Dec 31 16:00:04 1969 PST' ORDER BY value; 1460 QUERY PLAN 1461--------------------------------------------------------------------------------------------------------- 1462 Sort 1463 Sort Key: _hyper_4_124_chunk.value 1464 -> Append 1465 -> Seq Scan on _hyper_4_124_chunk 1466 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1467 -> Seq Scan on _hyper_4_125_chunk 1468 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1469 -> Seq Scan on _hyper_4_126_chunk 1470 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1471 -> Seq Scan on _hyper_4_127_chunk 1472 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1473 -> Seq Scan on _hyper_4_128_chunk 1474 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1475 -> Seq Scan on _hyper_4_129_chunk 1476 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1477 -> Seq Scan on _hyper_4_130_chunk 1478 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1479 -> Seq Scan on _hyper_4_131_chunk 1480 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1481 -> Seq Scan on _hyper_4_132_chunk 1482 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1483 -> Seq Scan on _hyper_4_133_chunk 1484 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1485 -> Seq Scan on _hyper_4_134_chunk 1486 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1487 -> Seq Scan on _hyper_4_135_chunk 1488 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1489 -> Seq Scan on _hyper_4_136_chunk 1490 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1491 -> Seq Scan on _hyper_4_137_chunk 1492 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1493 -> Seq Scan on _hyper_4_138_chunk 1494 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1495 -> Seq Scan on _hyper_4_139_chunk 1496 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1497 -> Seq Scan on _hyper_4_140_chunk 1498 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1499 -> Seq Scan on _hyper_4_141_chunk 1500 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1501 -> Seq Scan on _hyper_4_142_chunk 1502 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1503 -> Seq Scan on _hyper_4_143_chunk 1504 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1505 -> Seq Scan on _hyper_4_144_chunk 1506 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1507 -> Seq Scan on _hyper_4_145_chunk 1508 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1509 -> Seq Scan on _hyper_4_146_chunk 1510 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1511 -> Seq Scan on _hyper_4_147_chunk 1512 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1513 -> Seq Scan on _hyper_4_148_chunk 1514 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1515 -> Seq Scan on _hyper_4_149_chunk 1516 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1517 -> Seq Scan on _hyper_4_150_chunk 1518 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1519 -> Seq Scan on _hyper_4_151_chunk 1520 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1521 -> Seq Scan on _hyper_4_152_chunk 1522 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1523 -> Seq Scan on _hyper_4_153_chunk 1524 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1525 -> Seq Scan on _hyper_4_154_chunk 1526 Filter: (to_timestamp("time") < 'Wed Dec 31 16:00:04 1969 PST'::timestamp with time zone) 1527(65 rows) 1528 1529\qecho test qual propagation for joins 1530test qual propagation for joins 1531RESET constraint_exclusion; 1532\qecho nothing to propagate 1533nothing to propagate 1534:PREFIX SELECT m1.time FROM metrics_timestamptz m1, metrics_timestamptz_2 m2 WHERE m1.time = m2.time ORDER BY m1.time; 1535 QUERY PLAN 1536------------------------------------------------------------------------------------------------------------------------------- 1537 Merge Join 1538 Merge Cond: (m1."time" = m2."time") 1539 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 1540 Order: m1."time" 1541 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 1542 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 1543 -> Index Only Scan Backward using _hyper_6_162_chunk_metrics_timestamptz_time_idx on _hyper_6_162_chunk m1_3 1544 -> Index Only Scan Backward using _hyper_6_163_chunk_metrics_timestamptz_time_idx on _hyper_6_163_chunk m1_4 1545 -> Index Only Scan Backward using _hyper_6_164_chunk_metrics_timestamptz_time_idx on _hyper_6_164_chunk m1_5 1546 -> Materialize 1547 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 1548 Order: m2."time" 1549 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 1550 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 1551 -> Index Only Scan Backward using _hyper_7_167_chunk_metrics_timestamptz_2_time_idx on _hyper_7_167_chunk m2_3 1552 -> Index Only Scan Backward using _hyper_7_168_chunk_metrics_timestamptz_2_time_idx on _hyper_7_168_chunk m2_4 1553 -> Index Only Scan Backward using _hyper_7_169_chunk_metrics_timestamptz_2_time_idx on _hyper_7_169_chunk m2_5 1554 -> Index Only Scan Backward using _hyper_7_170_chunk_metrics_timestamptz_2_time_idx on _hyper_7_170_chunk m2_6 1555(18 rows) 1556 1557:PREFIX SELECT m1.time FROM metrics_timestamptz m1 INNER JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time ORDER BY m1.time; 1558 QUERY PLAN 1559------------------------------------------------------------------------------------------------------------------------------- 1560 Merge Join 1561 Merge Cond: (m1."time" = m2."time") 1562 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 1563 Order: m1."time" 1564 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 1565 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 1566 -> Index Only Scan Backward using _hyper_6_162_chunk_metrics_timestamptz_time_idx on _hyper_6_162_chunk m1_3 1567 -> Index Only Scan Backward using _hyper_6_163_chunk_metrics_timestamptz_time_idx on _hyper_6_163_chunk m1_4 1568 -> Index Only Scan Backward using _hyper_6_164_chunk_metrics_timestamptz_time_idx on _hyper_6_164_chunk m1_5 1569 -> Materialize 1570 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 1571 Order: m2."time" 1572 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 1573 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 1574 -> Index Only Scan Backward using _hyper_7_167_chunk_metrics_timestamptz_2_time_idx on _hyper_7_167_chunk m2_3 1575 -> Index Only Scan Backward using _hyper_7_168_chunk_metrics_timestamptz_2_time_idx on _hyper_7_168_chunk m2_4 1576 -> Index Only Scan Backward using _hyper_7_169_chunk_metrics_timestamptz_2_time_idx on _hyper_7_169_chunk m2_5 1577 -> Index Only Scan Backward using _hyper_7_170_chunk_metrics_timestamptz_2_time_idx on _hyper_7_170_chunk m2_6 1578(18 rows) 1579 1580:PREFIX SELECT m1.time FROM metrics_timestamptz m1 LEFT JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time ORDER BY m1.time; 1581 QUERY PLAN 1582------------------------------------------------------------------------------------------------------------------------------- 1583 Merge Left Join 1584 Merge Cond: (m1."time" = m2."time") 1585 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 1586 Order: m1."time" 1587 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 1588 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 1589 -> Index Only Scan Backward using _hyper_6_162_chunk_metrics_timestamptz_time_idx on _hyper_6_162_chunk m1_3 1590 -> Index Only Scan Backward using _hyper_6_163_chunk_metrics_timestamptz_time_idx on _hyper_6_163_chunk m1_4 1591 -> Index Only Scan Backward using _hyper_6_164_chunk_metrics_timestamptz_time_idx on _hyper_6_164_chunk m1_5 1592 -> Materialize 1593 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 1594 Order: m2."time" 1595 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 1596 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 1597 -> Index Only Scan Backward using _hyper_7_167_chunk_metrics_timestamptz_2_time_idx on _hyper_7_167_chunk m2_3 1598 -> Index Only Scan Backward using _hyper_7_168_chunk_metrics_timestamptz_2_time_idx on _hyper_7_168_chunk m2_4 1599 -> Index Only Scan Backward using _hyper_7_169_chunk_metrics_timestamptz_2_time_idx on _hyper_7_169_chunk m2_5 1600 -> Index Only Scan Backward using _hyper_7_170_chunk_metrics_timestamptz_2_time_idx on _hyper_7_170_chunk m2_6 1601(18 rows) 1602 1603:PREFIX SELECT m1.time FROM metrics_timestamptz m1 RIGHT JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time ORDER BY m1.time; 1604 QUERY PLAN 1605------------------------------------------------------------------------------------------------------------------------------------- 1606 Sort 1607 Sort Key: m1."time" 1608 -> Merge Right Join 1609 Merge Cond: (m1."time" = m2."time") 1610 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 1611 Order: m1."time" 1612 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 1613 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 1614 -> Index Only Scan Backward using _hyper_6_162_chunk_metrics_timestamptz_time_idx on _hyper_6_162_chunk m1_3 1615 -> Index Only Scan Backward using _hyper_6_163_chunk_metrics_timestamptz_time_idx on _hyper_6_163_chunk m1_4 1616 -> Index Only Scan Backward using _hyper_6_164_chunk_metrics_timestamptz_time_idx on _hyper_6_164_chunk m1_5 1617 -> Materialize 1618 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 1619 Order: m2."time" 1620 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 1621 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 1622 -> Index Only Scan Backward using _hyper_7_167_chunk_metrics_timestamptz_2_time_idx on _hyper_7_167_chunk m2_3 1623 -> Index Only Scan Backward using _hyper_7_168_chunk_metrics_timestamptz_2_time_idx on _hyper_7_168_chunk m2_4 1624 -> Index Only Scan Backward using _hyper_7_169_chunk_metrics_timestamptz_2_time_idx on _hyper_7_169_chunk m2_5 1625 -> Index Only Scan Backward using _hyper_7_170_chunk_metrics_timestamptz_2_time_idx on _hyper_7_170_chunk m2_6 1626(20 rows) 1627 1628\qecho OR constraints should not propagate 1629OR constraints should not propagate 1630:PREFIX SELECT m1.time FROM metrics_timestamptz m1 INNER JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time WHERE m1.time < '2000-01-10' OR m1.time > '2001-01-01' ORDER BY m1.time; 1631 QUERY PLAN 1632-------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1633 Merge Join 1634 Merge Cond: (m1."time" = m2."time") 1635 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 1636 Order: m1."time" 1637 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 1638 Filter: (("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) OR ("time" > 'Mon Jan 01 00:00:00 2001 PST'::timestamp with time zone)) 1639 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 1640 Filter: (("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) OR ("time" > 'Mon Jan 01 00:00:00 2001 PST'::timestamp with time zone)) 1641 -> Materialize 1642 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 1643 Order: m2."time" 1644 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 1645 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 1646 -> Index Only Scan Backward using _hyper_7_167_chunk_metrics_timestamptz_2_time_idx on _hyper_7_167_chunk m2_3 1647 -> Index Only Scan Backward using _hyper_7_168_chunk_metrics_timestamptz_2_time_idx on _hyper_7_168_chunk m2_4 1648 -> Index Only Scan Backward using _hyper_7_169_chunk_metrics_timestamptz_2_time_idx on _hyper_7_169_chunk m2_5 1649 -> Index Only Scan Backward using _hyper_7_170_chunk_metrics_timestamptz_2_time_idx on _hyper_7_170_chunk m2_6 1650(17 rows) 1651 1652\qecho test single constraint 1653test single constraint 1654\qecho constraint should be on both scans 1655constraint should be on both scans 1656\qecho these will propagate even for LEFT/RIGHT JOIN because the constraints are not in the ON clause and therefore imply a NOT NULL condition on the JOIN column 1657these will propagate even for LEFT/RIGHT JOIN because the constraints are not in the ON clause and therefore imply a NOT NULL condition on the JOIN column 1658:PREFIX SELECT m1.time FROM metrics_timestamptz m1, metrics_timestamptz_2 m2 WHERE m1.time = m2.time AND m1.time < '2000-01-10' ORDER BY m1.time; 1659 QUERY PLAN 1660------------------------------------------------------------------------------------------------------------------------------- 1661 Merge Join 1662 Merge Cond: (m1."time" = m2."time") 1663 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 1664 Order: m1."time" 1665 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 1666 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 1667 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 1668 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 1669 -> Materialize 1670 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 1671 Order: m2."time" 1672 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 1673 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 1674 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 1675 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 1676(15 rows) 1677 1678:PREFIX SELECT m1.time FROM metrics_timestamptz m1 INNER JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time WHERE m1.time < '2000-01-10' ORDER BY m1.time; 1679 QUERY PLAN 1680------------------------------------------------------------------------------------------------------------------------------- 1681 Merge Join 1682 Merge Cond: (m1."time" = m2."time") 1683 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 1684 Order: m1."time" 1685 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 1686 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 1687 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 1688 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 1689 -> Materialize 1690 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 1691 Order: m2."time" 1692 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 1693 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 1694 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 1695 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 1696(15 rows) 1697 1698:PREFIX SELECT m1.time FROM metrics_timestamptz m1 LEFT JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time WHERE m1.time < '2000-01-10' ORDER BY m1.time; 1699 QUERY PLAN 1700------------------------------------------------------------------------------------------------------------------------------- 1701 Merge Left Join 1702 Merge Cond: (m1."time" = m2."time") 1703 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 1704 Order: m1."time" 1705 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 1706 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 1707 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 1708 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 1709 -> Materialize 1710 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 1711 Order: m2."time" 1712 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 1713 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 1714 -> Index Only Scan Backward using _hyper_7_167_chunk_metrics_timestamptz_2_time_idx on _hyper_7_167_chunk m2_3 1715 -> Index Only Scan Backward using _hyper_7_168_chunk_metrics_timestamptz_2_time_idx on _hyper_7_168_chunk m2_4 1716 -> Index Only Scan Backward using _hyper_7_169_chunk_metrics_timestamptz_2_time_idx on _hyper_7_169_chunk m2_5 1717 -> Index Only Scan Backward using _hyper_7_170_chunk_metrics_timestamptz_2_time_idx on _hyper_7_170_chunk m2_6 1718(17 rows) 1719 1720:PREFIX SELECT m1.time FROM metrics_timestamptz m1 RIGHT JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time WHERE m1.time < '2000-01-10' ORDER BY m1.time; 1721 QUERY PLAN 1722------------------------------------------------------------------------------------------------------------------------------- 1723 Merge Join 1724 Merge Cond: (m1."time" = m2."time") 1725 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 1726 Order: m1."time" 1727 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 1728 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 1729 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 1730 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 1731 -> Materialize 1732 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 1733 Order: m2."time" 1734 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 1735 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 1736 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 1737 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 1738(15 rows) 1739 1740\qecho test 2 constraints on single relation 1741test 2 constraints on single relation 1742\qecho these will propagate even for LEFT/RIGHT JOIN because the constraints are not in the ON clause and therefore imply a NOT NULL condition on the JOIN column 1743these will propagate even for LEFT/RIGHT JOIN because the constraints are not in the ON clause and therefore imply a NOT NULL condition on the JOIN column 1744:PREFIX SELECT m1.time FROM metrics_timestamptz m1, metrics_timestamptz_2 m2 WHERE m1.time = m2.time AND m1.time > '2000-01-01' AND m1.time < '2000-01-10' ORDER BY m1.time; 1745 QUERY PLAN 1746------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1747 Merge Join 1748 Merge Cond: (m1."time" = m2."time") 1749 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 1750 Order: m1."time" 1751 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 1752 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1753 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 1754 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1755 -> Materialize 1756 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 1757 Order: m2."time" 1758 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 1759 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1760 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 1761 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1762(15 rows) 1763 1764:PREFIX SELECT m1.time FROM metrics_timestamptz m1 INNER JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time WHERE m1.time > '2000-01-01' AND m1.time < '2000-01-10' ORDER BY m1.time; 1765 QUERY PLAN 1766------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1767 Merge Join 1768 Merge Cond: (m1."time" = m2."time") 1769 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 1770 Order: m1."time" 1771 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 1772 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1773 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 1774 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1775 -> Materialize 1776 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 1777 Order: m2."time" 1778 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 1779 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1780 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 1781 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1782(15 rows) 1783 1784:PREFIX SELECT m1.time FROM metrics_timestamptz m1 LEFT JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time WHERE m1.time > '2000-01-01' AND m1.time < '2000-01-10' ORDER BY m1.time; 1785 QUERY PLAN 1786------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1787 Nested Loop Left Join 1788 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 1789 Order: m1."time" 1790 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 1791 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1792 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 1793 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1794 -> Append 1795 -> Index Only Scan using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2 1796 Index Cond: ("time" = m1."time") 1797 -> Index Only Scan using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_1 1798 Index Cond: ("time" = m1."time") 1799 -> Index Only Scan using _hyper_7_167_chunk_metrics_timestamptz_2_time_idx on _hyper_7_167_chunk m2_2 1800 Index Cond: ("time" = m1."time") 1801 -> Index Only Scan using _hyper_7_168_chunk_metrics_timestamptz_2_time_idx on _hyper_7_168_chunk m2_3 1802 Index Cond: ("time" = m1."time") 1803 -> Index Only Scan using _hyper_7_169_chunk_metrics_timestamptz_2_time_idx on _hyper_7_169_chunk m2_4 1804 Index Cond: ("time" = m1."time") 1805 -> Index Only Scan using _hyper_7_170_chunk_metrics_timestamptz_2_time_idx on _hyper_7_170_chunk m2_5 1806 Index Cond: ("time" = m1."time") 1807(20 rows) 1808 1809:PREFIX SELECT m1.time FROM metrics_timestamptz m1 RIGHT JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time WHERE m1.time > '2000-01-01' AND m1.time < '2000-01-10' ORDER BY m1.time; 1810 QUERY PLAN 1811------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1812 Merge Join 1813 Merge Cond: (m1."time" = m2."time") 1814 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 1815 Order: m1."time" 1816 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 1817 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1818 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 1819 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1820 -> Materialize 1821 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 1822 Order: m2."time" 1823 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 1824 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1825 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 1826 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1827(15 rows) 1828 1829\qecho test 2 constraints with 1 constraint on each relation 1830test 2 constraints with 1 constraint on each relation 1831\qecho these will propagate even for LEFT/RIGHT JOIN because the constraints are not in the ON clause and therefore imply a NOT NULL condition on the JOIN column 1832these will propagate even for LEFT/RIGHT JOIN because the constraints are not in the ON clause and therefore imply a NOT NULL condition on the JOIN column 1833:PREFIX SELECT m1.time FROM metrics_timestamptz m1, metrics_timestamptz_2 m2 WHERE m1.time = m2.time AND m1.time > '2000-01-01' AND m2.time < '2000-01-10' ORDER BY m1.time; 1834 QUERY PLAN 1835------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1836 Merge Join 1837 Merge Cond: (m1."time" = m2."time") 1838 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 1839 Order: m1."time" 1840 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 1841 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1842 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 1843 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1844 -> Materialize 1845 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 1846 Order: m2."time" 1847 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 1848 Index Cond: (("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) AND ("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone)) 1849 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 1850 Index Cond: (("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) AND ("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone)) 1851(15 rows) 1852 1853:PREFIX SELECT m1.time FROM metrics_timestamptz m1 INNER JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time WHERE m1.time > '2000-01-01' AND m2.time < '2000-01-10' ORDER BY m1.time; 1854 QUERY PLAN 1855------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1856 Merge Join 1857 Merge Cond: (m1."time" = m2."time") 1858 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 1859 Order: m1."time" 1860 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 1861 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1862 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 1863 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1864 -> Materialize 1865 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 1866 Order: m2."time" 1867 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 1868 Index Cond: (("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) AND ("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone)) 1869 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 1870 Index Cond: (("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) AND ("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone)) 1871(15 rows) 1872 1873:PREFIX SELECT m1.time FROM metrics_timestamptz m1 LEFT JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time WHERE m1.time > '2000-01-01' AND m2.time < '2000-01-10' ORDER BY m1.time; 1874 QUERY PLAN 1875------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1876 Merge Join 1877 Merge Cond: (m1."time" = m2."time") 1878 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 1879 Order: m1."time" 1880 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 1881 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1882 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 1883 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1884 -> Materialize 1885 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 1886 Order: m2."time" 1887 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 1888 Index Cond: (("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) AND ("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone)) 1889 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 1890 Index Cond: (("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) AND ("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone)) 1891(15 rows) 1892 1893:PREFIX SELECT m1.time FROM metrics_timestamptz m1 RIGHT JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time WHERE m1.time > '2000-01-01' AND m2.time < '2000-01-10' ORDER BY m1.time; 1894 QUERY PLAN 1895------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1896 Merge Join 1897 Merge Cond: (m1."time" = m2."time") 1898 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 1899 Order: m1."time" 1900 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 1901 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1902 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 1903 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1904 -> Materialize 1905 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 1906 Order: m2."time" 1907 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 1908 Index Cond: (("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) AND ("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone)) 1909 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 1910 Index Cond: (("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) AND ("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone)) 1911(15 rows) 1912 1913\qecho test constraints in ON clause of INNER JOIN 1914test constraints in ON clause of INNER JOIN 1915:PREFIX SELECT m1.time FROM metrics_timestamptz m1 INNER JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time AND m2.time > '2000-01-01' AND m2.time < '2000-01-10' ORDER BY m1.time; 1916 QUERY PLAN 1917------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1918 Merge Join 1919 Merge Cond: (m1."time" = m2."time") 1920 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 1921 Order: m1."time" 1922 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 1923 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1924 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 1925 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1926 -> Materialize 1927 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 1928 Order: m2."time" 1929 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 1930 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1931 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 1932 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1933(15 rows) 1934 1935\qecho test constraints in ON clause of LEFT JOIN 1936test constraints in ON clause of LEFT JOIN 1937\qecho must not propagate 1938must not propagate 1939:PREFIX SELECT m1.time FROM metrics_timestamptz m1 LEFT JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time AND m2.time > '2000-01-01' AND m2.time < '2000-01-10' ORDER BY m1.time; 1940 QUERY PLAN 1941------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1942 Merge Left Join 1943 Merge Cond: (m1."time" = m2."time") 1944 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 1945 Order: m1."time" 1946 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 1947 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 1948 -> Index Only Scan Backward using _hyper_6_162_chunk_metrics_timestamptz_time_idx on _hyper_6_162_chunk m1_3 1949 -> Index Only Scan Backward using _hyper_6_163_chunk_metrics_timestamptz_time_idx on _hyper_6_163_chunk m1_4 1950 -> Index Only Scan Backward using _hyper_6_164_chunk_metrics_timestamptz_time_idx on _hyper_6_164_chunk m1_5 1951 -> Materialize 1952 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 1953 Order: m2."time" 1954 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 1955 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1956 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 1957 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1958(16 rows) 1959 1960\qecho test constraints in ON clause of RIGHT JOIN 1961test constraints in ON clause of RIGHT JOIN 1962\qecho must not propagate 1963must not propagate 1964:PREFIX SELECT m1.time FROM metrics_timestamptz m1 RIGHT JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time AND m2.time > '2000-01-01' AND m2.time < '2000-01-10' ORDER BY m1.time; 1965 QUERY PLAN 1966-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1967 Gather Merge 1968 Workers Planned: 2 1969 -> Sort 1970 Sort Key: m1."time" 1971 -> Parallel Hash Left Join 1972 Hash Cond: (m2."time" = m1."time") 1973 Join Filter: ((m2."time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND (m2."time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 1974 -> Parallel Append 1975 -> Parallel Seq Scan on _hyper_7_165_chunk m2 1976 -> Parallel Seq Scan on _hyper_7_166_chunk m2_1 1977 -> Parallel Seq Scan on _hyper_7_167_chunk m2_2 1978 -> Parallel Seq Scan on _hyper_7_168_chunk m2_3 1979 -> Parallel Seq Scan on _hyper_7_169_chunk m2_4 1980 -> Parallel Seq Scan on _hyper_7_170_chunk m2_5 1981 -> Parallel Hash 1982 -> Parallel Append 1983 -> Parallel Seq Scan on _hyper_6_160_chunk m1 1984 -> Parallel Seq Scan on _hyper_6_161_chunk m1_1 1985 -> Parallel Seq Scan on _hyper_6_162_chunk m1_2 1986 -> Parallel Seq Scan on _hyper_6_163_chunk m1_3 1987 -> Parallel Seq Scan on _hyper_6_164_chunk m1_4 1988(21 rows) 1989 1990\qecho test equality condition not in ON clause 1991test equality condition not in ON clause 1992:PREFIX SELECT m1.time FROM metrics_timestamptz m1 INNER JOIN metrics_timestamptz_2 m2 ON true WHERE m2.time = m1.time AND m2.time < '2000-01-10' ORDER BY m1.time; 1993 QUERY PLAN 1994------------------------------------------------------------------------------------------------------------------------------- 1995 Merge Join 1996 Merge Cond: (m1."time" = m2."time") 1997 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 1998 Order: m1."time" 1999 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 2000 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 2001 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 2002 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 2003 -> Materialize 2004 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 2005 Order: m2."time" 2006 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 2007 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 2008 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 2009 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 2010(15 rows) 2011 2012\qecho test constraints not joined on 2013test constraints not joined on 2014\qecho device_id constraint must not propagate 2015device_id constraint must not propagate 2016:PREFIX SELECT m1.time FROM metrics_timestamptz m1 INNER JOIN metrics_timestamptz_2 m2 ON true WHERE m2.time = m1.time AND m2.time < '2000-01-10' AND m1.device_id = 1 ORDER BY m1.time; 2017 QUERY PLAN 2018-------------------------------------------------------------------------------------------------------------------------- 2019 Nested Loop 2020 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 2021 Order: m1."time" 2022 -> Index Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 2023 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 2024 Filter: (device_id = 1) 2025 -> Index Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 2026 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 2027 Filter: (device_id = 1) 2028 -> Append 2029 -> Index Only Scan using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2 2030 Index Cond: (("time" = m1."time") AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 2031 -> Index Only Scan using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_1 2032 Index Cond: (("time" = m1."time") AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 2033(14 rows) 2034 2035\qecho test multiple join conditions 2036test multiple join conditions 2037\qecho device_id constraint should propagate 2038device_id constraint should propagate 2039:PREFIX SELECT m1.time FROM metrics_timestamptz m1 INNER JOIN metrics_timestamptz_2 m2 ON true WHERE m2.time = m1.time AND m1.device_id = m2.device_id AND m2.time < '2000-01-10' AND m1.device_id = 1 ORDER BY m1.time; 2040 QUERY PLAN 2041-------------------------------------------------------------------------------------------------------------------------- 2042 Nested Loop 2043 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 2044 Order: m1."time" 2045 -> Index Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 2046 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 2047 Filter: (device_id = 1) 2048 -> Index Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 2049 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 2050 Filter: (device_id = 1) 2051 -> Append 2052 -> Index Scan using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2 2053 Index Cond: (("time" = m1."time") AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 2054 Filter: (device_id = 1) 2055 -> Index Scan using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_1 2056 Index Cond: (("time" = m1."time") AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 2057 Filter: (device_id = 1) 2058(16 rows) 2059 2060\qecho test join with 3 tables 2061test join with 3 tables 2062:PREFIX SELECT m1.time FROM metrics_timestamptz m1 INNER JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time INNER JOIN metrics_timestamptz m3 ON m2.time=m3.time WHERE m1.time > '2000-01-01' AND m1.time < '2000-01-10' ORDER BY m1.time; 2063 QUERY PLAN 2064------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2065 Nested Loop 2066 -> Merge Join 2067 Merge Cond: (m1."time" = m2."time") 2068 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 2069 Order: m1."time" 2070 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 2071 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 2072 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 2073 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 2074 -> Materialize 2075 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 2076 Order: m2."time" 2077 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 2078 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 2079 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 2080 Index Cond: (("time" > 'Sat Jan 01 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)) 2081 -> Append 2082 -> Index Only Scan using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m3 2083 Index Cond: ("time" = m1."time") 2084 -> Index Only Scan using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m3_1 2085 Index Cond: ("time" = m1."time") 2086 -> Index Only Scan using _hyper_6_162_chunk_metrics_timestamptz_time_idx on _hyper_6_162_chunk m3_2 2087 Index Cond: ("time" = m1."time") 2088 -> Index Only Scan using _hyper_6_163_chunk_metrics_timestamptz_time_idx on _hyper_6_163_chunk m3_3 2089 Index Cond: ("time" = m1."time") 2090 -> Index Only Scan using _hyper_6_164_chunk_metrics_timestamptz_time_idx on _hyper_6_164_chunk m3_4 2091 Index Cond: ("time" = m1."time") 2092(27 rows) 2093 2094\qecho test non-Const constraints 2095test non-Const constraints 2096:PREFIX SELECT m1.time FROM metrics_timestamptz m1 INNER JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time WHERE m1.time < '2000-01-10'::text::timestamptz ORDER BY m1.time; 2097 QUERY PLAN 2098------------------------------------------------------------------------------------------------------------------------------- 2099 Merge Join 2100 Merge Cond: (m1."time" = m2."time") 2101 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 2102 Order: m1."time" 2103 Chunks excluded during startup: 3 2104 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 2105 Index Cond: ("time" < ('2000-01-10'::cstring)::timestamp with time zone) 2106 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 2107 Index Cond: ("time" < ('2000-01-10'::cstring)::timestamp with time zone) 2108 -> Materialize 2109 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 2110 Order: m2."time" 2111 Chunks excluded during startup: 4 2112 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 2113 Index Cond: ("time" < ('2000-01-10'::cstring)::timestamp with time zone) 2114 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 2115 Index Cond: ("time" < ('2000-01-10'::cstring)::timestamp with time zone) 2116(17 rows) 2117 2118\qecho test now() 2119test now() 2120:PREFIX SELECT m1.time FROM metrics_timestamptz m1 INNER JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time WHERE m1.time < now() ORDER BY m1.time; 2121 QUERY PLAN 2122------------------------------------------------------------------------------------------------------------------------------- 2123 Merge Join 2124 Merge Cond: (m1."time" = m2."time") 2125 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 2126 Order: m1."time" 2127 Chunks excluded during startup: 0 2128 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 2129 Index Cond: ("time" < now()) 2130 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 2131 Index Cond: ("time" < now()) 2132 -> Index Only Scan Backward using _hyper_6_162_chunk_metrics_timestamptz_time_idx on _hyper_6_162_chunk m1_3 2133 Index Cond: ("time" < now()) 2134 -> Index Only Scan Backward using _hyper_6_163_chunk_metrics_timestamptz_time_idx on _hyper_6_163_chunk m1_4 2135 Index Cond: ("time" < now()) 2136 -> Index Only Scan Backward using _hyper_6_164_chunk_metrics_timestamptz_time_idx on _hyper_6_164_chunk m1_5 2137 Index Cond: ("time" < now()) 2138 -> Materialize 2139 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 2140 Order: m2."time" 2141 Chunks excluded during startup: 0 2142 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 2143 Index Cond: ("time" < now()) 2144 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 2145 Index Cond: ("time" < now()) 2146 -> Index Only Scan Backward using _hyper_7_167_chunk_metrics_timestamptz_2_time_idx on _hyper_7_167_chunk m2_3 2147 Index Cond: ("time" < now()) 2148 -> Index Only Scan Backward using _hyper_7_168_chunk_metrics_timestamptz_2_time_idx on _hyper_7_168_chunk m2_4 2149 Index Cond: ("time" < now()) 2150 -> Index Only Scan Backward using _hyper_7_169_chunk_metrics_timestamptz_2_time_idx on _hyper_7_169_chunk m2_5 2151 Index Cond: ("time" < now()) 2152 -> Index Only Scan Backward using _hyper_7_170_chunk_metrics_timestamptz_2_time_idx on _hyper_7_170_chunk m2_6 2153 Index Cond: ("time" < now()) 2154(31 rows) 2155 2156\qecho test volatile function 2157test volatile function 2158\qecho should not propagate 2159should not propagate 2160:PREFIX SELECT m1.time FROM metrics_timestamptz m1 INNER JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time WHERE m1.time < clock_timestamp() ORDER BY m1.time; 2161 QUERY PLAN 2162------------------------------------------------------------------------------------------------------------------------------- 2163 Merge Join 2164 Merge Cond: (m1."time" = m2."time") 2165 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 2166 Order: m1."time" 2167 Chunks excluded during startup: 0 2168 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 2169 Filter: ("time" < clock_timestamp()) 2170 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 2171 Filter: ("time" < clock_timestamp()) 2172 -> Index Only Scan Backward using _hyper_6_162_chunk_metrics_timestamptz_time_idx on _hyper_6_162_chunk m1_3 2173 Filter: ("time" < clock_timestamp()) 2174 -> Index Only Scan Backward using _hyper_6_163_chunk_metrics_timestamptz_time_idx on _hyper_6_163_chunk m1_4 2175 Filter: ("time" < clock_timestamp()) 2176 -> Index Only Scan Backward using _hyper_6_164_chunk_metrics_timestamptz_time_idx on _hyper_6_164_chunk m1_5 2177 Filter: ("time" < clock_timestamp()) 2178 -> Materialize 2179 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 2180 Order: m2."time" 2181 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 2182 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 2183 -> Index Only Scan Backward using _hyper_7_167_chunk_metrics_timestamptz_2_time_idx on _hyper_7_167_chunk m2_3 2184 -> Index Only Scan Backward using _hyper_7_168_chunk_metrics_timestamptz_2_time_idx on _hyper_7_168_chunk m2_4 2185 -> Index Only Scan Backward using _hyper_7_169_chunk_metrics_timestamptz_2_time_idx on _hyper_7_169_chunk m2_5 2186 -> Index Only Scan Backward using _hyper_7_170_chunk_metrics_timestamptz_2_time_idx on _hyper_7_170_chunk m2_6 2187(24 rows) 2188 2189:PREFIX SELECT m1.time FROM metrics_timestamptz m1 INNER JOIN metrics_timestamptz_2 m2 ON m1.time = m2.time WHERE m2.time < clock_timestamp() ORDER BY m1.time; 2190 QUERY PLAN 2191----------------------------------------------------------------------------------------------------------------------------- 2192 Merge Join 2193 Merge Cond: (m2."time" = m1."time") 2194 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 m2 2195 Order: m2."time" 2196 Chunks excluded during startup: 0 2197 -> Index Only Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk m2_1 2198 Filter: ("time" < clock_timestamp()) 2199 -> Index Only Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk m2_2 2200 Filter: ("time" < clock_timestamp()) 2201 -> Index Only Scan Backward using _hyper_7_167_chunk_metrics_timestamptz_2_time_idx on _hyper_7_167_chunk m2_3 2202 Filter: ("time" < clock_timestamp()) 2203 -> Index Only Scan Backward using _hyper_7_168_chunk_metrics_timestamptz_2_time_idx on _hyper_7_168_chunk m2_4 2204 Filter: ("time" < clock_timestamp()) 2205 -> Index Only Scan Backward using _hyper_7_169_chunk_metrics_timestamptz_2_time_idx on _hyper_7_169_chunk m2_5 2206 Filter: ("time" < clock_timestamp()) 2207 -> Index Only Scan Backward using _hyper_7_170_chunk_metrics_timestamptz_2_time_idx on _hyper_7_170_chunk m2_6 2208 Filter: ("time" < clock_timestamp()) 2209 -> Materialize 2210 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 2211 Order: m1."time" 2212 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 2213 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 2214 -> Index Only Scan Backward using _hyper_6_162_chunk_metrics_timestamptz_time_idx on _hyper_6_162_chunk m1_3 2215 -> Index Only Scan Backward using _hyper_6_163_chunk_metrics_timestamptz_time_idx on _hyper_6_163_chunk m1_4 2216 -> Index Only Scan Backward using _hyper_6_164_chunk_metrics_timestamptz_time_idx on _hyper_6_164_chunk m1_5 2217(25 rows) 2218 2219\qecho test JOINs with normal table 2220test JOINs with normal table 2221\qecho will not propagate because constraints are only added to hypertables 2222will not propagate because constraints are only added to hypertables 2223:PREFIX SELECT m1.time FROM metrics_timestamptz m1 INNER JOIN regular_timestamptz m2 ON m1.time = m2.time WHERE m1.time < '2000-01-10' ORDER BY m1.time; 2224 QUERY PLAN 2225----------------------------------------------------------------------------------------------------------------------- 2226 Merge Join 2227 Merge Cond: (m1."time" = m2."time") 2228 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 2229 Order: m1."time" 2230 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 2231 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 2232 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 2233 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 2234 -> Sort 2235 Sort Key: m2."time" 2236 -> Seq Scan on regular_timestamptz m2 2237(11 rows) 2238 2239\qecho test JOINs with normal table 2240test JOINs with normal table 2241:PREFIX SELECT m1.time FROM metrics_timestamptz m1 INNER JOIN regular_timestamptz m2 ON m1.time = m2.time WHERE m2.time < '2000-01-10' ORDER BY m1.time; 2242 QUERY PLAN 2243----------------------------------------------------------------------------------------------------------------------- 2244 Merge Join 2245 Merge Cond: (m1."time" = m2."time") 2246 -> Custom Scan (ChunkAppend) on metrics_timestamptz m1 2247 Order: m1."time" 2248 -> Index Only Scan Backward using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk m1_1 2249 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 2250 -> Index Only Scan Backward using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk m1_2 2251 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 2252 -> Sort 2253 Sort Key: m2."time" 2254 -> Seq Scan on regular_timestamptz m2 2255 Filter: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 2256(12 rows) 2257 2258\qecho test quals are not pushed into OUTER JOIN 2259test quals are not pushed into OUTER JOIN 2260CREATE TABLE outer_join_1 (id int, name text,time timestamptz NOT NULL DEFAULT '2000-01-01'); 2261CREATE TABLE outer_join_2 (id int, name text,time timestamptz NOT NULL DEFAULT '2000-01-01'); 2262SELECT (SELECT table_name FROM create_hypertable(tbl, 'time')) FROM (VALUES ('outer_join_1'),('outer_join_2')) v(tbl); 2263 table_name 2264-------------- 2265 outer_join_1 2266 outer_join_2 2267(2 rows) 2268 2269INSERT INTO outer_join_1 VALUES(1,'a'), (2,'b'); 2270INSERT INTO outer_join_2 VALUES(1,'a'); 2271:PREFIX SELECT one.id, two.name FROM outer_join_1 one LEFT OUTER JOIN outer_join_2 two ON one.id=two.id WHERE one.id=2; 2272 QUERY PLAN 2273------------------------------------------------- 2274 Nested Loop Left Join 2275 Join Filter: (one.id = two.id) 2276 -> Seq Scan on _hyper_9_176_chunk one 2277 Filter: (id = 2) 2278 -> Materialize 2279 -> Seq Scan on _hyper_10_177_chunk two 2280 Filter: (id = 2) 2281(7 rows) 2282 2283:PREFIX SELECT one.id, two.name FROM outer_join_2 two RIGHT OUTER JOIN outer_join_1 one ON one.id=two.id WHERE one.id=2; 2284 QUERY PLAN 2285------------------------------------------------- 2286 Nested Loop Left Join 2287 Join Filter: (one.id = two.id) 2288 -> Seq Scan on _hyper_9_176_chunk one 2289 Filter: (id = 2) 2290 -> Materialize 2291 -> Seq Scan on _hyper_10_177_chunk two 2292 Filter: (id = 2) 2293(7 rows) 2294 2295DROP TABLE outer_join_1; 2296DROP TABLE outer_join_2; 2297-- test UNION between regular table and hypertable 2298SELECT time FROM regular_timestamptz UNION SELECT time FROM metrics_timestamptz ORDER BY 1; 2299 time 2300------------------------------ 2301 Sat Jan 01 00:00:00 2000 PST 2302 Sun Jan 02 00:00:00 2000 PST 2303 Mon Jan 03 00:00:00 2000 PST 2304 Tue Jan 04 00:00:00 2000 PST 2305 Wed Jan 05 00:00:00 2000 PST 2306 Thu Jan 06 00:00:00 2000 PST 2307 Fri Jan 07 00:00:00 2000 PST 2308 Sat Jan 08 00:00:00 2000 PST 2309 Sun Jan 09 00:00:00 2000 PST 2310 Mon Jan 10 00:00:00 2000 PST 2311 Tue Jan 11 00:00:00 2000 PST 2312 Wed Jan 12 00:00:00 2000 PST 2313 Thu Jan 13 00:00:00 2000 PST 2314 Fri Jan 14 00:00:00 2000 PST 2315 Sat Jan 15 00:00:00 2000 PST 2316 Sun Jan 16 00:00:00 2000 PST 2317 Mon Jan 17 00:00:00 2000 PST 2318 Tue Jan 18 00:00:00 2000 PST 2319 Wed Jan 19 00:00:00 2000 PST 2320 Thu Jan 20 00:00:00 2000 PST 2321 Fri Jan 21 00:00:00 2000 PST 2322 Sat Jan 22 00:00:00 2000 PST 2323 Sun Jan 23 00:00:00 2000 PST 2324 Mon Jan 24 00:00:00 2000 PST 2325 Tue Jan 25 00:00:00 2000 PST 2326 Wed Jan 26 00:00:00 2000 PST 2327 Thu Jan 27 00:00:00 2000 PST 2328 Fri Jan 28 00:00:00 2000 PST 2329 Sat Jan 29 00:00:00 2000 PST 2330 Sun Jan 30 00:00:00 2000 PST 2331 Mon Jan 31 00:00:00 2000 PST 2332 Tue Feb 01 00:00:00 2000 PST 2333(32 rows) 2334 2335-- test UNION ALL between regular table and hypertable 2336SELECT time FROM regular_timestamptz UNION ALL SELECT time FROM metrics_timestamptz ORDER BY 1; 2337 time 2338------------------------------ 2339 Sat Jan 01 00:00:00 2000 PST 2340 Sat Jan 01 00:00:00 2000 PST 2341 Sat Jan 01 00:00:00 2000 PST 2342 Sat Jan 01 00:00:00 2000 PST 2343 Sun Jan 02 00:00:00 2000 PST 2344 Sun Jan 02 00:00:00 2000 PST 2345 Sun Jan 02 00:00:00 2000 PST 2346 Sun Jan 02 00:00:00 2000 PST 2347 Mon Jan 03 00:00:00 2000 PST 2348 Mon Jan 03 00:00:00 2000 PST 2349 Mon Jan 03 00:00:00 2000 PST 2350 Mon Jan 03 00:00:00 2000 PST 2351 Tue Jan 04 00:00:00 2000 PST 2352 Tue Jan 04 00:00:00 2000 PST 2353 Tue Jan 04 00:00:00 2000 PST 2354 Tue Jan 04 00:00:00 2000 PST 2355 Wed Jan 05 00:00:00 2000 PST 2356 Wed Jan 05 00:00:00 2000 PST 2357 Wed Jan 05 00:00:00 2000 PST 2358 Wed Jan 05 00:00:00 2000 PST 2359 Thu Jan 06 00:00:00 2000 PST 2360 Thu Jan 06 00:00:00 2000 PST 2361 Thu Jan 06 00:00:00 2000 PST 2362 Thu Jan 06 00:00:00 2000 PST 2363 Fri Jan 07 00:00:00 2000 PST 2364 Fri Jan 07 00:00:00 2000 PST 2365 Fri Jan 07 00:00:00 2000 PST 2366 Fri Jan 07 00:00:00 2000 PST 2367 Sat Jan 08 00:00:00 2000 PST 2368 Sat Jan 08 00:00:00 2000 PST 2369 Sat Jan 08 00:00:00 2000 PST 2370 Sat Jan 08 00:00:00 2000 PST 2371 Sun Jan 09 00:00:00 2000 PST 2372 Sun Jan 09 00:00:00 2000 PST 2373 Sun Jan 09 00:00:00 2000 PST 2374 Sun Jan 09 00:00:00 2000 PST 2375 Mon Jan 10 00:00:00 2000 PST 2376 Mon Jan 10 00:00:00 2000 PST 2377 Mon Jan 10 00:00:00 2000 PST 2378 Mon Jan 10 00:00:00 2000 PST 2379 Tue Jan 11 00:00:00 2000 PST 2380 Tue Jan 11 00:00:00 2000 PST 2381 Tue Jan 11 00:00:00 2000 PST 2382 Tue Jan 11 00:00:00 2000 PST 2383 Wed Jan 12 00:00:00 2000 PST 2384 Wed Jan 12 00:00:00 2000 PST 2385 Wed Jan 12 00:00:00 2000 PST 2386 Wed Jan 12 00:00:00 2000 PST 2387 Thu Jan 13 00:00:00 2000 PST 2388 Thu Jan 13 00:00:00 2000 PST 2389 Thu Jan 13 00:00:00 2000 PST 2390 Thu Jan 13 00:00:00 2000 PST 2391 Fri Jan 14 00:00:00 2000 PST 2392 Fri Jan 14 00:00:00 2000 PST 2393 Fri Jan 14 00:00:00 2000 PST 2394 Fri Jan 14 00:00:00 2000 PST 2395 Sat Jan 15 00:00:00 2000 PST 2396 Sat Jan 15 00:00:00 2000 PST 2397 Sat Jan 15 00:00:00 2000 PST 2398 Sat Jan 15 00:00:00 2000 PST 2399 Sun Jan 16 00:00:00 2000 PST 2400 Sun Jan 16 00:00:00 2000 PST 2401 Sun Jan 16 00:00:00 2000 PST 2402 Sun Jan 16 00:00:00 2000 PST 2403 Mon Jan 17 00:00:00 2000 PST 2404 Mon Jan 17 00:00:00 2000 PST 2405 Mon Jan 17 00:00:00 2000 PST 2406 Mon Jan 17 00:00:00 2000 PST 2407 Tue Jan 18 00:00:00 2000 PST 2408 Tue Jan 18 00:00:00 2000 PST 2409 Tue Jan 18 00:00:00 2000 PST 2410 Tue Jan 18 00:00:00 2000 PST 2411 Wed Jan 19 00:00:00 2000 PST 2412 Wed Jan 19 00:00:00 2000 PST 2413 Wed Jan 19 00:00:00 2000 PST 2414 Wed Jan 19 00:00:00 2000 PST 2415 Thu Jan 20 00:00:00 2000 PST 2416 Thu Jan 20 00:00:00 2000 PST 2417 Thu Jan 20 00:00:00 2000 PST 2418 Thu Jan 20 00:00:00 2000 PST 2419 Fri Jan 21 00:00:00 2000 PST 2420 Fri Jan 21 00:00:00 2000 PST 2421 Fri Jan 21 00:00:00 2000 PST 2422 Fri Jan 21 00:00:00 2000 PST 2423 Sat Jan 22 00:00:00 2000 PST 2424 Sat Jan 22 00:00:00 2000 PST 2425 Sat Jan 22 00:00:00 2000 PST 2426 Sat Jan 22 00:00:00 2000 PST 2427 Sun Jan 23 00:00:00 2000 PST 2428 Sun Jan 23 00:00:00 2000 PST 2429 Sun Jan 23 00:00:00 2000 PST 2430 Sun Jan 23 00:00:00 2000 PST 2431 Mon Jan 24 00:00:00 2000 PST 2432 Mon Jan 24 00:00:00 2000 PST 2433 Mon Jan 24 00:00:00 2000 PST 2434 Mon Jan 24 00:00:00 2000 PST 2435 Tue Jan 25 00:00:00 2000 PST 2436 Tue Jan 25 00:00:00 2000 PST 2437 Tue Jan 25 00:00:00 2000 PST 2438 Tue Jan 25 00:00:00 2000 PST 2439 Wed Jan 26 00:00:00 2000 PST 2440 Wed Jan 26 00:00:00 2000 PST 2441 Wed Jan 26 00:00:00 2000 PST 2442 Wed Jan 26 00:00:00 2000 PST 2443 Thu Jan 27 00:00:00 2000 PST 2444 Thu Jan 27 00:00:00 2000 PST 2445 Thu Jan 27 00:00:00 2000 PST 2446 Thu Jan 27 00:00:00 2000 PST 2447 Fri Jan 28 00:00:00 2000 PST 2448 Fri Jan 28 00:00:00 2000 PST 2449 Fri Jan 28 00:00:00 2000 PST 2450 Fri Jan 28 00:00:00 2000 PST 2451 Sat Jan 29 00:00:00 2000 PST 2452 Sat Jan 29 00:00:00 2000 PST 2453 Sat Jan 29 00:00:00 2000 PST 2454 Sat Jan 29 00:00:00 2000 PST 2455 Sun Jan 30 00:00:00 2000 PST 2456 Sun Jan 30 00:00:00 2000 PST 2457 Sun Jan 30 00:00:00 2000 PST 2458 Sun Jan 30 00:00:00 2000 PST 2459 Mon Jan 31 00:00:00 2000 PST 2460 Mon Jan 31 00:00:00 2000 PST 2461 Mon Jan 31 00:00:00 2000 PST 2462 Mon Jan 31 00:00:00 2000 PST 2463 Tue Feb 01 00:00:00 2000 PST 2464 Tue Feb 01 00:00:00 2000 PST 2465 Tue Feb 01 00:00:00 2000 PST 2466 Tue Feb 01 00:00:00 2000 PST 2467(128 rows) 2468 2469-- test nested join qual propagation 2470:PREFIX 2471SELECT * FROM ( 2472SELECT o1_m1.time FROM metrics_timestamptz o1_m1 INNER JOIN metrics_timestamptz_2 o1_m2 ON true WHERE o1_m2.time = o1_m1.time AND o1_m1.device_id = o1_m2.device_id AND o1_m2.time < '2000-01-10' AND o1_m1.device_id = 1 2473) o1 FULL OUTER JOIN ( 2474SELECT o2_m1.time FROM metrics_timestamptz o2_m1 FULL OUTER JOIN metrics_timestamptz_2 o2_m2 ON true WHERE o2_m2.time = o2_m1.time AND o2_m1.device_id = o2_m2.device_id AND o2_m2.time > '2000-01-20' AND o2_m1.device_id = 2 2475) o2 ON o1.time = o2.time ORDER BY 1,2; 2476 QUERY PLAN 2477----------------------------------------------------------------------------------------------------------------------------------------- 2478 Sort 2479 Sort Key: o1_m1."time", o2_m1."time" 2480 -> Merge Full Join 2481 Merge Cond: (o2_m1."time" = o1_m1."time") 2482 -> Nested Loop 2483 -> Merge Append 2484 Sort Key: o2_m2."time" 2485 -> Index Scan Backward using _hyper_7_170_chunk_metrics_timestamptz_2_time_idx on _hyper_7_170_chunk o2_m2 2486 Index Cond: ("time" > 'Thu Jan 20 00:00:00 2000 PST'::timestamp with time zone) 2487 Filter: (device_id = 2) 2488 -> Index Scan Backward using _hyper_7_168_chunk_metrics_timestamptz_2_time_idx on _hyper_7_168_chunk o2_m2_1 2489 Index Cond: ("time" > 'Thu Jan 20 00:00:00 2000 PST'::timestamp with time zone) 2490 Filter: (device_id = 2) 2491 -> Index Scan Backward using _hyper_7_169_chunk_metrics_timestamptz_2_time_idx on _hyper_7_169_chunk o2_m2_2 2492 Index Cond: ("time" > 'Thu Jan 20 00:00:00 2000 PST'::timestamp with time zone) 2493 Filter: (device_id = 2) 2494 -> Append 2495 -> Index Scan using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk o2_m1 2496 Index Cond: ("time" = o2_m2."time") 2497 Filter: (device_id = 2) 2498 -> Index Scan using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk o2_m1_1 2499 Index Cond: ("time" = o2_m2."time") 2500 Filter: (device_id = 2) 2501 -> Index Scan using _hyper_6_162_chunk_metrics_timestamptz_time_idx on _hyper_6_162_chunk o2_m1_2 2502 Index Cond: ("time" = o2_m2."time") 2503 Filter: (device_id = 2) 2504 -> Index Scan using _hyper_6_163_chunk_metrics_timestamptz_time_idx on _hyper_6_163_chunk o2_m1_3 2505 Index Cond: ("time" = o2_m2."time") 2506 Filter: (device_id = 2) 2507 -> Index Scan using _hyper_6_164_chunk_metrics_timestamptz_time_idx on _hyper_6_164_chunk o2_m1_4 2508 Index Cond: ("time" = o2_m2."time") 2509 Filter: (device_id = 2) 2510 -> Materialize 2511 -> Nested Loop 2512 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 o1_m2 2513 Order: o1_m2."time" 2514 -> Index Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk o1_m2_1 2515 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 2516 Filter: (device_id = 1) 2517 -> Index Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk o1_m2_2 2518 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 2519 Filter: (device_id = 1) 2520 -> Append 2521 -> Index Scan using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk o1_m1 2522 Index Cond: ("time" = o1_m2."time") 2523 Filter: (device_id = 1) 2524 -> Index Scan using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk o1_m1_1 2525 Index Cond: ("time" = o1_m2."time") 2526 Filter: (device_id = 1) 2527 -> Index Scan using _hyper_6_162_chunk_metrics_timestamptz_time_idx on _hyper_6_162_chunk o1_m1_2 2528 Index Cond: ("time" = o1_m2."time") 2529 Filter: (device_id = 1) 2530 -> Index Scan using _hyper_6_163_chunk_metrics_timestamptz_time_idx on _hyper_6_163_chunk o1_m1_3 2531 Index Cond: ("time" = o1_m2."time") 2532 Filter: (device_id = 1) 2533 -> Index Scan using _hyper_6_164_chunk_metrics_timestamptz_time_idx on _hyper_6_164_chunk o1_m1_4 2534 Index Cond: ("time" = o1_m2."time") 2535 Filter: (device_id = 1) 2536(58 rows) 2537 2538:PREFIX 2539SELECT * FROM ( 2540SELECT o1_m1.time FROM metrics_timestamptz o1_m1 INNER JOIN metrics_timestamptz_2 o1_m2 ON o1_m2.time = o1_m1.time AND o1_m1.device_id = o1_m2.device_id WHERE o1_m2.time < '2000-01-10' AND o1_m1.device_id = 1 2541) o1 FULL OUTER JOIN ( 2542SELECT o2_m1.time FROM metrics_timestamptz o2_m1 FULL OUTER JOIN metrics_timestamptz_2 o2_m2 ON o2_m2.time = o2_m1.time AND o2_m1.device_id = o2_m2.device_id WHERE o2_m2.time > '2000-01-20' AND o2_m1.device_id = 2 2543) o2 ON o1.time = o2.time ORDER BY 1,2; 2544 QUERY PLAN 2545----------------------------------------------------------------------------------------------------------------------------------------- 2546 Sort 2547 Sort Key: o1_m1."time", o2_m1."time" 2548 -> Merge Full Join 2549 Merge Cond: (o2_m1."time" = o1_m1."time") 2550 -> Nested Loop 2551 -> Merge Append 2552 Sort Key: o2_m2."time" 2553 -> Index Scan Backward using _hyper_7_170_chunk_metrics_timestamptz_2_time_idx on _hyper_7_170_chunk o2_m2 2554 Index Cond: ("time" > 'Thu Jan 20 00:00:00 2000 PST'::timestamp with time zone) 2555 Filter: (device_id = 2) 2556 -> Index Scan Backward using _hyper_7_168_chunk_metrics_timestamptz_2_time_idx on _hyper_7_168_chunk o2_m2_1 2557 Index Cond: ("time" > 'Thu Jan 20 00:00:00 2000 PST'::timestamp with time zone) 2558 Filter: (device_id = 2) 2559 -> Index Scan Backward using _hyper_7_169_chunk_metrics_timestamptz_2_time_idx on _hyper_7_169_chunk o2_m2_2 2560 Index Cond: ("time" > 'Thu Jan 20 00:00:00 2000 PST'::timestamp with time zone) 2561 Filter: (device_id = 2) 2562 -> Append 2563 -> Index Scan using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk o2_m1 2564 Index Cond: ("time" = o2_m2."time") 2565 Filter: (device_id = 2) 2566 -> Index Scan using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk o2_m1_1 2567 Index Cond: ("time" = o2_m2."time") 2568 Filter: (device_id = 2) 2569 -> Index Scan using _hyper_6_162_chunk_metrics_timestamptz_time_idx on _hyper_6_162_chunk o2_m1_2 2570 Index Cond: ("time" = o2_m2."time") 2571 Filter: (device_id = 2) 2572 -> Index Scan using _hyper_6_163_chunk_metrics_timestamptz_time_idx on _hyper_6_163_chunk o2_m1_3 2573 Index Cond: ("time" = o2_m2."time") 2574 Filter: (device_id = 2) 2575 -> Index Scan using _hyper_6_164_chunk_metrics_timestamptz_time_idx on _hyper_6_164_chunk o2_m1_4 2576 Index Cond: ("time" = o2_m2."time") 2577 Filter: (device_id = 2) 2578 -> Materialize 2579 -> Nested Loop 2580 -> Custom Scan (ChunkAppend) on metrics_timestamptz_2 o1_m2 2581 Order: o1_m2."time" 2582 -> Index Scan Backward using _hyper_7_165_chunk_metrics_timestamptz_2_time_idx on _hyper_7_165_chunk o1_m2_1 2583 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 2584 Filter: (device_id = 1) 2585 -> Index Scan Backward using _hyper_7_166_chunk_metrics_timestamptz_2_time_idx on _hyper_7_166_chunk o1_m2_2 2586 Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) 2587 Filter: (device_id = 1) 2588 -> Append 2589 -> Index Scan using _hyper_6_160_chunk_metrics_timestamptz_time_idx on _hyper_6_160_chunk o1_m1 2590 Index Cond: ("time" = o1_m2."time") 2591 Filter: (device_id = 1) 2592 -> Index Scan using _hyper_6_161_chunk_metrics_timestamptz_time_idx on _hyper_6_161_chunk o1_m1_1 2593 Index Cond: ("time" = o1_m2."time") 2594 Filter: (device_id = 1) 2595 -> Index Scan using _hyper_6_162_chunk_metrics_timestamptz_time_idx on _hyper_6_162_chunk o1_m1_2 2596 Index Cond: ("time" = o1_m2."time") 2597 Filter: (device_id = 1) 2598 -> Index Scan using _hyper_6_163_chunk_metrics_timestamptz_time_idx on _hyper_6_163_chunk o1_m1_3 2599 Index Cond: ("time" = o1_m2."time") 2600 Filter: (device_id = 1) 2601 -> Index Scan using _hyper_6_164_chunk_metrics_timestamptz_time_idx on _hyper_6_164_chunk o1_m1_4 2602 Index Cond: ("time" = o1_m2."time") 2603 Filter: (device_id = 1) 2604(58 rows) 2605 2606\ir include/plan_expand_hypertable_chunks_in_query.sql 2607-- This file and its contents are licensed under the Apache License 2.0. 2608-- Please see the included NOTICE for copyright information and 2609-- LICENSE-APACHE for a copy of the license. 2610--we want to see how our logic excludes chunks 2611--and not how much work constraint_exclusion does 2612SET constraint_exclusion = 'off'; 2613:PREFIX SELECT * FROM hyper ORDER BY value; 2614 QUERY PLAN 2615-------------------------------------------- 2616 Sort 2617 Sort Key: _hyper_1_1_chunk.value 2618 -> Append 2619 -> Seq Scan on _hyper_1_1_chunk 2620 -> Seq Scan on _hyper_1_2_chunk 2621 -> Seq Scan on _hyper_1_3_chunk 2622 -> Seq Scan on _hyper_1_4_chunk 2623 -> Seq Scan on _hyper_1_5_chunk 2624 -> Seq Scan on _hyper_1_6_chunk 2625 -> Seq Scan on _hyper_1_7_chunk 2626 -> Seq Scan on _hyper_1_8_chunk 2627 -> Seq Scan on _hyper_1_9_chunk 2628 -> Seq Scan on _hyper_1_10_chunk 2629 -> Seq Scan on _hyper_1_11_chunk 2630 -> Seq Scan on _hyper_1_12_chunk 2631 -> Seq Scan on _hyper_1_13_chunk 2632 -> Seq Scan on _hyper_1_14_chunk 2633 -> Seq Scan on _hyper_1_15_chunk 2634 -> Seq Scan on _hyper_1_16_chunk 2635 -> Seq Scan on _hyper_1_17_chunk 2636 -> Seq Scan on _hyper_1_18_chunk 2637 -> Seq Scan on _hyper_1_19_chunk 2638 -> Seq Scan on _hyper_1_20_chunk 2639 -> Seq Scan on _hyper_1_21_chunk 2640 -> Seq Scan on _hyper_1_22_chunk 2641 -> Seq Scan on _hyper_1_23_chunk 2642 -> Seq Scan on _hyper_1_24_chunk 2643 -> Seq Scan on _hyper_1_25_chunk 2644 -> Seq Scan on _hyper_1_26_chunk 2645 -> Seq Scan on _hyper_1_27_chunk 2646 -> Seq Scan on _hyper_1_28_chunk 2647 -> Seq Scan on _hyper_1_29_chunk 2648 -> Seq Scan on _hyper_1_30_chunk 2649 -> Seq Scan on _hyper_1_31_chunk 2650 -> Seq Scan on _hyper_1_32_chunk 2651 -> Seq Scan on _hyper_1_33_chunk 2652 -> Seq Scan on _hyper_1_34_chunk 2653 -> Seq Scan on _hyper_1_35_chunk 2654 -> Seq Scan on _hyper_1_36_chunk 2655 -> Seq Scan on _hyper_1_37_chunk 2656 -> Seq Scan on _hyper_1_38_chunk 2657 -> Seq Scan on _hyper_1_39_chunk 2658 -> Seq Scan on _hyper_1_40_chunk 2659 -> Seq Scan on _hyper_1_41_chunk 2660 -> Seq Scan on _hyper_1_42_chunk 2661 -> Seq Scan on _hyper_1_43_chunk 2662 -> Seq Scan on _hyper_1_44_chunk 2663 -> Seq Scan on _hyper_1_45_chunk 2664 -> Seq Scan on _hyper_1_46_chunk 2665 -> Seq Scan on _hyper_1_47_chunk 2666 -> Seq Scan on _hyper_1_48_chunk 2667 -> Seq Scan on _hyper_1_49_chunk 2668 -> Seq Scan on _hyper_1_50_chunk 2669 -> Seq Scan on _hyper_1_51_chunk 2670 -> Seq Scan on _hyper_1_52_chunk 2671 -> Seq Scan on _hyper_1_53_chunk 2672 -> Seq Scan on _hyper_1_54_chunk 2673 -> Seq Scan on _hyper_1_55_chunk 2674 -> Seq Scan on _hyper_1_56_chunk 2675 -> Seq Scan on _hyper_1_57_chunk 2676 -> Seq Scan on _hyper_1_58_chunk 2677 -> Seq Scan on _hyper_1_59_chunk 2678 -> Seq Scan on _hyper_1_60_chunk 2679 -> Seq Scan on _hyper_1_61_chunk 2680 -> Seq Scan on _hyper_1_62_chunk 2681 -> Seq Scan on _hyper_1_63_chunk 2682 -> Seq Scan on _hyper_1_64_chunk 2683 -> Seq Scan on _hyper_1_65_chunk 2684 -> Seq Scan on _hyper_1_66_chunk 2685 -> Seq Scan on _hyper_1_67_chunk 2686 -> Seq Scan on _hyper_1_68_chunk 2687 -> Seq Scan on _hyper_1_69_chunk 2688 -> Seq Scan on _hyper_1_70_chunk 2689 -> Seq Scan on _hyper_1_71_chunk 2690 -> Seq Scan on _hyper_1_72_chunk 2691 -> Seq Scan on _hyper_1_73_chunk 2692 -> Seq Scan on _hyper_1_74_chunk 2693 -> Seq Scan on _hyper_1_75_chunk 2694 -> Seq Scan on _hyper_1_76_chunk 2695 -> Seq Scan on _hyper_1_77_chunk 2696 -> Seq Scan on _hyper_1_78_chunk 2697 -> Seq Scan on _hyper_1_79_chunk 2698 -> Seq Scan on _hyper_1_80_chunk 2699 -> Seq Scan on _hyper_1_81_chunk 2700 -> Seq Scan on _hyper_1_82_chunk 2701 -> Seq Scan on _hyper_1_83_chunk 2702 -> Seq Scan on _hyper_1_84_chunk 2703 -> Seq Scan on _hyper_1_85_chunk 2704 -> Seq Scan on _hyper_1_86_chunk 2705 -> Seq Scan on _hyper_1_87_chunk 2706 -> Seq Scan on _hyper_1_88_chunk 2707 -> Seq Scan on _hyper_1_89_chunk 2708 -> Seq Scan on _hyper_1_90_chunk 2709 -> Seq Scan on _hyper_1_91_chunk 2710 -> Seq Scan on _hyper_1_92_chunk 2711 -> Seq Scan on _hyper_1_93_chunk 2712 -> Seq Scan on _hyper_1_94_chunk 2713 -> Seq Scan on _hyper_1_95_chunk 2714 -> Seq Scan on _hyper_1_96_chunk 2715 -> Seq Scan on _hyper_1_97_chunk 2716 -> Seq Scan on _hyper_1_98_chunk 2717 -> Seq Scan on _hyper_1_99_chunk 2718 -> Seq Scan on _hyper_1_100_chunk 2719 -> Seq Scan on _hyper_1_101_chunk 2720 -> Seq Scan on _hyper_1_102_chunk 2721(105 rows) 2722 2723-- explicit chunk exclusion 2724:PREFIX SELECT * FROM hyper WHERE _timescaledb_internal.chunks_in(hyper, ARRAY[1,2]) ORDER BY value; 2725 QUERY PLAN 2726------------------------------------------ 2727 Sort 2728 Sort Key: _hyper_1_1_chunk.value 2729 -> Append 2730 -> Seq Scan on _hyper_1_1_chunk 2731 -> Seq Scan on _hyper_1_2_chunk 2732(5 rows) 2733 2734:PREFIX SELECT * FROM (SELECT * FROM hyper h WHERE _timescaledb_internal.chunks_in(h, ARRAY[1,2,3])) T ORDER BY value; 2735 QUERY PLAN 2736---------------------------------------------- 2737 Sort 2738 Sort Key: h.value 2739 -> Append 2740 -> Seq Scan on _hyper_1_1_chunk h 2741 -> Seq Scan on _hyper_1_2_chunk h_1 2742 -> Seq Scan on _hyper_1_3_chunk h_2 2743(6 rows) 2744 2745:PREFIX SELECT * FROM hyper WHERE _timescaledb_internal.chunks_in(hyper, ARRAY[1,2,3]) AND time < 10 ORDER BY value; 2746 QUERY PLAN 2747------------------------------------------ 2748 Sort 2749 Sort Key: _hyper_1_1_chunk.value 2750 -> Append 2751 -> Seq Scan on _hyper_1_1_chunk 2752 Filter: ("time" < 10) 2753 -> Seq Scan on _hyper_1_2_chunk 2754 Filter: ("time" < 10) 2755 -> Seq Scan on _hyper_1_3_chunk 2756 Filter: ("time" < 10) 2757(9 rows) 2758 2759:PREFIX SELECT * FROM hyper_ts WHERE device_id = 'dev1' AND time < to_timestamp(10) AND _timescaledb_internal.chunks_in(hyper_ts, ARRAY[116]) ORDER BY value; 2760 QUERY PLAN 2761---------------------------------------------------------------------------------------------------------------------- 2762 Sort 2763 Sort Key: _hyper_3_116_chunk.value 2764 -> Seq Scan on _hyper_3_116_chunk 2765 Filter: (("time" < 'Wed Dec 31 16:00:10 1969 PST'::timestamp with time zone) AND (device_id = 'dev1'::text)) 2766(4 rows) 2767 2768:PREFIX SELECT * FROM hyper_ts h JOIN tag on (h.tag_id = tag.id ) WHERE _timescaledb_internal.chunks_in(h, ARRAY[116]) AND time < to_timestamp(10) AND device_id = 'dev1' ORDER BY value; 2769 QUERY PLAN 2770---------------------------------------------------------------------------------------------------------------------------------- 2771 Sort 2772 Sort Key: h.value 2773 -> Merge Join 2774 Merge Cond: (tag.id = h.tag_id) 2775 -> Index Scan using tag_pkey on tag 2776 -> Sort 2777 Sort Key: h.tag_id 2778 -> Seq Scan on _hyper_3_116_chunk h 2779 Filter: (("time" < 'Wed Dec 31 16:00:10 1969 PST'::timestamp with time zone) AND (device_id = 'dev1'::text)) 2780(9 rows) 2781 2782:PREFIX SELECT * FROM hyper_w_space h1 JOIN hyper_ts h2 ON h1.device_id=h2.device_id WHERE _timescaledb_internal.chunks_in(h1, ARRAY[104,105]) AND _timescaledb_internal.chunks_in(h2, ARRAY[116,117]) ORDER BY h1.value; 2783 QUERY PLAN 2784------------------------------------------------------------- 2785 Sort 2786 Sort Key: h1.value 2787 -> Hash Join 2788 Hash Cond: (h2.device_id = h1.device_id) 2789 -> Append 2790 -> Seq Scan on _hyper_3_116_chunk h2 2791 -> Seq Scan on _hyper_3_117_chunk h2_1 2792 -> Hash 2793 -> Append 2794 -> Seq Scan on _hyper_2_104_chunk h1 2795 -> Seq Scan on _hyper_2_105_chunk h1_1 2796(11 rows) 2797 2798:PREFIX SELECT * FROM hyper_w_space h1 JOIN hyper_ts h2 ON h1.device_id=h2.device_id AND _timescaledb_internal.chunks_in(h2, ARRAY[116,117]) WHERE _timescaledb_internal.chunks_in(h1, ARRAY[104,105]) ORDER BY h1.value; 2799 QUERY PLAN 2800------------------------------------------------------------- 2801 Sort 2802 Sort Key: h1.value 2803 -> Hash Join 2804 Hash Cond: (h2.device_id = h1.device_id) 2805 -> Append 2806 -> Seq Scan on _hyper_3_116_chunk h2 2807 -> Seq Scan on _hyper_3_117_chunk h2_1 2808 -> Hash 2809 -> Append 2810 -> Seq Scan on _hyper_2_104_chunk h1 2811 -> Seq Scan on _hyper_2_105_chunk h1_1 2812(11 rows) 2813 2814:PREFIX SELECT * FROM hyper h1, hyper h2 WHERE _timescaledb_internal.chunks_in(h1, ARRAY[1,2]) AND _timescaledb_internal.chunks_in(h2, ARRAY[2,3]); 2815 QUERY PLAN 2816----------------------------------------------------- 2817 Nested Loop 2818 -> Append 2819 -> Seq Scan on _hyper_1_1_chunk h1 2820 -> Seq Scan on _hyper_1_2_chunk h1_1 2821 -> Materialize 2822 -> Append 2823 -> Seq Scan on _hyper_1_2_chunk h2 2824 -> Seq Scan on _hyper_1_3_chunk h2_1 2825(8 rows) 2826 2827SET enable_seqscan=false; 2828-- Should perform index-only scan. Since we pass whole row into the function it might block planner from using index-only scan. 2829-- But since we'll remove the function from the query tree before planner decision it shouldn't affect index-only decision. 2830:PREFIX SELECT time FROM hyper WHERE time=0 AND _timescaledb_internal.chunks_in(hyper, ARRAY[1]); 2831 QUERY PLAN 2832--------------------------------------------------------------------------- 2833 Index Only Scan using _hyper_1_1_chunk_hyper_time_idx on _hyper_1_1_chunk 2834 Index Cond: ("time" = 0) 2835(2 rows) 2836 2837:PREFIX SELECT first(value, time) FROM hyper h WHERE _timescaledb_internal.chunks_in(h, ARRAY[1]); 2838 QUERY PLAN 2839----------------------------------------------------------------------------------------------- 2840 Result 2841 InitPlan 1 (returns $0) 2842 -> Limit 2843 -> Index Scan Backward using _hyper_1_1_chunk_hyper_time_idx on _hyper_1_1_chunk h 2844 Index Cond: ("time" IS NOT NULL) 2845(5 rows) 2846 2847\set ON_ERROR_STOP 0 2848SELECT * FROM hyper WHERE _timescaledb_internal.chunks_in(hyper, ARRAY[1,2]) AND _timescaledb_internal.chunks_in(hyper, ARRAY[2,3]); 2849psql:include/plan_expand_hypertable_chunks_in_query.sql:26: ERROR: illegal invocation of chunks_in function 2850SELECT * FROM hyper WHERE _timescaledb_internal.chunks_in(2, ARRAY[1]); 2851psql:include/plan_expand_hypertable_chunks_in_query.sql:27: ERROR: function _timescaledb_internal.chunks_in(integer, integer[]) does not exist at character 27 2852SELECT * FROM hyper WHERE time < 10 OR _timescaledb_internal.chunks_in(hyper, ARRAY[1,2]); 2853psql:include/plan_expand_hypertable_chunks_in_query.sql:28: ERROR: illegal invocation of chunks_in function 2854SELECT _timescaledb_internal.chunks_in(hyper, ARRAY[1,2]) FROM hyper; 2855psql:include/plan_expand_hypertable_chunks_in_query.sql:29: ERROR: illegal invocation of chunks_in function 2856-- non existing chunk id 2857SELECT * FROM hyper WHERE _timescaledb_internal.chunks_in(hyper, ARRAY[123456789]); 2858psql:include/plan_expand_hypertable_chunks_in_query.sql:31: ERROR: chunk id 123456789 not found 2859-- chunk that belongs to another hypertable 2860SELECT * FROM hyper WHERE _timescaledb_internal.chunks_in(hyper, ARRAY[104]); 2861psql:include/plan_expand_hypertable_chunks_in_query.sql:33: ERROR: chunk id 104 does not belong to hypertable "hyper" 2862-- passing wrong row ref 2863SELECT * FROM hyper WHERE _timescaledb_internal.chunks_in(ROW(1,2), ARRAY[104]); 2864psql:include/plan_expand_hypertable_chunks_in_query.sql:35: ERROR: first parameter for chunks_in function needs to be record 2865-- passing func as chunk id 2866SELECT * FROM hyper h WHERE _timescaledb_internal.chunks_in(h, array_append(ARRAY[1],current_setting('server_version_num')::int)); 2867psql:include/plan_expand_hypertable_chunks_in_query.sql:37: ERROR: second argument to chunk_in should contain only integer consts 2868-- NULL chunk IDs not allowed in chunk array 2869SELECT * FROM hyper h WHERE _timescaledb_internal.chunks_in(h, ARRAY[NULL::int]); 2870psql:include/plan_expand_hypertable_chunks_in_query.sql:39: ERROR: chunk id can't be NULL 2871\set ON_ERROR_STOP 1 2872-- chunks_in is STRICT function and for NULL arguments a null result is returned 2873SELECT * FROM hyper h WHERE _timescaledb_internal.chunks_in(h, NULL); 2874 value | time 2875-------+------ 2876(0 rows) 2877 2878\set ECHO errors 2879RESET timescaledb.enable_optimizations; 2880CREATE TABLE t(time timestamptz NOT NULL); 2881SELECT table_name FROM create_hypertable('t','time'); 2882 table_name 2883------------ 2884 t 2885(1 row) 2886 2887INSERT INTO t VALUES ('2000-01-01'), ('2010-01-01'), ('2020-01-01'); 2888EXPLAIN (costs off) SELECT * FROM t t1 INNER JOIN t t2 ON t1.time = t2.time WHERE t1.time < timestamptz '2010-01-01'; 2889 QUERY PLAN 2890------------------------------------------------------------------------------------------------------------- 2891 Merge Join 2892 Merge Cond: (t1."time" = t2."time") 2893 -> Merge Append 2894 Sort Key: t1."time" 2895 -> Index Only Scan Backward using _hyper_15_182_chunk_t_time_idx on _hyper_15_182_chunk t1 2896 Index Cond: ("time" < 'Fri Jan 01 00:00:00 2010 PST'::timestamp with time zone) 2897 -> Index Only Scan Backward using _hyper_15_183_chunk_t_time_idx on _hyper_15_183_chunk t1_1 2898 Index Cond: ("time" < 'Fri Jan 01 00:00:00 2010 PST'::timestamp with time zone) 2899 -> Materialize 2900 -> Merge Append 2901 Sort Key: t2."time" 2902 -> Index Only Scan Backward using _hyper_15_182_chunk_t_time_idx on _hyper_15_182_chunk t2 2903 Index Cond: ("time" < 'Fri Jan 01 00:00:00 2010 PST'::timestamp with time zone) 2904 -> Index Only Scan Backward using _hyper_15_183_chunk_t_time_idx on _hyper_15_183_chunk t2_1 2905 Index Cond: ("time" < 'Fri Jan 01 00:00:00 2010 PST'::timestamp with time zone) 2906(15 rows) 2907 2908SET timescaledb.enable_qual_propagation TO false; 2909EXPLAIN (costs off) SELECT * FROM t t1 INNER JOIN t t2 ON t1.time = t2.time WHERE t1.time < timestamptz '2010-01-01'; 2910 QUERY PLAN 2911------------------------------------------------------------------------------------------------------------- 2912 Merge Join 2913 Merge Cond: (t1."time" = t2."time") 2914 -> Merge Append 2915 Sort Key: t1."time" 2916 -> Index Only Scan Backward using _hyper_15_182_chunk_t_time_idx on _hyper_15_182_chunk t1 2917 Index Cond: ("time" < 'Fri Jan 01 00:00:00 2010 PST'::timestamp with time zone) 2918 -> Index Only Scan Backward using _hyper_15_183_chunk_t_time_idx on _hyper_15_183_chunk t1_1 2919 Index Cond: ("time" < 'Fri Jan 01 00:00:00 2010 PST'::timestamp with time zone) 2920 -> Materialize 2921 -> Merge Append 2922 Sort Key: t2."time" 2923 -> Index Only Scan Backward using _hyper_15_182_chunk_t_time_idx on _hyper_15_182_chunk t2 2924 -> Index Only Scan Backward using _hyper_15_183_chunk_t_time_idx on _hyper_15_183_chunk t2_1 2925 -> Index Only Scan Backward using _hyper_15_184_chunk_t_time_idx on _hyper_15_184_chunk t2_2 2926(14 rows) 2927 2928RESET timescaledb.enable_qual_propagation; 2929CREATE TABLE test (a int, time timestamptz NOT NULL); 2930SELECT table_name FROM create_hypertable('public.test', 'time'); 2931 table_name 2932------------ 2933 test 2934(1 row) 2935 2936INSERT INTO test SELECT i, '2020-04-01'::date-10-i from generate_series(1,20) i; 2937CREATE OR REPLACE FUNCTION test_f(_ts timestamptz) 2938RETURNS SETOF test LANGUAGE SQL STABLE PARALLEL SAFE 2939AS $f$ 2940 SELECT DISTINCT ON (a) * FROM test WHERE time >= _ts ORDER BY a, time DESC 2941$f$; 2942EXPLAIN (costs off) SELECT * FROM test_f(now()); 2943 QUERY PLAN 2944------------------------------------------------- 2945 Unique 2946 -> Sort 2947 Sort Key: test.a, test."time" DESC 2948 -> Custom Scan (ChunkAppend) on test 2949 Chunks excluded during startup: 4 2950(5 rows) 2951 2952EXPLAIN (costs off) SELECT * FROM test_f(now()); 2953 QUERY PLAN 2954------------------------------------------------- 2955 Unique 2956 -> Sort 2957 Sort Key: test.a, test."time" DESC 2958 -> Custom Scan (ChunkAppend) on test 2959 Chunks excluded during startup: 4 2960(5 rows) 2961 2962CREATE TABLE t1 (a int, b int NOT NULL); 2963SELECT create_hypertable('t1', 'b', chunk_time_interval=>10); 2964 create_hypertable 2965------------------- 2966 (17,public,t1,t) 2967(1 row) 2968 2969CREATE TABLE t2 (a int, b int NOT NULL); 2970SELECT create_hypertable('t2', 'b', chunk_time_interval=>10); 2971 create_hypertable 2972------------------- 2973 (18,public,t2,t) 2974(1 row) 2975 2976CREATE OR REPLACE FUNCTION f_t1(_a int, _b int) 2977 RETURNS SETOF t1 2978 LANGUAGE SQL 2979 STABLE PARALLEL SAFE 2980AS $function$ 2981 SELECT DISTINCT ON (a) * FROM t1 WHERE a = _a and b = _b ORDER BY a, b DESC 2982$function$ 2983; 2984CREATE OR REPLACE FUNCTION f_t2(_a int, _b int) RETURNS SETOF t2 LANGUAGE sql STABLE PARALLEL SAFE 2985AS $function$ 2986 SELECT DISTINCT ON (j.a) j.* 2987 FROM 2988 f_t1(_a, _b) sc, 2989 t2 j 2990 WHERE 2991 j.b = _b AND 2992 j.a = _a 2993 ORDER BY j.a, j.b DESC 2994$function$ 2995; 2996CREATE OR REPLACE FUNCTION f_t1_2(_b int) RETURNS SETOF t1 LANGUAGE SQL STABLE PARALLEL SAFE 2997AS $function$ 2998 SELECT DISTINCT ON (j.a) jt.* FROM t1 j, f_t1(j.a, _b) jt 2999$function$; 3000EXPLAIN (costs off) SELECT * FROM f_t1_2(10); 3001 QUERY PLAN 3002--------------------------------------------------------------- 3003 Subquery Scan on f_t1_2 3004 -> Unique 3005 -> Sort 3006 Sort Key: j.a 3007 -> Nested Loop 3008 -> Seq Scan on t1 j 3009 -> Unique 3010 -> Index Scan using t1_b_idx on t1 3011 Index Cond: (b = 10) 3012 Filter: (a = j.a) 3013(10 rows) 3014 3015EXPLAIN (costs off) SELECT * FROM f_t1_2(10) sc, f_t2(sc.a, 10); 3016 QUERY PLAN 3017--------------------------------------------------------------- 3018 Nested Loop 3019 -> Unique 3020 -> Sort 3021 Sort Key: j.a 3022 -> Nested Loop 3023 -> Seq Scan on t1 j 3024 -> Unique 3025 -> Index Scan using t1_b_idx on t1 3026 Index Cond: (b = 10) 3027 Filter: (a = j.a) 3028 -> Unique 3029 -> Nested Loop 3030 -> Unique 3031 -> Index Scan using t1_b_idx on t1 t1_1 3032 Index Cond: (b = 10) 3033 Filter: (a = t1.a) 3034 -> Index Scan using t2_b_idx on t2 j_1 3035 Index Cond: (b = 10) 3036 Filter: (a = t1.a) 3037(19 rows) 3038 3039--TEST END-- 3040