1-- 2-- Test partitioning planner code 3-- 4create table lp (a char) partition by list (a); 5create table lp_default partition of lp default; 6create table lp_ef partition of lp for values in ('e', 'f'); 7create table lp_ad partition of lp for values in ('a', 'd'); 8create table lp_bc partition of lp for values in ('b', 'c'); 9create table lp_g partition of lp for values in ('g'); 10create table lp_null partition of lp for values in (null); 11explain (costs off) select * from lp; 12 QUERY PLAN 13------------------------------ 14 Append 15 -> Seq Scan on lp_ad 16 -> Seq Scan on lp_bc 17 -> Seq Scan on lp_ef 18 -> Seq Scan on lp_g 19 -> Seq Scan on lp_null 20 -> Seq Scan on lp_default 21(7 rows) 22 23explain (costs off) select * from lp where a > 'a' and a < 'd'; 24 QUERY PLAN 25----------------------------------------------------------- 26 Append 27 -> Seq Scan on lp_bc 28 Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar)) 29 -> Seq Scan on lp_default 30 Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar)) 31(5 rows) 32 33explain (costs off) select * from lp where a > 'a' and a <= 'd'; 34 QUERY PLAN 35------------------------------------------------------------ 36 Append 37 -> Seq Scan on lp_ad 38 Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar)) 39 -> Seq Scan on lp_bc 40 Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar)) 41 -> Seq Scan on lp_default 42 Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar)) 43(7 rows) 44 45explain (costs off) select * from lp where a = 'a'; 46 QUERY PLAN 47----------------------------- 48 Seq Scan on lp_ad 49 Filter: (a = 'a'::bpchar) 50(2 rows) 51 52explain (costs off) select * from lp where 'a' = a; /* commuted */ 53 QUERY PLAN 54----------------------------- 55 Seq Scan on lp_ad 56 Filter: ('a'::bpchar = a) 57(2 rows) 58 59explain (costs off) select * from lp where a is not null; 60 QUERY PLAN 61--------------------------------- 62 Append 63 -> Seq Scan on lp_ad 64 Filter: (a IS NOT NULL) 65 -> Seq Scan on lp_bc 66 Filter: (a IS NOT NULL) 67 -> Seq Scan on lp_ef 68 Filter: (a IS NOT NULL) 69 -> Seq Scan on lp_g 70 Filter: (a IS NOT NULL) 71 -> Seq Scan on lp_default 72 Filter: (a IS NOT NULL) 73(11 rows) 74 75explain (costs off) select * from lp where a is null; 76 QUERY PLAN 77----------------------- 78 Seq Scan on lp_null 79 Filter: (a IS NULL) 80(2 rows) 81 82explain (costs off) select * from lp where a = 'a' or a = 'c'; 83 QUERY PLAN 84---------------------------------------------------------- 85 Append 86 -> Seq Scan on lp_ad 87 Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar)) 88 -> Seq Scan on lp_bc 89 Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar)) 90(5 rows) 91 92explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c'); 93 QUERY PLAN 94-------------------------------------------------------------------------------- 95 Append 96 -> Seq Scan on lp_ad 97 Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar))) 98 -> Seq Scan on lp_bc 99 Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar))) 100(5 rows) 101 102explain (costs off) select * from lp where a <> 'g'; 103 QUERY PLAN 104------------------------------------ 105 Append 106 -> Seq Scan on lp_ad 107 Filter: (a <> 'g'::bpchar) 108 -> Seq Scan on lp_bc 109 Filter: (a <> 'g'::bpchar) 110 -> Seq Scan on lp_ef 111 Filter: (a <> 'g'::bpchar) 112 -> Seq Scan on lp_default 113 Filter: (a <> 'g'::bpchar) 114(9 rows) 115 116explain (costs off) select * from lp where a <> 'a' and a <> 'd'; 117 QUERY PLAN 118------------------------------------------------------------- 119 Append 120 -> Seq Scan on lp_bc 121 Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) 122 -> Seq Scan on lp_ef 123 Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) 124 -> Seq Scan on lp_g 125 Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) 126 -> Seq Scan on lp_default 127 Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) 128(9 rows) 129 130explain (costs off) select * from lp where a not in ('a', 'd'); 131 QUERY PLAN 132------------------------------------------------ 133 Append 134 -> Seq Scan on lp_bc 135 Filter: (a <> ALL ('{a,d}'::bpchar[])) 136 -> Seq Scan on lp_ef 137 Filter: (a <> ALL ('{a,d}'::bpchar[])) 138 -> Seq Scan on lp_g 139 Filter: (a <> ALL ('{a,d}'::bpchar[])) 140 -> Seq Scan on lp_default 141 Filter: (a <> ALL ('{a,d}'::bpchar[])) 142(9 rows) 143 144-- collation matches the partitioning collation, pruning works 145create table coll_pruning (a text collate "C") partition by list (a); 146create table coll_pruning_a partition of coll_pruning for values in ('a'); 147create table coll_pruning_b partition of coll_pruning for values in ('b'); 148create table coll_pruning_def partition of coll_pruning default; 149explain (costs off) select * from coll_pruning where a collate "C" = 'a' collate "C"; 150 QUERY PLAN 151--------------------------------------- 152 Seq Scan on coll_pruning_a 153 Filter: (a = 'a'::text COLLATE "C") 154(2 rows) 155 156-- collation doesn't match the partitioning collation, no pruning occurs 157explain (costs off) select * from coll_pruning where a collate "POSIX" = 'a' collate "POSIX"; 158 QUERY PLAN 159--------------------------------------------------------- 160 Append 161 -> Seq Scan on coll_pruning_a 162 Filter: ((a)::text = 'a'::text COLLATE "POSIX") 163 -> Seq Scan on coll_pruning_b 164 Filter: ((a)::text = 'a'::text COLLATE "POSIX") 165 -> Seq Scan on coll_pruning_def 166 Filter: ((a)::text = 'a'::text COLLATE "POSIX") 167(7 rows) 168 169create table rlp (a int, b varchar) partition by range (a); 170create table rlp_default partition of rlp default partition by list (a); 171create table rlp_default_default partition of rlp_default default; 172create table rlp_default_10 partition of rlp_default for values in (10); 173create table rlp_default_30 partition of rlp_default for values in (30); 174create table rlp_default_null partition of rlp_default for values in (null); 175create table rlp1 partition of rlp for values from (minvalue) to (1); 176create table rlp2 partition of rlp for values from (1) to (10); 177create table rlp3 (b varchar, a int) partition by list (b varchar_ops); 178create table rlp3_default partition of rlp3 default; 179create table rlp3abcd partition of rlp3 for values in ('ab', 'cd'); 180create table rlp3efgh partition of rlp3 for values in ('ef', 'gh'); 181create table rlp3nullxy partition of rlp3 for values in (null, 'xy'); 182alter table rlp attach partition rlp3 for values from (15) to (20); 183create table rlp4 partition of rlp for values from (20) to (30) partition by range (a); 184create table rlp4_default partition of rlp4 default; 185create table rlp4_1 partition of rlp4 for values from (20) to (25); 186create table rlp4_2 partition of rlp4 for values from (25) to (29); 187create table rlp5 partition of rlp for values from (31) to (maxvalue) partition by range (a); 188create table rlp5_default partition of rlp5 default; 189create table rlp5_1 partition of rlp5 for values from (31) to (40); 190explain (costs off) select * from rlp where a < 1; 191 QUERY PLAN 192------------------- 193 Seq Scan on rlp1 194 Filter: (a < 1) 195(2 rows) 196 197explain (costs off) select * from rlp where 1 > a; /* commuted */ 198 QUERY PLAN 199------------------- 200 Seq Scan on rlp1 201 Filter: (1 > a) 202(2 rows) 203 204explain (costs off) select * from rlp where a <= 1; 205 QUERY PLAN 206-------------------------- 207 Append 208 -> Seq Scan on rlp1 209 Filter: (a <= 1) 210 -> Seq Scan on rlp2 211 Filter: (a <= 1) 212(5 rows) 213 214explain (costs off) select * from rlp where a = 1; 215 QUERY PLAN 216------------------- 217 Seq Scan on rlp2 218 Filter: (a = 1) 219(2 rows) 220 221explain (costs off) select * from rlp where a = 1::bigint; /* same as above */ 222 QUERY PLAN 223----------------------------- 224 Seq Scan on rlp2 225 Filter: (a = '1'::bigint) 226(2 rows) 227 228explain (costs off) select * from rlp where a = 1::numeric; /* no pruning */ 229 QUERY PLAN 230----------------------------------------------- 231 Append 232 -> Seq Scan on rlp1 233 Filter: ((a)::numeric = '1'::numeric) 234 -> Seq Scan on rlp2 235 Filter: ((a)::numeric = '1'::numeric) 236 -> Seq Scan on rlp3abcd 237 Filter: ((a)::numeric = '1'::numeric) 238 -> Seq Scan on rlp3efgh 239 Filter: ((a)::numeric = '1'::numeric) 240 -> Seq Scan on rlp3nullxy 241 Filter: ((a)::numeric = '1'::numeric) 242 -> Seq Scan on rlp3_default 243 Filter: ((a)::numeric = '1'::numeric) 244 -> Seq Scan on rlp4_1 245 Filter: ((a)::numeric = '1'::numeric) 246 -> Seq Scan on rlp4_2 247 Filter: ((a)::numeric = '1'::numeric) 248 -> Seq Scan on rlp4_default 249 Filter: ((a)::numeric = '1'::numeric) 250 -> Seq Scan on rlp5_1 251 Filter: ((a)::numeric = '1'::numeric) 252 -> Seq Scan on rlp5_default 253 Filter: ((a)::numeric = '1'::numeric) 254 -> Seq Scan on rlp_default_10 255 Filter: ((a)::numeric = '1'::numeric) 256 -> Seq Scan on rlp_default_30 257 Filter: ((a)::numeric = '1'::numeric) 258 -> Seq Scan on rlp_default_null 259 Filter: ((a)::numeric = '1'::numeric) 260 -> Seq Scan on rlp_default_default 261 Filter: ((a)::numeric = '1'::numeric) 262(31 rows) 263 264explain (costs off) select * from rlp where a <= 10; 265 QUERY PLAN 266--------------------------------------- 267 Append 268 -> Seq Scan on rlp1 269 Filter: (a <= 10) 270 -> Seq Scan on rlp2 271 Filter: (a <= 10) 272 -> Seq Scan on rlp_default_10 273 Filter: (a <= 10) 274 -> Seq Scan on rlp_default_default 275 Filter: (a <= 10) 276(9 rows) 277 278explain (costs off) select * from rlp where a > 10; 279 QUERY PLAN 280--------------------------------------- 281 Append 282 -> Seq Scan on rlp3abcd 283 Filter: (a > 10) 284 -> Seq Scan on rlp3efgh 285 Filter: (a > 10) 286 -> Seq Scan on rlp3nullxy 287 Filter: (a > 10) 288 -> Seq Scan on rlp3_default 289 Filter: (a > 10) 290 -> Seq Scan on rlp4_1 291 Filter: (a > 10) 292 -> Seq Scan on rlp4_2 293 Filter: (a > 10) 294 -> Seq Scan on rlp4_default 295 Filter: (a > 10) 296 -> Seq Scan on rlp5_1 297 Filter: (a > 10) 298 -> Seq Scan on rlp5_default 299 Filter: (a > 10) 300 -> Seq Scan on rlp_default_30 301 Filter: (a > 10) 302 -> Seq Scan on rlp_default_default 303 Filter: (a > 10) 304(23 rows) 305 306explain (costs off) select * from rlp where a < 15; 307 QUERY PLAN 308--------------------------------------- 309 Append 310 -> Seq Scan on rlp1 311 Filter: (a < 15) 312 -> Seq Scan on rlp2 313 Filter: (a < 15) 314 -> Seq Scan on rlp_default_10 315 Filter: (a < 15) 316 -> Seq Scan on rlp_default_default 317 Filter: (a < 15) 318(9 rows) 319 320explain (costs off) select * from rlp where a <= 15; 321 QUERY PLAN 322--------------------------------------- 323 Append 324 -> Seq Scan on rlp1 325 Filter: (a <= 15) 326 -> Seq Scan on rlp2 327 Filter: (a <= 15) 328 -> Seq Scan on rlp3abcd 329 Filter: (a <= 15) 330 -> Seq Scan on rlp3efgh 331 Filter: (a <= 15) 332 -> Seq Scan on rlp3nullxy 333 Filter: (a <= 15) 334 -> Seq Scan on rlp3_default 335 Filter: (a <= 15) 336 -> Seq Scan on rlp_default_10 337 Filter: (a <= 15) 338 -> Seq Scan on rlp_default_default 339 Filter: (a <= 15) 340(17 rows) 341 342explain (costs off) select * from rlp where a > 15 and b = 'ab'; 343 QUERY PLAN 344--------------------------------------------------------- 345 Append 346 -> Seq Scan on rlp3abcd 347 Filter: ((a > 15) AND ((b)::text = 'ab'::text)) 348 -> Seq Scan on rlp4_1 349 Filter: ((a > 15) AND ((b)::text = 'ab'::text)) 350 -> Seq Scan on rlp4_2 351 Filter: ((a > 15) AND ((b)::text = 'ab'::text)) 352 -> Seq Scan on rlp4_default 353 Filter: ((a > 15) AND ((b)::text = 'ab'::text)) 354 -> Seq Scan on rlp5_1 355 Filter: ((a > 15) AND ((b)::text = 'ab'::text)) 356 -> Seq Scan on rlp5_default 357 Filter: ((a > 15) AND ((b)::text = 'ab'::text)) 358 -> Seq Scan on rlp_default_30 359 Filter: ((a > 15) AND ((b)::text = 'ab'::text)) 360 -> Seq Scan on rlp_default_default 361 Filter: ((a > 15) AND ((b)::text = 'ab'::text)) 362(17 rows) 363 364explain (costs off) select * from rlp where a = 16; 365 QUERY PLAN 366-------------------------------- 367 Append 368 -> Seq Scan on rlp3abcd 369 Filter: (a = 16) 370 -> Seq Scan on rlp3efgh 371 Filter: (a = 16) 372 -> Seq Scan on rlp3nullxy 373 Filter: (a = 16) 374 -> Seq Scan on rlp3_default 375 Filter: (a = 16) 376(9 rows) 377 378explain (costs off) select * from rlp where a = 16 and b in ('not', 'in', 'here'); 379 QUERY PLAN 380---------------------------------------------------------------------- 381 Seq Scan on rlp3_default 382 Filter: ((a = 16) AND ((b)::text = ANY ('{not,in,here}'::text[]))) 383(2 rows) 384 385explain (costs off) select * from rlp where a = 16 and b < 'ab'; 386 QUERY PLAN 387--------------------------------------------------- 388 Seq Scan on rlp3_default 389 Filter: (((b)::text < 'ab'::text) AND (a = 16)) 390(2 rows) 391 392explain (costs off) select * from rlp where a = 16 and b <= 'ab'; 393 QUERY PLAN 394---------------------------------------------------------- 395 Append 396 -> Seq Scan on rlp3abcd 397 Filter: (((b)::text <= 'ab'::text) AND (a = 16)) 398 -> Seq Scan on rlp3_default 399 Filter: (((b)::text <= 'ab'::text) AND (a = 16)) 400(5 rows) 401 402explain (costs off) select * from rlp where a = 16 and b is null; 403 QUERY PLAN 404-------------------------------------- 405 Seq Scan on rlp3nullxy 406 Filter: ((b IS NULL) AND (a = 16)) 407(2 rows) 408 409explain (costs off) select * from rlp where a = 16 and b is not null; 410 QUERY PLAN 411------------------------------------------------ 412 Append 413 -> Seq Scan on rlp3abcd 414 Filter: ((b IS NOT NULL) AND (a = 16)) 415 -> Seq Scan on rlp3efgh 416 Filter: ((b IS NOT NULL) AND (a = 16)) 417 -> Seq Scan on rlp3nullxy 418 Filter: ((b IS NOT NULL) AND (a = 16)) 419 -> Seq Scan on rlp3_default 420 Filter: ((b IS NOT NULL) AND (a = 16)) 421(9 rows) 422 423explain (costs off) select * from rlp where a is null; 424 QUERY PLAN 425------------------------------ 426 Seq Scan on rlp_default_null 427 Filter: (a IS NULL) 428(2 rows) 429 430explain (costs off) select * from rlp where a is not null; 431 QUERY PLAN 432--------------------------------------- 433 Append 434 -> Seq Scan on rlp1 435 Filter: (a IS NOT NULL) 436 -> Seq Scan on rlp2 437 Filter: (a IS NOT NULL) 438 -> Seq Scan on rlp3abcd 439 Filter: (a IS NOT NULL) 440 -> Seq Scan on rlp3efgh 441 Filter: (a IS NOT NULL) 442 -> Seq Scan on rlp3nullxy 443 Filter: (a IS NOT NULL) 444 -> Seq Scan on rlp3_default 445 Filter: (a IS NOT NULL) 446 -> Seq Scan on rlp4_1 447 Filter: (a IS NOT NULL) 448 -> Seq Scan on rlp4_2 449 Filter: (a IS NOT NULL) 450 -> Seq Scan on rlp4_default 451 Filter: (a IS NOT NULL) 452 -> Seq Scan on rlp5_1 453 Filter: (a IS NOT NULL) 454 -> Seq Scan on rlp5_default 455 Filter: (a IS NOT NULL) 456 -> Seq Scan on rlp_default_10 457 Filter: (a IS NOT NULL) 458 -> Seq Scan on rlp_default_30 459 Filter: (a IS NOT NULL) 460 -> Seq Scan on rlp_default_default 461 Filter: (a IS NOT NULL) 462(29 rows) 463 464explain (costs off) select * from rlp where a > 30; 465 QUERY PLAN 466--------------------------------------- 467 Append 468 -> Seq Scan on rlp5_1 469 Filter: (a > 30) 470 -> Seq Scan on rlp5_default 471 Filter: (a > 30) 472 -> Seq Scan on rlp_default_default 473 Filter: (a > 30) 474(7 rows) 475 476explain (costs off) select * from rlp where a = 30; /* only default is scanned */ 477 QUERY PLAN 478---------------------------- 479 Seq Scan on rlp_default_30 480 Filter: (a = 30) 481(2 rows) 482 483explain (costs off) select * from rlp where a <= 31; 484 QUERY PLAN 485--------------------------------------- 486 Append 487 -> Seq Scan on rlp1 488 Filter: (a <= 31) 489 -> Seq Scan on rlp2 490 Filter: (a <= 31) 491 -> Seq Scan on rlp3abcd 492 Filter: (a <= 31) 493 -> Seq Scan on rlp3efgh 494 Filter: (a <= 31) 495 -> Seq Scan on rlp3nullxy 496 Filter: (a <= 31) 497 -> Seq Scan on rlp3_default 498 Filter: (a <= 31) 499 -> Seq Scan on rlp4_1 500 Filter: (a <= 31) 501 -> Seq Scan on rlp4_2 502 Filter: (a <= 31) 503 -> Seq Scan on rlp4_default 504 Filter: (a <= 31) 505 -> Seq Scan on rlp5_1 506 Filter: (a <= 31) 507 -> Seq Scan on rlp_default_10 508 Filter: (a <= 31) 509 -> Seq Scan on rlp_default_30 510 Filter: (a <= 31) 511 -> Seq Scan on rlp_default_default 512 Filter: (a <= 31) 513(27 rows) 514 515explain (costs off) select * from rlp where a = 1 or a = 7; 516 QUERY PLAN 517-------------------------------- 518 Seq Scan on rlp2 519 Filter: ((a = 1) OR (a = 7)) 520(2 rows) 521 522explain (costs off) select * from rlp where a = 1 or b = 'ab'; 523 QUERY PLAN 524------------------------------------------------------- 525 Append 526 -> Seq Scan on rlp1 527 Filter: ((a = 1) OR ((b)::text = 'ab'::text)) 528 -> Seq Scan on rlp2 529 Filter: ((a = 1) OR ((b)::text = 'ab'::text)) 530 -> Seq Scan on rlp3abcd 531 Filter: ((a = 1) OR ((b)::text = 'ab'::text)) 532 -> Seq Scan on rlp4_1 533 Filter: ((a = 1) OR ((b)::text = 'ab'::text)) 534 -> Seq Scan on rlp4_2 535 Filter: ((a = 1) OR ((b)::text = 'ab'::text)) 536 -> Seq Scan on rlp4_default 537 Filter: ((a = 1) OR ((b)::text = 'ab'::text)) 538 -> Seq Scan on rlp5_1 539 Filter: ((a = 1) OR ((b)::text = 'ab'::text)) 540 -> Seq Scan on rlp5_default 541 Filter: ((a = 1) OR ((b)::text = 'ab'::text)) 542 -> Seq Scan on rlp_default_10 543 Filter: ((a = 1) OR ((b)::text = 'ab'::text)) 544 -> Seq Scan on rlp_default_30 545 Filter: ((a = 1) OR ((b)::text = 'ab'::text)) 546 -> Seq Scan on rlp_default_null 547 Filter: ((a = 1) OR ((b)::text = 'ab'::text)) 548 -> Seq Scan on rlp_default_default 549 Filter: ((a = 1) OR ((b)::text = 'ab'::text)) 550(25 rows) 551 552explain (costs off) select * from rlp where a > 20 and a < 27; 553 QUERY PLAN 554----------------------------------------- 555 Append 556 -> Seq Scan on rlp4_1 557 Filter: ((a > 20) AND (a < 27)) 558 -> Seq Scan on rlp4_2 559 Filter: ((a > 20) AND (a < 27)) 560(5 rows) 561 562explain (costs off) select * from rlp where a = 29; 563 QUERY PLAN 564-------------------------- 565 Seq Scan on rlp4_default 566 Filter: (a = 29) 567(2 rows) 568 569explain (costs off) select * from rlp where a >= 29; 570 QUERY PLAN 571--------------------------------------- 572 Append 573 -> Seq Scan on rlp4_default 574 Filter: (a >= 29) 575 -> Seq Scan on rlp5_1 576 Filter: (a >= 29) 577 -> Seq Scan on rlp5_default 578 Filter: (a >= 29) 579 -> Seq Scan on rlp_default_30 580 Filter: (a >= 29) 581 -> Seq Scan on rlp_default_default 582 Filter: (a >= 29) 583(11 rows) 584 585explain (costs off) select * from rlp where a < 1 or (a > 20 and a < 25); 586 QUERY PLAN 587------------------------------------------------------ 588 Append 589 -> Seq Scan on rlp1 590 Filter: ((a < 1) OR ((a > 20) AND (a < 25))) 591 -> Seq Scan on rlp4_1 592 Filter: ((a < 1) OR ((a > 20) AND (a < 25))) 593(5 rows) 594 595-- redundant clauses are eliminated 596explain (costs off) select * from rlp where a > 1 and a = 10; /* only default */ 597 QUERY PLAN 598---------------------------------- 599 Seq Scan on rlp_default_10 600 Filter: ((a > 1) AND (a = 10)) 601(2 rows) 602 603explain (costs off) select * from rlp where a > 1 and a >=15; /* rlp3 onwards, including default */ 604 QUERY PLAN 605----------------------------------------- 606 Append 607 -> Seq Scan on rlp3abcd 608 Filter: ((a > 1) AND (a >= 15)) 609 -> Seq Scan on rlp3efgh 610 Filter: ((a > 1) AND (a >= 15)) 611 -> Seq Scan on rlp3nullxy 612 Filter: ((a > 1) AND (a >= 15)) 613 -> Seq Scan on rlp3_default 614 Filter: ((a > 1) AND (a >= 15)) 615 -> Seq Scan on rlp4_1 616 Filter: ((a > 1) AND (a >= 15)) 617 -> Seq Scan on rlp4_2 618 Filter: ((a > 1) AND (a >= 15)) 619 -> Seq Scan on rlp4_default 620 Filter: ((a > 1) AND (a >= 15)) 621 -> Seq Scan on rlp5_1 622 Filter: ((a > 1) AND (a >= 15)) 623 -> Seq Scan on rlp5_default 624 Filter: ((a > 1) AND (a >= 15)) 625 -> Seq Scan on rlp_default_30 626 Filter: ((a > 1) AND (a >= 15)) 627 -> Seq Scan on rlp_default_default 628 Filter: ((a > 1) AND (a >= 15)) 629(23 rows) 630 631explain (costs off) select * from rlp where a = 1 and a = 3; /* empty */ 632 QUERY PLAN 633-------------------------- 634 Result 635 One-Time Filter: false 636(2 rows) 637 638explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a = 15); 639 QUERY PLAN 640------------------------------------------------------------------- 641 Append 642 -> Seq Scan on rlp2 643 Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15))) 644 -> Seq Scan on rlp3abcd 645 Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15))) 646 -> Seq Scan on rlp3efgh 647 Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15))) 648 -> Seq Scan on rlp3nullxy 649 Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15))) 650 -> Seq Scan on rlp3_default 651 Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15))) 652(11 rows) 653 654-- multi-column keys 655create table mc3p (a int, b int, c int) partition by range (a, abs(b), c); 656create table mc3p_default partition of mc3p default; 657create table mc3p0 partition of mc3p for values from (minvalue, minvalue, minvalue) to (1, 1, 1); 658create table mc3p1 partition of mc3p for values from (1, 1, 1) to (10, 5, 10); 659create table mc3p2 partition of mc3p for values from (10, 5, 10) to (10, 10, 10); 660create table mc3p3 partition of mc3p for values from (10, 10, 10) to (10, 10, 20); 661create table mc3p4 partition of mc3p for values from (10, 10, 20) to (10, maxvalue, maxvalue); 662create table mc3p5 partition of mc3p for values from (11, 1, 1) to (20, 10, 10); 663create table mc3p6 partition of mc3p for values from (20, 10, 10) to (20, 20, 20); 664create table mc3p7 partition of mc3p for values from (20, 20, 20) to (maxvalue, maxvalue, maxvalue); 665explain (costs off) select * from mc3p where a = 1; 666 QUERY PLAN 667-------------------------------- 668 Append 669 -> Seq Scan on mc3p0 670 Filter: (a = 1) 671 -> Seq Scan on mc3p1 672 Filter: (a = 1) 673 -> Seq Scan on mc3p_default 674 Filter: (a = 1) 675(7 rows) 676 677explain (costs off) select * from mc3p where a = 1 and abs(b) < 1; 678 QUERY PLAN 679-------------------------------------------- 680 Append 681 -> Seq Scan on mc3p0 682 Filter: ((a = 1) AND (abs(b) < 1)) 683 -> Seq Scan on mc3p_default 684 Filter: ((a = 1) AND (abs(b) < 1)) 685(5 rows) 686 687explain (costs off) select * from mc3p where a = 1 and abs(b) = 1; 688 QUERY PLAN 689-------------------------------------------- 690 Append 691 -> Seq Scan on mc3p0 692 Filter: ((a = 1) AND (abs(b) = 1)) 693 -> Seq Scan on mc3p1 694 Filter: ((a = 1) AND (abs(b) = 1)) 695 -> Seq Scan on mc3p_default 696 Filter: ((a = 1) AND (abs(b) = 1)) 697(7 rows) 698 699explain (costs off) select * from mc3p where a = 1 and abs(b) = 1 and c < 8; 700 QUERY PLAN 701-------------------------------------------------------- 702 Append 703 -> Seq Scan on mc3p0 704 Filter: ((c < 8) AND (a = 1) AND (abs(b) = 1)) 705 -> Seq Scan on mc3p1 706 Filter: ((c < 8) AND (a = 1) AND (abs(b) = 1)) 707(5 rows) 708 709explain (costs off) select * from mc3p where a = 10 and abs(b) between 5 and 35; 710 QUERY PLAN 711----------------------------------------------------------------- 712 Append 713 -> Seq Scan on mc3p1 714 Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35)) 715 -> Seq Scan on mc3p2 716 Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35)) 717 -> Seq Scan on mc3p3 718 Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35)) 719 -> Seq Scan on mc3p4 720 Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35)) 721 -> Seq Scan on mc3p_default 722 Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35)) 723(11 rows) 724 725explain (costs off) select * from mc3p where a > 10; 726 QUERY PLAN 727-------------------------------- 728 Append 729 -> Seq Scan on mc3p5 730 Filter: (a > 10) 731 -> Seq Scan on mc3p6 732 Filter: (a > 10) 733 -> Seq Scan on mc3p7 734 Filter: (a > 10) 735 -> Seq Scan on mc3p_default 736 Filter: (a > 10) 737(9 rows) 738 739explain (costs off) select * from mc3p where a >= 10; 740 QUERY PLAN 741-------------------------------- 742 Append 743 -> Seq Scan on mc3p1 744 Filter: (a >= 10) 745 -> Seq Scan on mc3p2 746 Filter: (a >= 10) 747 -> Seq Scan on mc3p3 748 Filter: (a >= 10) 749 -> Seq Scan on mc3p4 750 Filter: (a >= 10) 751 -> Seq Scan on mc3p5 752 Filter: (a >= 10) 753 -> Seq Scan on mc3p6 754 Filter: (a >= 10) 755 -> Seq Scan on mc3p7 756 Filter: (a >= 10) 757 -> Seq Scan on mc3p_default 758 Filter: (a >= 10) 759(17 rows) 760 761explain (costs off) select * from mc3p where a < 10; 762 QUERY PLAN 763-------------------------------- 764 Append 765 -> Seq Scan on mc3p0 766 Filter: (a < 10) 767 -> Seq Scan on mc3p1 768 Filter: (a < 10) 769 -> Seq Scan on mc3p_default 770 Filter: (a < 10) 771(7 rows) 772 773explain (costs off) select * from mc3p where a <= 10 and abs(b) < 10; 774 QUERY PLAN 775----------------------------------------------- 776 Append 777 -> Seq Scan on mc3p0 778 Filter: ((a <= 10) AND (abs(b) < 10)) 779 -> Seq Scan on mc3p1 780 Filter: ((a <= 10) AND (abs(b) < 10)) 781 -> Seq Scan on mc3p2 782 Filter: ((a <= 10) AND (abs(b) < 10)) 783 -> Seq Scan on mc3p_default 784 Filter: ((a <= 10) AND (abs(b) < 10)) 785(9 rows) 786 787explain (costs off) select * from mc3p where a = 11 and abs(b) = 0; 788 QUERY PLAN 789--------------------------------------- 790 Seq Scan on mc3p_default 791 Filter: ((a = 11) AND (abs(b) = 0)) 792(2 rows) 793 794explain (costs off) select * from mc3p where a = 20 and abs(b) = 10 and c = 100; 795 QUERY PLAN 796------------------------------------------------------ 797 Seq Scan on mc3p6 798 Filter: ((a = 20) AND (c = 100) AND (abs(b) = 10)) 799(2 rows) 800 801explain (costs off) select * from mc3p where a > 20; 802 QUERY PLAN 803-------------------------------- 804 Append 805 -> Seq Scan on mc3p7 806 Filter: (a > 20) 807 -> Seq Scan on mc3p_default 808 Filter: (a > 20) 809(5 rows) 810 811explain (costs off) select * from mc3p where a >= 20; 812 QUERY PLAN 813-------------------------------- 814 Append 815 -> Seq Scan on mc3p5 816 Filter: (a >= 20) 817 -> Seq Scan on mc3p6 818 Filter: (a >= 20) 819 -> Seq Scan on mc3p7 820 Filter: (a >= 20) 821 -> Seq Scan on mc3p_default 822 Filter: (a >= 20) 823(9 rows) 824 825explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1 and c = 1) or (a = 10 and abs(b) = 5 and c = 10) or (a > 11 and a < 20); 826 QUERY PLAN 827--------------------------------------------------------------------------------------------------------------------------------- 828 Append 829 -> Seq Scan on mc3p1 830 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20))) 831 -> Seq Scan on mc3p2 832 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20))) 833 -> Seq Scan on mc3p5 834 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20))) 835 -> Seq Scan on mc3p_default 836 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20))) 837(9 rows) 838 839explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1 and c = 1) or (a = 10 and abs(b) = 5 and c = 10) or (a > 11 and a < 20) or a < 1; 840 QUERY PLAN 841-------------------------------------------------------------------------------------------------------------------------------------------- 842 Append 843 -> Seq Scan on mc3p0 844 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1)) 845 -> Seq Scan on mc3p1 846 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1)) 847 -> Seq Scan on mc3p2 848 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1)) 849 -> Seq Scan on mc3p5 850 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1)) 851 -> Seq Scan on mc3p_default 852 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1)) 853(11 rows) 854 855explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1 and c = 1) or (a = 10 and abs(b) = 5 and c = 10) or (a > 11 and a < 20) or a < 1 or a = 1; 856 QUERY PLAN 857------------------------------------------------------------------------------------------------------------------------------------------------------- 858 Append 859 -> Seq Scan on mc3p0 860 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1)) 861 -> Seq Scan on mc3p1 862 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1)) 863 -> Seq Scan on mc3p2 864 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1)) 865 -> Seq Scan on mc3p5 866 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1)) 867 -> Seq Scan on mc3p_default 868 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1)) 869(11 rows) 870 871explain (costs off) select * from mc3p where a = 1 or abs(b) = 1 or c = 1; 872 QUERY PLAN 873------------------------------------------------------ 874 Append 875 -> Seq Scan on mc3p0 876 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1)) 877 -> Seq Scan on mc3p1 878 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1)) 879 -> Seq Scan on mc3p2 880 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1)) 881 -> Seq Scan on mc3p3 882 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1)) 883 -> Seq Scan on mc3p4 884 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1)) 885 -> Seq Scan on mc3p5 886 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1)) 887 -> Seq Scan on mc3p6 888 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1)) 889 -> Seq Scan on mc3p7 890 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1)) 891 -> Seq Scan on mc3p_default 892 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1)) 893(19 rows) 894 895explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1) or (a = 10 and abs(b) = 10); 896 QUERY PLAN 897------------------------------------------------------------------------------ 898 Append 899 -> Seq Scan on mc3p0 900 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10))) 901 -> Seq Scan on mc3p1 902 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10))) 903 -> Seq Scan on mc3p2 904 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10))) 905 -> Seq Scan on mc3p3 906 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10))) 907 -> Seq Scan on mc3p4 908 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10))) 909 -> Seq Scan on mc3p_default 910 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10))) 911(13 rows) 912 913explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1) or (a = 10 and abs(b) = 9); 914 QUERY PLAN 915----------------------------------------------------------------------------- 916 Append 917 -> Seq Scan on mc3p0 918 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9))) 919 -> Seq Scan on mc3p1 920 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9))) 921 -> Seq Scan on mc3p2 922 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9))) 923 -> Seq Scan on mc3p_default 924 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9))) 925(9 rows) 926 927-- a simpler multi-column keys case 928create table mc2p (a int, b int) partition by range (a, b); 929create table mc2p_default partition of mc2p default; 930create table mc2p0 partition of mc2p for values from (minvalue, minvalue) to (1, minvalue); 931create table mc2p1 partition of mc2p for values from (1, minvalue) to (1, 1); 932create table mc2p2 partition of mc2p for values from (1, 1) to (2, minvalue); 933create table mc2p3 partition of mc2p for values from (2, minvalue) to (2, 1); 934create table mc2p4 partition of mc2p for values from (2, 1) to (2, maxvalue); 935create table mc2p5 partition of mc2p for values from (2, maxvalue) to (maxvalue, maxvalue); 936explain (costs off) select * from mc2p where a < 2; 937 QUERY PLAN 938-------------------------------- 939 Append 940 -> Seq Scan on mc2p0 941 Filter: (a < 2) 942 -> Seq Scan on mc2p1 943 Filter: (a < 2) 944 -> Seq Scan on mc2p2 945 Filter: (a < 2) 946 -> Seq Scan on mc2p_default 947 Filter: (a < 2) 948(9 rows) 949 950explain (costs off) select * from mc2p where a = 2 and b < 1; 951 QUERY PLAN 952--------------------------------- 953 Seq Scan on mc2p3 954 Filter: ((b < 1) AND (a = 2)) 955(2 rows) 956 957explain (costs off) select * from mc2p where a > 1; 958 QUERY PLAN 959-------------------------------- 960 Append 961 -> Seq Scan on mc2p2 962 Filter: (a > 1) 963 -> Seq Scan on mc2p3 964 Filter: (a > 1) 965 -> Seq Scan on mc2p4 966 Filter: (a > 1) 967 -> Seq Scan on mc2p5 968 Filter: (a > 1) 969 -> Seq Scan on mc2p_default 970 Filter: (a > 1) 971(11 rows) 972 973explain (costs off) select * from mc2p where a = 1 and b > 1; 974 QUERY PLAN 975--------------------------------- 976 Seq Scan on mc2p2 977 Filter: ((b > 1) AND (a = 1)) 978(2 rows) 979 980-- all partitions but the default one should be pruned 981explain (costs off) select * from mc2p where a = 1 and b is null; 982 QUERY PLAN 983------------------------------------- 984 Seq Scan on mc2p_default 985 Filter: ((b IS NULL) AND (a = 1)) 986(2 rows) 987 988explain (costs off) select * from mc2p where a is null and b is null; 989 QUERY PLAN 990----------------------------------------- 991 Seq Scan on mc2p_default 992 Filter: ((a IS NULL) AND (b IS NULL)) 993(2 rows) 994 995explain (costs off) select * from mc2p where a is null and b = 1; 996 QUERY PLAN 997------------------------------------- 998 Seq Scan on mc2p_default 999 Filter: ((a IS NULL) AND (b = 1)) 1000(2 rows) 1001 1002explain (costs off) select * from mc2p where a is null; 1003 QUERY PLAN 1004-------------------------- 1005 Seq Scan on mc2p_default 1006 Filter: (a IS NULL) 1007(2 rows) 1008 1009explain (costs off) select * from mc2p where b is null; 1010 QUERY PLAN 1011-------------------------- 1012 Seq Scan on mc2p_default 1013 Filter: (b IS NULL) 1014(2 rows) 1015 1016-- boolean partitioning 1017create table boolpart (a bool) partition by list (a); 1018create table boolpart_default partition of boolpart default; 1019create table boolpart_t partition of boolpart for values in ('true'); 1020create table boolpart_f partition of boolpart for values in ('false'); 1021explain (costs off) select * from boolpart where a in (true, false); 1022 QUERY PLAN 1023------------------------------------------------ 1024 Append 1025 -> Seq Scan on boolpart_f 1026 Filter: (a = ANY ('{t,f}'::boolean[])) 1027 -> Seq Scan on boolpart_t 1028 Filter: (a = ANY ('{t,f}'::boolean[])) 1029(5 rows) 1030 1031explain (costs off) select * from boolpart where a = false; 1032 QUERY PLAN 1033------------------------ 1034 Seq Scan on boolpart_f 1035 Filter: (NOT a) 1036(2 rows) 1037 1038explain (costs off) select * from boolpart where not a = false; 1039 QUERY PLAN 1040------------------------ 1041 Seq Scan on boolpart_t 1042 Filter: a 1043(2 rows) 1044 1045explain (costs off) select * from boolpart where a is true or a is not true; 1046 QUERY PLAN 1047-------------------------------------------------- 1048 Append 1049 -> Seq Scan on boolpart_f 1050 Filter: ((a IS TRUE) OR (a IS NOT TRUE)) 1051 -> Seq Scan on boolpart_t 1052 Filter: ((a IS TRUE) OR (a IS NOT TRUE)) 1053(5 rows) 1054 1055explain (costs off) select * from boolpart where a is not true; 1056 QUERY PLAN 1057--------------------------- 1058 Seq Scan on boolpart_f 1059 Filter: (a IS NOT TRUE) 1060(2 rows) 1061 1062explain (costs off) select * from boolpart where a is not true and a is not false; 1063 QUERY PLAN 1064-------------------------- 1065 Result 1066 One-Time Filter: false 1067(2 rows) 1068 1069explain (costs off) select * from boolpart where a is unknown; 1070 QUERY PLAN 1071------------------------------------ 1072 Append 1073 -> Seq Scan on boolpart_f 1074 Filter: (a IS UNKNOWN) 1075 -> Seq Scan on boolpart_t 1076 Filter: (a IS UNKNOWN) 1077 -> Seq Scan on boolpart_default 1078 Filter: (a IS UNKNOWN) 1079(7 rows) 1080 1081explain (costs off) select * from boolpart where a is not unknown; 1082 QUERY PLAN 1083------------------------------------ 1084 Append 1085 -> Seq Scan on boolpart_f 1086 Filter: (a IS NOT UNKNOWN) 1087 -> Seq Scan on boolpart_t 1088 Filter: (a IS NOT UNKNOWN) 1089 -> Seq Scan on boolpart_default 1090 Filter: (a IS NOT UNKNOWN) 1091(7 rows) 1092 1093create table boolrangep (a bool, b bool, c int) partition by range (a,b,c); 1094create table boolrangep_tf partition of boolrangep for values from ('true', 'false', 0) to ('true', 'false', 100); 1095create table boolrangep_ft partition of boolrangep for values from ('false', 'true', 0) to ('false', 'true', 100); 1096create table boolrangep_ff1 partition of boolrangep for values from ('false', 'false', 0) to ('false', 'false', 50); 1097create table boolrangep_ff2 partition of boolrangep for values from ('false', 'false', 50) to ('false', 'false', 100); 1098-- try a more complex case that's been known to trip up pruning in the past 1099explain (costs off) select * from boolrangep where not a and not b and c = 25; 1100 QUERY PLAN 1101---------------------------------------------- 1102 Seq Scan on boolrangep_ff1 1103 Filter: ((NOT a) AND (NOT b) AND (c = 25)) 1104(2 rows) 1105 1106-- test scalar-to-array operators 1107create table coercepart (a varchar) partition by list (a); 1108create table coercepart_ab partition of coercepart for values in ('ab'); 1109create table coercepart_bc partition of coercepart for values in ('bc'); 1110create table coercepart_cd partition of coercepart for values in ('cd'); 1111explain (costs off) select * from coercepart where a in ('ab', to_char(125, '999')); 1112 QUERY PLAN 1113------------------------------------------------------------------------------------------------------------------------------ 1114 Append 1115 -> Seq Scan on coercepart_ab 1116 Filter: ((a)::text = ANY ((ARRAY['ab'::character varying, (to_char(125, '999'::text))::character varying])::text[])) 1117 -> Seq Scan on coercepart_bc 1118 Filter: ((a)::text = ANY ((ARRAY['ab'::character varying, (to_char(125, '999'::text))::character varying])::text[])) 1119 -> Seq Scan on coercepart_cd 1120 Filter: ((a)::text = ANY ((ARRAY['ab'::character varying, (to_char(125, '999'::text))::character varying])::text[])) 1121(7 rows) 1122 1123explain (costs off) select * from coercepart where a ~ any ('{ab}'); 1124 QUERY PLAN 1125---------------------------------------------------- 1126 Append 1127 -> Seq Scan on coercepart_ab 1128 Filter: ((a)::text ~ ANY ('{ab}'::text[])) 1129 -> Seq Scan on coercepart_bc 1130 Filter: ((a)::text ~ ANY ('{ab}'::text[])) 1131 -> Seq Scan on coercepart_cd 1132 Filter: ((a)::text ~ ANY ('{ab}'::text[])) 1133(7 rows) 1134 1135explain (costs off) select * from coercepart where a !~ all ('{ab}'); 1136 QUERY PLAN 1137----------------------------------------------------- 1138 Append 1139 -> Seq Scan on coercepart_ab 1140 Filter: ((a)::text !~ ALL ('{ab}'::text[])) 1141 -> Seq Scan on coercepart_bc 1142 Filter: ((a)::text !~ ALL ('{ab}'::text[])) 1143 -> Seq Scan on coercepart_cd 1144 Filter: ((a)::text !~ ALL ('{ab}'::text[])) 1145(7 rows) 1146 1147explain (costs off) select * from coercepart where a ~ any ('{ab,bc}'); 1148 QUERY PLAN 1149------------------------------------------------------- 1150 Append 1151 -> Seq Scan on coercepart_ab 1152 Filter: ((a)::text ~ ANY ('{ab,bc}'::text[])) 1153 -> Seq Scan on coercepart_bc 1154 Filter: ((a)::text ~ ANY ('{ab,bc}'::text[])) 1155 -> Seq Scan on coercepart_cd 1156 Filter: ((a)::text ~ ANY ('{ab,bc}'::text[])) 1157(7 rows) 1158 1159explain (costs off) select * from coercepart where a !~ all ('{ab,bc}'); 1160 QUERY PLAN 1161-------------------------------------------------------- 1162 Append 1163 -> Seq Scan on coercepart_ab 1164 Filter: ((a)::text !~ ALL ('{ab,bc}'::text[])) 1165 -> Seq Scan on coercepart_bc 1166 Filter: ((a)::text !~ ALL ('{ab,bc}'::text[])) 1167 -> Seq Scan on coercepart_cd 1168 Filter: ((a)::text !~ ALL ('{ab,bc}'::text[])) 1169(7 rows) 1170 1171explain (costs off) select * from coercepart where a = any ('{ab,bc}'); 1172 QUERY PLAN 1173------------------------------------------------------- 1174 Append 1175 -> Seq Scan on coercepart_ab 1176 Filter: ((a)::text = ANY ('{ab,bc}'::text[])) 1177 -> Seq Scan on coercepart_bc 1178 Filter: ((a)::text = ANY ('{ab,bc}'::text[])) 1179(5 rows) 1180 1181explain (costs off) select * from coercepart where a = any ('{ab,null}'); 1182 QUERY PLAN 1183--------------------------------------------------- 1184 Seq Scan on coercepart_ab 1185 Filter: ((a)::text = ANY ('{ab,NULL}'::text[])) 1186(2 rows) 1187 1188explain (costs off) select * from coercepart where a = any (null::text[]); 1189 QUERY PLAN 1190-------------------------- 1191 Result 1192 One-Time Filter: false 1193(2 rows) 1194 1195explain (costs off) select * from coercepart where a = all ('{ab}'); 1196 QUERY PLAN 1197---------------------------------------------- 1198 Seq Scan on coercepart_ab 1199 Filter: ((a)::text = ALL ('{ab}'::text[])) 1200(2 rows) 1201 1202explain (costs off) select * from coercepart where a = all ('{ab,bc}'); 1203 QUERY PLAN 1204-------------------------- 1205 Result 1206 One-Time Filter: false 1207(2 rows) 1208 1209explain (costs off) select * from coercepart where a = all ('{ab,null}'); 1210 QUERY PLAN 1211-------------------------- 1212 Result 1213 One-Time Filter: false 1214(2 rows) 1215 1216explain (costs off) select * from coercepart where a = all (null::text[]); 1217 QUERY PLAN 1218-------------------------- 1219 Result 1220 One-Time Filter: false 1221(2 rows) 1222 1223drop table coercepart; 1224CREATE TABLE part (a INT, b INT) PARTITION BY LIST (a); 1225CREATE TABLE part_p1 PARTITION OF part FOR VALUES IN (-2,-1,0,1,2); 1226CREATE TABLE part_p2 PARTITION OF part DEFAULT PARTITION BY RANGE(a); 1227CREATE TABLE part_p2_p1 PARTITION OF part_p2 DEFAULT; 1228INSERT INTO part VALUES (-1,-1), (1,1), (2,NULL), (NULL,-2),(NULL,NULL); 1229EXPLAIN (COSTS OFF) SELECT tableoid::regclass as part, a, b FROM part WHERE a IS NULL ORDER BY 1, 2, 3; 1230 QUERY PLAN 1231--------------------------------------------------------------------------- 1232 Sort 1233 Sort Key: ((part_p2_p1.tableoid)::regclass), part_p2_p1.a, part_p2_p1.b 1234 -> Seq Scan on part_p2_p1 1235 Filter: (a IS NULL) 1236(4 rows) 1237 1238-- 1239-- some more cases 1240-- 1241-- 1242-- pruning for partitioned table appearing inside a sub-query 1243-- 1244-- pruning won't work for mc3p, because some keys are Params 1245explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 where t2.a = t1.b and abs(t2.b) = 1 and t2.c = 1) s where t1.a = 1; 1246 QUERY PLAN 1247----------------------------------------------------------------------- 1248 Nested Loop 1249 -> Append 1250 -> Seq Scan on mc2p1 t1 1251 Filter: (a = 1) 1252 -> Seq Scan on mc2p2 t1_1 1253 Filter: (a = 1) 1254 -> Seq Scan on mc2p_default t1_2 1255 Filter: (a = 1) 1256 -> Aggregate 1257 -> Append 1258 -> Seq Scan on mc3p0 t2 1259 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) 1260 -> Seq Scan on mc3p1 t2_1 1261 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) 1262 -> Seq Scan on mc3p2 t2_2 1263 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) 1264 -> Seq Scan on mc3p3 t2_3 1265 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) 1266 -> Seq Scan on mc3p4 t2_4 1267 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) 1268 -> Seq Scan on mc3p5 t2_5 1269 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) 1270 -> Seq Scan on mc3p6 t2_6 1271 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) 1272 -> Seq Scan on mc3p7 t2_7 1273 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) 1274 -> Seq Scan on mc3p_default t2_8 1275 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) 1276(28 rows) 1277 1278-- pruning should work fine, because values for a prefix of keys (a, b) are 1279-- available 1280explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 where t2.c = t1.b and abs(t2.b) = 1 and t2.a = 1) s where t1.a = 1; 1281 QUERY PLAN 1282----------------------------------------------------------------------- 1283 Nested Loop 1284 -> Append 1285 -> Seq Scan on mc2p1 t1 1286 Filter: (a = 1) 1287 -> Seq Scan on mc2p2 t1_1 1288 Filter: (a = 1) 1289 -> Seq Scan on mc2p_default t1_2 1290 Filter: (a = 1) 1291 -> Aggregate 1292 -> Append 1293 -> Seq Scan on mc3p0 t2 1294 Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1)) 1295 -> Seq Scan on mc3p1 t2_1 1296 Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1)) 1297 -> Seq Scan on mc3p_default t2_2 1298 Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1)) 1299(16 rows) 1300 1301-- also here, because values for all keys are provided 1302explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 where t2.a = 1 and abs(t2.b) = 1 and t2.c = 1) s where t1.a = 1; 1303 QUERY PLAN 1304-------------------------------------------------------------- 1305 Nested Loop 1306 -> Aggregate 1307 -> Seq Scan on mc3p1 t2 1308 Filter: ((a = 1) AND (c = 1) AND (abs(b) = 1)) 1309 -> Append 1310 -> Seq Scan on mc2p1 t1 1311 Filter: (a = 1) 1312 -> Seq Scan on mc2p2 t1_1 1313 Filter: (a = 1) 1314 -> Seq Scan on mc2p_default t1_2 1315 Filter: (a = 1) 1316(11 rows) 1317 1318-- 1319-- pruning with clauses containing <> operator 1320-- 1321-- doesn't prune range partitions 1322create table rp (a int) partition by range (a); 1323create table rp0 partition of rp for values from (minvalue) to (1); 1324create table rp1 partition of rp for values from (1) to (2); 1325create table rp2 partition of rp for values from (2) to (maxvalue); 1326explain (costs off) select * from rp where a <> 1; 1327 QUERY PLAN 1328-------------------------- 1329 Append 1330 -> Seq Scan on rp0 1331 Filter: (a <> 1) 1332 -> Seq Scan on rp1 1333 Filter: (a <> 1) 1334 -> Seq Scan on rp2 1335 Filter: (a <> 1) 1336(7 rows) 1337 1338explain (costs off) select * from rp where a <> 1 and a <> 2; 1339 QUERY PLAN 1340----------------------------------------- 1341 Append 1342 -> Seq Scan on rp0 1343 Filter: ((a <> 1) AND (a <> 2)) 1344 -> Seq Scan on rp1 1345 Filter: ((a <> 1) AND (a <> 2)) 1346 -> Seq Scan on rp2 1347 Filter: ((a <> 1) AND (a <> 2)) 1348(7 rows) 1349 1350-- null partition should be eliminated due to strict <> clause. 1351explain (costs off) select * from lp where a <> 'a'; 1352 QUERY PLAN 1353------------------------------------ 1354 Append 1355 -> Seq Scan on lp_ad 1356 Filter: (a <> 'a'::bpchar) 1357 -> Seq Scan on lp_bc 1358 Filter: (a <> 'a'::bpchar) 1359 -> Seq Scan on lp_ef 1360 Filter: (a <> 'a'::bpchar) 1361 -> Seq Scan on lp_g 1362 Filter: (a <> 'a'::bpchar) 1363 -> Seq Scan on lp_default 1364 Filter: (a <> 'a'::bpchar) 1365(11 rows) 1366 1367-- ensure we detect contradictions in clauses; a can't be NULL and NOT NULL. 1368explain (costs off) select * from lp where a <> 'a' and a is null; 1369 QUERY PLAN 1370-------------------------- 1371 Result 1372 One-Time Filter: false 1373(2 rows) 1374 1375explain (costs off) select * from lp where (a <> 'a' and a <> 'd') or a is null; 1376 QUERY PLAN 1377------------------------------------------------------------------------------ 1378 Append 1379 -> Seq Scan on lp_bc 1380 Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL)) 1381 -> Seq Scan on lp_ef 1382 Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL)) 1383 -> Seq Scan on lp_g 1384 Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL)) 1385 -> Seq Scan on lp_null 1386 Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL)) 1387 -> Seq Scan on lp_default 1388 Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL)) 1389(11 rows) 1390 1391-- check that it also works for a partitioned table that's not root, 1392-- which in this case are partitions of rlp that are themselves 1393-- list-partitioned on b 1394explain (costs off) select * from rlp where a = 15 and b <> 'ab' and b <> 'cd' and b <> 'xy' and b is not null; 1395 QUERY PLAN 1396------------------------------------------------------------------------------------------------------------------------------------------ 1397 Append 1398 -> Seq Scan on rlp3efgh 1399 Filter: ((b IS NOT NULL) AND ((b)::text <> 'ab'::text) AND ((b)::text <> 'cd'::text) AND ((b)::text <> 'xy'::text) AND (a = 15)) 1400 -> Seq Scan on rlp3_default 1401 Filter: ((b IS NOT NULL) AND ((b)::text <> 'ab'::text) AND ((b)::text <> 'cd'::text) AND ((b)::text <> 'xy'::text) AND (a = 15)) 1402(5 rows) 1403 1404-- 1405-- different collations for different keys with same expression 1406-- 1407create table coll_pruning_multi (a text) partition by range (substr(a, 1) collate "POSIX", substr(a, 1) collate "C"); 1408create table coll_pruning_multi1 partition of coll_pruning_multi for values from ('a', 'a') to ('a', 'e'); 1409create table coll_pruning_multi2 partition of coll_pruning_multi for values from ('a', 'e') to ('a', 'z'); 1410create table coll_pruning_multi3 partition of coll_pruning_multi for values from ('b', 'a') to ('b', 'e'); 1411-- no pruning, because no value for the leading key 1412explain (costs off) select * from coll_pruning_multi where substr(a, 1) = 'e' collate "C"; 1413 QUERY PLAN 1414-------------------------------------------------------- 1415 Append 1416 -> Seq Scan on coll_pruning_multi1 1417 Filter: (substr(a, 1) = 'e'::text COLLATE "C") 1418 -> Seq Scan on coll_pruning_multi2 1419 Filter: (substr(a, 1) = 'e'::text COLLATE "C") 1420 -> Seq Scan on coll_pruning_multi3 1421 Filter: (substr(a, 1) = 'e'::text COLLATE "C") 1422(7 rows) 1423 1424-- pruning, with a value provided for the leading key 1425explain (costs off) select * from coll_pruning_multi where substr(a, 1) = 'a' collate "POSIX"; 1426 QUERY PLAN 1427------------------------------------------------------------ 1428 Append 1429 -> Seq Scan on coll_pruning_multi1 1430 Filter: (substr(a, 1) = 'a'::text COLLATE "POSIX") 1431 -> Seq Scan on coll_pruning_multi2 1432 Filter: (substr(a, 1) = 'a'::text COLLATE "POSIX") 1433(5 rows) 1434 1435-- pruning, with values provided for both keys 1436explain (costs off) select * from coll_pruning_multi where substr(a, 1) = 'e' collate "C" and substr(a, 1) = 'a' collate "POSIX"; 1437 QUERY PLAN 1438--------------------------------------------------------------------------------------------------- 1439 Seq Scan on coll_pruning_multi2 1440 Filter: ((substr(a, 1) = 'e'::text COLLATE "C") AND (substr(a, 1) = 'a'::text COLLATE "POSIX")) 1441(2 rows) 1442 1443-- 1444-- LIKE operators don't prune 1445-- 1446create table like_op_noprune (a text) partition by list (a); 1447create table like_op_noprune1 partition of like_op_noprune for values in ('ABC'); 1448create table like_op_noprune2 partition of like_op_noprune for values in ('BCD'); 1449explain (costs off) select * from like_op_noprune where a like '%BC'; 1450 QUERY PLAN 1451------------------------------------ 1452 Append 1453 -> Seq Scan on like_op_noprune1 1454 Filter: (a ~~ '%BC'::text) 1455 -> Seq Scan on like_op_noprune2 1456 Filter: (a ~~ '%BC'::text) 1457(5 rows) 1458 1459-- 1460-- tests wherein clause value requires a cross-type comparison function 1461-- 1462create table lparted_by_int2 (a smallint) partition by list (a); 1463create table lparted_by_int2_1 partition of lparted_by_int2 for values in (1); 1464create table lparted_by_int2_16384 partition of lparted_by_int2 for values in (16384); 1465explain (costs off) select * from lparted_by_int2 where a = 100000000000000; 1466 QUERY PLAN 1467-------------------------- 1468 Result 1469 One-Time Filter: false 1470(2 rows) 1471 1472create table rparted_by_int2 (a smallint) partition by range (a); 1473create table rparted_by_int2_1 partition of rparted_by_int2 for values from (1) to (10); 1474create table rparted_by_int2_16384 partition of rparted_by_int2 for values from (10) to (16384); 1475-- all partitions pruned 1476explain (costs off) select * from rparted_by_int2 where a > 100000000000000; 1477 QUERY PLAN 1478-------------------------- 1479 Result 1480 One-Time Filter: false 1481(2 rows) 1482 1483create table rparted_by_int2_maxvalue partition of rparted_by_int2 for values from (16384) to (maxvalue); 1484-- all partitions but rparted_by_int2_maxvalue pruned 1485explain (costs off) select * from rparted_by_int2 where a > 100000000000000; 1486 QUERY PLAN 1487------------------------------------------- 1488 Seq Scan on rparted_by_int2_maxvalue 1489 Filter: (a > '100000000000000'::bigint) 1490(2 rows) 1491 1492drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, boolrangep, rp, coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2; 1493-- 1494-- Test Partition pruning for HASH partitioning 1495-- 1496-- Use hand-rolled hash functions and operator classes to get predictable 1497-- result on different machines. See the definitions of 1498-- part_part_test_int4_ops and part_test_text_ops in insert.sql. 1499-- 1500create table hp (a int, b text, c int) 1501 partition by hash (a part_test_int4_ops, b part_test_text_ops); 1502create table hp0 partition of hp for values with (modulus 4, remainder 0); 1503create table hp3 partition of hp for values with (modulus 4, remainder 3); 1504create table hp1 partition of hp for values with (modulus 4, remainder 1); 1505create table hp2 partition of hp for values with (modulus 4, remainder 2); 1506insert into hp values (null, null, 0); 1507insert into hp values (1, null, 1); 1508insert into hp values (1, 'xxx', 2); 1509insert into hp values (null, 'xxx', 3); 1510insert into hp values (2, 'xxx', 4); 1511insert into hp values (1, 'abcde', 5); 1512select tableoid::regclass, * from hp order by c; 1513 tableoid | a | b | c 1514----------+---+-------+--- 1515 hp0 | | | 0 1516 hp1 | 1 | | 1 1517 hp0 | 1 | xxx | 2 1518 hp2 | | xxx | 3 1519 hp3 | 2 | xxx | 4 1520 hp2 | 1 | abcde | 5 1521(6 rows) 1522 1523-- partial keys won't prune, nor would non-equality conditions 1524explain (costs off) select * from hp where a = 1; 1525 QUERY PLAN 1526------------------------- 1527 Append 1528 -> Seq Scan on hp0 1529 Filter: (a = 1) 1530 -> Seq Scan on hp1 1531 Filter: (a = 1) 1532 -> Seq Scan on hp2 1533 Filter: (a = 1) 1534 -> Seq Scan on hp3 1535 Filter: (a = 1) 1536(9 rows) 1537 1538explain (costs off) select * from hp where b = 'xxx'; 1539 QUERY PLAN 1540----------------------------------- 1541 Append 1542 -> Seq Scan on hp0 1543 Filter: (b = 'xxx'::text) 1544 -> Seq Scan on hp1 1545 Filter: (b = 'xxx'::text) 1546 -> Seq Scan on hp2 1547 Filter: (b = 'xxx'::text) 1548 -> Seq Scan on hp3 1549 Filter: (b = 'xxx'::text) 1550(9 rows) 1551 1552explain (costs off) select * from hp where a is null; 1553 QUERY PLAN 1554----------------------------- 1555 Append 1556 -> Seq Scan on hp0 1557 Filter: (a IS NULL) 1558 -> Seq Scan on hp1 1559 Filter: (a IS NULL) 1560 -> Seq Scan on hp2 1561 Filter: (a IS NULL) 1562 -> Seq Scan on hp3 1563 Filter: (a IS NULL) 1564(9 rows) 1565 1566explain (costs off) select * from hp where b is null; 1567 QUERY PLAN 1568----------------------------- 1569 Append 1570 -> Seq Scan on hp0 1571 Filter: (b IS NULL) 1572 -> Seq Scan on hp1 1573 Filter: (b IS NULL) 1574 -> Seq Scan on hp2 1575 Filter: (b IS NULL) 1576 -> Seq Scan on hp3 1577 Filter: (b IS NULL) 1578(9 rows) 1579 1580explain (costs off) select * from hp where a < 1 and b = 'xxx'; 1581 QUERY PLAN 1582------------------------------------------------- 1583 Append 1584 -> Seq Scan on hp0 1585 Filter: ((a < 1) AND (b = 'xxx'::text)) 1586 -> Seq Scan on hp1 1587 Filter: ((a < 1) AND (b = 'xxx'::text)) 1588 -> Seq Scan on hp2 1589 Filter: ((a < 1) AND (b = 'xxx'::text)) 1590 -> Seq Scan on hp3 1591 Filter: ((a < 1) AND (b = 'xxx'::text)) 1592(9 rows) 1593 1594explain (costs off) select * from hp where a <> 1 and b = 'yyy'; 1595 QUERY PLAN 1596-------------------------------------------------- 1597 Append 1598 -> Seq Scan on hp0 1599 Filter: ((a <> 1) AND (b = 'yyy'::text)) 1600 -> Seq Scan on hp1 1601 Filter: ((a <> 1) AND (b = 'yyy'::text)) 1602 -> Seq Scan on hp2 1603 Filter: ((a <> 1) AND (b = 'yyy'::text)) 1604 -> Seq Scan on hp3 1605 Filter: ((a <> 1) AND (b = 'yyy'::text)) 1606(9 rows) 1607 1608explain (costs off) select * from hp where a <> 1 and b <> 'xxx'; 1609 QUERY PLAN 1610--------------------------------------------------- 1611 Append 1612 -> Seq Scan on hp0 1613 Filter: ((a <> 1) AND (b <> 'xxx'::text)) 1614 -> Seq Scan on hp1 1615 Filter: ((a <> 1) AND (b <> 'xxx'::text)) 1616 -> Seq Scan on hp2 1617 Filter: ((a <> 1) AND (b <> 'xxx'::text)) 1618 -> Seq Scan on hp3 1619 Filter: ((a <> 1) AND (b <> 'xxx'::text)) 1620(9 rows) 1621 1622-- pruning should work if either a value or a IS NULL clause is provided for 1623-- each of the keys 1624explain (costs off) select * from hp where a is null and b is null; 1625 QUERY PLAN 1626----------------------------------------- 1627 Seq Scan on hp0 1628 Filter: ((a IS NULL) AND (b IS NULL)) 1629(2 rows) 1630 1631explain (costs off) select * from hp where a = 1 and b is null; 1632 QUERY PLAN 1633------------------------------------- 1634 Seq Scan on hp1 1635 Filter: ((b IS NULL) AND (a = 1)) 1636(2 rows) 1637 1638explain (costs off) select * from hp where a = 1 and b = 'xxx'; 1639 QUERY PLAN 1640------------------------------------------- 1641 Seq Scan on hp0 1642 Filter: ((a = 1) AND (b = 'xxx'::text)) 1643(2 rows) 1644 1645explain (costs off) select * from hp where a is null and b = 'xxx'; 1646 QUERY PLAN 1647----------------------------------------------- 1648 Seq Scan on hp2 1649 Filter: ((a IS NULL) AND (b = 'xxx'::text)) 1650(2 rows) 1651 1652explain (costs off) select * from hp where a = 2 and b = 'xxx'; 1653 QUERY PLAN 1654------------------------------------------- 1655 Seq Scan on hp3 1656 Filter: ((a = 2) AND (b = 'xxx'::text)) 1657(2 rows) 1658 1659explain (costs off) select * from hp where a = 1 and b = 'abcde'; 1660 QUERY PLAN 1661--------------------------------------------- 1662 Seq Scan on hp2 1663 Filter: ((a = 1) AND (b = 'abcde'::text)) 1664(2 rows) 1665 1666explain (costs off) select * from hp where (a = 1 and b = 'abcde') or (a = 2 and b = 'xxx') or (a is null and b is null); 1667 QUERY PLAN 1668------------------------------------------------------------------------------------------------------------------------- 1669 Append 1670 -> Seq Scan on hp0 1671 Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL))) 1672 -> Seq Scan on hp2 1673 Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL))) 1674 -> Seq Scan on hp3 1675 Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL))) 1676(7 rows) 1677 1678-- test pruning when not all the partitions exist 1679drop table hp1; 1680drop table hp3; 1681explain (costs off) select * from hp where a = 1 and b = 'abcde'; 1682 QUERY PLAN 1683--------------------------------------------- 1684 Seq Scan on hp2 1685 Filter: ((a = 1) AND (b = 'abcde'::text)) 1686(2 rows) 1687 1688explain (costs off) select * from hp where a = 1 and b = 'abcde' and 1689 (c = 2 or c = 3); 1690 QUERY PLAN 1691---------------------------------------------------------------------- 1692 Seq Scan on hp2 1693 Filter: ((a = 1) AND (b = 'abcde'::text) AND ((c = 2) OR (c = 3))) 1694(2 rows) 1695 1696drop table hp2; 1697explain (costs off) select * from hp where a = 1 and b = 'abcde' and 1698 (c = 2 or c = 3); 1699 QUERY PLAN 1700-------------------------- 1701 Result 1702 One-Time Filter: false 1703(2 rows) 1704 1705drop table hp; 1706-- 1707-- Test runtime partition pruning 1708-- 1709create table ab (a int not null, b int not null) partition by list (a); 1710create table ab_a2 partition of ab for values in(2) partition by list (b); 1711create table ab_a2_b1 partition of ab_a2 for values in (1); 1712create table ab_a2_b2 partition of ab_a2 for values in (2); 1713create table ab_a2_b3 partition of ab_a2 for values in (3); 1714create table ab_a1 partition of ab for values in(1) partition by list (b); 1715create table ab_a1_b1 partition of ab_a1 for values in (1); 1716create table ab_a1_b2 partition of ab_a1 for values in (2); 1717create table ab_a1_b3 partition of ab_a1 for values in (3); 1718create table ab_a3 partition of ab for values in(3) partition by list (b); 1719create table ab_a3_b1 partition of ab_a3 for values in (1); 1720create table ab_a3_b2 partition of ab_a3 for values in (2); 1721create table ab_a3_b3 partition of ab_a3 for values in (3); 1722-- Disallow index only scans as concurrent transactions may stop visibility 1723-- bits being set causing "Heap Fetches" to be unstable in the EXPLAIN ANALYZE 1724-- output. 1725set enable_indexonlyscan = off; 1726prepare ab_q1 (int, int, int) as 1727select * from ab where a between $1 and $2 and b <= $3; 1728-- Execute query 5 times to allow choose_custom_plan 1729-- to start considering a generic plan. 1730execute ab_q1 (1, 8, 3); 1731 a | b 1732---+--- 1733(0 rows) 1734 1735execute ab_q1 (1, 8, 3); 1736 a | b 1737---+--- 1738(0 rows) 1739 1740execute ab_q1 (1, 8, 3); 1741 a | b 1742---+--- 1743(0 rows) 1744 1745execute ab_q1 (1, 8, 3); 1746 a | b 1747---+--- 1748(0 rows) 1749 1750execute ab_q1 (1, 8, 3); 1751 a | b 1752---+--- 1753(0 rows) 1754 1755explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3); 1756 QUERY PLAN 1757--------------------------------------------------------- 1758 Append (actual rows=0 loops=1) 1759 Subplans Removed: 6 1760 -> Seq Scan on ab_a2_b1 (actual rows=0 loops=1) 1761 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) 1762 -> Seq Scan on ab_a2_b2 (actual rows=0 loops=1) 1763 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) 1764 -> Seq Scan on ab_a2_b3 (actual rows=0 loops=1) 1765 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) 1766(8 rows) 1767 1768explain (analyze, costs off, summary off, timing off) execute ab_q1 (1, 2, 3); 1769 QUERY PLAN 1770--------------------------------------------------------- 1771 Append (actual rows=0 loops=1) 1772 Subplans Removed: 3 1773 -> Seq Scan on ab_a1_b1 (actual rows=0 loops=1) 1774 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) 1775 -> Seq Scan on ab_a1_b2 (actual rows=0 loops=1) 1776 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) 1777 -> Seq Scan on ab_a1_b3 (actual rows=0 loops=1) 1778 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) 1779 -> Seq Scan on ab_a2_b1 (actual rows=0 loops=1) 1780 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) 1781 -> Seq Scan on ab_a2_b2 (actual rows=0 loops=1) 1782 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) 1783 -> Seq Scan on ab_a2_b3 (actual rows=0 loops=1) 1784 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) 1785(14 rows) 1786 1787deallocate ab_q1; 1788-- Runtime pruning after optimizer pruning 1789prepare ab_q1 (int, int) as 1790select a from ab where a between $1 and $2 and b < 3; 1791-- Execute query 5 times to allow choose_custom_plan 1792-- to start considering a generic plan. 1793execute ab_q1 (1, 8); 1794 a 1795--- 1796(0 rows) 1797 1798execute ab_q1 (1, 8); 1799 a 1800--- 1801(0 rows) 1802 1803execute ab_q1 (1, 8); 1804 a 1805--- 1806(0 rows) 1807 1808execute ab_q1 (1, 8); 1809 a 1810--- 1811(0 rows) 1812 1813execute ab_q1 (1, 8); 1814 a 1815--- 1816(0 rows) 1817 1818explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2); 1819 QUERY PLAN 1820------------------------------------------------------- 1821 Append (actual rows=0 loops=1) 1822 Subplans Removed: 4 1823 -> Seq Scan on ab_a2_b1 (actual rows=0 loops=1) 1824 Filter: ((a >= $1) AND (a <= $2) AND (b < 3)) 1825 -> Seq Scan on ab_a2_b2 (actual rows=0 loops=1) 1826 Filter: ((a >= $1) AND (a <= $2) AND (b < 3)) 1827(6 rows) 1828 1829explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4); 1830 QUERY PLAN 1831------------------------------------------------------- 1832 Append (actual rows=0 loops=1) 1833 Subplans Removed: 2 1834 -> Seq Scan on ab_a2_b1 (actual rows=0 loops=1) 1835 Filter: ((a >= $1) AND (a <= $2) AND (b < 3)) 1836 -> Seq Scan on ab_a2_b2 (actual rows=0 loops=1) 1837 Filter: ((a >= $1) AND (a <= $2) AND (b < 3)) 1838 -> Seq Scan on ab_a3_b1 (actual rows=0 loops=1) 1839 Filter: ((a >= $1) AND (a <= $2) AND (b < 3)) 1840 -> Seq Scan on ab_a3_b2 (actual rows=0 loops=1) 1841 Filter: ((a >= $1) AND (a <= $2) AND (b < 3)) 1842(10 rows) 1843 1844-- Ensure a mix of PARAM_EXTERN and PARAM_EXEC Params work together at 1845-- different levels of partitioning. 1846prepare ab_q2 (int, int) as 1847select a from ab where a between $1 and $2 and b < (select 3); 1848execute ab_q2 (1, 8); 1849 a 1850--- 1851(0 rows) 1852 1853execute ab_q2 (1, 8); 1854 a 1855--- 1856(0 rows) 1857 1858execute ab_q2 (1, 8); 1859 a 1860--- 1861(0 rows) 1862 1863execute ab_q2 (1, 8); 1864 a 1865--- 1866(0 rows) 1867 1868execute ab_q2 (1, 8); 1869 a 1870--- 1871(0 rows) 1872 1873explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2); 1874 QUERY PLAN 1875-------------------------------------------------------- 1876 Append (actual rows=0 loops=1) 1877 Subplans Removed: 6 1878 InitPlan 1 (returns $0) 1879 -> Result (actual rows=1 loops=1) 1880 -> Seq Scan on ab_a2_b1 (actual rows=0 loops=1) 1881 Filter: ((a >= $1) AND (a <= $2) AND (b < $0)) 1882 -> Seq Scan on ab_a2_b2 (actual rows=0 loops=1) 1883 Filter: ((a >= $1) AND (a <= $2) AND (b < $0)) 1884 -> Seq Scan on ab_a2_b3 (never executed) 1885 Filter: ((a >= $1) AND (a <= $2) AND (b < $0)) 1886(10 rows) 1887 1888-- As above, but swap the PARAM_EXEC Param to the first partition level 1889prepare ab_q3 (int, int) as 1890select a from ab where b between $1 and $2 and a < (select 3); 1891execute ab_q3 (1, 8); 1892 a 1893--- 1894(0 rows) 1895 1896execute ab_q3 (1, 8); 1897 a 1898--- 1899(0 rows) 1900 1901execute ab_q3 (1, 8); 1902 a 1903--- 1904(0 rows) 1905 1906execute ab_q3 (1, 8); 1907 a 1908--- 1909(0 rows) 1910 1911execute ab_q3 (1, 8); 1912 a 1913--- 1914(0 rows) 1915 1916explain (analyze, costs off, summary off, timing off) execute ab_q3 (2, 2); 1917 QUERY PLAN 1918-------------------------------------------------------- 1919 Append (actual rows=0 loops=1) 1920 Subplans Removed: 6 1921 InitPlan 1 (returns $0) 1922 -> Result (actual rows=1 loops=1) 1923 -> Seq Scan on ab_a1_b2 (actual rows=0 loops=1) 1924 Filter: ((b >= $1) AND (b <= $2) AND (a < $0)) 1925 -> Seq Scan on ab_a2_b2 (actual rows=0 loops=1) 1926 Filter: ((b >= $1) AND (b <= $2) AND (a < $0)) 1927 -> Seq Scan on ab_a3_b2 (never executed) 1928 Filter: ((b >= $1) AND (b <= $2) AND (a < $0)) 1929(10 rows) 1930 1931-- Test a backwards Append scan 1932create table list_part (a int) partition by list (a); 1933create table list_part1 partition of list_part for values in (1); 1934create table list_part2 partition of list_part for values in (2); 1935create table list_part3 partition of list_part for values in (3); 1936create table list_part4 partition of list_part for values in (4); 1937insert into list_part select generate_series(1,4); 1938begin; 1939-- Don't select an actual value out of the table as the order of the Append's 1940-- subnodes may not be stable. 1941declare cur SCROLL CURSOR for select 1 from list_part where a > (select 1) and a < (select 4); 1942-- move beyond the final row 1943move 3 from cur; 1944-- Ensure we get two rows. 1945fetch backward all from cur; 1946 ?column? 1947---------- 1948 1 1949 1 1950(2 rows) 1951 1952commit; 1953begin; 1954-- Test run-time pruning using stable functions 1955create function list_part_fn(int) returns int as $$ begin return $1; end;$$ language plpgsql stable; 1956-- Ensure pruning works using a stable function containing no Vars 1957explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1); 1958 QUERY PLAN 1959------------------------------------------------------ 1960 Append (actual rows=1 loops=1) 1961 Subplans Removed: 3 1962 -> Seq Scan on list_part1 (actual rows=1 loops=1) 1963 Filter: (a = list_part_fn(1)) 1964(4 rows) 1965 1966-- Ensure pruning does not take place when the function has a Var parameter 1967explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(a); 1968 QUERY PLAN 1969------------------------------------------------------ 1970 Append (actual rows=4 loops=1) 1971 -> Seq Scan on list_part1 (actual rows=1 loops=1) 1972 Filter: (a = list_part_fn(a)) 1973 -> Seq Scan on list_part2 (actual rows=1 loops=1) 1974 Filter: (a = list_part_fn(a)) 1975 -> Seq Scan on list_part3 (actual rows=1 loops=1) 1976 Filter: (a = list_part_fn(a)) 1977 -> Seq Scan on list_part4 (actual rows=1 loops=1) 1978 Filter: (a = list_part_fn(a)) 1979(9 rows) 1980 1981-- Ensure pruning does not take place when the expression contains a Var. 1982explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1) + a; 1983 QUERY PLAN 1984------------------------------------------------------ 1985 Append (actual rows=0 loops=1) 1986 -> Seq Scan on list_part1 (actual rows=0 loops=1) 1987 Filter: (a = (list_part_fn(1) + a)) 1988 Rows Removed by Filter: 1 1989 -> Seq Scan on list_part2 (actual rows=0 loops=1) 1990 Filter: (a = (list_part_fn(1) + a)) 1991 Rows Removed by Filter: 1 1992 -> Seq Scan on list_part3 (actual rows=0 loops=1) 1993 Filter: (a = (list_part_fn(1) + a)) 1994 Rows Removed by Filter: 1 1995 -> Seq Scan on list_part4 (actual rows=0 loops=1) 1996 Filter: (a = (list_part_fn(1) + a)) 1997 Rows Removed by Filter: 1 1998(13 rows) 1999 2000rollback; 2001drop table list_part; 2002-- Parallel append 2003-- Parallel queries won't necessarily get as many workers as the planner 2004-- asked for. This affects not only the "Workers Launched:" field of EXPLAIN 2005-- results, but also row counts and loop counts for parallel scans, Gathers, 2006-- and everything in between. This function filters out the values we can't 2007-- rely on to be stable. 2008-- This removes enough info that you might wonder why bother with EXPLAIN 2009-- ANALYZE at all. The answer is that we need to see '(never executed)' 2010-- notations because that's the only way to verify runtime pruning. 2011create function explain_parallel_append(text) returns setof text 2012language plpgsql as 2013$$ 2014declare 2015 ln text; 2016begin 2017 for ln in 2018 execute format('explain (analyze, costs off, summary off, timing off) %s', 2019 $1) 2020 loop 2021 ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); 2022 ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N'); 2023 ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N'); 2024 return next ln; 2025 end loop; 2026end; 2027$$; 2028prepare ab_q4 (int, int) as 2029select avg(a) from ab where a between $1 and $2 and b < 4; 2030-- Encourage use of parallel plans 2031set parallel_setup_cost = 0; 2032set parallel_tuple_cost = 0; 2033set min_parallel_table_scan_size = 0; 2034set max_parallel_workers_per_gather = 2; 2035-- Execute query 5 times to allow choose_custom_plan 2036-- to start considering a generic plan. 2037execute ab_q4 (1, 8); 2038 avg 2039----- 2040 2041(1 row) 2042 2043execute ab_q4 (1, 8); 2044 avg 2045----- 2046 2047(1 row) 2048 2049execute ab_q4 (1, 8); 2050 avg 2051----- 2052 2053(1 row) 2054 2055execute ab_q4 (1, 8); 2056 avg 2057----- 2058 2059(1 row) 2060 2061execute ab_q4 (1, 8); 2062 avg 2063----- 2064 2065(1 row) 2066 2067select explain_parallel_append('execute ab_q4 (2, 2)'); 2068 explain_parallel_append 2069------------------------------------------------------------------------------- 2070 Finalize Aggregate (actual rows=N loops=N) 2071 -> Gather (actual rows=N loops=N) 2072 Workers Planned: 2 2073 Workers Launched: N 2074 -> Partial Aggregate (actual rows=N loops=N) 2075 -> Parallel Append (actual rows=N loops=N) 2076 Subplans Removed: 6 2077 -> Parallel Seq Scan on ab_a2_b1 (actual rows=N loops=N) 2078 Filter: ((a >= $1) AND (a <= $2) AND (b < 4)) 2079 -> Parallel Seq Scan on ab_a2_b2 (actual rows=N loops=N) 2080 Filter: ((a >= $1) AND (a <= $2) AND (b < 4)) 2081 -> Parallel Seq Scan on ab_a2_b3 (actual rows=N loops=N) 2082 Filter: ((a >= $1) AND (a <= $2) AND (b < 4)) 2083(13 rows) 2084 2085-- Test run-time pruning with IN lists. 2086prepare ab_q5 (int, int, int) as 2087select avg(a) from ab where a in($1,$2,$3) and b < 4; 2088-- Execute query 5 times to allow choose_custom_plan 2089-- to start considering a generic plan. 2090execute ab_q5 (1, 2, 3); 2091 avg 2092----- 2093 2094(1 row) 2095 2096execute ab_q5 (1, 2, 3); 2097 avg 2098----- 2099 2100(1 row) 2101 2102execute ab_q5 (1, 2, 3); 2103 avg 2104----- 2105 2106(1 row) 2107 2108execute ab_q5 (1, 2, 3); 2109 avg 2110----- 2111 2112(1 row) 2113 2114execute ab_q5 (1, 2, 3); 2115 avg 2116----- 2117 2118(1 row) 2119 2120select explain_parallel_append('execute ab_q5 (1, 1, 1)'); 2121 explain_parallel_append 2122------------------------------------------------------------------------------- 2123 Finalize Aggregate (actual rows=N loops=N) 2124 -> Gather (actual rows=N loops=N) 2125 Workers Planned: 2 2126 Workers Launched: N 2127 -> Partial Aggregate (actual rows=N loops=N) 2128 -> Parallel Append (actual rows=N loops=N) 2129 Subplans Removed: 6 2130 -> Parallel Seq Scan on ab_a1_b1 (actual rows=N loops=N) 2131 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) 2132 -> Parallel Seq Scan on ab_a1_b2 (actual rows=N loops=N) 2133 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) 2134 -> Parallel Seq Scan on ab_a1_b3 (actual rows=N loops=N) 2135 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) 2136(13 rows) 2137 2138select explain_parallel_append('execute ab_q5 (2, 3, 3)'); 2139 explain_parallel_append 2140------------------------------------------------------------------------------- 2141 Finalize Aggregate (actual rows=N loops=N) 2142 -> Gather (actual rows=N loops=N) 2143 Workers Planned: 2 2144 Workers Launched: N 2145 -> Partial Aggregate (actual rows=N loops=N) 2146 -> Parallel Append (actual rows=N loops=N) 2147 Subplans Removed: 3 2148 -> Parallel Seq Scan on ab_a2_b1 (actual rows=N loops=N) 2149 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) 2150 -> Parallel Seq Scan on ab_a2_b2 (actual rows=N loops=N) 2151 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) 2152 -> Parallel Seq Scan on ab_a2_b3 (actual rows=N loops=N) 2153 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) 2154 -> Parallel Seq Scan on ab_a3_b1 (actual rows=N loops=N) 2155 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) 2156 -> Parallel Seq Scan on ab_a3_b2 (actual rows=N loops=N) 2157 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) 2158 -> Parallel Seq Scan on ab_a3_b3 (actual rows=N loops=N) 2159 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) 2160(19 rows) 2161 2162-- Try some params whose values do not belong to any partition. 2163-- We'll still get a single subplan in this case, but it should not be scanned. 2164select explain_parallel_append('execute ab_q5 (33, 44, 55)'); 2165 explain_parallel_append 2166------------------------------------------------------------------------------- 2167 Finalize Aggregate (actual rows=N loops=N) 2168 -> Gather (actual rows=N loops=N) 2169 Workers Planned: 2 2170 Workers Launched: N 2171 -> Partial Aggregate (actual rows=N loops=N) 2172 -> Parallel Append (actual rows=N loops=N) 2173 Subplans Removed: 8 2174 -> Parallel Seq Scan on ab_a1_b1 (never executed) 2175 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) 2176(9 rows) 2177 2178-- Test Parallel Append with PARAM_EXEC Params 2179select explain_parallel_append('select count(*) from ab where (a = (select 1) or a = (select 3)) and b = 2'); 2180 explain_parallel_append 2181------------------------------------------------------------------------- 2182 Aggregate (actual rows=N loops=N) 2183 InitPlan 1 (returns $0) 2184 -> Result (actual rows=N loops=N) 2185 InitPlan 2 (returns $1) 2186 -> Result (actual rows=N loops=N) 2187 -> Gather (actual rows=N loops=N) 2188 Workers Planned: 2 2189 Params Evaluated: $0, $1 2190 Workers Launched: N 2191 -> Parallel Append (actual rows=N loops=N) 2192 -> Parallel Seq Scan on ab_a1_b2 (actual rows=N loops=N) 2193 Filter: ((b = 2) AND ((a = $0) OR (a = $1))) 2194 -> Parallel Seq Scan on ab_a2_b2 (never executed) 2195 Filter: ((b = 2) AND ((a = $0) OR (a = $1))) 2196 -> Parallel Seq Scan on ab_a3_b2 (actual rows=N loops=N) 2197 Filter: ((b = 2) AND ((a = $0) OR (a = $1))) 2198(16 rows) 2199 2200-- Test pruning during parallel nested loop query 2201create table lprt_a (a int not null); 2202-- Insert some values we won't find in ab 2203insert into lprt_a select 0 from generate_series(1,100); 2204-- and insert some values that we should find. 2205insert into lprt_a values(1),(1); 2206analyze lprt_a; 2207create index ab_a2_b1_a_idx on ab_a2_b1 (a); 2208create index ab_a2_b2_a_idx on ab_a2_b2 (a); 2209create index ab_a2_b3_a_idx on ab_a2_b3 (a); 2210create index ab_a1_b1_a_idx on ab_a1_b1 (a); 2211create index ab_a1_b2_a_idx on ab_a1_b2 (a); 2212create index ab_a1_b3_a_idx on ab_a1_b3 (a); 2213create index ab_a3_b1_a_idx on ab_a3_b1 (a); 2214create index ab_a3_b2_a_idx on ab_a3_b2 (a); 2215create index ab_a3_b3_a_idx on ab_a3_b3 (a); 2216set enable_hashjoin = 0; 2217set enable_mergejoin = 0; 2218select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(0, 0, 1)'); 2219 explain_parallel_append 2220--------------------------------------------------------------------------------------------------- 2221 Finalize Aggregate (actual rows=N loops=N) 2222 -> Gather (actual rows=N loops=N) 2223 Workers Planned: 1 2224 Workers Launched: N 2225 -> Partial Aggregate (actual rows=N loops=N) 2226 -> Nested Loop (actual rows=N loops=N) 2227 -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) 2228 Filter: (a = ANY ('{0,0,1}'::integer[])) 2229 -> Append (actual rows=N loops=N) 2230 -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=N loops=N) 2231 Index Cond: (a = a.a) 2232 -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=N loops=N) 2233 Index Cond: (a = a.a) 2234 -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=N loops=N) 2235 Index Cond: (a = a.a) 2236 -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed) 2237 Index Cond: (a = a.a) 2238 -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed) 2239 Index Cond: (a = a.a) 2240 -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed) 2241 Index Cond: (a = a.a) 2242 -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 (never executed) 2243 Index Cond: (a = a.a) 2244 -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 (never executed) 2245 Index Cond: (a = a.a) 2246 -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 (never executed) 2247 Index Cond: (a = a.a) 2248(27 rows) 2249 2250-- Ensure the same partitions are pruned when we make the nested loop 2251-- parameter an Expr rather than a plain Param. 2252select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a + 0 where a.a in(0, 0, 1)'); 2253 explain_parallel_append 2254--------------------------------------------------------------------------------------------------- 2255 Finalize Aggregate (actual rows=N loops=N) 2256 -> Gather (actual rows=N loops=N) 2257 Workers Planned: 1 2258 Workers Launched: N 2259 -> Partial Aggregate (actual rows=N loops=N) 2260 -> Nested Loop (actual rows=N loops=N) 2261 -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) 2262 Filter: (a = ANY ('{0,0,1}'::integer[])) 2263 -> Append (actual rows=N loops=N) 2264 -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=N loops=N) 2265 Index Cond: (a = (a.a + 0)) 2266 -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=N loops=N) 2267 Index Cond: (a = (a.a + 0)) 2268 -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=N loops=N) 2269 Index Cond: (a = (a.a + 0)) 2270 -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed) 2271 Index Cond: (a = (a.a + 0)) 2272 -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed) 2273 Index Cond: (a = (a.a + 0)) 2274 -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed) 2275 Index Cond: (a = (a.a + 0)) 2276 -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 (never executed) 2277 Index Cond: (a = (a.a + 0)) 2278 -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 (never executed) 2279 Index Cond: (a = (a.a + 0)) 2280 -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 (never executed) 2281 Index Cond: (a = (a.a + 0)) 2282(27 rows) 2283 2284insert into lprt_a values(3),(3); 2285select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 3)'); 2286 explain_parallel_append 2287--------------------------------------------------------------------------------------------------- 2288 Finalize Aggregate (actual rows=N loops=N) 2289 -> Gather (actual rows=N loops=N) 2290 Workers Planned: 1 2291 Workers Launched: N 2292 -> Partial Aggregate (actual rows=N loops=N) 2293 -> Nested Loop (actual rows=N loops=N) 2294 -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) 2295 Filter: (a = ANY ('{1,0,3}'::integer[])) 2296 -> Append (actual rows=N loops=N) 2297 -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=N loops=N) 2298 Index Cond: (a = a.a) 2299 -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=N loops=N) 2300 Index Cond: (a = a.a) 2301 -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=N loops=N) 2302 Index Cond: (a = a.a) 2303 -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed) 2304 Index Cond: (a = a.a) 2305 -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed) 2306 Index Cond: (a = a.a) 2307 -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed) 2308 Index Cond: (a = a.a) 2309 -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 (actual rows=N loops=N) 2310 Index Cond: (a = a.a) 2311 -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 (actual rows=N loops=N) 2312 Index Cond: (a = a.a) 2313 -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 (actual rows=N loops=N) 2314 Index Cond: (a = a.a) 2315(27 rows) 2316 2317select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)'); 2318 explain_parallel_append 2319--------------------------------------------------------------------------------------------------- 2320 Finalize Aggregate (actual rows=N loops=N) 2321 -> Gather (actual rows=N loops=N) 2322 Workers Planned: 1 2323 Workers Launched: N 2324 -> Partial Aggregate (actual rows=N loops=N) 2325 -> Nested Loop (actual rows=N loops=N) 2326 -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) 2327 Filter: (a = ANY ('{1,0,0}'::integer[])) 2328 Rows Removed by Filter: N 2329 -> Append (actual rows=N loops=N) 2330 -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=N loops=N) 2331 Index Cond: (a = a.a) 2332 -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=N loops=N) 2333 Index Cond: (a = a.a) 2334 -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=N loops=N) 2335 Index Cond: (a = a.a) 2336 -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed) 2337 Index Cond: (a = a.a) 2338 -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed) 2339 Index Cond: (a = a.a) 2340 -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed) 2341 Index Cond: (a = a.a) 2342 -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 (never executed) 2343 Index Cond: (a = a.a) 2344 -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 (never executed) 2345 Index Cond: (a = a.a) 2346 -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 (never executed) 2347 Index Cond: (a = a.a) 2348(28 rows) 2349 2350delete from lprt_a where a = 1; 2351select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)'); 2352 explain_parallel_append 2353-------------------------------------------------------------------------------------------- 2354 Finalize Aggregate (actual rows=N loops=N) 2355 -> Gather (actual rows=N loops=N) 2356 Workers Planned: 1 2357 Workers Launched: N 2358 -> Partial Aggregate (actual rows=N loops=N) 2359 -> Nested Loop (actual rows=N loops=N) 2360 -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) 2361 Filter: (a = ANY ('{1,0,0}'::integer[])) 2362 Rows Removed by Filter: N 2363 -> Append (actual rows=N loops=N) 2364 -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (never executed) 2365 Index Cond: (a = a.a) 2366 -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (never executed) 2367 Index Cond: (a = a.a) 2368 -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (never executed) 2369 Index Cond: (a = a.a) 2370 -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed) 2371 Index Cond: (a = a.a) 2372 -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed) 2373 Index Cond: (a = a.a) 2374 -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed) 2375 Index Cond: (a = a.a) 2376 -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 (never executed) 2377 Index Cond: (a = a.a) 2378 -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 (never executed) 2379 Index Cond: (a = a.a) 2380 -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 (never executed) 2381 Index Cond: (a = a.a) 2382(28 rows) 2383 2384reset enable_hashjoin; 2385reset enable_mergejoin; 2386reset parallel_setup_cost; 2387reset parallel_tuple_cost; 2388reset min_parallel_table_scan_size; 2389reset max_parallel_workers_per_gather; 2390-- Test run-time partition pruning with an initplan 2391explain (analyze, costs off, summary off, timing off) 2392select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a); 2393 QUERY PLAN 2394------------------------------------------------------------------------- 2395 Append (actual rows=0 loops=1) 2396 InitPlan 1 (returns $0) 2397 -> Aggregate (actual rows=1 loops=1) 2398 -> Seq Scan on lprt_a (actual rows=102 loops=1) 2399 InitPlan 2 (returns $1) 2400 -> Aggregate (actual rows=1 loops=1) 2401 -> Seq Scan on lprt_a lprt_a_1 (actual rows=102 loops=1) 2402 -> Bitmap Heap Scan on ab_a1_b1 (never executed) 2403 Recheck Cond: (a = $0) 2404 Filter: (b = $1) 2405 -> Bitmap Index Scan on ab_a1_b1_a_idx (never executed) 2406 Index Cond: (a = $0) 2407 -> Bitmap Heap Scan on ab_a1_b2 (never executed) 2408 Recheck Cond: (a = $0) 2409 Filter: (b = $1) 2410 -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed) 2411 Index Cond: (a = $0) 2412 -> Bitmap Heap Scan on ab_a1_b3 (never executed) 2413 Recheck Cond: (a = $0) 2414 Filter: (b = $1) 2415 -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed) 2416 Index Cond: (a = $0) 2417 -> Bitmap Heap Scan on ab_a2_b1 (never executed) 2418 Recheck Cond: (a = $0) 2419 Filter: (b = $1) 2420 -> Bitmap Index Scan on ab_a2_b1_a_idx (never executed) 2421 Index Cond: (a = $0) 2422 -> Bitmap Heap Scan on ab_a2_b2 (never executed) 2423 Recheck Cond: (a = $0) 2424 Filter: (b = $1) 2425 -> Bitmap Index Scan on ab_a2_b2_a_idx (never executed) 2426 Index Cond: (a = $0) 2427 -> Bitmap Heap Scan on ab_a2_b3 (never executed) 2428 Recheck Cond: (a = $0) 2429 Filter: (b = $1) 2430 -> Bitmap Index Scan on ab_a2_b3_a_idx (never executed) 2431 Index Cond: (a = $0) 2432 -> Bitmap Heap Scan on ab_a3_b1 (never executed) 2433 Recheck Cond: (a = $0) 2434 Filter: (b = $1) 2435 -> Bitmap Index Scan on ab_a3_b1_a_idx (never executed) 2436 Index Cond: (a = $0) 2437 -> Bitmap Heap Scan on ab_a3_b2 (actual rows=0 loops=1) 2438 Recheck Cond: (a = $0) 2439 Filter: (b = $1) 2440 -> Bitmap Index Scan on ab_a3_b2_a_idx (actual rows=0 loops=1) 2441 Index Cond: (a = $0) 2442 -> Bitmap Heap Scan on ab_a3_b3 (never executed) 2443 Recheck Cond: (a = $0) 2444 Filter: (b = $1) 2445 -> Bitmap Index Scan on ab_a3_b3_a_idx (never executed) 2446 Index Cond: (a = $0) 2447(52 rows) 2448 2449-- Test run-time partition pruning with UNION ALL parents 2450explain (analyze, costs off, summary off, timing off) 2451select * from (select * from ab where a = 1 union all select * from ab) ab where b = (select 1); 2452 QUERY PLAN 2453------------------------------------------------------------------------------- 2454 Append (actual rows=0 loops=1) 2455 InitPlan 1 (returns $0) 2456 -> Result (actual rows=1 loops=1) 2457 -> Append (actual rows=0 loops=1) 2458 -> Bitmap Heap Scan on ab_a1_b1 ab_a1_b1_1 (actual rows=0 loops=1) 2459 Recheck Cond: (a = 1) 2460 Filter: (b = $0) 2461 -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1) 2462 Index Cond: (a = 1) 2463 -> Bitmap Heap Scan on ab_a1_b2 ab_a1_b2_1 (never executed) 2464 Recheck Cond: (a = 1) 2465 Filter: (b = $0) 2466 -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed) 2467 Index Cond: (a = 1) 2468 -> Bitmap Heap Scan on ab_a1_b3 ab_a1_b3_1 (never executed) 2469 Recheck Cond: (a = 1) 2470 Filter: (b = $0) 2471 -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed) 2472 Index Cond: (a = 1) 2473 -> Seq Scan on ab_a1_b1 (actual rows=0 loops=1) 2474 Filter: (b = $0) 2475 -> Seq Scan on ab_a1_b2 (never executed) 2476 Filter: (b = $0) 2477 -> Seq Scan on ab_a1_b3 (never executed) 2478 Filter: (b = $0) 2479 -> Seq Scan on ab_a2_b1 (actual rows=0 loops=1) 2480 Filter: (b = $0) 2481 -> Seq Scan on ab_a2_b2 (never executed) 2482 Filter: (b = $0) 2483 -> Seq Scan on ab_a2_b3 (never executed) 2484 Filter: (b = $0) 2485 -> Seq Scan on ab_a3_b1 (actual rows=0 loops=1) 2486 Filter: (b = $0) 2487 -> Seq Scan on ab_a3_b2 (never executed) 2488 Filter: (b = $0) 2489 -> Seq Scan on ab_a3_b3 (never executed) 2490 Filter: (b = $0) 2491(37 rows) 2492 2493-- A case containing a UNION ALL with a non-partitioned child. 2494explain (analyze, costs off, summary off, timing off) 2495select * from (select * from ab where a = 1 union all (values(10,5)) union all select * from ab) ab where b = (select 1); 2496 QUERY PLAN 2497------------------------------------------------------------------------------- 2498 Append (actual rows=0 loops=1) 2499 InitPlan 1 (returns $0) 2500 -> Result (actual rows=1 loops=1) 2501 -> Append (actual rows=0 loops=1) 2502 -> Bitmap Heap Scan on ab_a1_b1 ab_a1_b1_1 (actual rows=0 loops=1) 2503 Recheck Cond: (a = 1) 2504 Filter: (b = $0) 2505 -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1) 2506 Index Cond: (a = 1) 2507 -> Bitmap Heap Scan on ab_a1_b2 ab_a1_b2_1 (never executed) 2508 Recheck Cond: (a = 1) 2509 Filter: (b = $0) 2510 -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed) 2511 Index Cond: (a = 1) 2512 -> Bitmap Heap Scan on ab_a1_b3 ab_a1_b3_1 (never executed) 2513 Recheck Cond: (a = 1) 2514 Filter: (b = $0) 2515 -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed) 2516 Index Cond: (a = 1) 2517 -> Result (actual rows=0 loops=1) 2518 One-Time Filter: (5 = $0) 2519 -> Seq Scan on ab_a1_b1 (actual rows=0 loops=1) 2520 Filter: (b = $0) 2521 -> Seq Scan on ab_a1_b2 (never executed) 2522 Filter: (b = $0) 2523 -> Seq Scan on ab_a1_b3 (never executed) 2524 Filter: (b = $0) 2525 -> Seq Scan on ab_a2_b1 (actual rows=0 loops=1) 2526 Filter: (b = $0) 2527 -> Seq Scan on ab_a2_b2 (never executed) 2528 Filter: (b = $0) 2529 -> Seq Scan on ab_a2_b3 (never executed) 2530 Filter: (b = $0) 2531 -> Seq Scan on ab_a3_b1 (actual rows=0 loops=1) 2532 Filter: (b = $0) 2533 -> Seq Scan on ab_a3_b2 (never executed) 2534 Filter: (b = $0) 2535 -> Seq Scan on ab_a3_b3 (never executed) 2536 Filter: (b = $0) 2537(39 rows) 2538 2539-- Another UNION ALL test, but containing a mix of exec init and exec run-time pruning. 2540create table xy_1 (x int, y int); 2541insert into xy_1 values(100,-10); 2542set enable_bitmapscan = 0; 2543set enable_indexscan = 0; 2544set plan_cache_mode = 'force_generic_plan'; 2545prepare ab_q6 as 2546select * from ( 2547 select tableoid::regclass,a,b from ab 2548union all 2549 select tableoid::regclass,x,y from xy_1 2550union all 2551 select tableoid::regclass,a,b from ab 2552) ab where a = $1 and b = (select -10); 2553-- Ensure the xy_1 subplan is not pruned. 2554explain (analyze, costs off, summary off, timing off) execute ab_q6(1); 2555 QUERY PLAN 2556-------------------------------------------------------- 2557 Append (actual rows=0 loops=1) 2558 Subplans Removed: 12 2559 InitPlan 1 (returns $0) 2560 -> Result (actual rows=1 loops=1) 2561 -> Seq Scan on ab_a1_b1 (never executed) 2562 Filter: ((a = $1) AND (b = $0)) 2563 -> Seq Scan on ab_a1_b2 (never executed) 2564 Filter: ((a = $1) AND (b = $0)) 2565 -> Seq Scan on ab_a1_b3 (never executed) 2566 Filter: ((a = $1) AND (b = $0)) 2567 -> Seq Scan on xy_1 (actual rows=0 loops=1) 2568 Filter: ((x = $1) AND (y = $0)) 2569 Rows Removed by Filter: 1 2570 -> Seq Scan on ab_a1_b1 ab_a1_b1_1 (never executed) 2571 Filter: ((a = $1) AND (b = $0)) 2572 -> Seq Scan on ab_a1_b2 ab_a1_b2_1 (never executed) 2573 Filter: ((a = $1) AND (b = $0)) 2574 -> Seq Scan on ab_a1_b3 ab_a1_b3_1 (never executed) 2575 Filter: ((a = $1) AND (b = $0)) 2576(19 rows) 2577 2578-- Ensure we see just the xy_1 row. 2579execute ab_q6(100); 2580 tableoid | a | b 2581----------+-----+----- 2582 xy_1 | 100 | -10 2583(1 row) 2584 2585reset enable_bitmapscan; 2586reset enable_indexscan; 2587reset plan_cache_mode; 2588deallocate ab_q1; 2589deallocate ab_q2; 2590deallocate ab_q3; 2591deallocate ab_q4; 2592deallocate ab_q5; 2593deallocate ab_q6; 2594-- UPDATE on a partition subtree has been seen to have problems. 2595insert into ab values (1,2); 2596explain (analyze, costs off, summary off, timing off) 2597update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a; 2598 QUERY PLAN 2599------------------------------------------------------------------------------------- 2600 Update on ab_a1 (actual rows=0 loops=1) 2601 Update on ab_a1_b1 2602 Update on ab_a1_b2 2603 Update on ab_a1_b3 2604 -> Nested Loop (actual rows=0 loops=1) 2605 -> Append (actual rows=1 loops=1) 2606 -> Bitmap Heap Scan on ab_a1_b1 ab_a1_b1_1 (actual rows=0 loops=1) 2607 Recheck Cond: (a = 1) 2608 -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1) 2609 Index Cond: (a = 1) 2610 -> Bitmap Heap Scan on ab_a1_b2 ab_a1_b2_1 (actual rows=1 loops=1) 2611 Recheck Cond: (a = 1) 2612 Heap Blocks: exact=1 2613 -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1) 2614 Index Cond: (a = 1) 2615 -> Bitmap Heap Scan on ab_a1_b3 ab_a1_b3_1 (actual rows=0 loops=1) 2616 Recheck Cond: (a = 1) 2617 -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=0 loops=1) 2618 Index Cond: (a = 1) 2619 -> Materialize (actual rows=0 loops=1) 2620 -> Bitmap Heap Scan on ab_a1_b1 (actual rows=0 loops=1) 2621 Recheck Cond: (a = 1) 2622 -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1) 2623 Index Cond: (a = 1) 2624 -> Nested Loop (actual rows=1 loops=1) 2625 -> Append (actual rows=1 loops=1) 2626 -> Bitmap Heap Scan on ab_a1_b1 ab_a1_b1_1 (actual rows=0 loops=1) 2627 Recheck Cond: (a = 1) 2628 -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1) 2629 Index Cond: (a = 1) 2630 -> Bitmap Heap Scan on ab_a1_b2 ab_a1_b2_1 (actual rows=1 loops=1) 2631 Recheck Cond: (a = 1) 2632 Heap Blocks: exact=1 2633 -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1) 2634 Index Cond: (a = 1) 2635 -> Bitmap Heap Scan on ab_a1_b3 ab_a1_b3_1 (actual rows=0 loops=1) 2636 Recheck Cond: (a = 1) 2637 -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1) 2638 Index Cond: (a = 1) 2639 -> Materialize (actual rows=1 loops=1) 2640 -> Bitmap Heap Scan on ab_a1_b2 (actual rows=1 loops=1) 2641 Recheck Cond: (a = 1) 2642 Heap Blocks: exact=1 2643 -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1) 2644 Index Cond: (a = 1) 2645 -> Nested Loop (actual rows=0 loops=1) 2646 -> Append (actual rows=1 loops=1) 2647 -> Bitmap Heap Scan on ab_a1_b1 ab_a1_b1_1 (actual rows=0 loops=1) 2648 Recheck Cond: (a = 1) 2649 -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1) 2650 Index Cond: (a = 1) 2651 -> Bitmap Heap Scan on ab_a1_b2 ab_a1_b2_1 (actual rows=1 loops=1) 2652 Recheck Cond: (a = 1) 2653 Heap Blocks: exact=1 2654 -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1) 2655 Index Cond: (a = 1) 2656 -> Bitmap Heap Scan on ab_a1_b3 ab_a1_b3_1 (actual rows=0 loops=1) 2657 Recheck Cond: (a = 1) 2658 -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1) 2659 Index Cond: (a = 1) 2660 -> Materialize (actual rows=0 loops=1) 2661 -> Bitmap Heap Scan on ab_a1_b3 (actual rows=0 loops=1) 2662 Recheck Cond: (a = 1) 2663 -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1) 2664 Index Cond: (a = 1) 2665(65 rows) 2666 2667table ab; 2668 a | b 2669---+--- 2670 1 | 3 2671(1 row) 2672 2673-- Test UPDATE where source relation has run-time pruning enabled 2674truncate ab; 2675insert into ab values (1, 1), (1, 2), (1, 3), (2, 1); 2676explain (analyze, costs off, summary off, timing off) 2677update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1); 2678 QUERY PLAN 2679---------------------------------------------------------------------- 2680 Update on ab_a1 (actual rows=0 loops=1) 2681 Update on ab_a1_b1 2682 Update on ab_a1_b2 2683 Update on ab_a1_b3 2684 InitPlan 1 (returns $0) 2685 -> Result (actual rows=1 loops=1) 2686 -> Nested Loop (actual rows=1 loops=1) 2687 -> Seq Scan on ab_a1_b1 (actual rows=1 loops=1) 2688 -> Materialize (actual rows=1 loops=1) 2689 -> Append (actual rows=1 loops=1) 2690 -> Seq Scan on ab_a2_b1 (actual rows=1 loops=1) 2691 Filter: (b = $0) 2692 -> Seq Scan on ab_a2_b2 (never executed) 2693 Filter: (b = $0) 2694 -> Seq Scan on ab_a2_b3 (never executed) 2695 Filter: (b = $0) 2696 -> Nested Loop (actual rows=1 loops=1) 2697 -> Seq Scan on ab_a1_b2 (actual rows=1 loops=1) 2698 -> Materialize (actual rows=1 loops=1) 2699 -> Append (actual rows=1 loops=1) 2700 -> Seq Scan on ab_a2_b1 (actual rows=1 loops=1) 2701 Filter: (b = $0) 2702 -> Seq Scan on ab_a2_b2 (never executed) 2703 Filter: (b = $0) 2704 -> Seq Scan on ab_a2_b3 (never executed) 2705 Filter: (b = $0) 2706 -> Nested Loop (actual rows=1 loops=1) 2707 -> Seq Scan on ab_a1_b3 (actual rows=1 loops=1) 2708 -> Materialize (actual rows=1 loops=1) 2709 -> Append (actual rows=1 loops=1) 2710 -> Seq Scan on ab_a2_b1 (actual rows=1 loops=1) 2711 Filter: (b = $0) 2712 -> Seq Scan on ab_a2_b2 (never executed) 2713 Filter: (b = $0) 2714 -> Seq Scan on ab_a2_b3 (never executed) 2715 Filter: (b = $0) 2716(36 rows) 2717 2718select tableoid::regclass, * from ab; 2719 tableoid | a | b 2720----------+---+--- 2721 ab_a1_b3 | 1 | 3 2722 ab_a1_b3 | 1 | 3 2723 ab_a1_b3 | 1 | 3 2724 ab_a2_b1 | 2 | 1 2725(4 rows) 2726 2727drop table ab, lprt_a; 2728-- Join 2729create table tbl1(col1 int); 2730insert into tbl1 values (501), (505); 2731-- Basic table 2732create table tprt (col1 int) partition by range (col1); 2733create table tprt_1 partition of tprt for values from (1) to (501); 2734create table tprt_2 partition of tprt for values from (501) to (1001); 2735create table tprt_3 partition of tprt for values from (1001) to (2001); 2736create table tprt_4 partition of tprt for values from (2001) to (3001); 2737create table tprt_5 partition of tprt for values from (3001) to (4001); 2738create table tprt_6 partition of tprt for values from (4001) to (5001); 2739create index tprt1_idx on tprt_1 (col1); 2740create index tprt2_idx on tprt_2 (col1); 2741create index tprt3_idx on tprt_3 (col1); 2742create index tprt4_idx on tprt_4 (col1); 2743create index tprt5_idx on tprt_5 (col1); 2744create index tprt6_idx on tprt_6 (col1); 2745insert into tprt values (10), (20), (501), (502), (505), (1001), (4500); 2746set enable_hashjoin = off; 2747set enable_mergejoin = off; 2748explain (analyze, costs off, summary off, timing off) 2749select * from tbl1 join tprt on tbl1.col1 > tprt.col1; 2750 QUERY PLAN 2751-------------------------------------------------------------------------- 2752 Nested Loop (actual rows=6 loops=1) 2753 -> Seq Scan on tbl1 (actual rows=2 loops=1) 2754 -> Append (actual rows=3 loops=2) 2755 -> Index Scan using tprt1_idx on tprt_1 (actual rows=2 loops=2) 2756 Index Cond: (col1 < tbl1.col1) 2757 -> Index Scan using tprt2_idx on tprt_2 (actual rows=2 loops=1) 2758 Index Cond: (col1 < tbl1.col1) 2759 -> Index Scan using tprt3_idx on tprt_3 (never executed) 2760 Index Cond: (col1 < tbl1.col1) 2761 -> Index Scan using tprt4_idx on tprt_4 (never executed) 2762 Index Cond: (col1 < tbl1.col1) 2763 -> Index Scan using tprt5_idx on tprt_5 (never executed) 2764 Index Cond: (col1 < tbl1.col1) 2765 -> Index Scan using tprt6_idx on tprt_6 (never executed) 2766 Index Cond: (col1 < tbl1.col1) 2767(15 rows) 2768 2769explain (analyze, costs off, summary off, timing off) 2770select * from tbl1 join tprt on tbl1.col1 = tprt.col1; 2771 QUERY PLAN 2772-------------------------------------------------------------------------- 2773 Nested Loop (actual rows=2 loops=1) 2774 -> Seq Scan on tbl1 (actual rows=2 loops=1) 2775 -> Append (actual rows=1 loops=2) 2776 -> Index Scan using tprt1_idx on tprt_1 (never executed) 2777 Index Cond: (col1 = tbl1.col1) 2778 -> Index Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2) 2779 Index Cond: (col1 = tbl1.col1) 2780 -> Index Scan using tprt3_idx on tprt_3 (never executed) 2781 Index Cond: (col1 = tbl1.col1) 2782 -> Index Scan using tprt4_idx on tprt_4 (never executed) 2783 Index Cond: (col1 = tbl1.col1) 2784 -> Index Scan using tprt5_idx on tprt_5 (never executed) 2785 Index Cond: (col1 = tbl1.col1) 2786 -> Index Scan using tprt6_idx on tprt_6 (never executed) 2787 Index Cond: (col1 = tbl1.col1) 2788(15 rows) 2789 2790select tbl1.col1, tprt.col1 from tbl1 2791inner join tprt on tbl1.col1 > tprt.col1 2792order by tbl1.col1, tprt.col1; 2793 col1 | col1 2794------+------ 2795 501 | 10 2796 501 | 20 2797 505 | 10 2798 505 | 20 2799 505 | 501 2800 505 | 502 2801(6 rows) 2802 2803select tbl1.col1, tprt.col1 from tbl1 2804inner join tprt on tbl1.col1 = tprt.col1 2805order by tbl1.col1, tprt.col1; 2806 col1 | col1 2807------+------ 2808 501 | 501 2809 505 | 505 2810(2 rows) 2811 2812-- Multiple partitions 2813insert into tbl1 values (1001), (1010), (1011); 2814explain (analyze, costs off, summary off, timing off) 2815select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1; 2816 QUERY PLAN 2817-------------------------------------------------------------------------- 2818 Nested Loop (actual rows=23 loops=1) 2819 -> Seq Scan on tbl1 (actual rows=5 loops=1) 2820 -> Append (actual rows=5 loops=5) 2821 -> Index Scan using tprt1_idx on tprt_1 (actual rows=2 loops=5) 2822 Index Cond: (col1 < tbl1.col1) 2823 -> Index Scan using tprt2_idx on tprt_2 (actual rows=3 loops=4) 2824 Index Cond: (col1 < tbl1.col1) 2825 -> Index Scan using tprt3_idx on tprt_3 (actual rows=1 loops=2) 2826 Index Cond: (col1 < tbl1.col1) 2827 -> Index Scan using tprt4_idx on tprt_4 (never executed) 2828 Index Cond: (col1 < tbl1.col1) 2829 -> Index Scan using tprt5_idx on tprt_5 (never executed) 2830 Index Cond: (col1 < tbl1.col1) 2831 -> Index Scan using tprt6_idx on tprt_6 (never executed) 2832 Index Cond: (col1 < tbl1.col1) 2833(15 rows) 2834 2835explain (analyze, costs off, summary off, timing off) 2836select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1; 2837 QUERY PLAN 2838-------------------------------------------------------------------------- 2839 Nested Loop (actual rows=3 loops=1) 2840 -> Seq Scan on tbl1 (actual rows=5 loops=1) 2841 -> Append (actual rows=1 loops=5) 2842 -> Index Scan using tprt1_idx on tprt_1 (never executed) 2843 Index Cond: (col1 = tbl1.col1) 2844 -> Index Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2) 2845 Index Cond: (col1 = tbl1.col1) 2846 -> Index Scan using tprt3_idx on tprt_3 (actual rows=0 loops=3) 2847 Index Cond: (col1 = tbl1.col1) 2848 -> Index Scan using tprt4_idx on tprt_4 (never executed) 2849 Index Cond: (col1 = tbl1.col1) 2850 -> Index Scan using tprt5_idx on tprt_5 (never executed) 2851 Index Cond: (col1 = tbl1.col1) 2852 -> Index Scan using tprt6_idx on tprt_6 (never executed) 2853 Index Cond: (col1 = tbl1.col1) 2854(15 rows) 2855 2856select tbl1.col1, tprt.col1 from tbl1 2857inner join tprt on tbl1.col1 > tprt.col1 2858order by tbl1.col1, tprt.col1; 2859 col1 | col1 2860------+------ 2861 501 | 10 2862 501 | 20 2863 505 | 10 2864 505 | 20 2865 505 | 501 2866 505 | 502 2867 1001 | 10 2868 1001 | 20 2869 1001 | 501 2870 1001 | 502 2871 1001 | 505 2872 1010 | 10 2873 1010 | 20 2874 1010 | 501 2875 1010 | 502 2876 1010 | 505 2877 1010 | 1001 2878 1011 | 10 2879 1011 | 20 2880 1011 | 501 2881 1011 | 502 2882 1011 | 505 2883 1011 | 1001 2884(23 rows) 2885 2886select tbl1.col1, tprt.col1 from tbl1 2887inner join tprt on tbl1.col1 = tprt.col1 2888order by tbl1.col1, tprt.col1; 2889 col1 | col1 2890------+------ 2891 501 | 501 2892 505 | 505 2893 1001 | 1001 2894(3 rows) 2895 2896-- Last partition 2897delete from tbl1; 2898insert into tbl1 values (4400); 2899explain (analyze, costs off, summary off, timing off) 2900select * from tbl1 join tprt on tbl1.col1 < tprt.col1; 2901 QUERY PLAN 2902-------------------------------------------------------------------------- 2903 Nested Loop (actual rows=1 loops=1) 2904 -> Seq Scan on tbl1 (actual rows=1 loops=1) 2905 -> Append (actual rows=1 loops=1) 2906 -> Index Scan using tprt1_idx on tprt_1 (never executed) 2907 Index Cond: (col1 > tbl1.col1) 2908 -> Index Scan using tprt2_idx on tprt_2 (never executed) 2909 Index Cond: (col1 > tbl1.col1) 2910 -> Index Scan using tprt3_idx on tprt_3 (never executed) 2911 Index Cond: (col1 > tbl1.col1) 2912 -> Index Scan using tprt4_idx on tprt_4 (never executed) 2913 Index Cond: (col1 > tbl1.col1) 2914 -> Index Scan using tprt5_idx on tprt_5 (never executed) 2915 Index Cond: (col1 > tbl1.col1) 2916 -> Index Scan using tprt6_idx on tprt_6 (actual rows=1 loops=1) 2917 Index Cond: (col1 > tbl1.col1) 2918(15 rows) 2919 2920select tbl1.col1, tprt.col1 from tbl1 2921inner join tprt on tbl1.col1 < tprt.col1 2922order by tbl1.col1, tprt.col1; 2923 col1 | col1 2924------+------ 2925 4400 | 4500 2926(1 row) 2927 2928-- No matching partition 2929delete from tbl1; 2930insert into tbl1 values (10000); 2931explain (analyze, costs off, summary off, timing off) 2932select * from tbl1 join tprt on tbl1.col1 = tprt.col1; 2933 QUERY PLAN 2934------------------------------------------------------------------- 2935 Nested Loop (actual rows=0 loops=1) 2936 -> Seq Scan on tbl1 (actual rows=1 loops=1) 2937 -> Append (actual rows=0 loops=1) 2938 -> Index Scan using tprt1_idx on tprt_1 (never executed) 2939 Index Cond: (col1 = tbl1.col1) 2940 -> Index Scan using tprt2_idx on tprt_2 (never executed) 2941 Index Cond: (col1 = tbl1.col1) 2942 -> Index Scan using tprt3_idx on tprt_3 (never executed) 2943 Index Cond: (col1 = tbl1.col1) 2944 -> Index Scan using tprt4_idx on tprt_4 (never executed) 2945 Index Cond: (col1 = tbl1.col1) 2946 -> Index Scan using tprt5_idx on tprt_5 (never executed) 2947 Index Cond: (col1 = tbl1.col1) 2948 -> Index Scan using tprt6_idx on tprt_6 (never executed) 2949 Index Cond: (col1 = tbl1.col1) 2950(15 rows) 2951 2952select tbl1.col1, tprt.col1 from tbl1 2953inner join tprt on tbl1.col1 = tprt.col1 2954order by tbl1.col1, tprt.col1; 2955 col1 | col1 2956------+------ 2957(0 rows) 2958 2959drop table tbl1, tprt; 2960-- Test with columns defined in varying orders between each level 2961create table part_abc (a int not null, b int not null, c int not null) partition by list (a); 2962create table part_bac (b int not null, a int not null, c int not null) partition by list (b); 2963create table part_cab (c int not null, a int not null, b int not null) partition by list (c); 2964create table part_abc_p1 (a int not null, b int not null, c int not null); 2965alter table part_abc attach partition part_bac for values in(1); 2966alter table part_bac attach partition part_cab for values in(2); 2967alter table part_cab attach partition part_abc_p1 for values in(3); 2968prepare part_abc_q1 (int, int, int) as 2969select * from part_abc where a = $1 and b = $2 and c = $3; 2970-- Execute query 5 times to allow choose_custom_plan 2971-- to start considering a generic plan. 2972execute part_abc_q1 (1, 2, 3); 2973 a | b | c 2974---+---+--- 2975(0 rows) 2976 2977execute part_abc_q1 (1, 2, 3); 2978 a | b | c 2979---+---+--- 2980(0 rows) 2981 2982execute part_abc_q1 (1, 2, 3); 2983 a | b | c 2984---+---+--- 2985(0 rows) 2986 2987execute part_abc_q1 (1, 2, 3); 2988 a | b | c 2989---+---+--- 2990(0 rows) 2991 2992execute part_abc_q1 (1, 2, 3); 2993 a | b | c 2994---+---+--- 2995(0 rows) 2996 2997-- Single partition should be scanned. 2998explain (analyze, costs off, summary off, timing off) execute part_abc_q1 (1, 2, 3); 2999 QUERY PLAN 3000------------------------------------------------- 3001 Seq Scan on part_abc_p1 (actual rows=0 loops=1) 3002 Filter: ((a = $1) AND (b = $2) AND (c = $3)) 3003(2 rows) 3004 3005deallocate part_abc_q1; 3006drop table part_abc; 3007-- Ensure that an Append node properly handles a sub-partitioned table 3008-- matching without any of its leaf partitions matching the clause. 3009create table listp (a int, b int) partition by list (a); 3010create table listp_1 partition of listp for values in(1) partition by list (b); 3011create table listp_1_1 partition of listp_1 for values in(1); 3012create table listp_2 partition of listp for values in(2) partition by list (b); 3013create table listp_2_1 partition of listp_2 for values in(2); 3014select * from listp where b = 1; 3015 a | b 3016---+--- 3017(0 rows) 3018 3019-- Ensure that an Append node properly can handle selection of all first level 3020-- partitions before finally detecting the correct set of 2nd level partitions 3021-- which match the given parameter. 3022prepare q1 (int,int) as select * from listp where b in ($1,$2); 3023execute q1 (1,2); 3024 a | b 3025---+--- 3026(0 rows) 3027 3028execute q1 (1,2); 3029 a | b 3030---+--- 3031(0 rows) 3032 3033execute q1 (1,2); 3034 a | b 3035---+--- 3036(0 rows) 3037 3038execute q1 (1,2); 3039 a | b 3040---+--- 3041(0 rows) 3042 3043execute q1 (1,2); 3044 a | b 3045---+--- 3046(0 rows) 3047 3048explain (analyze, costs off, summary off, timing off) execute q1 (1,1); 3049 QUERY PLAN 3050----------------------------------------------------- 3051 Append (actual rows=0 loops=1) 3052 Subplans Removed: 1 3053 -> Seq Scan on listp_1_1 (actual rows=0 loops=1) 3054 Filter: (b = ANY (ARRAY[$1, $2])) 3055(4 rows) 3056 3057explain (analyze, costs off, summary off, timing off) execute q1 (2,2); 3058 QUERY PLAN 3059----------------------------------------------------- 3060 Append (actual rows=0 loops=1) 3061 Subplans Removed: 1 3062 -> Seq Scan on listp_2_1 (actual rows=0 loops=1) 3063 Filter: (b = ANY (ARRAY[$1, $2])) 3064(4 rows) 3065 3066-- Try with no matching partitions. One subplan should remain in this case, 3067-- but it shouldn't be executed. 3068explain (analyze, costs off, summary off, timing off) execute q1 (0,0); 3069 QUERY PLAN 3070---------------------------------------------- 3071 Append (actual rows=0 loops=1) 3072 Subplans Removed: 1 3073 -> Seq Scan on listp_1_1 (never executed) 3074 Filter: (b = ANY (ARRAY[$1, $2])) 3075(4 rows) 3076 3077deallocate q1; 3078-- Test more complex cases where a not-equal condition further eliminates partitions. 3079prepare q1 (int,int,int,int) as select * from listp where b in($1,$2) and $3 <> b and $4 <> b; 3080execute q1 (1,2,3,4); 3081 a | b 3082---+--- 3083(0 rows) 3084 3085execute q1 (1,2,3,4); 3086 a | b 3087---+--- 3088(0 rows) 3089 3090execute q1 (1,2,3,4); 3091 a | b 3092---+--- 3093(0 rows) 3094 3095execute q1 (1,2,3,4); 3096 a | b 3097---+--- 3098(0 rows) 3099 3100execute q1 (1,2,3,4); 3101 a | b 3102---+--- 3103(0 rows) 3104 3105-- Both partitions allowed by IN clause, but one disallowed by <> clause 3106explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,0); 3107 QUERY PLAN 3108------------------------------------------------------------------------- 3109 Append (actual rows=0 loops=1) 3110 Subplans Removed: 1 3111 -> Seq Scan on listp_1_1 (actual rows=0 loops=1) 3112 Filter: ((b = ANY (ARRAY[$1, $2])) AND ($3 <> b) AND ($4 <> b)) 3113(4 rows) 3114 3115-- Both partitions allowed by IN clause, then both excluded again by <> clauses. 3116-- One subplan will remain in this case, but it should not be executed. 3117explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,1); 3118 QUERY PLAN 3119------------------------------------------------------------------------- 3120 Append (actual rows=0 loops=1) 3121 Subplans Removed: 1 3122 -> Seq Scan on listp_1_1 (never executed) 3123 Filter: ((b = ANY (ARRAY[$1, $2])) AND ($3 <> b) AND ($4 <> b)) 3124(4 rows) 3125 3126-- Ensure Params that evaluate to NULL properly prune away all partitions 3127explain (analyze, costs off, summary off, timing off) 3128select * from listp where a = (select null::int); 3129 QUERY PLAN 3130---------------------------------------------- 3131 Append (actual rows=0 loops=1) 3132 InitPlan 1 (returns $0) 3133 -> Result (actual rows=1 loops=1) 3134 -> Seq Scan on listp_1_1 (never executed) 3135 Filter: (a = $0) 3136 -> Seq Scan on listp_2_1 (never executed) 3137 Filter: (a = $0) 3138(7 rows) 3139 3140drop table listp; 3141-- 3142-- check that stable query clauses are only used in run-time pruning 3143-- 3144create table stable_qual_pruning (a timestamp) partition by range (a); 3145create table stable_qual_pruning1 partition of stable_qual_pruning 3146 for values from ('2000-01-01') to ('2000-02-01'); 3147create table stable_qual_pruning2 partition of stable_qual_pruning 3148 for values from ('2000-02-01') to ('2000-03-01'); 3149create table stable_qual_pruning3 partition of stable_qual_pruning 3150 for values from ('3000-02-01') to ('3000-03-01'); 3151-- comparison against a stable value requires run-time pruning 3152explain (analyze, costs off, summary off, timing off) 3153select * from stable_qual_pruning where a < localtimestamp; 3154 QUERY PLAN 3155---------------------------------------------------------------- 3156 Append (actual rows=0 loops=1) 3157 Subplans Removed: 1 3158 -> Seq Scan on stable_qual_pruning1 (actual rows=0 loops=1) 3159 Filter: (a < LOCALTIMESTAMP) 3160 -> Seq Scan on stable_qual_pruning2 (actual rows=0 loops=1) 3161 Filter: (a < LOCALTIMESTAMP) 3162(6 rows) 3163 3164-- timestamp < timestamptz comparison is only stable, not immutable 3165explain (analyze, costs off, summary off, timing off) 3166select * from stable_qual_pruning where a < '2000-02-01'::timestamptz; 3167 QUERY PLAN 3168-------------------------------------------------------------------------------- 3169 Append (actual rows=0 loops=1) 3170 Subplans Removed: 2 3171 -> Seq Scan on stable_qual_pruning1 (actual rows=0 loops=1) 3172 Filter: (a < 'Tue Feb 01 00:00:00 2000 PST'::timestamp with time zone) 3173(4 rows) 3174 3175-- check ScalarArrayOp cases 3176explain (analyze, costs off, summary off, timing off) 3177select * from stable_qual_pruning 3178 where a = any(array['2010-02-01', '2020-01-01']::timestamp[]); 3179 QUERY PLAN 3180-------------------------------- 3181 Result (actual rows=0 loops=1) 3182 One-Time Filter: false 3183(2 rows) 3184 3185explain (analyze, costs off, summary off, timing off) 3186select * from stable_qual_pruning 3187 where a = any(array['2000-02-01', '2010-01-01']::timestamp[]); 3188 QUERY PLAN 3189---------------------------------------------------------------------------------------------------------------- 3190 Seq Scan on stable_qual_pruning2 (actual rows=0 loops=1) 3191 Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000","Fri Jan 01 00:00:00 2010"}'::timestamp without time zone[])) 3192(2 rows) 3193 3194explain (analyze, costs off, summary off, timing off) 3195select * from stable_qual_pruning 3196 where a = any(array['2000-02-01', localtimestamp]::timestamp[]); 3197 QUERY PLAN 3198------------------------------------------------------------------------------------------------------------ 3199 Append (actual rows=0 loops=1) 3200 Subplans Removed: 2 3201 -> Seq Scan on stable_qual_pruning2 (actual rows=0 loops=1) 3202 Filter: (a = ANY (ARRAY['Tue Feb 01 00:00:00 2000'::timestamp without time zone, LOCALTIMESTAMP])) 3203(4 rows) 3204 3205explain (analyze, costs off, summary off, timing off) 3206select * from stable_qual_pruning 3207 where a = any(array['2010-02-01', '2020-01-01']::timestamptz[]); 3208 QUERY PLAN 3209--------------------------------------------------------------------------------------------------------------------------- 3210 Append (actual rows=0 loops=1) 3211 Subplans Removed: 2 3212 -> Seq Scan on stable_qual_pruning1 (never executed) 3213 Filter: (a = ANY ('{"Mon Feb 01 00:00:00 2010 PST","Wed Jan 01 00:00:00 2020 PST"}'::timestamp with time zone[])) 3214(4 rows) 3215 3216explain (analyze, costs off, summary off, timing off) 3217select * from stable_qual_pruning 3218 where a = any(array['2000-02-01', '2010-01-01']::timestamptz[]); 3219 QUERY PLAN 3220--------------------------------------------------------------------------------------------------------------------------- 3221 Append (actual rows=0 loops=1) 3222 Subplans Removed: 2 3223 -> Seq Scan on stable_qual_pruning2 (actual rows=0 loops=1) 3224 Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000 PST","Fri Jan 01 00:00:00 2010 PST"}'::timestamp with time zone[])) 3225(4 rows) 3226 3227explain (analyze, costs off, summary off, timing off) 3228select * from stable_qual_pruning 3229 where a = any(null::timestamptz[]); 3230 QUERY PLAN 3231---------------------------------------------------------------- 3232 Append (actual rows=0 loops=1) 3233 -> Seq Scan on stable_qual_pruning1 (actual rows=0 loops=1) 3234 Filter: (a = ANY (NULL::timestamp with time zone[])) 3235 -> Seq Scan on stable_qual_pruning2 (actual rows=0 loops=1) 3236 Filter: (a = ANY (NULL::timestamp with time zone[])) 3237 -> Seq Scan on stable_qual_pruning3 (actual rows=0 loops=1) 3238 Filter: (a = ANY (NULL::timestamp with time zone[])) 3239(7 rows) 3240 3241drop table stable_qual_pruning; 3242-- 3243-- Check that pruning with composite range partitioning works correctly when 3244-- it must ignore clauses for trailing keys once it has seen a clause with 3245-- non-inclusive operator for an earlier key 3246-- 3247create table mc3p (a int, b int, c int) partition by range (a, abs(b), c); 3248create table mc3p0 partition of mc3p 3249 for values from (0, 0, 0) to (0, maxvalue, maxvalue); 3250create table mc3p1 partition of mc3p 3251 for values from (1, 1, 1) to (2, minvalue, minvalue); 3252create table mc3p2 partition of mc3p 3253 for values from (2, minvalue, minvalue) to (3, maxvalue, maxvalue); 3254insert into mc3p values (0, 1, 1), (1, 1, 1), (2, 1, 1); 3255explain (analyze, costs off, summary off, timing off) 3256select * from mc3p where a < 3 and abs(b) = 1; 3257 QUERY PLAN 3258------------------------------------------------- 3259 Append (actual rows=3 loops=1) 3260 -> Seq Scan on mc3p0 (actual rows=1 loops=1) 3261 Filter: ((a < 3) AND (abs(b) = 1)) 3262 -> Seq Scan on mc3p1 (actual rows=1 loops=1) 3263 Filter: ((a < 3) AND (abs(b) = 1)) 3264 -> Seq Scan on mc3p2 (actual rows=1 loops=1) 3265 Filter: ((a < 3) AND (abs(b) = 1)) 3266(7 rows) 3267 3268-- 3269-- Check that pruning with composite range partitioning works correctly when 3270-- a combination of runtime parameters is specified, not all of whose values 3271-- are available at the same time 3272-- 3273set plan_cache_mode = force_generic_plan; 3274prepare ps1 as 3275 select * from mc3p where a = $1 and abs(b) < (select 3); 3276explain (analyze, costs off, summary off, timing off) 3277execute ps1(1); 3278 QUERY PLAN 3279------------------------------------------------- 3280 Append (actual rows=1 loops=1) 3281 Subplans Removed: 2 3282 InitPlan 1 (returns $0) 3283 -> Result (actual rows=1 loops=1) 3284 -> Seq Scan on mc3p1 (actual rows=1 loops=1) 3285 Filter: ((a = $1) AND (abs(b) < $0)) 3286(6 rows) 3287 3288deallocate ps1; 3289prepare ps2 as 3290 select * from mc3p where a <= $1 and abs(b) < (select 3); 3291explain (analyze, costs off, summary off, timing off) 3292execute ps2(1); 3293 QUERY PLAN 3294------------------------------------------------- 3295 Append (actual rows=2 loops=1) 3296 Subplans Removed: 1 3297 InitPlan 1 (returns $0) 3298 -> Result (actual rows=1 loops=1) 3299 -> Seq Scan on mc3p0 (actual rows=1 loops=1) 3300 Filter: ((a <= $1) AND (abs(b) < $0)) 3301 -> Seq Scan on mc3p1 (actual rows=1 loops=1) 3302 Filter: ((a <= $1) AND (abs(b) < $0)) 3303(8 rows) 3304 3305deallocate ps2; 3306reset plan_cache_mode; 3307drop table mc3p; 3308-- Ensure runtime pruning works with initplans params with boolean types 3309create table boolvalues (value bool not null); 3310insert into boolvalues values('t'),('f'); 3311create table boolp (a bool) partition by list (a); 3312create table boolp_t partition of boolp for values in('t'); 3313create table boolp_f partition of boolp for values in('f'); 3314explain (analyze, costs off, summary off, timing off) 3315select * from boolp where a = (select value from boolvalues where value); 3316 QUERY PLAN 3317-------------------------------------------------------- 3318 Append (actual rows=0 loops=1) 3319 InitPlan 1 (returns $0) 3320 -> Seq Scan on boolvalues (actual rows=1 loops=1) 3321 Filter: value 3322 Rows Removed by Filter: 1 3323 -> Seq Scan on boolp_f (never executed) 3324 Filter: (a = $0) 3325 -> Seq Scan on boolp_t (actual rows=0 loops=1) 3326 Filter: (a = $0) 3327(9 rows) 3328 3329explain (analyze, costs off, summary off, timing off) 3330select * from boolp where a = (select value from boolvalues where not value); 3331 QUERY PLAN 3332-------------------------------------------------------- 3333 Append (actual rows=0 loops=1) 3334 InitPlan 1 (returns $0) 3335 -> Seq Scan on boolvalues (actual rows=1 loops=1) 3336 Filter: (NOT value) 3337 Rows Removed by Filter: 1 3338 -> Seq Scan on boolp_f (actual rows=0 loops=1) 3339 Filter: (a = $0) 3340 -> Seq Scan on boolp_t (never executed) 3341 Filter: (a = $0) 3342(9 rows) 3343 3344drop table boolp; 3345-- 3346-- Test run-time pruning of MergeAppend subnodes 3347-- 3348set enable_seqscan = off; 3349set enable_sort = off; 3350create table ma_test (a int, b int) partition by range (a); 3351create table ma_test_p1 partition of ma_test for values from (0) to (10); 3352create table ma_test_p2 partition of ma_test for values from (10) to (20); 3353create table ma_test_p3 partition of ma_test for values from (20) to (30); 3354insert into ma_test select x,x from generate_series(0,29) t(x); 3355create index on ma_test (b); 3356analyze ma_test; 3357prepare mt_q1 (int) as select a from ma_test where a >= $1 and a % 10 = 5 order by b; 3358-- Execute query 5 times to allow choose_custom_plan 3359-- to start considering a generic plan. 3360execute mt_q1(0); 3361 a 3362---- 3363 5 3364 15 3365 25 3366(3 rows) 3367 3368execute mt_q1(0); 3369 a 3370---- 3371 5 3372 15 3373 25 3374(3 rows) 3375 3376execute mt_q1(0); 3377 a 3378---- 3379 5 3380 15 3381 25 3382(3 rows) 3383 3384execute mt_q1(0); 3385 a 3386---- 3387 5 3388 15 3389 25 3390(3 rows) 3391 3392execute mt_q1(0); 3393 a 3394---- 3395 5 3396 15 3397 25 3398(3 rows) 3399 3400explain (analyze, costs off, summary off, timing off) execute mt_q1(15); 3401 QUERY PLAN 3402------------------------------------------------------------------------------- 3403 Merge Append (actual rows=2 loops=1) 3404 Sort Key: ma_test_p2.b 3405 Subplans Removed: 1 3406 -> Index Scan using ma_test_p2_b_idx on ma_test_p2 (actual rows=1 loops=1) 3407 Filter: ((a >= $1) AND ((a % 10) = 5)) 3408 Rows Removed by Filter: 9 3409 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 (actual rows=1 loops=1) 3410 Filter: ((a >= $1) AND ((a % 10) = 5)) 3411 Rows Removed by Filter: 9 3412(9 rows) 3413 3414execute mt_q1(15); 3415 a 3416---- 3417 15 3418 25 3419(2 rows) 3420 3421explain (analyze, costs off, summary off, timing off) execute mt_q1(25); 3422 QUERY PLAN 3423------------------------------------------------------------------------------- 3424 Merge Append (actual rows=1 loops=1) 3425 Sort Key: ma_test_p3.b 3426 Subplans Removed: 2 3427 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 (actual rows=1 loops=1) 3428 Filter: ((a >= $1) AND ((a % 10) = 5)) 3429 Rows Removed by Filter: 9 3430(6 rows) 3431 3432execute mt_q1(25); 3433 a 3434---- 3435 25 3436(1 row) 3437 3438-- Ensure MergeAppend behaves correctly when no subplans match 3439explain (analyze, costs off, summary off, timing off) execute mt_q1(35); 3440 QUERY PLAN 3441------------------------------------------------------------------------ 3442 Merge Append (actual rows=0 loops=1) 3443 Sort Key: ma_test_p1.b 3444 Subplans Removed: 2 3445 -> Index Scan using ma_test_p1_b_idx on ma_test_p1 (never executed) 3446 Filter: ((a >= $1) AND ((a % 10) = 5)) 3447(5 rows) 3448 3449execute mt_q1(35); 3450 a 3451--- 3452(0 rows) 3453 3454deallocate mt_q1; 3455-- ensure initplan params properly prune partitions 3456explain (analyze, costs off, summary off, timing off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b; 3457 QUERY PLAN 3458------------------------------------------------------------------------------------------------------------ 3459 Merge Append (actual rows=20 loops=1) 3460 Sort Key: ma_test_p1.b 3461 InitPlan 2 (returns $1) 3462 -> Result (actual rows=1 loops=1) 3463 InitPlan 1 (returns $0) 3464 -> Limit (actual rows=1 loops=1) 3465 -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_p2_1 (actual rows=1 loops=1) 3466 Index Cond: (b IS NOT NULL) 3467 -> Index Scan using ma_test_p1_b_idx on ma_test_p1 (never executed) 3468 Filter: (a >= $1) 3469 -> Index Scan using ma_test_p2_b_idx on ma_test_p2 (actual rows=10 loops=1) 3470 Filter: (a >= $1) 3471 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 (actual rows=10 loops=1) 3472 Filter: (a >= $1) 3473(14 rows) 3474 3475reset enable_seqscan; 3476reset enable_sort; 3477drop table ma_test; 3478reset enable_indexonlyscan; 3479-- 3480-- check that pruning works properly when the partition key is of a 3481-- pseudotype 3482-- 3483-- array type list partition key 3484create table pp_arrpart (a int[]) partition by list (a); 3485create table pp_arrpart1 partition of pp_arrpart for values in ('{1}'); 3486create table pp_arrpart2 partition of pp_arrpart for values in ('{2, 3}', '{4, 5}'); 3487explain (costs off) select * from pp_arrpart where a = '{1}'; 3488 QUERY PLAN 3489---------------------------------- 3490 Seq Scan on pp_arrpart1 3491 Filter: (a = '{1}'::integer[]) 3492(2 rows) 3493 3494explain (costs off) select * from pp_arrpart where a = '{1, 2}'; 3495 QUERY PLAN 3496-------------------------- 3497 Result 3498 One-Time Filter: false 3499(2 rows) 3500 3501explain (costs off) select * from pp_arrpart where a in ('{4, 5}', '{1}'); 3502 QUERY PLAN 3503---------------------------------------------------------------------- 3504 Append 3505 -> Seq Scan on pp_arrpart1 3506 Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[])) 3507 -> Seq Scan on pp_arrpart2 3508 Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[])) 3509(5 rows) 3510 3511explain (costs off) update pp_arrpart set a = a where a = '{1}'; 3512 QUERY PLAN 3513---------------------------------------- 3514 Update on pp_arrpart 3515 Update on pp_arrpart1 3516 -> Seq Scan on pp_arrpart1 3517 Filter: (a = '{1}'::integer[]) 3518(4 rows) 3519 3520explain (costs off) delete from pp_arrpart where a = '{1}'; 3521 QUERY PLAN 3522---------------------------------------- 3523 Delete on pp_arrpart 3524 Delete on pp_arrpart1 3525 -> Seq Scan on pp_arrpart1 3526 Filter: (a = '{1}'::integer[]) 3527(4 rows) 3528 3529drop table pp_arrpart; 3530-- array type hash partition key 3531create table pph_arrpart (a int[]) partition by hash (a); 3532create table pph_arrpart1 partition of pph_arrpart for values with (modulus 2, remainder 0); 3533create table pph_arrpart2 partition of pph_arrpart for values with (modulus 2, remainder 1); 3534insert into pph_arrpart values ('{1}'), ('{1, 2}'), ('{4, 5}'); 3535select tableoid::regclass, * from pph_arrpart order by 1; 3536 tableoid | a 3537--------------+------- 3538 pph_arrpart1 | {1,2} 3539 pph_arrpart1 | {4,5} 3540 pph_arrpart2 | {1} 3541(3 rows) 3542 3543explain (costs off) select * from pph_arrpart where a = '{1}'; 3544 QUERY PLAN 3545---------------------------------- 3546 Seq Scan on pph_arrpart2 3547 Filter: (a = '{1}'::integer[]) 3548(2 rows) 3549 3550explain (costs off) select * from pph_arrpart where a = '{1, 2}'; 3551 QUERY PLAN 3552------------------------------------ 3553 Seq Scan on pph_arrpart1 3554 Filter: (a = '{1,2}'::integer[]) 3555(2 rows) 3556 3557explain (costs off) select * from pph_arrpart where a in ('{4, 5}', '{1}'); 3558 QUERY PLAN 3559---------------------------------------------------------------------- 3560 Append 3561 -> Seq Scan on pph_arrpart1 3562 Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[])) 3563 -> Seq Scan on pph_arrpart2 3564 Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[])) 3565(5 rows) 3566 3567drop table pph_arrpart; 3568-- enum type list partition key 3569create type pp_colors as enum ('green', 'blue', 'black'); 3570create table pp_enumpart (a pp_colors) partition by list (a); 3571create table pp_enumpart_green partition of pp_enumpart for values in ('green'); 3572create table pp_enumpart_blue partition of pp_enumpart for values in ('blue'); 3573explain (costs off) select * from pp_enumpart where a = 'blue'; 3574 QUERY PLAN 3575----------------------------------- 3576 Seq Scan on pp_enumpart_blue 3577 Filter: (a = 'blue'::pp_colors) 3578(2 rows) 3579 3580explain (costs off) select * from pp_enumpart where a = 'black'; 3581 QUERY PLAN 3582-------------------------- 3583 Result 3584 One-Time Filter: false 3585(2 rows) 3586 3587drop table pp_enumpart; 3588drop type pp_colors; 3589-- record type as partition key 3590create type pp_rectype as (a int, b int); 3591create table pp_recpart (a pp_rectype) partition by list (a); 3592create table pp_recpart_11 partition of pp_recpart for values in ('(1,1)'); 3593create table pp_recpart_23 partition of pp_recpart for values in ('(2,3)'); 3594explain (costs off) select * from pp_recpart where a = '(1,1)'::pp_rectype; 3595 QUERY PLAN 3596------------------------------------- 3597 Seq Scan on pp_recpart_11 3598 Filter: (a = '(1,1)'::pp_rectype) 3599(2 rows) 3600 3601explain (costs off) select * from pp_recpart where a = '(1,2)'::pp_rectype; 3602 QUERY PLAN 3603-------------------------- 3604 Result 3605 One-Time Filter: false 3606(2 rows) 3607 3608drop table pp_recpart; 3609drop type pp_rectype; 3610-- range type partition key 3611create table pp_intrangepart (a int4range) partition by list (a); 3612create table pp_intrangepart12 partition of pp_intrangepart for values in ('[1,2]'); 3613create table pp_intrangepart2inf partition of pp_intrangepart for values in ('[2,)'); 3614explain (costs off) select * from pp_intrangepart where a = '[1,2]'::int4range; 3615 QUERY PLAN 3616------------------------------------ 3617 Seq Scan on pp_intrangepart12 3618 Filter: (a = '[1,3)'::int4range) 3619(2 rows) 3620 3621explain (costs off) select * from pp_intrangepart where a = '(1,2)'::int4range; 3622 QUERY PLAN 3623-------------------------- 3624 Result 3625 One-Time Filter: false 3626(2 rows) 3627 3628drop table pp_intrangepart; 3629-- 3630-- Ensure the enable_partition_prune GUC properly disables partition pruning. 3631-- 3632create table pp_lp (a int, value int) partition by list (a); 3633create table pp_lp1 partition of pp_lp for values in(1); 3634create table pp_lp2 partition of pp_lp for values in(2); 3635explain (costs off) select * from pp_lp where a = 1; 3636 QUERY PLAN 3637-------------------- 3638 Seq Scan on pp_lp1 3639 Filter: (a = 1) 3640(2 rows) 3641 3642explain (costs off) update pp_lp set value = 10 where a = 1; 3643 QUERY PLAN 3644-------------------------- 3645 Update on pp_lp 3646 Update on pp_lp1 3647 -> Seq Scan on pp_lp1 3648 Filter: (a = 1) 3649(4 rows) 3650 3651explain (costs off) delete from pp_lp where a = 1; 3652 QUERY PLAN 3653-------------------------- 3654 Delete on pp_lp 3655 Delete on pp_lp1 3656 -> Seq Scan on pp_lp1 3657 Filter: (a = 1) 3658(4 rows) 3659 3660set enable_partition_pruning = off; 3661set constraint_exclusion = 'partition'; -- this should not affect the result. 3662explain (costs off) select * from pp_lp where a = 1; 3663 QUERY PLAN 3664-------------------------- 3665 Append 3666 -> Seq Scan on pp_lp1 3667 Filter: (a = 1) 3668 -> Seq Scan on pp_lp2 3669 Filter: (a = 1) 3670(5 rows) 3671 3672explain (costs off) update pp_lp set value = 10 where a = 1; 3673 QUERY PLAN 3674-------------------------- 3675 Update on pp_lp 3676 Update on pp_lp1 3677 Update on pp_lp2 3678 -> Seq Scan on pp_lp1 3679 Filter: (a = 1) 3680 -> Seq Scan on pp_lp2 3681 Filter: (a = 1) 3682(7 rows) 3683 3684explain (costs off) delete from pp_lp where a = 1; 3685 QUERY PLAN 3686-------------------------- 3687 Delete on pp_lp 3688 Delete on pp_lp1 3689 Delete on pp_lp2 3690 -> Seq Scan on pp_lp1 3691 Filter: (a = 1) 3692 -> Seq Scan on pp_lp2 3693 Filter: (a = 1) 3694(7 rows) 3695 3696set constraint_exclusion = 'off'; -- this should not affect the result. 3697explain (costs off) select * from pp_lp where a = 1; 3698 QUERY PLAN 3699-------------------------- 3700 Append 3701 -> Seq Scan on pp_lp1 3702 Filter: (a = 1) 3703 -> Seq Scan on pp_lp2 3704 Filter: (a = 1) 3705(5 rows) 3706 3707explain (costs off) update pp_lp set value = 10 where a = 1; 3708 QUERY PLAN 3709-------------------------- 3710 Update on pp_lp 3711 Update on pp_lp1 3712 Update on pp_lp2 3713 -> Seq Scan on pp_lp1 3714 Filter: (a = 1) 3715 -> Seq Scan on pp_lp2 3716 Filter: (a = 1) 3717(7 rows) 3718 3719explain (costs off) delete from pp_lp where a = 1; 3720 QUERY PLAN 3721-------------------------- 3722 Delete on pp_lp 3723 Delete on pp_lp1 3724 Delete on pp_lp2 3725 -> Seq Scan on pp_lp1 3726 Filter: (a = 1) 3727 -> Seq Scan on pp_lp2 3728 Filter: (a = 1) 3729(7 rows) 3730 3731drop table pp_lp; 3732-- Ensure enable_partition_prune does not affect non-partitioned tables. 3733create table inh_lp (a int, value int); 3734create table inh_lp1 (a int, value int, check(a = 1)) inherits (inh_lp); 3735NOTICE: merging column "a" with inherited definition 3736NOTICE: merging column "value" with inherited definition 3737create table inh_lp2 (a int, value int, check(a = 2)) inherits (inh_lp); 3738NOTICE: merging column "a" with inherited definition 3739NOTICE: merging column "value" with inherited definition 3740set constraint_exclusion = 'partition'; 3741-- inh_lp2 should be removed in the following 3 cases. 3742explain (costs off) select * from inh_lp where a = 1; 3743 QUERY PLAN 3744--------------------------- 3745 Append 3746 -> Seq Scan on inh_lp 3747 Filter: (a = 1) 3748 -> Seq Scan on inh_lp1 3749 Filter: (a = 1) 3750(5 rows) 3751 3752explain (costs off) update inh_lp set value = 10 where a = 1; 3753 QUERY PLAN 3754--------------------------- 3755 Update on inh_lp 3756 Update on inh_lp 3757 Update on inh_lp1 3758 -> Seq Scan on inh_lp 3759 Filter: (a = 1) 3760 -> Seq Scan on inh_lp1 3761 Filter: (a = 1) 3762(7 rows) 3763 3764explain (costs off) delete from inh_lp where a = 1; 3765 QUERY PLAN 3766--------------------------- 3767 Delete on inh_lp 3768 Delete on inh_lp 3769 Delete on inh_lp1 3770 -> Seq Scan on inh_lp 3771 Filter: (a = 1) 3772 -> Seq Scan on inh_lp1 3773 Filter: (a = 1) 3774(7 rows) 3775 3776-- Ensure we don't exclude normal relations when we only expect to exclude 3777-- inheritance children 3778explain (costs off) update inh_lp1 set value = 10 where a = 2; 3779 QUERY PLAN 3780--------------------------- 3781 Update on inh_lp1 3782 -> Seq Scan on inh_lp1 3783 Filter: (a = 2) 3784(3 rows) 3785 3786drop table inh_lp cascade; 3787NOTICE: drop cascades to 2 other objects 3788DETAIL: drop cascades to table inh_lp1 3789drop cascades to table inh_lp2 3790reset enable_partition_pruning; 3791reset constraint_exclusion; 3792-- Check pruning for a partition tree containing only temporary relations 3793create temp table pp_temp_parent (a int) partition by list (a); 3794create temp table pp_temp_part_1 partition of pp_temp_parent for values in (1); 3795create temp table pp_temp_part_def partition of pp_temp_parent default; 3796explain (costs off) select * from pp_temp_parent where true; 3797 QUERY PLAN 3798------------------------------------ 3799 Append 3800 -> Seq Scan on pp_temp_part_1 3801 -> Seq Scan on pp_temp_part_def 3802(3 rows) 3803 3804explain (costs off) select * from pp_temp_parent where a = 2; 3805 QUERY PLAN 3806------------------------------ 3807 Seq Scan on pp_temp_part_def 3808 Filter: (a = 2) 3809(2 rows) 3810 3811drop table pp_temp_parent; 3812-- Stress run-time partition pruning a bit more, per bug reports 3813create temp table p (a int, b int, c int) partition by list (a); 3814create temp table p1 partition of p for values in (1); 3815create temp table p2 partition of p for values in (2); 3816create temp table q (a int, b int, c int) partition by list (a); 3817create temp table q1 partition of q for values in (1) partition by list (b); 3818create temp table q11 partition of q1 for values in (1) partition by list (c); 3819create temp table q111 partition of q11 for values in (1); 3820create temp table q2 partition of q for values in (2) partition by list (b); 3821create temp table q21 partition of q2 for values in (1); 3822create temp table q22 partition of q2 for values in (2); 3823insert into q22 values (2, 2, 3); 3824explain (costs off) 3825select * 3826from ( 3827 select * from p 3828 union all 3829 select * from q1 3830 union all 3831 select 1, 1, 1 3832 ) s(a, b, c) 3833where s.a = 1 and s.b = 1 and s.c = (select 1); 3834 QUERY PLAN 3835---------------------------------------------------- 3836 Append 3837 InitPlan 1 (returns $0) 3838 -> Result 3839 -> Seq Scan on p1 3840 Filter: ((a = 1) AND (b = 1) AND (c = $0)) 3841 -> Seq Scan on q111 3842 Filter: ((a = 1) AND (b = 1) AND (c = $0)) 3843 -> Result 3844 One-Time Filter: (1 = $0) 3845(9 rows) 3846 3847select * 3848from ( 3849 select * from p 3850 union all 3851 select * from q1 3852 union all 3853 select 1, 1, 1 3854 ) s(a, b, c) 3855where s.a = 1 and s.b = 1 and s.c = (select 1); 3856 a | b | c 3857---+---+--- 3858 1 | 1 | 1 3859(1 row) 3860 3861prepare q (int, int) as 3862select * 3863from ( 3864 select * from p 3865 union all 3866 select * from q1 3867 union all 3868 select 1, 1, 1 3869 ) s(a, b, c) 3870where s.a = $1 and s.b = $2 and s.c = (select 1); 3871set plan_cache_mode to force_generic_plan; 3872explain (costs off) execute q (1, 1); 3873 QUERY PLAN 3874--------------------------------------------------------------- 3875 Append 3876 Subplans Removed: 1 3877 InitPlan 1 (returns $0) 3878 -> Result 3879 -> Seq Scan on p1 3880 Filter: ((a = $1) AND (b = $2) AND (c = $0)) 3881 -> Seq Scan on q111 3882 Filter: ((a = $1) AND (b = $2) AND (c = $0)) 3883 -> Result 3884 One-Time Filter: ((1 = $1) AND (1 = $2) AND (1 = $0)) 3885(10 rows) 3886 3887execute q (1, 1); 3888 a | b | c 3889---+---+--- 3890 1 | 1 | 1 3891(1 row) 3892 3893reset plan_cache_mode; 3894drop table p, q; 3895-- Ensure run-time pruning works correctly when we match a partitioned table 3896-- on the first level but find no matching partitions on the second level. 3897create table listp (a int, b int) partition by list (a); 3898create table listp1 partition of listp for values in(1); 3899create table listp2 partition of listp for values in(2) partition by list(b); 3900create table listp2_10 partition of listp2 for values in (10); 3901explain (analyze, costs off, summary off, timing off) 3902select * from listp where a = (select 2) and b <> 10; 3903 QUERY PLAN 3904-------------------------------------------- 3905 Seq Scan on listp1 (actual rows=0 loops=1) 3906 Filter: ((b <> 10) AND (a = $0)) 3907 InitPlan 1 (returns $0) 3908 -> Result (never executed) 3909(4 rows) 3910 3911-- 3912-- check that a partition directly accessed in a query is excluded with 3913-- constraint_exclusion = on 3914-- 3915-- turn off partition pruning, so that it doesn't interfere 3916set enable_partition_pruning to off; 3917-- setting constraint_exclusion to 'partition' disables exclusion 3918set constraint_exclusion to 'partition'; 3919explain (costs off) select * from listp1 where a = 2; 3920 QUERY PLAN 3921-------------------- 3922 Seq Scan on listp1 3923 Filter: (a = 2) 3924(2 rows) 3925 3926explain (costs off) update listp1 set a = 1 where a = 2; 3927 QUERY PLAN 3928-------------------------- 3929 Update on listp1 3930 -> Seq Scan on listp1 3931 Filter: (a = 2) 3932(3 rows) 3933 3934-- constraint exclusion enabled 3935set constraint_exclusion to 'on'; 3936explain (costs off) select * from listp1 where a = 2; 3937 QUERY PLAN 3938-------------------------- 3939 Result 3940 One-Time Filter: false 3941(2 rows) 3942 3943explain (costs off) update listp1 set a = 1 where a = 2; 3944 QUERY PLAN 3945-------------------------------- 3946 Update on listp1 3947 -> Result 3948 One-Time Filter: false 3949(3 rows) 3950 3951reset constraint_exclusion; 3952reset enable_partition_pruning; 3953drop table listp; 3954-- 3955-- Check that gen_prune_steps_from_opexps() works well for various cases of 3956-- clauses for different partition keys 3957-- 3958create table rp_prefix_test1 (a int, b varchar) partition by range(a, b); 3959create table rp_prefix_test1_p1 partition of rp_prefix_test1 for values from (1, 'a') to (1, 'b'); 3960create table rp_prefix_test1_p2 partition of rp_prefix_test1 for values from (2, 'a') to (2, 'b'); 3961-- Don't call get_steps_using_prefix() with the last partition key b plus 3962-- an empty prefix 3963explain (costs off) select * from rp_prefix_test1 where a <= 1 and b = 'a'; 3964 QUERY PLAN 3965-------------------------------------------------- 3966 Seq Scan on rp_prefix_test1_p1 3967 Filter: ((a <= 1) AND ((b)::text = 'a'::text)) 3968(2 rows) 3969 3970create table rp_prefix_test2 (a int, b int, c int) partition by range(a, b, c); 3971create table rp_prefix_test2_p1 partition of rp_prefix_test2 for values from (1, 1, 0) to (1, 1, 10); 3972create table rp_prefix_test2_p2 partition of rp_prefix_test2 for values from (2, 2, 0) to (2, 2, 10); 3973-- Don't call get_steps_using_prefix() with the last partition key c plus 3974-- an invalid prefix (ie, b = 1) 3975explain (costs off) select * from rp_prefix_test2 where a <= 1 and b = 1 and c >= 0; 3976 QUERY PLAN 3977----------------------------------------------- 3978 Seq Scan on rp_prefix_test2_p1 3979 Filter: ((a <= 1) AND (c >= 0) AND (b = 1)) 3980(2 rows) 3981 3982create table rp_prefix_test3 (a int, b int, c int, d int) partition by range(a, b, c, d); 3983create table rp_prefix_test3_p1 partition of rp_prefix_test3 for values from (1, 1, 1, 0) to (1, 1, 1, 10); 3984create table rp_prefix_test3_p2 partition of rp_prefix_test3 for values from (2, 2, 2, 0) to (2, 2, 2, 10); 3985-- Test that get_steps_using_prefix() handles a prefix that contains multiple 3986-- clauses for the partition key b (ie, b >= 1 and b >= 2) 3987explain (costs off) select * from rp_prefix_test3 where a >= 1 and b >= 1 and b >= 2 and c >= 2 and d >= 0; 3988 QUERY PLAN 3989-------------------------------------------------------------------------- 3990 Seq Scan on rp_prefix_test3_p2 3991 Filter: ((a >= 1) AND (b >= 1) AND (b >= 2) AND (c >= 2) AND (d >= 0)) 3992(2 rows) 3993 3994-- Test that get_steps_using_prefix() handles a prefix that contains multiple 3995-- clauses for the partition key b (ie, b >= 1 and b = 2) (This also tests 3996-- that the caller arranges clauses in that prefix in the required order) 3997explain (costs off) select * from rp_prefix_test3 where a >= 1 and b >= 1 and b = 2 and c = 2 and d >= 0; 3998 QUERY PLAN 3999------------------------------------------------------------------------ 4000 Seq Scan on rp_prefix_test3_p2 4001 Filter: ((a >= 1) AND (b >= 1) AND (d >= 0) AND (b = 2) AND (c = 2)) 4002(2 rows) 4003 4004create table hp_prefix_test (a int, b int, c int, d int) partition by hash (a part_test_int4_ops, b part_test_int4_ops, c part_test_int4_ops, d part_test_int4_ops); 4005create table hp_prefix_test_p1 partition of hp_prefix_test for values with (modulus 2, remainder 0); 4006create table hp_prefix_test_p2 partition of hp_prefix_test for values with (modulus 2, remainder 1); 4007-- Test that get_steps_using_prefix() handles non-NULL step_nullkeys 4008explain (costs off) select * from hp_prefix_test where a = 1 and b is null and c = 1 and d = 1; 4009 QUERY PLAN 4010------------------------------------------------------------- 4011 Seq Scan on hp_prefix_test_p1 4012 Filter: ((b IS NULL) AND (a = 1) AND (c = 1) AND (d = 1)) 4013(2 rows) 4014 4015drop table rp_prefix_test1; 4016drop table rp_prefix_test2; 4017drop table rp_prefix_test3; 4018drop table hp_prefix_test; 4019-- 4020-- Check that gen_partprune_steps() detects self-contradiction from clauses 4021-- regardless of the order of the clauses (Here we use a custom operator to 4022-- prevent the equivclass.c machinery from reordering the clauses) 4023-- 4024create operator === ( 4025 leftarg = int4, 4026 rightarg = int4, 4027 procedure = int4eq, 4028 commutator = ===, 4029 hashes 4030); 4031create operator class part_test_int4_ops2 4032for type int4 4033using hash as 4034operator 1 ===, 4035function 2 part_hashint4_noop(int4, int8); 4036create table hp_contradict_test (a int, b int) partition by hash (a part_test_int4_ops2, b part_test_int4_ops2); 4037create table hp_contradict_test_p1 partition of hp_contradict_test for values with (modulus 2, remainder 0); 4038create table hp_contradict_test_p2 partition of hp_contradict_test for values with (modulus 2, remainder 1); 4039explain (costs off) select * from hp_contradict_test where a is null and a === 1 and b === 1; 4040 QUERY PLAN 4041-------------------------- 4042 Result 4043 One-Time Filter: false 4044(2 rows) 4045 4046explain (costs off) select * from hp_contradict_test where a === 1 and b === 1 and a is null; 4047 QUERY PLAN 4048-------------------------- 4049 Result 4050 One-Time Filter: false 4051(2 rows) 4052 4053drop table hp_contradict_test; 4054drop operator class part_test_int4_ops2 using hash; 4055drop operator ===(int4, int4); 4056