1-- 2-- Test partitioning planner code 3-- 4create table lp (a char) partition by list (a); 5create table lp_default partition of lp default; 6create table lp_ef partition of lp for values in ('e', 'f'); 7create table lp_ad partition of lp for values in ('a', 'd'); 8create table lp_bc partition of lp for values in ('b', 'c'); 9create table lp_g partition of lp for values in ('g'); 10create table lp_null partition of lp for values in (null); 11explain (costs off) select * from lp; 12explain (costs off) select * from lp where a > 'a' and a < 'd'; 13explain (costs off) select * from lp where a > 'a' and a <= 'd'; 14explain (costs off) select * from lp where a = 'a'; 15explain (costs off) select * from lp where 'a' = a; /* commuted */ 16explain (costs off) select * from lp where a is not null; 17explain (costs off) select * from lp where a is null; 18explain (costs off) select * from lp where a = 'a' or a = 'c'; 19explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c'); 20explain (costs off) select * from lp where a <> 'g'; 21explain (costs off) select * from lp where a <> 'a' and a <> 'd'; 22explain (costs off) select * from lp where a not in ('a', 'd'); 23 24-- collation matches the partitioning collation, pruning works 25create table coll_pruning (a text collate "C") partition by list (a); 26create table coll_pruning_a partition of coll_pruning for values in ('a'); 27create table coll_pruning_b partition of coll_pruning for values in ('b'); 28create table coll_pruning_def partition of coll_pruning default; 29explain (costs off) select * from coll_pruning where a collate "C" = 'a' collate "C"; 30-- collation doesn't match the partitioning collation, no pruning occurs 31explain (costs off) select * from coll_pruning where a collate "POSIX" = 'a' collate "POSIX"; 32 33create table rlp (a int, b varchar) partition by range (a); 34create table rlp_default partition of rlp default partition by list (a); 35create table rlp_default_default partition of rlp_default default; 36create table rlp_default_10 partition of rlp_default for values in (10); 37create table rlp_default_30 partition of rlp_default for values in (30); 38create table rlp_default_null partition of rlp_default for values in (null); 39create table rlp1 partition of rlp for values from (minvalue) to (1); 40create table rlp2 partition of rlp for values from (1) to (10); 41 42create table rlp3 (b varchar, a int) partition by list (b varchar_ops); 43create table rlp3_default partition of rlp3 default; 44create table rlp3abcd partition of rlp3 for values in ('ab', 'cd'); 45create table rlp3efgh partition of rlp3 for values in ('ef', 'gh'); 46create table rlp3nullxy partition of rlp3 for values in (null, 'xy'); 47alter table rlp attach partition rlp3 for values from (15) to (20); 48 49create table rlp4 partition of rlp for values from (20) to (30) partition by range (a); 50create table rlp4_default partition of rlp4 default; 51create table rlp4_1 partition of rlp4 for values from (20) to (25); 52create table rlp4_2 partition of rlp4 for values from (25) to (29); 53 54create table rlp5 partition of rlp for values from (31) to (maxvalue) partition by range (a); 55create table rlp5_default partition of rlp5 default; 56create table rlp5_1 partition of rlp5 for values from (31) to (40); 57 58explain (costs off) select * from rlp where a < 1; 59explain (costs off) select * from rlp where 1 > a; /* commuted */ 60explain (costs off) select * from rlp where a <= 1; 61explain (costs off) select * from rlp where a = 1; 62explain (costs off) select * from rlp where a = 1::bigint; /* same as above */ 63explain (costs off) select * from rlp where a = 1::numeric; /* no pruning */ 64explain (costs off) select * from rlp where a <= 10; 65explain (costs off) select * from rlp where a > 10; 66explain (costs off) select * from rlp where a < 15; 67explain (costs off) select * from rlp where a <= 15; 68explain (costs off) select * from rlp where a > 15 and b = 'ab'; 69explain (costs off) select * from rlp where a = 16; 70explain (costs off) select * from rlp where a = 16 and b in ('not', 'in', 'here'); 71explain (costs off) select * from rlp where a = 16 and b < 'ab'; 72explain (costs off) select * from rlp where a = 16 and b <= 'ab'; 73explain (costs off) select * from rlp where a = 16 and b is null; 74explain (costs off) select * from rlp where a = 16 and b is not null; 75explain (costs off) select * from rlp where a is null; 76explain (costs off) select * from rlp where a is not null; 77explain (costs off) select * from rlp where a > 30; 78explain (costs off) select * from rlp where a = 30; /* only default is scanned */ 79explain (costs off) select * from rlp where a <= 31; 80explain (costs off) select * from rlp where a = 1 or a = 7; 81explain (costs off) select * from rlp where a = 1 or b = 'ab'; 82 83explain (costs off) select * from rlp where a > 20 and a < 27; 84explain (costs off) select * from rlp where a = 29; 85explain (costs off) select * from rlp where a >= 29; 86 87-- redundant clauses are eliminated 88explain (costs off) select * from rlp where a > 1 and a = 10; /* only default */ 89explain (costs off) select * from rlp where a > 1 and a >=15; /* rlp3 onwards, including default */ 90explain (costs off) select * from rlp where a = 1 and a = 3; /* empty */ 91explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a = 15); 92 93-- multi-column keys 94create table mc3p (a int, b int, c int) partition by range (a, abs(b), c); 95create table mc3p_default partition of mc3p default; 96create table mc3p0 partition of mc3p for values from (minvalue, minvalue, minvalue) to (1, 1, 1); 97create table mc3p1 partition of mc3p for values from (1, 1, 1) to (10, 5, 10); 98create table mc3p2 partition of mc3p for values from (10, 5, 10) to (10, 10, 10); 99create table mc3p3 partition of mc3p for values from (10, 10, 10) to (10, 10, 20); 100create table mc3p4 partition of mc3p for values from (10, 10, 20) to (10, maxvalue, maxvalue); 101create table mc3p5 partition of mc3p for values from (11, 1, 1) to (20, 10, 10); 102create table mc3p6 partition of mc3p for values from (20, 10, 10) to (20, 20, 20); 103create table mc3p7 partition of mc3p for values from (20, 20, 20) to (maxvalue, maxvalue, maxvalue); 104 105explain (costs off) select * from mc3p where a = 1; 106explain (costs off) select * from mc3p where a = 1 and abs(b) < 1; 107explain (costs off) select * from mc3p where a = 1 and abs(b) = 1; 108explain (costs off) select * from mc3p where a = 1 and abs(b) = 1 and c < 8; 109explain (costs off) select * from mc3p where a = 10 and abs(b) between 5 and 35; 110explain (costs off) select * from mc3p where a > 10; 111explain (costs off) select * from mc3p where a >= 10; 112explain (costs off) select * from mc3p where a < 10; 113explain (costs off) select * from mc3p where a <= 10 and abs(b) < 10; 114explain (costs off) select * from mc3p where a = 11 and abs(b) = 0; 115explain (costs off) select * from mc3p where a = 20 and abs(b) = 10 and c = 100; 116explain (costs off) select * from mc3p where a > 20; 117explain (costs off) select * from mc3p where a >= 20; 118explain (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); 119explain (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; 120explain (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; 121explain (costs off) select * from mc3p where a = 1 or abs(b) = 1 or c = 1; 122explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1) or (a = 10 and abs(b) = 10); 123explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1) or (a = 10 and abs(b) = 9); 124 125-- a simpler multi-column keys case 126create table mc2p (a int, b int) partition by range (a, b); 127create table mc2p_default partition of mc2p default; 128create table mc2p0 partition of mc2p for values from (minvalue, minvalue) to (1, minvalue); 129create table mc2p1 partition of mc2p for values from (1, minvalue) to (1, 1); 130create table mc2p2 partition of mc2p for values from (1, 1) to (2, minvalue); 131create table mc2p3 partition of mc2p for values from (2, minvalue) to (2, 1); 132create table mc2p4 partition of mc2p for values from (2, 1) to (2, maxvalue); 133create table mc2p5 partition of mc2p for values from (2, maxvalue) to (maxvalue, maxvalue); 134 135explain (costs off) select * from mc2p where a < 2; 136explain (costs off) select * from mc2p where a = 2 and b < 1; 137explain (costs off) select * from mc2p where a > 1; 138explain (costs off) select * from mc2p where a = 1 and b > 1; 139 140-- all partitions but the default one should be pruned 141explain (costs off) select * from mc2p where a = 1 and b is null; 142explain (costs off) select * from mc2p where a is null and b is null; 143explain (costs off) select * from mc2p where a is null and b = 1; 144explain (costs off) select * from mc2p where a is null; 145explain (costs off) select * from mc2p where b is null; 146 147-- boolean partitioning 148create table boolpart (a bool) partition by list (a); 149create table boolpart_default partition of boolpart default; 150create table boolpart_t partition of boolpart for values in ('true'); 151create table boolpart_f partition of boolpart for values in ('false'); 152 153explain (costs off) select * from boolpart where a in (true, false); 154explain (costs off) select * from boolpart where a = false; 155explain (costs off) select * from boolpart where not a = false; 156explain (costs off) select * from boolpart where a is true or a is not true; 157explain (costs off) select * from boolpart where a is not true; 158explain (costs off) select * from boolpart where a is not true and a is not false; 159explain (costs off) select * from boolpart where a is unknown; 160explain (costs off) select * from boolpart where a is not unknown; 161 162-- test scalar-to-array operators 163create table coercepart (a varchar) partition by list (a); 164create table coercepart_ab partition of coercepart for values in ('ab'); 165create table coercepart_bc partition of coercepart for values in ('bc'); 166create table coercepart_cd partition of coercepart for values in ('cd'); 167 168explain (costs off) select * from coercepart where a in ('ab', to_char(125, '999')); 169explain (costs off) select * from coercepart where a ~ any ('{ab}'); 170explain (costs off) select * from coercepart where a !~ all ('{ab}'); 171explain (costs off) select * from coercepart where a ~ any ('{ab,bc}'); 172explain (costs off) select * from coercepart where a !~ all ('{ab,bc}'); 173 174drop table coercepart; 175 176CREATE TABLE part (a INT, b INT) PARTITION BY LIST (a); 177CREATE TABLE part_p1 PARTITION OF part FOR VALUES IN (-2,-1,0,1,2); 178CREATE TABLE part_p2 PARTITION OF part DEFAULT PARTITION BY RANGE(a); 179CREATE TABLE part_p2_p1 PARTITION OF part_p2 DEFAULT; 180INSERT INTO part VALUES (-1,-1), (1,1), (2,NULL), (NULL,-2),(NULL,NULL); 181EXPLAIN (COSTS OFF) SELECT tableoid::regclass as part, a, b FROM part WHERE a IS NULL ORDER BY 1, 2, 3; 182 183-- 184-- some more cases 185-- 186 187-- 188-- pruning for partitioned table appearing inside a sub-query 189-- 190-- pruning won't work for mc3p, because some keys are Params 191explain (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; 192 193-- pruning should work fine, because values for a prefix of keys (a, b) are 194-- available 195explain (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; 196 197-- also here, because values for all keys are provided 198explain (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; 199 200-- 201-- pruning with clauses containing <> operator 202-- 203 204-- doesn't prune range partitions 205create table rp (a int) partition by range (a); 206create table rp0 partition of rp for values from (minvalue) to (1); 207create table rp1 partition of rp for values from (1) to (2); 208create table rp2 partition of rp for values from (2) to (maxvalue); 209 210explain (costs off) select * from rp where a <> 1; 211explain (costs off) select * from rp where a <> 1 and a <> 2; 212 213-- null partition should be eliminated due to strict <> clause. 214explain (costs off) select * from lp where a <> 'a'; 215 216-- ensure we detect contradictions in clauses; a can't be NULL and NOT NULL. 217explain (costs off) select * from lp where a <> 'a' and a is null; 218explain (costs off) select * from lp where (a <> 'a' and a <> 'd') or a is null; 219 220-- check that it also works for a partitioned table that's not root, 221-- which in this case are partitions of rlp that are themselves 222-- list-partitioned on b 223explain (costs off) select * from rlp where a = 15 and b <> 'ab' and b <> 'cd' and b <> 'xy' and b is not null; 224 225-- 226-- different collations for different keys with same expression 227-- 228create table coll_pruning_multi (a text) partition by range (substr(a, 1) collate "POSIX", substr(a, 1) collate "C"); 229create table coll_pruning_multi1 partition of coll_pruning_multi for values from ('a', 'a') to ('a', 'e'); 230create table coll_pruning_multi2 partition of coll_pruning_multi for values from ('a', 'e') to ('a', 'z'); 231create table coll_pruning_multi3 partition of coll_pruning_multi for values from ('b', 'a') to ('b', 'e'); 232 233-- no pruning, because no value for the leading key 234explain (costs off) select * from coll_pruning_multi where substr(a, 1) = 'e' collate "C"; 235 236-- pruning, with a value provided for the leading key 237explain (costs off) select * from coll_pruning_multi where substr(a, 1) = 'a' collate "POSIX"; 238 239-- pruning, with values provided for both keys 240explain (costs off) select * from coll_pruning_multi where substr(a, 1) = 'e' collate "C" and substr(a, 1) = 'a' collate "POSIX"; 241 242-- 243-- LIKE operators don't prune 244-- 245create table like_op_noprune (a text) partition by list (a); 246create table like_op_noprune1 partition of like_op_noprune for values in ('ABC'); 247create table like_op_noprune2 partition of like_op_noprune for values in ('BCD'); 248explain (costs off) select * from like_op_noprune where a like '%BC'; 249 250-- 251-- tests wherein clause value requires a cross-type comparison function 252-- 253create table lparted_by_int2 (a smallint) partition by list (a); 254create table lparted_by_int2_1 partition of lparted_by_int2 for values in (1); 255create table lparted_by_int2_16384 partition of lparted_by_int2 for values in (16384); 256explain (costs off) select * from lparted_by_int2 where a = 100000000000000; 257 258create table rparted_by_int2 (a smallint) partition by range (a); 259create table rparted_by_int2_1 partition of rparted_by_int2 for values from (1) to (10); 260create table rparted_by_int2_16384 partition of rparted_by_int2 for values from (10) to (16384); 261-- all partitions pruned 262explain (costs off) select * from rparted_by_int2 where a > 100000000000000; 263create table rparted_by_int2_maxvalue partition of rparted_by_int2 for values from (16384) to (maxvalue); 264-- all partitions but rparted_by_int2_maxvalue pruned 265explain (costs off) select * from rparted_by_int2 where a > 100000000000000; 266 267drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, rp, coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2; 268 269-- 270-- Test Partition pruning for HASH partitioning 271-- 272-- Use hand-rolled hash functions and operator classes to get predictable 273-- result on different matchines. See the definitions of 274-- part_part_test_int4_ops and part_test_text_ops in insert.sql. 275-- 276 277create table hp (a int, b text) partition by hash (a part_test_int4_ops, b part_test_text_ops); 278create table hp0 partition of hp for values with (modulus 4, remainder 0); 279create table hp3 partition of hp for values with (modulus 4, remainder 3); 280create table hp1 partition of hp for values with (modulus 4, remainder 1); 281create table hp2 partition of hp for values with (modulus 4, remainder 2); 282 283insert into hp values (null, null); 284insert into hp values (1, null); 285insert into hp values (1, 'xxx'); 286insert into hp values (null, 'xxx'); 287insert into hp values (2, 'xxx'); 288insert into hp values (1, 'abcde'); 289select tableoid::regclass, * from hp order by 1; 290 291-- partial keys won't prune, nor would non-equality conditions 292explain (costs off) select * from hp where a = 1; 293explain (costs off) select * from hp where b = 'xxx'; 294explain (costs off) select * from hp where a is null; 295explain (costs off) select * from hp where b is null; 296explain (costs off) select * from hp where a < 1 and b = 'xxx'; 297explain (costs off) select * from hp where a <> 1 and b = 'yyy'; 298explain (costs off) select * from hp where a <> 1 and b <> 'xxx'; 299 300-- pruning should work if either a value or a IS NULL clause is provided for 301-- each of the keys 302explain (costs off) select * from hp where a is null and b is null; 303explain (costs off) select * from hp where a = 1 and b is null; 304explain (costs off) select * from hp where a = 1 and b = 'xxx'; 305explain (costs off) select * from hp where a is null and b = 'xxx'; 306explain (costs off) select * from hp where a = 2 and b = 'xxx'; 307explain (costs off) select * from hp where a = 1 and b = 'abcde'; 308explain (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); 309 310drop table hp; 311 312-- 313-- Test runtime partition pruning 314-- 315create table ab (a int not null, b int not null) partition by list (a); 316create table ab_a2 partition of ab for values in(2) partition by list (b); 317create table ab_a2_b1 partition of ab_a2 for values in (1); 318create table ab_a2_b2 partition of ab_a2 for values in (2); 319create table ab_a2_b3 partition of ab_a2 for values in (3); 320create table ab_a1 partition of ab for values in(1) partition by list (b); 321create table ab_a1_b1 partition of ab_a1 for values in (1); 322create table ab_a1_b2 partition of ab_a1 for values in (2); 323create table ab_a1_b3 partition of ab_a1 for values in (3); 324create table ab_a3 partition of ab for values in(3) partition by list (b); 325create table ab_a3_b1 partition of ab_a3 for values in (1); 326create table ab_a3_b2 partition of ab_a3 for values in (2); 327create table ab_a3_b3 partition of ab_a3 for values in (3); 328 329-- Disallow index only scans as concurrent transactions may stop visibility 330-- bits being set causing "Heap Fetches" to be unstable in the EXPLAIN ANALYZE 331-- output. 332set enable_indexonlyscan = off; 333 334prepare ab_q1 (int, int, int) as 335select * from ab where a between $1 and $2 and b <= $3; 336 337-- Execute query 5 times to allow choose_custom_plan 338-- to start considering a generic plan. 339execute ab_q1 (1, 8, 3); 340execute ab_q1 (1, 8, 3); 341execute ab_q1 (1, 8, 3); 342execute ab_q1 (1, 8, 3); 343execute ab_q1 (1, 8, 3); 344 345explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3); 346explain (analyze, costs off, summary off, timing off) execute ab_q1 (1, 2, 3); 347 348deallocate ab_q1; 349 350-- Runtime pruning after optimizer pruning 351prepare ab_q1 (int, int) as 352select a from ab where a between $1 and $2 and b < 3; 353 354-- Execute query 5 times to allow choose_custom_plan 355-- to start considering a generic plan. 356execute ab_q1 (1, 8); 357execute ab_q1 (1, 8); 358execute ab_q1 (1, 8); 359execute ab_q1 (1, 8); 360execute ab_q1 (1, 8); 361 362explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2); 363explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4); 364 365-- Ensure a mix of PARAM_EXTERN and PARAM_EXEC Params work together at 366-- different levels of partitioning. 367prepare ab_q2 (int, int) as 368select a from ab where a between $1 and $2 and b < (select 3); 369 370execute ab_q2 (1, 8); 371execute ab_q2 (1, 8); 372execute ab_q2 (1, 8); 373execute ab_q2 (1, 8); 374execute ab_q2 (1, 8); 375 376explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2); 377 378-- As above, but swap the PARAM_EXEC Param to the first partition level 379prepare ab_q3 (int, int) as 380select a from ab where b between $1 and $2 and a < (select 3); 381 382execute ab_q3 (1, 8); 383execute ab_q3 (1, 8); 384execute ab_q3 (1, 8); 385execute ab_q3 (1, 8); 386execute ab_q3 (1, 8); 387 388explain (analyze, costs off, summary off, timing off) execute ab_q3 (2, 2); 389 390-- Test a backwards Append scan 391create table list_part (a int) partition by list (a); 392create table list_part1 partition of list_part for values in (1); 393create table list_part2 partition of list_part for values in (2); 394create table list_part3 partition of list_part for values in (3); 395create table list_part4 partition of list_part for values in (4); 396 397insert into list_part select generate_series(1,4); 398 399begin; 400 401-- Don't select an actual value out of the table as the order of the Append's 402-- subnodes may not be stable. 403declare cur SCROLL CURSOR for select 1 from list_part where a > (select 1) and a < (select 4); 404 405-- move beyond the final row 406move 3 from cur; 407 408-- Ensure we get two rows. 409fetch backward all from cur; 410 411commit; 412 413begin; 414 415-- Test run-time pruning using stable functions 416create function list_part_fn(int) returns int as $$ begin return $1; end;$$ language plpgsql stable; 417 418-- Ensure pruning works using a stable function containing no Vars 419explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1); 420 421-- Ensure pruning does not take place when the function has a Var parameter 422explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(a); 423 424-- Ensure pruning does not take place when the expression contains a Var. 425explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1) + a; 426 427rollback; 428 429drop table list_part; 430 431-- Parallel append 432 433-- Suppress the number of loops each parallel node runs for. This is because 434-- more than one worker may run the same parallel node if timing conditions 435-- are just right, which destabilizes the test. 436create function explain_parallel_append(text) returns setof text 437language plpgsql as 438$$ 439declare 440 ln text; 441begin 442 for ln in 443 execute format('explain (analyze, costs off, summary off, timing off) %s', 444 $1) 445 loop 446 if ln like '%Parallel%' then 447 ln := regexp_replace(ln, 'loops=\d*', 'loops=N'); 448 end if; 449 return next ln; 450 end loop; 451end; 452$$; 453 454prepare ab_q4 (int, int) as 455select avg(a) from ab where a between $1 and $2 and b < 4; 456 457-- Encourage use of parallel plans 458set parallel_setup_cost = 0; 459set parallel_tuple_cost = 0; 460set min_parallel_table_scan_size = 0; 461set max_parallel_workers_per_gather = 2; 462 463-- Execute query 5 times to allow choose_custom_plan 464-- to start considering a generic plan. 465execute ab_q4 (1, 8); 466execute ab_q4 (1, 8); 467execute ab_q4 (1, 8); 468execute ab_q4 (1, 8); 469execute ab_q4 (1, 8); 470select explain_parallel_append('execute ab_q4 (2, 2)'); 471 472-- Test run-time pruning with IN lists. 473prepare ab_q5 (int, int, int) as 474select avg(a) from ab where a in($1,$2,$3) and b < 4; 475 476-- Execute query 5 times to allow choose_custom_plan 477-- to start considering a generic plan. 478execute ab_q5 (1, 2, 3); 479execute ab_q5 (1, 2, 3); 480execute ab_q5 (1, 2, 3); 481execute ab_q5 (1, 2, 3); 482execute ab_q5 (1, 2, 3); 483 484select explain_parallel_append('execute ab_q5 (1, 1, 1)'); 485select explain_parallel_append('execute ab_q5 (2, 3, 3)'); 486 487-- Try some params whose values do not belong to any partition. 488-- We'll still get a single subplan in this case, but it should not be scanned. 489select explain_parallel_append('execute ab_q5 (33, 44, 55)'); 490 491-- Test Parallel Append with PARAM_EXEC Params 492select explain_parallel_append('select count(*) from ab where (a = (select 1) or a = (select 3)) and b = 2'); 493 494-- Test pruning during parallel nested loop query 495create table lprt_a (a int not null); 496-- Insert some values we won't find in ab 497insert into lprt_a select 0 from generate_series(1,100); 498 499-- and insert some values that we should find. 500insert into lprt_a values(1),(1); 501 502analyze lprt_a; 503 504create index ab_a2_b1_a_idx on ab_a2_b1 (a); 505create index ab_a2_b2_a_idx on ab_a2_b2 (a); 506create index ab_a2_b3_a_idx on ab_a2_b3 (a); 507create index ab_a1_b1_a_idx on ab_a1_b1 (a); 508create index ab_a1_b2_a_idx on ab_a1_b2 (a); 509create index ab_a1_b3_a_idx on ab_a1_b3 (a); 510create index ab_a3_b1_a_idx on ab_a3_b1 (a); 511create index ab_a3_b2_a_idx on ab_a3_b2 (a); 512create index ab_a3_b3_a_idx on ab_a3_b3 (a); 513 514set enable_hashjoin = 0; 515set enable_mergejoin = 0; 516 517select 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)'); 518 519-- Ensure the same partitions are pruned when we make the nested loop 520-- parameter an Expr rather than a plain Param. 521select 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)'); 522 523insert into lprt_a values(3),(3); 524 525select 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)'); 526select 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)'); 527 528delete from lprt_a where a = 1; 529 530select 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)'); 531 532reset enable_hashjoin; 533reset enable_mergejoin; 534reset parallel_setup_cost; 535reset parallel_tuple_cost; 536reset min_parallel_table_scan_size; 537reset max_parallel_workers_per_gather; 538 539-- Test run-time partition pruning with an initplan 540explain (analyze, costs off, summary off, timing off) 541select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a); 542 543-- Test run-time partition pruning with UNION ALL parents 544explain (analyze, costs off, summary off, timing off) 545select * from (select * from ab where a = 1 union all select * from ab) ab where b = (select 1); 546 547-- A case containing a UNION ALL with a non-partitioned child. 548explain (analyze, costs off, summary off, timing off) 549select * from (select * from ab where a = 1 union all (values(10,5)) union all select * from ab) ab where b = (select 1); 550 551-- Another UNION ALL test, but containing a mix of exec init and exec run-time pruning. 552create table xy_1 (x int, y int); 553insert into xy_1 values(100,-10); 554 555set enable_bitmapscan = 0; 556set enable_indexscan = 0; 557set plan_cache_mode = 'force_generic_plan'; 558 559prepare ab_q6 as 560select * from ( 561 select tableoid::regclass,a,b from ab 562union all 563 select tableoid::regclass,x,y from xy_1 564union all 565 select tableoid::regclass,a,b from ab 566) ab where a = $1 and b = (select -10); 567 568-- Ensure the xy_1 subplan is not pruned. 569explain (analyze, costs off, summary off, timing off) execute ab_q6(1); 570 571-- Ensure we see just the xy_1 row. 572execute ab_q6(100); 573 574reset enable_bitmapscan; 575reset enable_indexscan; 576reset plan_cache_mode; 577 578deallocate ab_q1; 579deallocate ab_q2; 580deallocate ab_q3; 581deallocate ab_q4; 582deallocate ab_q5; 583deallocate ab_q6; 584 585-- UPDATE on a partition subtree has been seen to have problems. 586insert into ab values (1,2); 587explain (analyze, costs off, summary off, timing off) 588update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a; 589table ab; 590 591-- Test UPDATE where source relation has run-time pruning enabled 592truncate ab; 593insert into ab values (1, 1), (1, 2), (1, 3), (2, 1); 594explain (analyze, costs off, summary off, timing off) 595update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1); 596select tableoid::regclass, * from ab; 597 598drop table ab, lprt_a; 599 600-- Join 601create table tbl1(col1 int); 602insert into tbl1 values (501), (505); 603 604-- Basic table 605create table tprt (col1 int) partition by range (col1); 606create table tprt_1 partition of tprt for values from (1) to (501); 607create table tprt_2 partition of tprt for values from (501) to (1001); 608create table tprt_3 partition of tprt for values from (1001) to (2001); 609create table tprt_4 partition of tprt for values from (2001) to (3001); 610create table tprt_5 partition of tprt for values from (3001) to (4001); 611create table tprt_6 partition of tprt for values from (4001) to (5001); 612 613create index tprt1_idx on tprt_1 (col1); 614create index tprt2_idx on tprt_2 (col1); 615create index tprt3_idx on tprt_3 (col1); 616create index tprt4_idx on tprt_4 (col1); 617create index tprt5_idx on tprt_5 (col1); 618create index tprt6_idx on tprt_6 (col1); 619 620insert into tprt values (10), (20), (501), (502), (505), (1001), (4500); 621 622set enable_hashjoin = off; 623set enable_mergejoin = off; 624 625explain (analyze, costs off, summary off, timing off) 626select * from tbl1 join tprt on tbl1.col1 > tprt.col1; 627 628explain (analyze, costs off, summary off, timing off) 629select * from tbl1 join tprt on tbl1.col1 = tprt.col1; 630 631select tbl1.col1, tprt.col1 from tbl1 632inner join tprt on tbl1.col1 > tprt.col1 633order by tbl1.col1, tprt.col1; 634 635select tbl1.col1, tprt.col1 from tbl1 636inner join tprt on tbl1.col1 = tprt.col1 637order by tbl1.col1, tprt.col1; 638 639-- Multiple partitions 640insert into tbl1 values (1001), (1010), (1011); 641explain (analyze, costs off, summary off, timing off) 642select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1; 643 644explain (analyze, costs off, summary off, timing off) 645select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1; 646 647select tbl1.col1, tprt.col1 from tbl1 648inner join tprt on tbl1.col1 > tprt.col1 649order by tbl1.col1, tprt.col1; 650 651select tbl1.col1, tprt.col1 from tbl1 652inner join tprt on tbl1.col1 = tprt.col1 653order by tbl1.col1, tprt.col1; 654 655-- Last partition 656delete from tbl1; 657insert into tbl1 values (4400); 658explain (analyze, costs off, summary off, timing off) 659select * from tbl1 join tprt on tbl1.col1 < tprt.col1; 660 661select tbl1.col1, tprt.col1 from tbl1 662inner join tprt on tbl1.col1 < tprt.col1 663order by tbl1.col1, tprt.col1; 664 665-- No matching partition 666delete from tbl1; 667insert into tbl1 values (10000); 668explain (analyze, costs off, summary off, timing off) 669select * from tbl1 join tprt on tbl1.col1 = tprt.col1; 670 671select tbl1.col1, tprt.col1 from tbl1 672inner join tprt on tbl1.col1 = tprt.col1 673order by tbl1.col1, tprt.col1; 674 675drop table tbl1, tprt; 676 677-- Test with columns defined in varying orders between each level 678create table part_abc (a int not null, b int not null, c int not null) partition by list (a); 679create table part_bac (b int not null, a int not null, c int not null) partition by list (b); 680create table part_cab (c int not null, a int not null, b int not null) partition by list (c); 681create table part_abc_p1 (a int not null, b int not null, c int not null); 682 683alter table part_abc attach partition part_bac for values in(1); 684alter table part_bac attach partition part_cab for values in(2); 685alter table part_cab attach partition part_abc_p1 for values in(3); 686 687prepare part_abc_q1 (int, int, int) as 688select * from part_abc where a = $1 and b = $2 and c = $3; 689 690-- Execute query 5 times to allow choose_custom_plan 691-- to start considering a generic plan. 692execute part_abc_q1 (1, 2, 3); 693execute part_abc_q1 (1, 2, 3); 694execute part_abc_q1 (1, 2, 3); 695execute part_abc_q1 (1, 2, 3); 696execute part_abc_q1 (1, 2, 3); 697 698-- Single partition should be scanned. 699explain (analyze, costs off, summary off, timing off) execute part_abc_q1 (1, 2, 3); 700 701deallocate part_abc_q1; 702 703drop table part_abc; 704 705-- Ensure that an Append node properly handles a sub-partitioned table 706-- matching without any of its leaf partitions matching the clause. 707create table listp (a int, b int) partition by list (a); 708create table listp_1 partition of listp for values in(1) partition by list (b); 709create table listp_1_1 partition of listp_1 for values in(1); 710create table listp_2 partition of listp for values in(2) partition by list (b); 711create table listp_2_1 partition of listp_2 for values in(2); 712select * from listp where b = 1; 713 714-- Ensure that an Append node properly can handle selection of all first level 715-- partitions before finally detecting the correct set of 2nd level partitions 716-- which match the given parameter. 717prepare q1 (int,int) as select * from listp where b in ($1,$2); 718 719execute q1 (1,2); 720execute q1 (1,2); 721execute q1 (1,2); 722execute q1 (1,2); 723execute q1 (1,2); 724 725explain (analyze, costs off, summary off, timing off) execute q1 (1,1); 726 727explain (analyze, costs off, summary off, timing off) execute q1 (2,2); 728 729-- Try with no matching partitions. One subplan should remain in this case, 730-- but it shouldn't be executed. 731explain (analyze, costs off, summary off, timing off) execute q1 (0,0); 732 733deallocate q1; 734 735-- Test more complex cases where a not-equal condition further eliminates partitions. 736prepare q1 (int,int,int,int) as select * from listp where b in($1,$2) and $3 <> b and $4 <> b; 737 738execute q1 (1,2,3,4); 739execute q1 (1,2,3,4); 740execute q1 (1,2,3,4); 741execute q1 (1,2,3,4); 742execute q1 (1,2,3,4); 743 744-- Both partitions allowed by IN clause, but one disallowed by <> clause 745explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,0); 746 747-- Both partitions allowed by IN clause, then both excluded again by <> clauses. 748-- One subplan will remain in this case, but it should not be executed. 749explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,1); 750 751-- Ensure Params that evaluate to NULL properly prune away all partitions 752explain (analyze, costs off, summary off, timing off) 753select * from listp where a = (select null::int); 754 755drop table listp; 756 757-- Ensure runtime pruning works with initplans params with boolean types 758create table boolvalues (value bool not null); 759insert into boolvalues values('t'),('f'); 760 761create table boolp (a bool) partition by list (a); 762create table boolp_t partition of boolp for values in('t'); 763create table boolp_f partition of boolp for values in('f'); 764 765explain (analyze, costs off, summary off, timing off) 766select * from boolp where a = (select value from boolvalues where value); 767 768explain (analyze, costs off, summary off, timing off) 769select * from boolp where a = (select value from boolvalues where not value); 770 771drop table boolp; 772 773-- 774-- Test run-time pruning of MergeAppend subnodes 775-- 776set enable_seqscan = off; 777set enable_sort = off; 778create table ma_test (a int, b int) partition by range (a); 779create table ma_test_p1 partition of ma_test for values from (0) to (10); 780create table ma_test_p2 partition of ma_test for values from (10) to (20); 781create table ma_test_p3 partition of ma_test for values from (20) to (30); 782insert into ma_test select x,x from generate_series(0,29) t(x); 783create index on ma_test (b); 784 785analyze ma_test; 786prepare mt_q1 (int) as select a from ma_test where a >= $1 and a % 10 = 5 order by b; 787 788-- Execute query 5 times to allow choose_custom_plan 789-- to start considering a generic plan. 790execute mt_q1(0); 791execute mt_q1(0); 792execute mt_q1(0); 793execute mt_q1(0); 794execute mt_q1(0); 795 796explain (analyze, costs off, summary off, timing off) execute mt_q1(15); 797execute mt_q1(15); 798explain (analyze, costs off, summary off, timing off) execute mt_q1(25); 799execute mt_q1(25); 800-- Ensure MergeAppend behaves correctly when no subplans match 801explain (analyze, costs off, summary off, timing off) execute mt_q1(35); 802execute mt_q1(35); 803 804deallocate mt_q1; 805 806-- ensure initplan params properly prune partitions 807explain (analyze, costs off, summary off, timing off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b; 808 809reset enable_seqscan; 810reset enable_sort; 811 812drop table ma_test; 813 814reset enable_indexonlyscan; 815 816-- 817-- check that pruning works properly when the partition key is of a 818-- pseudotype 819-- 820 821-- array type list partition key 822create table pp_arrpart (a int[]) partition by list (a); 823create table pp_arrpart1 partition of pp_arrpart for values in ('{1}'); 824create table pp_arrpart2 partition of pp_arrpart for values in ('{2, 3}', '{4, 5}'); 825explain (costs off) select * from pp_arrpart where a = '{1}'; 826explain (costs off) select * from pp_arrpart where a = '{1, 2}'; 827explain (costs off) select * from pp_arrpart where a in ('{4, 5}', '{1}'); 828explain (costs off) update pp_arrpart set a = a where a = '{1}'; 829explain (costs off) delete from pp_arrpart where a = '{1}'; 830drop table pp_arrpart; 831 832-- array type hash partition key 833create table pph_arrpart (a int[]) partition by hash (a); 834create table pph_arrpart1 partition of pph_arrpart for values with (modulus 2, remainder 0); 835create table pph_arrpart2 partition of pph_arrpart for values with (modulus 2, remainder 1); 836insert into pph_arrpart values ('{1}'), ('{1, 2}'), ('{4, 5}'); 837select tableoid::regclass, * from pph_arrpart order by 1; 838explain (costs off) select * from pph_arrpart where a = '{1}'; 839explain (costs off) select * from pph_arrpart where a = '{1, 2}'; 840explain (costs off) select * from pph_arrpart where a in ('{4, 5}', '{1}'); 841drop table pph_arrpart; 842 843-- enum type list partition key 844create type pp_colors as enum ('green', 'blue', 'black'); 845create table pp_enumpart (a pp_colors) partition by list (a); 846create table pp_enumpart_green partition of pp_enumpart for values in ('green'); 847create table pp_enumpart_blue partition of pp_enumpart for values in ('blue'); 848explain (costs off) select * from pp_enumpart where a = 'blue'; 849explain (costs off) select * from pp_enumpart where a = 'black'; 850drop table pp_enumpart; 851drop type pp_colors; 852 853-- record type as partition key 854create type pp_rectype as (a int, b int); 855create table pp_recpart (a pp_rectype) partition by list (a); 856create table pp_recpart_11 partition of pp_recpart for values in ('(1,1)'); 857create table pp_recpart_23 partition of pp_recpart for values in ('(2,3)'); 858explain (costs off) select * from pp_recpart where a = '(1,1)'::pp_rectype; 859explain (costs off) select * from pp_recpart where a = '(1,2)'::pp_rectype; 860drop table pp_recpart; 861drop type pp_rectype; 862 863-- range type partition key 864create table pp_intrangepart (a int4range) partition by list (a); 865create table pp_intrangepart12 partition of pp_intrangepart for values in ('[1,2]'); 866create table pp_intrangepart2inf partition of pp_intrangepart for values in ('[2,)'); 867explain (costs off) select * from pp_intrangepart where a = '[1,2]'::int4range; 868explain (costs off) select * from pp_intrangepart where a = '(1,2)'::int4range; 869drop table pp_intrangepart; 870 871-- 872-- Ensure the enable_partition_prune GUC properly disables partition pruning. 873-- 874 875create table pp_lp (a int, value int) partition by list (a); 876create table pp_lp1 partition of pp_lp for values in(1); 877create table pp_lp2 partition of pp_lp for values in(2); 878 879explain (costs off) select * from pp_lp where a = 1; 880explain (costs off) update pp_lp set value = 10 where a = 1; 881explain (costs off) delete from pp_lp where a = 1; 882 883set enable_partition_pruning = off; 884 885set constraint_exclusion = 'partition'; -- this should not affect the result. 886 887explain (costs off) select * from pp_lp where a = 1; 888explain (costs off) update pp_lp set value = 10 where a = 1; 889explain (costs off) delete from pp_lp where a = 1; 890 891set constraint_exclusion = 'off'; -- this should not affect the result. 892 893explain (costs off) select * from pp_lp where a = 1; 894explain (costs off) update pp_lp set value = 10 where a = 1; 895explain (costs off) delete from pp_lp where a = 1; 896 897drop table pp_lp; 898 899-- Ensure enable_partition_prune does not affect non-partitioned tables. 900 901create table inh_lp (a int, value int); 902create table inh_lp1 (a int, value int, check(a = 1)) inherits (inh_lp); 903create table inh_lp2 (a int, value int, check(a = 2)) inherits (inh_lp); 904 905set constraint_exclusion = 'partition'; 906 907-- inh_lp2 should be removed in the following 3 cases. 908explain (costs off) select * from inh_lp where a = 1; 909explain (costs off) update inh_lp set value = 10 where a = 1; 910explain (costs off) delete from inh_lp where a = 1; 911 912-- Ensure we don't exclude normal relations when we only expect to exclude 913-- inheritance children 914explain (costs off) update inh_lp1 set value = 10 where a = 2; 915 916drop table inh_lp cascade; 917 918reset enable_partition_pruning; 919reset constraint_exclusion; 920 921-- Check pruning for a partition tree containing only temporary relations 922create temp table pp_temp_parent (a int) partition by list (a); 923create temp table pp_temp_part_1 partition of pp_temp_parent for values in (1); 924create temp table pp_temp_part_def partition of pp_temp_parent default; 925explain (costs off) select * from pp_temp_parent where true; 926explain (costs off) select * from pp_temp_parent where a = 2; 927drop table pp_temp_parent; 928 929-- Stress run-time partition pruning a bit more, per bug reports 930create temp table p (a int, b int, c int) partition by list (a); 931create temp table p1 partition of p for values in (1); 932create temp table p2 partition of p for values in (2); 933create temp table q (a int, b int, c int) partition by list (a); 934create temp table q1 partition of q for values in (1) partition by list (b); 935create temp table q11 partition of q1 for values in (1) partition by list (c); 936create temp table q111 partition of q11 for values in (1); 937create temp table q2 partition of q for values in (2) partition by list (b); 938create temp table q21 partition of q2 for values in (1); 939create temp table q22 partition of q2 for values in (2); 940 941insert into q22 values (2, 2, 3); 942 943explain (costs off) 944select * 945from ( 946 select * from p 947 union all 948 select * from q1 949 union all 950 select 1, 1, 1 951 ) s(a, b, c) 952where s.a = 1 and s.b = 1 and s.c = (select 1); 953 954select * 955from ( 956 select * from p 957 union all 958 select * from q1 959 union all 960 select 1, 1, 1 961 ) s(a, b, c) 962where s.a = 1 and s.b = 1 and s.c = (select 1); 963 964prepare q (int, int) as 965select * 966from ( 967 select * from p 968 union all 969 select * from q1 970 union all 971 select 1, 1, 1 972 ) s(a, b, c) 973where s.a = $1 and s.b = $2 and s.c = (select 1); 974 975set plan_cache_mode to force_generic_plan; 976 977explain (costs off) execute q (1, 1); 978execute q (1, 1); 979 980reset plan_cache_mode; 981drop table p, q; 982 983-- Ensure run-time pruning works correctly when we match a partitioned table 984-- on the first level but find no matching partitions on the second level. 985create table listp (a int, b int) partition by list (a); 986create table listp1 partition of listp for values in(1); 987create table listp2 partition of listp for values in(2) partition by list(b); 988create table listp2_10 partition of listp2 for values in (10); 989 990explain (analyze, costs off, summary off, timing off) 991select * from listp where a = (select 2) and b <> 10; 992 993-- 994-- check that a partition directly accessed in a query is excluded with 995-- constraint_exclusion = on 996-- 997 998-- turn off partition pruning, so that it doesn't interfere 999set enable_partition_pruning to off; 1000 1001-- setting constraint_exclusion to 'partition' disables exclusion 1002set constraint_exclusion to 'partition'; 1003explain (costs off) select * from listp1 where a = 2; 1004explain (costs off) update listp1 set a = 1 where a = 2; 1005-- constraint exclusion enabled 1006set constraint_exclusion to 'on'; 1007explain (costs off) select * from listp1 where a = 2; 1008explain (costs off) update listp1 set a = 1 where a = 2; 1009 1010reset constraint_exclusion; 1011reset enable_partition_pruning; 1012 1013drop table listp; 1014