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