diff options
Diffstat (limited to 'src/test/regress/expected/partition_prune.out')
| -rw-r--r-- | src/test/regress/expected/partition_prune.out | 4287 |
1 files changed, 4287 insertions, 0 deletions
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out new file mode 100644 index 0000000..a820385 --- /dev/null +++ b/src/test/regress/expected/partition_prune.out @@ -0,0 +1,4287 @@ +-- +-- Test partitioning planner code +-- +-- Force generic plans to be used for all prepared statements in this file. +set plan_cache_mode = force_generic_plan; +create table lp (a char) partition by list (a); +create table lp_default partition of lp default; +create table lp_ef partition of lp for values in ('e', 'f'); +create table lp_ad partition of lp for values in ('a', 'd'); +create table lp_bc partition of lp for values in ('b', 'c'); +create table lp_g partition of lp for values in ('g'); +create table lp_null partition of lp for values in (null); +explain (costs off) select * from lp; + QUERY PLAN +----------------------------------- + Append + -> Seq Scan on lp_ad lp_1 + -> Seq Scan on lp_bc lp_2 + -> Seq Scan on lp_ef lp_3 + -> Seq Scan on lp_g lp_4 + -> Seq Scan on lp_null lp_5 + -> Seq Scan on lp_default lp_6 +(7 rows) + +explain (costs off) select * from lp where a > 'a' and a < 'd'; + QUERY PLAN +----------------------------------------------------------- + Append + -> Seq Scan on lp_bc lp_1 + Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar)) + -> Seq Scan on lp_default lp_2 + Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar)) +(5 rows) + +explain (costs off) select * from lp where a > 'a' and a <= 'd'; + QUERY PLAN +------------------------------------------------------------ + Append + -> Seq Scan on lp_ad lp_1 + Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar)) + -> Seq Scan on lp_bc lp_2 + Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar)) + -> Seq Scan on lp_default lp_3 + Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar)) +(7 rows) + +explain (costs off) select * from lp where a = 'a'; + QUERY PLAN +----------------------------- + Seq Scan on lp_ad lp + Filter: (a = 'a'::bpchar) +(2 rows) + +explain (costs off) select * from lp where 'a' = a; /* commuted */ + QUERY PLAN +----------------------------- + Seq Scan on lp_ad lp + Filter: ('a'::bpchar = a) +(2 rows) + +explain (costs off) select * from lp where a is not null; + QUERY PLAN +----------------------------------- + Append + -> Seq Scan on lp_ad lp_1 + Filter: (a IS NOT NULL) + -> Seq Scan on lp_bc lp_2 + Filter: (a IS NOT NULL) + -> Seq Scan on lp_ef lp_3 + Filter: (a IS NOT NULL) + -> Seq Scan on lp_g lp_4 + Filter: (a IS NOT NULL) + -> Seq Scan on lp_default lp_5 + Filter: (a IS NOT NULL) +(11 rows) + +explain (costs off) select * from lp where a is null; + QUERY PLAN +------------------------ + Seq Scan on lp_null lp + Filter: (a IS NULL) +(2 rows) + +explain (costs off) select * from lp where a = 'a' or a = 'c'; + QUERY PLAN +---------------------------------------------------------- + Append + -> Seq Scan on lp_ad lp_1 + Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar)) + -> Seq Scan on lp_bc lp_2 + Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar)) +(5 rows) + +explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c'); + QUERY PLAN +-------------------------------------------------------------------------------- + Append + -> Seq Scan on lp_ad lp_1 + Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar))) + -> Seq Scan on lp_bc lp_2 + Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar))) +(5 rows) + +explain (costs off) select * from lp where a <> 'g'; + QUERY PLAN +------------------------------------ + Append + -> Seq Scan on lp_ad lp_1 + Filter: (a <> 'g'::bpchar) + -> Seq Scan on lp_bc lp_2 + Filter: (a <> 'g'::bpchar) + -> Seq Scan on lp_ef lp_3 + Filter: (a <> 'g'::bpchar) + -> Seq Scan on lp_default lp_4 + Filter: (a <> 'g'::bpchar) +(9 rows) + +explain (costs off) select * from lp where a <> 'a' and a <> 'd'; + QUERY PLAN +------------------------------------------------------------- + Append + -> Seq Scan on lp_bc lp_1 + Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) + -> Seq Scan on lp_ef lp_2 + Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) + -> Seq Scan on lp_g lp_3 + Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) + -> Seq Scan on lp_default lp_4 + Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) +(9 rows) + +explain (costs off) select * from lp where a not in ('a', 'd'); + QUERY PLAN +------------------------------------------------ + Append + -> Seq Scan on lp_bc lp_1 + Filter: (a <> ALL ('{a,d}'::bpchar[])) + -> Seq Scan on lp_ef lp_2 + Filter: (a <> ALL ('{a,d}'::bpchar[])) + -> Seq Scan on lp_g lp_3 + Filter: (a <> ALL ('{a,d}'::bpchar[])) + -> Seq Scan on lp_default lp_4 + Filter: (a <> ALL ('{a,d}'::bpchar[])) +(9 rows) + +-- collation matches the partitioning collation, pruning works +create table coll_pruning (a text collate "C") partition by list (a); +create table coll_pruning_a partition of coll_pruning for values in ('a'); +create table coll_pruning_b partition of coll_pruning for values in ('b'); +create table coll_pruning_def partition of coll_pruning default; +explain (costs off) select * from coll_pruning where a collate "C" = 'a' collate "C"; + QUERY PLAN +----------------------------------------- + Seq Scan on coll_pruning_a coll_pruning + Filter: (a = 'a'::text COLLATE "C") +(2 rows) + +-- collation doesn't match the partitioning collation, no pruning occurs +explain (costs off) select * from coll_pruning where a collate "POSIX" = 'a' collate "POSIX"; + QUERY PLAN +--------------------------------------------------------- + Append + -> Seq Scan on coll_pruning_a coll_pruning_1 + Filter: ((a)::text = 'a'::text COLLATE "POSIX") + -> Seq Scan on coll_pruning_b coll_pruning_2 + Filter: ((a)::text = 'a'::text COLLATE "POSIX") + -> Seq Scan on coll_pruning_def coll_pruning_3 + Filter: ((a)::text = 'a'::text COLLATE "POSIX") +(7 rows) + +create table rlp (a int, b varchar) partition by range (a); +create table rlp_default partition of rlp default partition by list (a); +create table rlp_default_default partition of rlp_default default; +create table rlp_default_10 partition of rlp_default for values in (10); +create table rlp_default_30 partition of rlp_default for values in (30); +create table rlp_default_null partition of rlp_default for values in (null); +create table rlp1 partition of rlp for values from (minvalue) to (1); +create table rlp2 partition of rlp for values from (1) to (10); +create table rlp3 (b varchar, a int) partition by list (b varchar_ops); +create table rlp3_default partition of rlp3 default; +create table rlp3abcd partition of rlp3 for values in ('ab', 'cd'); +create table rlp3efgh partition of rlp3 for values in ('ef', 'gh'); +create table rlp3nullxy partition of rlp3 for values in (null, 'xy'); +alter table rlp attach partition rlp3 for values from (15) to (20); +create table rlp4 partition of rlp for values from (20) to (30) partition by range (a); +create table rlp4_default partition of rlp4 default; +create table rlp4_1 partition of rlp4 for values from (20) to (25); +create table rlp4_2 partition of rlp4 for values from (25) to (29); +create table rlp5 partition of rlp for values from (31) to (maxvalue) partition by range (a); +create table rlp5_default partition of rlp5 default; +create table rlp5_1 partition of rlp5 for values from (31) to (40); +explain (costs off) select * from rlp where a < 1; + QUERY PLAN +---------------------- + Seq Scan on rlp1 rlp + Filter: (a < 1) +(2 rows) + +explain (costs off) select * from rlp where 1 > a; /* commuted */ + QUERY PLAN +---------------------- + Seq Scan on rlp1 rlp + Filter: (1 > a) +(2 rows) + +explain (costs off) select * from rlp where a <= 1; + QUERY PLAN +------------------------------ + Append + -> Seq Scan on rlp1 rlp_1 + Filter: (a <= 1) + -> Seq Scan on rlp2 rlp_2 + Filter: (a <= 1) +(5 rows) + +explain (costs off) select * from rlp where a = 1; + QUERY PLAN +---------------------- + Seq Scan on rlp2 rlp + Filter: (a = 1) +(2 rows) + +explain (costs off) select * from rlp where a = 1::bigint; /* same as above */ + QUERY PLAN +----------------------------- + Seq Scan on rlp2 rlp + Filter: (a = '1'::bigint) +(2 rows) + +explain (costs off) select * from rlp where a = 1::numeric; /* no pruning */ + QUERY PLAN +----------------------------------------------- + Append + -> Seq Scan on rlp1 rlp_1 + Filter: ((a)::numeric = '1'::numeric) + -> Seq Scan on rlp2 rlp_2 + Filter: ((a)::numeric = '1'::numeric) + -> Seq Scan on rlp3abcd rlp_3 + Filter: ((a)::numeric = '1'::numeric) + -> Seq Scan on rlp3efgh rlp_4 + Filter: ((a)::numeric = '1'::numeric) + -> Seq Scan on rlp3nullxy rlp_5 + Filter: ((a)::numeric = '1'::numeric) + -> Seq Scan on rlp3_default rlp_6 + Filter: ((a)::numeric = '1'::numeric) + -> Seq Scan on rlp4_1 rlp_7 + Filter: ((a)::numeric = '1'::numeric) + -> Seq Scan on rlp4_2 rlp_8 + Filter: ((a)::numeric = '1'::numeric) + -> Seq Scan on rlp4_default rlp_9 + Filter: ((a)::numeric = '1'::numeric) + -> Seq Scan on rlp5_1 rlp_10 + Filter: ((a)::numeric = '1'::numeric) + -> Seq Scan on rlp5_default rlp_11 + Filter: ((a)::numeric = '1'::numeric) + -> Seq Scan on rlp_default_10 rlp_12 + Filter: ((a)::numeric = '1'::numeric) + -> Seq Scan on rlp_default_30 rlp_13 + Filter: ((a)::numeric = '1'::numeric) + -> Seq Scan on rlp_default_null rlp_14 + Filter: ((a)::numeric = '1'::numeric) + -> Seq Scan on rlp_default_default rlp_15 + Filter: ((a)::numeric = '1'::numeric) +(31 rows) + +explain (costs off) select * from rlp where a <= 10; + QUERY PLAN +--------------------------------------------- + Append + -> Seq Scan on rlp1 rlp_1 + Filter: (a <= 10) + -> Seq Scan on rlp2 rlp_2 + Filter: (a <= 10) + -> Seq Scan on rlp_default_10 rlp_3 + Filter: (a <= 10) + -> Seq Scan on rlp_default_default rlp_4 + Filter: (a <= 10) +(9 rows) + +explain (costs off) select * from rlp where a > 10; + QUERY PLAN +---------------------------------------------- + Append + -> Seq Scan on rlp3abcd rlp_1 + Filter: (a > 10) + -> Seq Scan on rlp3efgh rlp_2 + Filter: (a > 10) + -> Seq Scan on rlp3nullxy rlp_3 + Filter: (a > 10) + -> Seq Scan on rlp3_default rlp_4 + Filter: (a > 10) + -> Seq Scan on rlp4_1 rlp_5 + Filter: (a > 10) + -> Seq Scan on rlp4_2 rlp_6 + Filter: (a > 10) + -> Seq Scan on rlp4_default rlp_7 + Filter: (a > 10) + -> Seq Scan on rlp5_1 rlp_8 + Filter: (a > 10) + -> Seq Scan on rlp5_default rlp_9 + Filter: (a > 10) + -> Seq Scan on rlp_default_30 rlp_10 + Filter: (a > 10) + -> Seq Scan on rlp_default_default rlp_11 + Filter: (a > 10) +(23 rows) + +explain (costs off) select * from rlp where a < 15; + QUERY PLAN +--------------------------------------------- + Append + -> Seq Scan on rlp1 rlp_1 + Filter: (a < 15) + -> Seq Scan on rlp2 rlp_2 + Filter: (a < 15) + -> Seq Scan on rlp_default_10 rlp_3 + Filter: (a < 15) + -> Seq Scan on rlp_default_default rlp_4 + Filter: (a < 15) +(9 rows) + +explain (costs off) select * from rlp where a <= 15; + QUERY PLAN +--------------------------------------------- + Append + -> Seq Scan on rlp1 rlp_1 + Filter: (a <= 15) + -> Seq Scan on rlp2 rlp_2 + Filter: (a <= 15) + -> Seq Scan on rlp3abcd rlp_3 + Filter: (a <= 15) + -> Seq Scan on rlp3efgh rlp_4 + Filter: (a <= 15) + -> Seq Scan on rlp3nullxy rlp_5 + Filter: (a <= 15) + -> Seq Scan on rlp3_default rlp_6 + Filter: (a <= 15) + -> Seq Scan on rlp_default_10 rlp_7 + Filter: (a <= 15) + -> Seq Scan on rlp_default_default rlp_8 + Filter: (a <= 15) +(17 rows) + +explain (costs off) select * from rlp where a > 15 and b = 'ab'; + QUERY PLAN +--------------------------------------------------------- + Append + -> Seq Scan on rlp3abcd rlp_1 + Filter: ((a > 15) AND ((b)::text = 'ab'::text)) + -> Seq Scan on rlp4_1 rlp_2 + Filter: ((a > 15) AND ((b)::text = 'ab'::text)) + -> Seq Scan on rlp4_2 rlp_3 + Filter: ((a > 15) AND ((b)::text = 'ab'::text)) + -> Seq Scan on rlp4_default rlp_4 + Filter: ((a > 15) AND ((b)::text = 'ab'::text)) + -> Seq Scan on rlp5_1 rlp_5 + Filter: ((a > 15) AND ((b)::text = 'ab'::text)) + -> Seq Scan on rlp5_default rlp_6 + Filter: ((a > 15) AND ((b)::text = 'ab'::text)) + -> Seq Scan on rlp_default_30 rlp_7 + Filter: ((a > 15) AND ((b)::text = 'ab'::text)) + -> Seq Scan on rlp_default_default rlp_8 + Filter: ((a > 15) AND ((b)::text = 'ab'::text)) +(17 rows) + +explain (costs off) select * from rlp where a = 16; + QUERY PLAN +-------------------------------------- + Append + -> Seq Scan on rlp3abcd rlp_1 + Filter: (a = 16) + -> Seq Scan on rlp3efgh rlp_2 + Filter: (a = 16) + -> Seq Scan on rlp3nullxy rlp_3 + Filter: (a = 16) + -> Seq Scan on rlp3_default rlp_4 + Filter: (a = 16) +(9 rows) + +explain (costs off) select * from rlp where a = 16 and b in ('not', 'in', 'here'); + QUERY PLAN +---------------------------------------------------------------------- + Seq Scan on rlp3_default rlp + Filter: ((a = 16) AND ((b)::text = ANY ('{not,in,here}'::text[]))) +(2 rows) + +explain (costs off) select * from rlp where a = 16 and b < 'ab'; + QUERY PLAN +--------------------------------------------------- + Seq Scan on rlp3_default rlp + Filter: (((b)::text < 'ab'::text) AND (a = 16)) +(2 rows) + +explain (costs off) select * from rlp where a = 16 and b <= 'ab'; + QUERY PLAN +---------------------------------------------------------- + Append + -> Seq Scan on rlp3abcd rlp_1 + Filter: (((b)::text <= 'ab'::text) AND (a = 16)) + -> Seq Scan on rlp3_default rlp_2 + Filter: (((b)::text <= 'ab'::text) AND (a = 16)) +(5 rows) + +explain (costs off) select * from rlp where a = 16 and b is null; + QUERY PLAN +-------------------------------------- + Seq Scan on rlp3nullxy rlp + Filter: ((b IS NULL) AND (a = 16)) +(2 rows) + +explain (costs off) select * from rlp where a = 16 and b is not null; + QUERY PLAN +------------------------------------------------ + Append + -> Seq Scan on rlp3abcd rlp_1 + Filter: ((b IS NOT NULL) AND (a = 16)) + -> Seq Scan on rlp3efgh rlp_2 + Filter: ((b IS NOT NULL) AND (a = 16)) + -> Seq Scan on rlp3nullxy rlp_3 + Filter: ((b IS NOT NULL) AND (a = 16)) + -> Seq Scan on rlp3_default rlp_4 + Filter: ((b IS NOT NULL) AND (a = 16)) +(9 rows) + +explain (costs off) select * from rlp where a is null; + QUERY PLAN +---------------------------------- + Seq Scan on rlp_default_null rlp + Filter: (a IS NULL) +(2 rows) + +explain (costs off) select * from rlp where a is not null; + QUERY PLAN +---------------------------------------------- + Append + -> Seq Scan on rlp1 rlp_1 + Filter: (a IS NOT NULL) + -> Seq Scan on rlp2 rlp_2 + Filter: (a IS NOT NULL) + -> Seq Scan on rlp3abcd rlp_3 + Filter: (a IS NOT NULL) + -> Seq Scan on rlp3efgh rlp_4 + Filter: (a IS NOT NULL) + -> Seq Scan on rlp3nullxy rlp_5 + Filter: (a IS NOT NULL) + -> Seq Scan on rlp3_default rlp_6 + Filter: (a IS NOT NULL) + -> Seq Scan on rlp4_1 rlp_7 + Filter: (a IS NOT NULL) + -> Seq Scan on rlp4_2 rlp_8 + Filter: (a IS NOT NULL) + -> Seq Scan on rlp4_default rlp_9 + Filter: (a IS NOT NULL) + -> Seq Scan on rlp5_1 rlp_10 + Filter: (a IS NOT NULL) + -> Seq Scan on rlp5_default rlp_11 + Filter: (a IS NOT NULL) + -> Seq Scan on rlp_default_10 rlp_12 + Filter: (a IS NOT NULL) + -> Seq Scan on rlp_default_30 rlp_13 + Filter: (a IS NOT NULL) + -> Seq Scan on rlp_default_default rlp_14 + Filter: (a IS NOT NULL) +(29 rows) + +explain (costs off) select * from rlp where a > 30; + QUERY PLAN +--------------------------------------------- + Append + -> Seq Scan on rlp5_1 rlp_1 + Filter: (a > 30) + -> Seq Scan on rlp5_default rlp_2 + Filter: (a > 30) + -> Seq Scan on rlp_default_default rlp_3 + Filter: (a > 30) +(7 rows) + +explain (costs off) select * from rlp where a = 30; /* only default is scanned */ + QUERY PLAN +-------------------------------- + Seq Scan on rlp_default_30 rlp + Filter: (a = 30) +(2 rows) + +explain (costs off) select * from rlp where a <= 31; + QUERY PLAN +---------------------------------------------- + Append + -> Seq Scan on rlp1 rlp_1 + Filter: (a <= 31) + -> Seq Scan on rlp2 rlp_2 + Filter: (a <= 31) + -> Seq Scan on rlp3abcd rlp_3 + Filter: (a <= 31) + -> Seq Scan on rlp3efgh rlp_4 + Filter: (a <= 31) + -> Seq Scan on rlp3nullxy rlp_5 + Filter: (a <= 31) + -> Seq Scan on rlp3_default rlp_6 + Filter: (a <= 31) + -> Seq Scan on rlp4_1 rlp_7 + Filter: (a <= 31) + -> Seq Scan on rlp4_2 rlp_8 + Filter: (a <= 31) + -> Seq Scan on rlp4_default rlp_9 + Filter: (a <= 31) + -> Seq Scan on rlp5_1 rlp_10 + Filter: (a <= 31) + -> Seq Scan on rlp_default_10 rlp_11 + Filter: (a <= 31) + -> Seq Scan on rlp_default_30 rlp_12 + Filter: (a <= 31) + -> Seq Scan on rlp_default_default rlp_13 + Filter: (a <= 31) +(27 rows) + +explain (costs off) select * from rlp where a = 1 or a = 7; + QUERY PLAN +-------------------------------- + Seq Scan on rlp2 rlp + Filter: ((a = 1) OR (a = 7)) +(2 rows) + +explain (costs off) select * from rlp where a = 1 or b = 'ab'; + QUERY PLAN +------------------------------------------------------- + Append + -> Seq Scan on rlp1 rlp_1 + Filter: ((a = 1) OR ((b)::text = 'ab'::text)) + -> Seq Scan on rlp2 rlp_2 + Filter: ((a = 1) OR ((b)::text = 'ab'::text)) + -> Seq Scan on rlp3abcd rlp_3 + Filter: ((a = 1) OR ((b)::text = 'ab'::text)) + -> Seq Scan on rlp4_1 rlp_4 + Filter: ((a = 1) OR ((b)::text = 'ab'::text)) + -> Seq Scan on rlp4_2 rlp_5 + Filter: ((a = 1) OR ((b)::text = 'ab'::text)) + -> Seq Scan on rlp4_default rlp_6 + Filter: ((a = 1) OR ((b)::text = 'ab'::text)) + -> Seq Scan on rlp5_1 rlp_7 + Filter: ((a = 1) OR ((b)::text = 'ab'::text)) + -> Seq Scan on rlp5_default rlp_8 + Filter: ((a = 1) OR ((b)::text = 'ab'::text)) + -> Seq Scan on rlp_default_10 rlp_9 + Filter: ((a = 1) OR ((b)::text = 'ab'::text)) + -> Seq Scan on rlp_default_30 rlp_10 + Filter: ((a = 1) OR ((b)::text = 'ab'::text)) + -> Seq Scan on rlp_default_null rlp_11 + Filter: ((a = 1) OR ((b)::text = 'ab'::text)) + -> Seq Scan on rlp_default_default rlp_12 + Filter: ((a = 1) OR ((b)::text = 'ab'::text)) +(25 rows) + +explain (costs off) select * from rlp where a > 20 and a < 27; + QUERY PLAN +----------------------------------------- + Append + -> Seq Scan on rlp4_1 rlp_1 + Filter: ((a > 20) AND (a < 27)) + -> Seq Scan on rlp4_2 rlp_2 + Filter: ((a > 20) AND (a < 27)) +(5 rows) + +explain (costs off) select * from rlp where a = 29; + QUERY PLAN +------------------------------ + Seq Scan on rlp4_default rlp + Filter: (a = 29) +(2 rows) + +explain (costs off) select * from rlp where a >= 29; + QUERY PLAN +--------------------------------------------- + Append + -> Seq Scan on rlp4_default rlp_1 + Filter: (a >= 29) + -> Seq Scan on rlp5_1 rlp_2 + Filter: (a >= 29) + -> Seq Scan on rlp5_default rlp_3 + Filter: (a >= 29) + -> Seq Scan on rlp_default_30 rlp_4 + Filter: (a >= 29) + -> Seq Scan on rlp_default_default rlp_5 + Filter: (a >= 29) +(11 rows) + +explain (costs off) select * from rlp where a < 1 or (a > 20 and a < 25); + QUERY PLAN +------------------------------------------------------ + Append + -> Seq Scan on rlp1 rlp_1 + Filter: ((a < 1) OR ((a > 20) AND (a < 25))) + -> Seq Scan on rlp4_1 rlp_2 + Filter: ((a < 1) OR ((a > 20) AND (a < 25))) +(5 rows) + +-- where clause contradicts sub-partition's constraint +explain (costs off) select * from rlp where a = 20 or a = 40; + QUERY PLAN +---------------------------------------- + Append + -> Seq Scan on rlp4_1 rlp_1 + Filter: ((a = 20) OR (a = 40)) + -> Seq Scan on rlp5_default rlp_2 + Filter: ((a = 20) OR (a = 40)) +(5 rows) + +explain (costs off) select * from rlp3 where a = 20; /* empty */ + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +-- redundant clauses are eliminated +explain (costs off) select * from rlp where a > 1 and a = 10; /* only default */ + QUERY PLAN +---------------------------------- + Seq Scan on rlp_default_10 rlp + Filter: ((a > 1) AND (a = 10)) +(2 rows) + +explain (costs off) select * from rlp where a > 1 and a >=15; /* rlp3 onwards, including default */ + QUERY PLAN +---------------------------------------------- + Append + -> Seq Scan on rlp3abcd rlp_1 + Filter: ((a > 1) AND (a >= 15)) + -> Seq Scan on rlp3efgh rlp_2 + Filter: ((a > 1) AND (a >= 15)) + -> Seq Scan on rlp3nullxy rlp_3 + Filter: ((a > 1) AND (a >= 15)) + -> Seq Scan on rlp3_default rlp_4 + Filter: ((a > 1) AND (a >= 15)) + -> Seq Scan on rlp4_1 rlp_5 + Filter: ((a > 1) AND (a >= 15)) + -> Seq Scan on rlp4_2 rlp_6 + Filter: ((a > 1) AND (a >= 15)) + -> Seq Scan on rlp4_default rlp_7 + Filter: ((a > 1) AND (a >= 15)) + -> Seq Scan on rlp5_1 rlp_8 + Filter: ((a > 1) AND (a >= 15)) + -> Seq Scan on rlp5_default rlp_9 + Filter: ((a > 1) AND (a >= 15)) + -> Seq Scan on rlp_default_30 rlp_10 + Filter: ((a > 1) AND (a >= 15)) + -> Seq Scan on rlp_default_default rlp_11 + Filter: ((a > 1) AND (a >= 15)) +(23 rows) + +explain (costs off) select * from rlp where a = 1 and a = 3; /* empty */ + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a = 15); + QUERY PLAN +------------------------------------------------------------------- + Append + -> Seq Scan on rlp2 rlp_1 + Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15))) + -> Seq Scan on rlp3abcd rlp_2 + Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15))) + -> Seq Scan on rlp3efgh rlp_3 + Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15))) + -> Seq Scan on rlp3nullxy rlp_4 + Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15))) + -> Seq Scan on rlp3_default rlp_5 + Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15))) +(11 rows) + +-- multi-column keys +create table mc3p (a int, b int, c int) partition by range (a, abs(b), c); +create table mc3p_default partition of mc3p default; +create table mc3p0 partition of mc3p for values from (minvalue, minvalue, minvalue) to (1, 1, 1); +create table mc3p1 partition of mc3p for values from (1, 1, 1) to (10, 5, 10); +create table mc3p2 partition of mc3p for values from (10, 5, 10) to (10, 10, 10); +create table mc3p3 partition of mc3p for values from (10, 10, 10) to (10, 10, 20); +create table mc3p4 partition of mc3p for values from (10, 10, 20) to (10, maxvalue, maxvalue); +create table mc3p5 partition of mc3p for values from (11, 1, 1) to (20, 10, 10); +create table mc3p6 partition of mc3p for values from (20, 10, 10) to (20, 20, 20); +create table mc3p7 partition of mc3p for values from (20, 20, 20) to (maxvalue, maxvalue, maxvalue); +explain (costs off) select * from mc3p where a = 1; + QUERY PLAN +--------------------------------------- + Append + -> Seq Scan on mc3p0 mc3p_1 + Filter: (a = 1) + -> Seq Scan on mc3p1 mc3p_2 + Filter: (a = 1) + -> Seq Scan on mc3p_default mc3p_3 + Filter: (a = 1) +(7 rows) + +explain (costs off) select * from mc3p where a = 1 and abs(b) < 1; + QUERY PLAN +-------------------------------------------- + Append + -> Seq Scan on mc3p0 mc3p_1 + Filter: ((a = 1) AND (abs(b) < 1)) + -> Seq Scan on mc3p_default mc3p_2 + Filter: ((a = 1) AND (abs(b) < 1)) +(5 rows) + +explain (costs off) select * from mc3p where a = 1 and abs(b) = 1; + QUERY PLAN +-------------------------------------------- + Append + -> Seq Scan on mc3p0 mc3p_1 + Filter: ((a = 1) AND (abs(b) = 1)) + -> Seq Scan on mc3p1 mc3p_2 + Filter: ((a = 1) AND (abs(b) = 1)) + -> Seq Scan on mc3p_default mc3p_3 + Filter: ((a = 1) AND (abs(b) = 1)) +(7 rows) + +explain (costs off) select * from mc3p where a = 1 and abs(b) = 1 and c < 8; + QUERY PLAN +-------------------------------------------------------- + Append + -> Seq Scan on mc3p0 mc3p_1 + Filter: ((c < 8) AND (a = 1) AND (abs(b) = 1)) + -> Seq Scan on mc3p1 mc3p_2 + Filter: ((c < 8) AND (a = 1) AND (abs(b) = 1)) +(5 rows) + +explain (costs off) select * from mc3p where a = 10 and abs(b) between 5 and 35; + QUERY PLAN +----------------------------------------------------------------- + Append + -> Seq Scan on mc3p1 mc3p_1 + Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35)) + -> Seq Scan on mc3p2 mc3p_2 + Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35)) + -> Seq Scan on mc3p3 mc3p_3 + Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35)) + -> Seq Scan on mc3p4 mc3p_4 + Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35)) + -> Seq Scan on mc3p_default mc3p_5 + Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35)) +(11 rows) + +explain (costs off) select * from mc3p where a > 10; + QUERY PLAN +--------------------------------------- + Append + -> Seq Scan on mc3p5 mc3p_1 + Filter: (a > 10) + -> Seq Scan on mc3p6 mc3p_2 + Filter: (a > 10) + -> Seq Scan on mc3p7 mc3p_3 + Filter: (a > 10) + -> Seq Scan on mc3p_default mc3p_4 + Filter: (a > 10) +(9 rows) + +explain (costs off) select * from mc3p where a >= 10; + QUERY PLAN +--------------------------------------- + Append + -> Seq Scan on mc3p1 mc3p_1 + Filter: (a >= 10) + -> Seq Scan on mc3p2 mc3p_2 + Filter: (a >= 10) + -> Seq Scan on mc3p3 mc3p_3 + Filter: (a >= 10) + -> Seq Scan on mc3p4 mc3p_4 + Filter: (a >= 10) + -> Seq Scan on mc3p5 mc3p_5 + Filter: (a >= 10) + -> Seq Scan on mc3p6 mc3p_6 + Filter: (a >= 10) + -> Seq Scan on mc3p7 mc3p_7 + Filter: (a >= 10) + -> Seq Scan on mc3p_default mc3p_8 + Filter: (a >= 10) +(17 rows) + +explain (costs off) select * from mc3p where a < 10; + QUERY PLAN +--------------------------------------- + Append + -> Seq Scan on mc3p0 mc3p_1 + Filter: (a < 10) + -> Seq Scan on mc3p1 mc3p_2 + Filter: (a < 10) + -> Seq Scan on mc3p_default mc3p_3 + Filter: (a < 10) +(7 rows) + +explain (costs off) select * from mc3p where a <= 10 and abs(b) < 10; + QUERY PLAN +----------------------------------------------- + Append + -> Seq Scan on mc3p0 mc3p_1 + Filter: ((a <= 10) AND (abs(b) < 10)) + -> Seq Scan on mc3p1 mc3p_2 + Filter: ((a <= 10) AND (abs(b) < 10)) + -> Seq Scan on mc3p2 mc3p_3 + Filter: ((a <= 10) AND (abs(b) < 10)) + -> Seq Scan on mc3p_default mc3p_4 + Filter: ((a <= 10) AND (abs(b) < 10)) +(9 rows) + +explain (costs off) select * from mc3p where a = 11 and abs(b) = 0; + QUERY PLAN +--------------------------------------- + Seq Scan on mc3p_default mc3p + Filter: ((a = 11) AND (abs(b) = 0)) +(2 rows) + +explain (costs off) select * from mc3p where a = 20 and abs(b) = 10 and c = 100; + QUERY PLAN +------------------------------------------------------ + Seq Scan on mc3p6 mc3p + Filter: ((a = 20) AND (c = 100) AND (abs(b) = 10)) +(2 rows) + +explain (costs off) select * from mc3p where a > 20; + QUERY PLAN +--------------------------------------- + Append + -> Seq Scan on mc3p7 mc3p_1 + Filter: (a > 20) + -> Seq Scan on mc3p_default mc3p_2 + Filter: (a > 20) +(5 rows) + +explain (costs off) select * from mc3p where a >= 20; + QUERY PLAN +--------------------------------------- + Append + -> Seq Scan on mc3p5 mc3p_1 + Filter: (a >= 20) + -> Seq Scan on mc3p6 mc3p_2 + Filter: (a >= 20) + -> Seq Scan on mc3p7 mc3p_3 + Filter: (a >= 20) + -> Seq Scan on mc3p_default mc3p_4 + Filter: (a >= 20) +(9 rows) + +explain (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); + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------- + Append + -> Seq Scan on mc3p1 mc3p_1 + 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))) + -> Seq Scan on mc3p2 mc3p_2 + 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))) + -> Seq Scan on mc3p5 mc3p_3 + 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))) + -> Seq Scan on mc3p_default mc3p_4 + 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))) +(9 rows) + +explain (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; + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------------- + Append + -> Seq Scan on mc3p0 mc3p_1 + 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)) + -> Seq Scan on mc3p1 mc3p_2 + 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)) + -> Seq Scan on mc3p2 mc3p_3 + 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)) + -> Seq Scan on mc3p5 mc3p_4 + 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)) + -> Seq Scan on mc3p_default mc3p_5 + 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)) +(11 rows) + +explain (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; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------- + Append + -> Seq Scan on mc3p0 mc3p_1 + 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)) + -> Seq Scan on mc3p1 mc3p_2 + 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)) + -> Seq Scan on mc3p2 mc3p_3 + 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)) + -> Seq Scan on mc3p5 mc3p_4 + 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)) + -> Seq Scan on mc3p_default mc3p_5 + 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)) +(11 rows) + +explain (costs off) select * from mc3p where a = 1 or abs(b) = 1 or c = 1; + QUERY PLAN +------------------------------------------------------ + Append + -> Seq Scan on mc3p0 mc3p_1 + Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1)) + -> Seq Scan on mc3p1 mc3p_2 + Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1)) + -> Seq Scan on mc3p2 mc3p_3 + Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1)) + -> Seq Scan on mc3p3 mc3p_4 + Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1)) + -> Seq Scan on mc3p4 mc3p_5 + Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1)) + -> Seq Scan on mc3p5 mc3p_6 + Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1)) + -> Seq Scan on mc3p6 mc3p_7 + Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1)) + -> Seq Scan on mc3p7 mc3p_8 + Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1)) + -> Seq Scan on mc3p_default mc3p_9 + Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1)) +(19 rows) + +explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1) or (a = 10 and abs(b) = 10); + QUERY PLAN +------------------------------------------------------------------------------ + Append + -> Seq Scan on mc3p0 mc3p_1 + Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10))) + -> Seq Scan on mc3p1 mc3p_2 + Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10))) + -> Seq Scan on mc3p2 mc3p_3 + Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10))) + -> Seq Scan on mc3p3 mc3p_4 + Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10))) + -> Seq Scan on mc3p4 mc3p_5 + Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10))) + -> Seq Scan on mc3p_default mc3p_6 + Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10))) +(13 rows) + +explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1) or (a = 10 and abs(b) = 9); + QUERY PLAN +----------------------------------------------------------------------------- + Append + -> Seq Scan on mc3p0 mc3p_1 + Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9))) + -> Seq Scan on mc3p1 mc3p_2 + Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9))) + -> Seq Scan on mc3p2 mc3p_3 + Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9))) + -> Seq Scan on mc3p_default mc3p_4 + Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9))) +(9 rows) + +-- a simpler multi-column keys case +create table mc2p (a int, b int) partition by range (a, b); +create table mc2p_default partition of mc2p default; +create table mc2p0 partition of mc2p for values from (minvalue, minvalue) to (1, minvalue); +create table mc2p1 partition of mc2p for values from (1, minvalue) to (1, 1); +create table mc2p2 partition of mc2p for values from (1, 1) to (2, minvalue); +create table mc2p3 partition of mc2p for values from (2, minvalue) to (2, 1); +create table mc2p4 partition of mc2p for values from (2, 1) to (2, maxvalue); +create table mc2p5 partition of mc2p for values from (2, maxvalue) to (maxvalue, maxvalue); +explain (costs off) select * from mc2p where a < 2; + QUERY PLAN +--------------------------------------- + Append + -> Seq Scan on mc2p0 mc2p_1 + Filter: (a < 2) + -> Seq Scan on mc2p1 mc2p_2 + Filter: (a < 2) + -> Seq Scan on mc2p2 mc2p_3 + Filter: (a < 2) + -> Seq Scan on mc2p_default mc2p_4 + Filter: (a < 2) +(9 rows) + +explain (costs off) select * from mc2p where a = 2 and b < 1; + QUERY PLAN +--------------------------------- + Seq Scan on mc2p3 mc2p + Filter: ((b < 1) AND (a = 2)) +(2 rows) + +explain (costs off) select * from mc2p where a > 1; + QUERY PLAN +--------------------------------------- + Append + -> Seq Scan on mc2p2 mc2p_1 + Filter: (a > 1) + -> Seq Scan on mc2p3 mc2p_2 + Filter: (a > 1) + -> Seq Scan on mc2p4 mc2p_3 + Filter: (a > 1) + -> Seq Scan on mc2p5 mc2p_4 + Filter: (a > 1) + -> Seq Scan on mc2p_default mc2p_5 + Filter: (a > 1) +(11 rows) + +explain (costs off) select * from mc2p where a = 1 and b > 1; + QUERY PLAN +--------------------------------- + Seq Scan on mc2p2 mc2p + Filter: ((b > 1) AND (a = 1)) +(2 rows) + +-- all partitions but the default one should be pruned +explain (costs off) select * from mc2p where a = 1 and b is null; + QUERY PLAN +------------------------------------- + Seq Scan on mc2p_default mc2p + Filter: ((b IS NULL) AND (a = 1)) +(2 rows) + +explain (costs off) select * from mc2p where a is null and b is null; + QUERY PLAN +----------------------------------------- + Seq Scan on mc2p_default mc2p + Filter: ((a IS NULL) AND (b IS NULL)) +(2 rows) + +explain (costs off) select * from mc2p where a is null and b = 1; + QUERY PLAN +------------------------------------- + Seq Scan on mc2p_default mc2p + Filter: ((a IS NULL) AND (b = 1)) +(2 rows) + +explain (costs off) select * from mc2p where a is null; + QUERY PLAN +------------------------------- + Seq Scan on mc2p_default mc2p + Filter: (a IS NULL) +(2 rows) + +explain (costs off) select * from mc2p where b is null; + QUERY PLAN +------------------------------- + Seq Scan on mc2p_default mc2p + Filter: (b IS NULL) +(2 rows) + +-- boolean partitioning +create table boolpart (a bool) partition by list (a); +create table boolpart_default partition of boolpart default; +create table boolpart_t partition of boolpart for values in ('true'); +create table boolpart_f partition of boolpart for values in ('false'); +insert into boolpart values (true), (false), (null); +explain (costs off) select * from boolpart where a in (true, false); + QUERY PLAN +------------------------------------------------ + Append + -> Seq Scan on boolpart_f boolpart_1 + Filter: (a = ANY ('{t,f}'::boolean[])) + -> Seq Scan on boolpart_t boolpart_2 + Filter: (a = ANY ('{t,f}'::boolean[])) +(5 rows) + +explain (costs off) select * from boolpart where a = false; + QUERY PLAN +--------------------------------- + Seq Scan on boolpart_f boolpart + Filter: (NOT a) +(2 rows) + +explain (costs off) select * from boolpart where not a = false; + QUERY PLAN +--------------------------------- + Seq Scan on boolpart_t boolpart + Filter: a +(2 rows) + +explain (costs off) select * from boolpart where a is true or a is not true; + QUERY PLAN +-------------------------------------------------- + Append + -> Seq Scan on boolpart_f boolpart_1 + Filter: ((a IS TRUE) OR (a IS NOT TRUE)) + -> Seq Scan on boolpart_t boolpart_2 + Filter: ((a IS TRUE) OR (a IS NOT TRUE)) + -> Seq Scan on boolpart_default boolpart_3 + Filter: ((a IS TRUE) OR (a IS NOT TRUE)) +(7 rows) + +explain (costs off) select * from boolpart where a is not true; + QUERY PLAN +----------------------------------------------- + Append + -> Seq Scan on boolpart_f boolpart_1 + Filter: (a IS NOT TRUE) + -> Seq Scan on boolpart_default boolpart_2 + Filter: (a IS NOT TRUE) +(5 rows) + +explain (costs off) select * from boolpart where a is not true and a is not false; + QUERY PLAN +-------------------------------------------------- + Seq Scan on boolpart_default boolpart + Filter: ((a IS NOT TRUE) AND (a IS NOT FALSE)) +(2 rows) + +explain (costs off) select * from boolpart where a is unknown; + QUERY PLAN +----------------------------------------------- + Append + -> Seq Scan on boolpart_f boolpart_1 + Filter: (a IS UNKNOWN) + -> Seq Scan on boolpart_t boolpart_2 + Filter: (a IS UNKNOWN) + -> Seq Scan on boolpart_default boolpart_3 + Filter: (a IS UNKNOWN) +(7 rows) + +explain (costs off) select * from boolpart where a is not unknown; + QUERY PLAN +----------------------------------------------- + Append + -> Seq Scan on boolpart_f boolpart_1 + Filter: (a IS NOT UNKNOWN) + -> Seq Scan on boolpart_t boolpart_2 + Filter: (a IS NOT UNKNOWN) + -> Seq Scan on boolpart_default boolpart_3 + Filter: (a IS NOT UNKNOWN) +(7 rows) + +select * from boolpart where a in (true, false); + a +--- + f + t +(2 rows) + +select * from boolpart where a = false; + a +--- + f +(1 row) + +select * from boolpart where not a = false; + a +--- + t +(1 row) + +select * from boolpart where a is true or a is not true; + a +--- + f + t + +(3 rows) + +select * from boolpart where a is not true; + a +--- + f + +(2 rows) + +select * from boolpart where a is not true and a is not false; + a +--- + +(1 row) + +select * from boolpart where a is unknown; + a +--- + +(1 row) + +select * from boolpart where a is not unknown; + a +--- + f + t +(2 rows) + +-- inverse boolean partitioning - a seemingly unlikely design, but we've got +-- code for it, so we'd better test it. +create table iboolpart (a bool) partition by list ((not a)); +create table iboolpart_default partition of iboolpart default; +create table iboolpart_f partition of iboolpart for values in ('true'); +create table iboolpart_t partition of iboolpart for values in ('false'); +insert into iboolpart values (true), (false), (null); +explain (costs off) select * from iboolpart where a in (true, false); + QUERY PLAN +------------------------------------------------- + Append + -> Seq Scan on iboolpart_t iboolpart_1 + Filter: (a = ANY ('{t,f}'::boolean[])) + -> Seq Scan on iboolpart_f iboolpart_2 + Filter: (a = ANY ('{t,f}'::boolean[])) + -> Seq Scan on iboolpart_default iboolpart_3 + Filter: (a = ANY ('{t,f}'::boolean[])) +(7 rows) + +explain (costs off) select * from iboolpart where a = false; + QUERY PLAN +----------------------------------- + Seq Scan on iboolpart_f iboolpart + Filter: (NOT a) +(2 rows) + +explain (costs off) select * from iboolpart where not a = false; + QUERY PLAN +----------------------------------- + Seq Scan on iboolpart_t iboolpart + Filter: a +(2 rows) + +explain (costs off) select * from iboolpart where a is true or a is not true; + QUERY PLAN +-------------------------------------------------- + Append + -> Seq Scan on iboolpart_t iboolpart_1 + Filter: ((a IS TRUE) OR (a IS NOT TRUE)) + -> Seq Scan on iboolpart_f iboolpart_2 + Filter: ((a IS TRUE) OR (a IS NOT TRUE)) + -> Seq Scan on iboolpart_default iboolpart_3 + Filter: ((a IS TRUE) OR (a IS NOT TRUE)) +(7 rows) + +explain (costs off) select * from iboolpart where a is not true; + QUERY PLAN +------------------------------------------------- + Append + -> Seq Scan on iboolpart_t iboolpart_1 + Filter: (a IS NOT TRUE) + -> Seq Scan on iboolpart_f iboolpart_2 + Filter: (a IS NOT TRUE) + -> Seq Scan on iboolpart_default iboolpart_3 + Filter: (a IS NOT TRUE) +(7 rows) + +explain (costs off) select * from iboolpart where a is not true and a is not false; + QUERY PLAN +-------------------------------------------------------- + Append + -> Seq Scan on iboolpart_t iboolpart_1 + Filter: ((a IS NOT TRUE) AND (a IS NOT FALSE)) + -> Seq Scan on iboolpart_f iboolpart_2 + Filter: ((a IS NOT TRUE) AND (a IS NOT FALSE)) + -> Seq Scan on iboolpart_default iboolpart_3 + Filter: ((a IS NOT TRUE) AND (a IS NOT FALSE)) +(7 rows) + +explain (costs off) select * from iboolpart where a is unknown; + QUERY PLAN +------------------------------------------------- + Append + -> Seq Scan on iboolpart_t iboolpart_1 + Filter: (a IS UNKNOWN) + -> Seq Scan on iboolpart_f iboolpart_2 + Filter: (a IS UNKNOWN) + -> Seq Scan on iboolpart_default iboolpart_3 + Filter: (a IS UNKNOWN) +(7 rows) + +explain (costs off) select * from iboolpart where a is not unknown; + QUERY PLAN +------------------------------------------------- + Append + -> Seq Scan on iboolpart_t iboolpart_1 + Filter: (a IS NOT UNKNOWN) + -> Seq Scan on iboolpart_f iboolpart_2 + Filter: (a IS NOT UNKNOWN) + -> Seq Scan on iboolpart_default iboolpart_3 + Filter: (a IS NOT UNKNOWN) +(7 rows) + +select * from iboolpart where a in (true, false); + a +--- + t + f +(2 rows) + +select * from iboolpart where a = false; + a +--- + f +(1 row) + +select * from iboolpart where not a = false; + a +--- + t +(1 row) + +select * from iboolpart where a is true or a is not true; + a +--- + t + f + +(3 rows) + +select * from iboolpart where a is not true; + a +--- + f + +(2 rows) + +select * from iboolpart where a is not true and a is not false; + a +--- + +(1 row) + +select * from iboolpart where a is unknown; + a +--- + +(1 row) + +select * from iboolpart where a is not unknown; + a +--- + t + f +(2 rows) + +create table boolrangep (a bool, b bool, c int) partition by range (a,b,c); +create table boolrangep_tf partition of boolrangep for values from ('true', 'false', 0) to ('true', 'false', 100); +create table boolrangep_ft partition of boolrangep for values from ('false', 'true', 0) to ('false', 'true', 100); +create table boolrangep_ff1 partition of boolrangep for values from ('false', 'false', 0) to ('false', 'false', 50); +create table boolrangep_ff2 partition of boolrangep for values from ('false', 'false', 50) to ('false', 'false', 100); +-- try a more complex case that's been known to trip up pruning in the past +explain (costs off) select * from boolrangep where not a and not b and c = 25; + QUERY PLAN +---------------------------------------------- + Seq Scan on boolrangep_ff1 boolrangep + Filter: ((NOT a) AND (NOT b) AND (c = 25)) +(2 rows) + +-- test scalar-to-array operators +create table coercepart (a varchar) partition by list (a); +create table coercepart_ab partition of coercepart for values in ('ab'); +create table coercepart_bc partition of coercepart for values in ('bc'); +create table coercepart_cd partition of coercepart for values in ('cd'); +explain (costs off) select * from coercepart where a in ('ab', to_char(125, '999')); + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------ + Append + -> Seq Scan on coercepart_ab coercepart_1 + Filter: ((a)::text = ANY ((ARRAY['ab'::character varying, (to_char(125, '999'::text))::character varying])::text[])) + -> Seq Scan on coercepart_bc coercepart_2 + Filter: ((a)::text = ANY ((ARRAY['ab'::character varying, (to_char(125, '999'::text))::character varying])::text[])) + -> Seq Scan on coercepart_cd coercepart_3 + Filter: ((a)::text = ANY ((ARRAY['ab'::character varying, (to_char(125, '999'::text))::character varying])::text[])) +(7 rows) + +explain (costs off) select * from coercepart where a ~ any ('{ab}'); + QUERY PLAN +---------------------------------------------------- + Append + -> Seq Scan on coercepart_ab coercepart_1 + Filter: ((a)::text ~ ANY ('{ab}'::text[])) + -> Seq Scan on coercepart_bc coercepart_2 + Filter: ((a)::text ~ ANY ('{ab}'::text[])) + -> Seq Scan on coercepart_cd coercepart_3 + Filter: ((a)::text ~ ANY ('{ab}'::text[])) +(7 rows) + +explain (costs off) select * from coercepart where a !~ all ('{ab}'); + QUERY PLAN +----------------------------------------------------- + Append + -> Seq Scan on coercepart_ab coercepart_1 + Filter: ((a)::text !~ ALL ('{ab}'::text[])) + -> Seq Scan on coercepart_bc coercepart_2 + Filter: ((a)::text !~ ALL ('{ab}'::text[])) + -> Seq Scan on coercepart_cd coercepart_3 + Filter: ((a)::text !~ ALL ('{ab}'::text[])) +(7 rows) + +explain (costs off) select * from coercepart where a ~ any ('{ab,bc}'); + QUERY PLAN +------------------------------------------------------- + Append + -> Seq Scan on coercepart_ab coercepart_1 + Filter: ((a)::text ~ ANY ('{ab,bc}'::text[])) + -> Seq Scan on coercepart_bc coercepart_2 + Filter: ((a)::text ~ ANY ('{ab,bc}'::text[])) + -> Seq Scan on coercepart_cd coercepart_3 + Filter: ((a)::text ~ ANY ('{ab,bc}'::text[])) +(7 rows) + +explain (costs off) select * from coercepart where a !~ all ('{ab,bc}'); + QUERY PLAN +-------------------------------------------------------- + Append + -> Seq Scan on coercepart_ab coercepart_1 + Filter: ((a)::text !~ ALL ('{ab,bc}'::text[])) + -> Seq Scan on coercepart_bc coercepart_2 + Filter: ((a)::text !~ ALL ('{ab,bc}'::text[])) + -> Seq Scan on coercepart_cd coercepart_3 + Filter: ((a)::text !~ ALL ('{ab,bc}'::text[])) +(7 rows) + +explain (costs off) select * from coercepart where a = any ('{ab,bc}'); + QUERY PLAN +------------------------------------------------------- + Append + -> Seq Scan on coercepart_ab coercepart_1 + Filter: ((a)::text = ANY ('{ab,bc}'::text[])) + -> Seq Scan on coercepart_bc coercepart_2 + Filter: ((a)::text = ANY ('{ab,bc}'::text[])) +(5 rows) + +explain (costs off) select * from coercepart where a = any ('{ab,null}'); + QUERY PLAN +--------------------------------------------------- + Seq Scan on coercepart_ab coercepart + Filter: ((a)::text = ANY ('{ab,NULL}'::text[])) +(2 rows) + +explain (costs off) select * from coercepart where a = any (null::text[]); + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +explain (costs off) select * from coercepart where a = all ('{ab}'); + QUERY PLAN +---------------------------------------------- + Seq Scan on coercepart_ab coercepart + Filter: ((a)::text = ALL ('{ab}'::text[])) +(2 rows) + +explain (costs off) select * from coercepart where a = all ('{ab,bc}'); + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +explain (costs off) select * from coercepart where a = all ('{ab,null}'); + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +explain (costs off) select * from coercepart where a = all (null::text[]); + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +drop table coercepart; +CREATE TABLE part (a INT, b INT) PARTITION BY LIST (a); +CREATE TABLE part_p1 PARTITION OF part FOR VALUES IN (-2,-1,0,1,2); +CREATE TABLE part_p2 PARTITION OF part DEFAULT PARTITION BY RANGE(a); +CREATE TABLE part_p2_p1 PARTITION OF part_p2 DEFAULT; +CREATE TABLE part_rev (b INT, c INT, a INT); +ALTER TABLE part ATTACH PARTITION part_rev FOR VALUES IN (3); -- fail +ERROR: table "part_rev" contains column "c" not found in parent "part" +DETAIL: The new partition may contain only the columns present in parent. +ALTER TABLE part_rev DROP COLUMN c; +ALTER TABLE part ATTACH PARTITION part_rev FOR VALUES IN (3); -- now it's ok +INSERT INTO part VALUES (-1,-1), (1,1), (2,NULL), (NULL,-2),(NULL,NULL); +EXPLAIN (COSTS OFF) SELECT tableoid::regclass as part, a, b FROM part WHERE a IS NULL ORDER BY 1, 2, 3; + QUERY PLAN +--------------------------------------------------------- + Sort + Sort Key: ((part.tableoid)::regclass), part.a, part.b + -> Seq Scan on part_p2_p1 part + Filter: (a IS NULL) +(4 rows) + +EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM part p(x) ORDER BY x; + QUERY PLAN +----------------------------------------------- + Sort + Output: p.x, p.b + Sort Key: p.x + -> Append + -> Seq Scan on public.part_p1 p_1 + Output: p_1.x, p_1.b + -> Seq Scan on public.part_rev p_2 + Output: p_2.x, p_2.b + -> Seq Scan on public.part_p2_p1 p_3 + Output: p_3.x, p_3.b +(10 rows) + +-- +-- some more cases +-- +-- +-- pruning for partitioned table appearing inside a sub-query +-- +-- pruning won't work for mc3p, because some keys are Params +explain (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; + QUERY PLAN +----------------------------------------------------------------------- + Nested Loop + -> Append + -> Seq Scan on mc2p1 t1_1 + Filter: (a = 1) + -> Seq Scan on mc2p2 t1_2 + Filter: (a = 1) + -> Seq Scan on mc2p_default t1_3 + Filter: (a = 1) + -> Aggregate + -> Append + -> Seq Scan on mc3p0 t2_1 + Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) + -> Seq Scan on mc3p1 t2_2 + Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) + -> Seq Scan on mc3p2 t2_3 + Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) + -> Seq Scan on mc3p3 t2_4 + Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) + -> Seq Scan on mc3p4 t2_5 + Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) + -> Seq Scan on mc3p5 t2_6 + Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) + -> Seq Scan on mc3p6 t2_7 + Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) + -> Seq Scan on mc3p7 t2_8 + Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) + -> Seq Scan on mc3p_default t2_9 + Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) +(28 rows) + +-- pruning should work fine, because values for a prefix of keys (a, b) are +-- available +explain (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; + QUERY PLAN +----------------------------------------------------------------------- + Nested Loop + -> Append + -> Seq Scan on mc2p1 t1_1 + Filter: (a = 1) + -> Seq Scan on mc2p2 t1_2 + Filter: (a = 1) + -> Seq Scan on mc2p_default t1_3 + Filter: (a = 1) + -> Aggregate + -> Append + -> Seq Scan on mc3p0 t2_1 + Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1)) + -> Seq Scan on mc3p1 t2_2 + Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1)) + -> Seq Scan on mc3p_default t2_3 + Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1)) +(16 rows) + +-- also here, because values for all keys are provided +explain (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; + QUERY PLAN +-------------------------------------------------------------- + Nested Loop + -> Aggregate + -> Seq Scan on mc3p1 t2 + Filter: ((a = 1) AND (c = 1) AND (abs(b) = 1)) + -> Append + -> Seq Scan on mc2p1 t1_1 + Filter: (a = 1) + -> Seq Scan on mc2p2 t1_2 + Filter: (a = 1) + -> Seq Scan on mc2p_default t1_3 + Filter: (a = 1) +(11 rows) + +-- +-- pruning with clauses containing <> operator +-- +-- doesn't prune range partitions +create table rp (a int) partition by range (a); +create table rp0 partition of rp for values from (minvalue) to (1); +create table rp1 partition of rp for values from (1) to (2); +create table rp2 partition of rp for values from (2) to (maxvalue); +explain (costs off) select * from rp where a <> 1; + QUERY PLAN +---------------------------- + Append + -> Seq Scan on rp0 rp_1 + Filter: (a <> 1) + -> Seq Scan on rp1 rp_2 + Filter: (a <> 1) + -> Seq Scan on rp2 rp_3 + Filter: (a <> 1) +(7 rows) + +explain (costs off) select * from rp where a <> 1 and a <> 2; + QUERY PLAN +----------------------------------------- + Append + -> Seq Scan on rp0 rp_1 + Filter: ((a <> 1) AND (a <> 2)) + -> Seq Scan on rp1 rp_2 + Filter: ((a <> 1) AND (a <> 2)) + -> Seq Scan on rp2 rp_3 + Filter: ((a <> 1) AND (a <> 2)) +(7 rows) + +-- null partition should be eliminated due to strict <> clause. +explain (costs off) select * from lp where a <> 'a'; + QUERY PLAN +------------------------------------ + Append + -> Seq Scan on lp_ad lp_1 + Filter: (a <> 'a'::bpchar) + -> Seq Scan on lp_bc lp_2 + Filter: (a <> 'a'::bpchar) + -> Seq Scan on lp_ef lp_3 + Filter: (a <> 'a'::bpchar) + -> Seq Scan on lp_g lp_4 + Filter: (a <> 'a'::bpchar) + -> Seq Scan on lp_default lp_5 + Filter: (a <> 'a'::bpchar) +(11 rows) + +-- ensure we detect contradictions in clauses; a can't be NULL and NOT NULL. +explain (costs off) select * from lp where a <> 'a' and a is null; + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +explain (costs off) select * from lp where (a <> 'a' and a <> 'd') or a is null; + QUERY PLAN +------------------------------------------------------------------------------ + Append + -> Seq Scan on lp_bc lp_1 + Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL)) + -> Seq Scan on lp_ef lp_2 + Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL)) + -> Seq Scan on lp_g lp_3 + Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL)) + -> Seq Scan on lp_null lp_4 + Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL)) + -> Seq Scan on lp_default lp_5 + Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL)) +(11 rows) + +-- check that it also works for a partitioned table that's not root, +-- which in this case are partitions of rlp that are themselves +-- list-partitioned on b +explain (costs off) select * from rlp where a = 15 and b <> 'ab' and b <> 'cd' and b <> 'xy' and b is not null; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------ + Append + -> Seq Scan on rlp3efgh rlp_1 + Filter: ((b IS NOT NULL) AND ((b)::text <> 'ab'::text) AND ((b)::text <> 'cd'::text) AND ((b)::text <> 'xy'::text) AND (a = 15)) + -> Seq Scan on rlp3_default rlp_2 + Filter: ((b IS NOT NULL) AND ((b)::text <> 'ab'::text) AND ((b)::text <> 'cd'::text) AND ((b)::text <> 'xy'::text) AND (a = 15)) +(5 rows) + +-- +-- different collations for different keys with same expression +-- +create table coll_pruning_multi (a text) partition by range (substr(a, 1) collate "POSIX", substr(a, 1) collate "C"); +create table coll_pruning_multi1 partition of coll_pruning_multi for values from ('a', 'a') to ('a', 'e'); +create table coll_pruning_multi2 partition of coll_pruning_multi for values from ('a', 'e') to ('a', 'z'); +create table coll_pruning_multi3 partition of coll_pruning_multi for values from ('b', 'a') to ('b', 'e'); +-- no pruning, because no value for the leading key +explain (costs off) select * from coll_pruning_multi where substr(a, 1) = 'e' collate "C"; + QUERY PLAN +------------------------------------------------------------ + Append + -> Seq Scan on coll_pruning_multi1 coll_pruning_multi_1 + Filter: (substr(a, 1) = 'e'::text COLLATE "C") + -> Seq Scan on coll_pruning_multi2 coll_pruning_multi_2 + Filter: (substr(a, 1) = 'e'::text COLLATE "C") + -> Seq Scan on coll_pruning_multi3 coll_pruning_multi_3 + Filter: (substr(a, 1) = 'e'::text COLLATE "C") +(7 rows) + +-- pruning, with a value provided for the leading key +explain (costs off) select * from coll_pruning_multi where substr(a, 1) = 'a' collate "POSIX"; + QUERY PLAN +------------------------------------------------------------ + Append + -> Seq Scan on coll_pruning_multi1 coll_pruning_multi_1 + Filter: (substr(a, 1) = 'a'::text COLLATE "POSIX") + -> Seq Scan on coll_pruning_multi2 coll_pruning_multi_2 + Filter: (substr(a, 1) = 'a'::text COLLATE "POSIX") +(5 rows) + +-- pruning, with values provided for both keys +explain (costs off) select * from coll_pruning_multi where substr(a, 1) = 'e' collate "C" and substr(a, 1) = 'a' collate "POSIX"; + QUERY PLAN +--------------------------------------------------------------------------------------------------- + Seq Scan on coll_pruning_multi2 coll_pruning_multi + Filter: ((substr(a, 1) = 'e'::text COLLATE "C") AND (substr(a, 1) = 'a'::text COLLATE "POSIX")) +(2 rows) + +-- +-- LIKE operators don't prune +-- +create table like_op_noprune (a text) partition by list (a); +create table like_op_noprune1 partition of like_op_noprune for values in ('ABC'); +create table like_op_noprune2 partition of like_op_noprune for values in ('BCD'); +explain (costs off) select * from like_op_noprune where a like '%BC'; + QUERY PLAN +------------------------------------------------------ + Append + -> Seq Scan on like_op_noprune1 like_op_noprune_1 + Filter: (a ~~ '%BC'::text) + -> Seq Scan on like_op_noprune2 like_op_noprune_2 + Filter: (a ~~ '%BC'::text) +(5 rows) + +-- +-- tests wherein clause value requires a cross-type comparison function +-- +create table lparted_by_int2 (a smallint) partition by list (a); +create table lparted_by_int2_1 partition of lparted_by_int2 for values in (1); +create table lparted_by_int2_16384 partition of lparted_by_int2 for values in (16384); +explain (costs off) select * from lparted_by_int2 where a = 100_000_000_000_000; + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +create table rparted_by_int2 (a smallint) partition by range (a); +create table rparted_by_int2_1 partition of rparted_by_int2 for values from (1) to (10); +create table rparted_by_int2_16384 partition of rparted_by_int2 for values from (10) to (16384); +-- all partitions pruned +explain (costs off) select * from rparted_by_int2 where a > 100_000_000_000_000; + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +create table rparted_by_int2_maxvalue partition of rparted_by_int2 for values from (16384) to (maxvalue); +-- all partitions but rparted_by_int2_maxvalue pruned +explain (costs off) select * from rparted_by_int2 where a > 100_000_000_000_000; + QUERY PLAN +------------------------------------------------------ + Seq Scan on rparted_by_int2_maxvalue rparted_by_int2 + Filter: (a > '100000000000000'::bigint) +(2 rows) + +drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, iboolpart, boolrangep, rp, coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2; +-- +-- Test Partition pruning for HASH partitioning +-- +-- Use hand-rolled hash functions and operator classes to get predictable +-- result on different machines. See the definitions of +-- part_part_test_int4_ops and part_test_text_ops in insert.sql. +-- +create table hp (a int, b text, c int) + partition by hash (a part_test_int4_ops, b part_test_text_ops); +create table hp0 partition of hp for values with (modulus 4, remainder 0); +create table hp3 partition of hp for values with (modulus 4, remainder 3); +create table hp1 partition of hp for values with (modulus 4, remainder 1); +create table hp2 partition of hp for values with (modulus 4, remainder 2); +insert into hp values (null, null, 0); +insert into hp values (1, null, 1); +insert into hp values (1, 'xxx', 2); +insert into hp values (null, 'xxx', 3); +insert into hp values (2, 'xxx', 4); +insert into hp values (1, 'abcde', 5); +select tableoid::regclass, * from hp order by c; + tableoid | a | b | c +----------+---+-------+--- + hp0 | | | 0 + hp1 | 1 | | 1 + hp0 | 1 | xxx | 2 + hp2 | | xxx | 3 + hp3 | 2 | xxx | 4 + hp2 | 1 | abcde | 5 +(6 rows) + +-- partial keys won't prune, nor would non-equality conditions +explain (costs off) select * from hp where a = 1; + QUERY PLAN +---------------------------- + Append + -> Seq Scan on hp0 hp_1 + Filter: (a = 1) + -> Seq Scan on hp1 hp_2 + Filter: (a = 1) + -> Seq Scan on hp2 hp_3 + Filter: (a = 1) + -> Seq Scan on hp3 hp_4 + Filter: (a = 1) +(9 rows) + +explain (costs off) select * from hp where b = 'xxx'; + QUERY PLAN +----------------------------------- + Append + -> Seq Scan on hp0 hp_1 + Filter: (b = 'xxx'::text) + -> Seq Scan on hp1 hp_2 + Filter: (b = 'xxx'::text) + -> Seq Scan on hp2 hp_3 + Filter: (b = 'xxx'::text) + -> Seq Scan on hp3 hp_4 + Filter: (b = 'xxx'::text) +(9 rows) + +explain (costs off) select * from hp where a is null; + QUERY PLAN +----------------------------- + Append + -> Seq Scan on hp0 hp_1 + Filter: (a IS NULL) + -> Seq Scan on hp1 hp_2 + Filter: (a IS NULL) + -> Seq Scan on hp2 hp_3 + Filter: (a IS NULL) + -> Seq Scan on hp3 hp_4 + Filter: (a IS NULL) +(9 rows) + +explain (costs off) select * from hp where b is null; + QUERY PLAN +----------------------------- + Append + -> Seq Scan on hp0 hp_1 + Filter: (b IS NULL) + -> Seq Scan on hp1 hp_2 + Filter: (b IS NULL) + -> Seq Scan on hp2 hp_3 + Filter: (b IS NULL) + -> Seq Scan on hp3 hp_4 + Filter: (b IS NULL) +(9 rows) + +explain (costs off) select * from hp where a < 1 and b = 'xxx'; + QUERY PLAN +------------------------------------------------- + Append + -> Seq Scan on hp0 hp_1 + Filter: ((a < 1) AND (b = 'xxx'::text)) + -> Seq Scan on hp1 hp_2 + Filter: ((a < 1) AND (b = 'xxx'::text)) + -> Seq Scan on hp2 hp_3 + Filter: ((a < 1) AND (b = 'xxx'::text)) + -> Seq Scan on hp3 hp_4 + Filter: ((a < 1) AND (b = 'xxx'::text)) +(9 rows) + +explain (costs off) select * from hp where a <> 1 and b = 'yyy'; + QUERY PLAN +-------------------------------------------------- + Append + -> Seq Scan on hp0 hp_1 + Filter: ((a <> 1) AND (b = 'yyy'::text)) + -> Seq Scan on hp1 hp_2 + Filter: ((a <> 1) AND (b = 'yyy'::text)) + -> Seq Scan on hp2 hp_3 + Filter: ((a <> 1) AND (b = 'yyy'::text)) + -> Seq Scan on hp3 hp_4 + Filter: ((a <> 1) AND (b = 'yyy'::text)) +(9 rows) + +explain (costs off) select * from hp where a <> 1 and b <> 'xxx'; + QUERY PLAN +--------------------------------------------------- + Append + -> Seq Scan on hp0 hp_1 + Filter: ((a <> 1) AND (b <> 'xxx'::text)) + -> Seq Scan on hp1 hp_2 + Filter: ((a <> 1) AND (b <> 'xxx'::text)) + -> Seq Scan on hp2 hp_3 + Filter: ((a <> 1) AND (b <> 'xxx'::text)) + -> Seq Scan on hp3 hp_4 + Filter: ((a <> 1) AND (b <> 'xxx'::text)) +(9 rows) + +-- pruning should work if either a value or a IS NULL clause is provided for +-- each of the keys +explain (costs off) select * from hp where a is null and b is null; + QUERY PLAN +----------------------------------------- + Seq Scan on hp0 hp + Filter: ((a IS NULL) AND (b IS NULL)) +(2 rows) + +explain (costs off) select * from hp where a = 1 and b is null; + QUERY PLAN +------------------------------------- + Seq Scan on hp1 hp + Filter: ((b IS NULL) AND (a = 1)) +(2 rows) + +explain (costs off) select * from hp where a = 1 and b = 'xxx'; + QUERY PLAN +------------------------------------------- + Seq Scan on hp0 hp + Filter: ((a = 1) AND (b = 'xxx'::text)) +(2 rows) + +explain (costs off) select * from hp where a is null and b = 'xxx'; + QUERY PLAN +----------------------------------------------- + Seq Scan on hp2 hp + Filter: ((a IS NULL) AND (b = 'xxx'::text)) +(2 rows) + +explain (costs off) select * from hp where a = 2 and b = 'xxx'; + QUERY PLAN +------------------------------------------- + Seq Scan on hp3 hp + Filter: ((a = 2) AND (b = 'xxx'::text)) +(2 rows) + +explain (costs off) select * from hp where a = 1 and b = 'abcde'; + QUERY PLAN +--------------------------------------------- + Seq Scan on hp2 hp + Filter: ((a = 1) AND (b = 'abcde'::text)) +(2 rows) + +explain (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); + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------- + Append + -> Seq Scan on hp0 hp_1 + Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL))) + -> Seq Scan on hp2 hp_2 + Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL))) + -> Seq Scan on hp3 hp_3 + Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL))) +(7 rows) + +-- test pruning when not all the partitions exist +drop table hp1; +drop table hp3; +explain (costs off) select * from hp where a = 1 and b = 'abcde'; + QUERY PLAN +--------------------------------------------- + Seq Scan on hp2 hp + Filter: ((a = 1) AND (b = 'abcde'::text)) +(2 rows) + +explain (costs off) select * from hp where a = 1 and b = 'abcde' and + (c = 2 or c = 3); + QUERY PLAN +---------------------------------------------------------------------- + Seq Scan on hp2 hp + Filter: ((a = 1) AND (b = 'abcde'::text) AND ((c = 2) OR (c = 3))) +(2 rows) + +drop table hp2; +explain (costs off) select * from hp where a = 1 and b = 'abcde' and + (c = 2 or c = 3); + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +-- +-- Test runtime partition pruning +-- +create table ab (a int not null, b int not null) partition by list (a); +create table ab_a2 partition of ab for values in(2) partition by list (b); +create table ab_a2_b1 partition of ab_a2 for values in (1); +create table ab_a2_b2 partition of ab_a2 for values in (2); +create table ab_a2_b3 partition of ab_a2 for values in (3); +create table ab_a1 partition of ab for values in(1) partition by list (b); +create table ab_a1_b1 partition of ab_a1 for values in (1); +create table ab_a1_b2 partition of ab_a1 for values in (2); +create table ab_a1_b3 partition of ab_a1 for values in (3); +create table ab_a3 partition of ab for values in(3) partition by list (b); +create table ab_a3_b1 partition of ab_a3 for values in (1); +create table ab_a3_b2 partition of ab_a3 for values in (2); +create table ab_a3_b3 partition of ab_a3 for values in (3); +-- Disallow index only scans as concurrent transactions may stop visibility +-- bits being set causing "Heap Fetches" to be unstable in the EXPLAIN ANALYZE +-- output. +set enable_indexonlyscan = off; +prepare ab_q1 (int, int, int) as +select * from ab where a between $1 and $2 and b <= $3; +explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3); + QUERY PLAN +--------------------------------------------------------- + Append (actual rows=0 loops=1) + Subplans Removed: 6 + -> Seq Scan on ab_a2_b1 ab_1 (actual rows=0 loops=1) + Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) + -> Seq Scan on ab_a2_b2 ab_2 (actual rows=0 loops=1) + Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) + -> Seq Scan on ab_a2_b3 ab_3 (actual rows=0 loops=1) + Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) +(8 rows) + +explain (analyze, costs off, summary off, timing off) execute ab_q1 (1, 2, 3); + QUERY PLAN +--------------------------------------------------------- + Append (actual rows=0 loops=1) + Subplans Removed: 3 + -> Seq Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1) + Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) + -> Seq Scan on ab_a1_b2 ab_2 (actual rows=0 loops=1) + Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) + -> Seq Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1) + Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) + -> Seq Scan on ab_a2_b1 ab_4 (actual rows=0 loops=1) + Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) + -> Seq Scan on ab_a2_b2 ab_5 (actual rows=0 loops=1) + Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) + -> Seq Scan on ab_a2_b3 ab_6 (actual rows=0 loops=1) + Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) +(14 rows) + +deallocate ab_q1; +-- Runtime pruning after optimizer pruning +prepare ab_q1 (int, int) as +select a from ab where a between $1 and $2 and b < 3; +explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2); + QUERY PLAN +--------------------------------------------------------- + Append (actual rows=0 loops=1) + Subplans Removed: 4 + -> Seq Scan on ab_a2_b1 ab_1 (actual rows=0 loops=1) + Filter: ((a >= $1) AND (a <= $2) AND (b < 3)) + -> Seq Scan on ab_a2_b2 ab_2 (actual rows=0 loops=1) + Filter: ((a >= $1) AND (a <= $2) AND (b < 3)) +(6 rows) + +explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4); + QUERY PLAN +--------------------------------------------------------- + Append (actual rows=0 loops=1) + Subplans Removed: 2 + -> Seq Scan on ab_a2_b1 ab_1 (actual rows=0 loops=1) + Filter: ((a >= $1) AND (a <= $2) AND (b < 3)) + -> Seq Scan on ab_a2_b2 ab_2 (actual rows=0 loops=1) + Filter: ((a >= $1) AND (a <= $2) AND (b < 3)) + -> Seq Scan on ab_a3_b1 ab_3 (actual rows=0 loops=1) + Filter: ((a >= $1) AND (a <= $2) AND (b < 3)) + -> Seq Scan on ab_a3_b2 ab_4 (actual rows=0 loops=1) + Filter: ((a >= $1) AND (a <= $2) AND (b < 3)) +(10 rows) + +-- Ensure a mix of PARAM_EXTERN and PARAM_EXEC Params work together at +-- different levels of partitioning. +prepare ab_q2 (int, int) as +select a from ab where a between $1 and $2 and b < (select 3); +explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2); + QUERY PLAN +--------------------------------------------------------- + Append (actual rows=0 loops=1) + Subplans Removed: 6 + InitPlan 1 (returns $0) + -> Result (actual rows=1 loops=1) + -> Seq Scan on ab_a2_b1 ab_1 (actual rows=0 loops=1) + Filter: ((a >= $1) AND (a <= $2) AND (b < $0)) + -> Seq Scan on ab_a2_b2 ab_2 (actual rows=0 loops=1) + Filter: ((a >= $1) AND (a <= $2) AND (b < $0)) + -> Seq Scan on ab_a2_b3 ab_3 (never executed) + Filter: ((a >= $1) AND (a <= $2) AND (b < $0)) +(10 rows) + +-- As above, but swap the PARAM_EXEC Param to the first partition level +prepare ab_q3 (int, int) as +select a from ab where b between $1 and $2 and a < (select 3); +explain (analyze, costs off, summary off, timing off) execute ab_q3 (2, 2); + QUERY PLAN +--------------------------------------------------------- + Append (actual rows=0 loops=1) + Subplans Removed: 6 + InitPlan 1 (returns $0) + -> Result (actual rows=1 loops=1) + -> Seq Scan on ab_a1_b2 ab_1 (actual rows=0 loops=1) + Filter: ((b >= $1) AND (b <= $2) AND (a < $0)) + -> Seq Scan on ab_a2_b2 ab_2 (actual rows=0 loops=1) + Filter: ((b >= $1) AND (b <= $2) AND (a < $0)) + -> Seq Scan on ab_a3_b2 ab_3 (never executed) + Filter: ((b >= $1) AND (b <= $2) AND (a < $0)) +(10 rows) + +-- +-- Test runtime pruning with hash partitioned tables +-- +-- recreate partitions dropped above +create table hp1 partition of hp for values with (modulus 4, remainder 1); +create table hp2 partition of hp for values with (modulus 4, remainder 2); +create table hp3 partition of hp for values with (modulus 4, remainder 3); +-- Ensure we correctly prune unneeded partitions when there is an IS NULL qual +prepare hp_q1 (text) as +select * from hp where a is null and b = $1; +explain (costs off) execute hp_q1('xxx'); + QUERY PLAN +-------------------------------------------- + Append + Subplans Removed: 3 + -> Seq Scan on hp2 hp_1 + Filter: ((a IS NULL) AND (b = $1)) +(4 rows) + +deallocate hp_q1; +drop table hp; +-- Test a backwards Append scan +create table list_part (a int) partition by list (a); +create table list_part1 partition of list_part for values in (1); +create table list_part2 partition of list_part for values in (2); +create table list_part3 partition of list_part for values in (3); +create table list_part4 partition of list_part for values in (4); +insert into list_part select generate_series(1,4); +begin; +-- Don't select an actual value out of the table as the order of the Append's +-- subnodes may not be stable. +declare cur SCROLL CURSOR for select 1 from list_part where a > (select 1) and a < (select 4); +-- move beyond the final row +move 3 from cur; +-- Ensure we get two rows. +fetch backward all from cur; + ?column? +---------- + 1 + 1 +(2 rows) + +commit; +begin; +-- Test run-time pruning using stable functions +create function list_part_fn(int) returns int as $$ begin return $1; end;$$ language plpgsql stable; +-- Ensure pruning works using a stable function containing no Vars +explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1); + QUERY PLAN +------------------------------------------------------------------ + Append (actual rows=1 loops=1) + Subplans Removed: 3 + -> Seq Scan on list_part1 list_part_1 (actual rows=1 loops=1) + Filter: (a = list_part_fn(1)) +(4 rows) + +-- Ensure pruning does not take place when the function has a Var parameter +explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(a); + QUERY PLAN +------------------------------------------------------------------ + Append (actual rows=4 loops=1) + -> Seq Scan on list_part1 list_part_1 (actual rows=1 loops=1) + Filter: (a = list_part_fn(a)) + -> Seq Scan on list_part2 list_part_2 (actual rows=1 loops=1) + Filter: (a = list_part_fn(a)) + -> Seq Scan on list_part3 list_part_3 (actual rows=1 loops=1) + Filter: (a = list_part_fn(a)) + -> Seq Scan on list_part4 list_part_4 (actual rows=1 loops=1) + Filter: (a = list_part_fn(a)) +(9 rows) + +-- Ensure pruning does not take place when the expression contains a Var. +explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1) + a; + QUERY PLAN +------------------------------------------------------------------ + Append (actual rows=0 loops=1) + -> Seq Scan on list_part1 list_part_1 (actual rows=0 loops=1) + Filter: (a = (list_part_fn(1) + a)) + Rows Removed by Filter: 1 + -> Seq Scan on list_part2 list_part_2 (actual rows=0 loops=1) + Filter: (a = (list_part_fn(1) + a)) + Rows Removed by Filter: 1 + -> Seq Scan on list_part3 list_part_3 (actual rows=0 loops=1) + Filter: (a = (list_part_fn(1) + a)) + Rows Removed by Filter: 1 + -> Seq Scan on list_part4 list_part_4 (actual rows=0 loops=1) + Filter: (a = (list_part_fn(1) + a)) + Rows Removed by Filter: 1 +(13 rows) + +rollback; +drop table list_part; +-- Parallel append +-- Parallel queries won't necessarily get as many workers as the planner +-- asked for. This affects not only the "Workers Launched:" field of EXPLAIN +-- results, but also row counts and loop counts for parallel scans, Gathers, +-- and everything in between. This function filters out the values we can't +-- rely on to be stable. +-- This removes enough info that you might wonder why bother with EXPLAIN +-- ANALYZE at all. The answer is that we need to see '(never executed)' +-- notations because that's the only way to verify runtime pruning. +create function explain_parallel_append(text) returns setof text +language plpgsql as +$$ +declare + ln text; +begin + for ln in + execute format('explain (analyze, costs off, summary off, timing off) %s', + $1) + loop + ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); + ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N'); + ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N'); + return next ln; + end loop; +end; +$$; +prepare ab_q4 (int, int) as +select avg(a) from ab where a between $1 and $2 and b < 4; +-- Encourage use of parallel plans +set parallel_setup_cost = 0; +set parallel_tuple_cost = 0; +set min_parallel_table_scan_size = 0; +set max_parallel_workers_per_gather = 2; +select explain_parallel_append('execute ab_q4 (2, 2)'); + explain_parallel_append +------------------------------------------------------------------------------------ + Finalize Aggregate (actual rows=N loops=N) + -> Gather (actual rows=N loops=N) + Workers Planned: 2 + Workers Launched: N + -> Partial Aggregate (actual rows=N loops=N) + -> Parallel Append (actual rows=N loops=N) + Subplans Removed: 6 + -> Parallel Seq Scan on ab_a2_b1 ab_1 (actual rows=N loops=N) + Filter: ((a >= $1) AND (a <= $2) AND (b < 4)) + -> Parallel Seq Scan on ab_a2_b2 ab_2 (actual rows=N loops=N) + Filter: ((a >= $1) AND (a <= $2) AND (b < 4)) + -> Parallel Seq Scan on ab_a2_b3 ab_3 (actual rows=N loops=N) + Filter: ((a >= $1) AND (a <= $2) AND (b < 4)) +(13 rows) + +-- Test run-time pruning with IN lists. +prepare ab_q5 (int, int, int) as +select avg(a) from ab where a in($1,$2,$3) and b < 4; +select explain_parallel_append('execute ab_q5 (1, 1, 1)'); + explain_parallel_append +------------------------------------------------------------------------------------ + Finalize Aggregate (actual rows=N loops=N) + -> Gather (actual rows=N loops=N) + Workers Planned: 2 + Workers Launched: N + -> Partial Aggregate (actual rows=N loops=N) + -> Parallel Append (actual rows=N loops=N) + Subplans Removed: 6 + -> Parallel Seq Scan on ab_a1_b1 ab_1 (actual rows=N loops=N) + Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) + -> Parallel Seq Scan on ab_a1_b2 ab_2 (actual rows=N loops=N) + Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) + -> Parallel Seq Scan on ab_a1_b3 ab_3 (actual rows=N loops=N) + Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) +(13 rows) + +select explain_parallel_append('execute ab_q5 (2, 3, 3)'); + explain_parallel_append +------------------------------------------------------------------------------------ + Finalize Aggregate (actual rows=N loops=N) + -> Gather (actual rows=N loops=N) + Workers Planned: 2 + Workers Launched: N + -> Partial Aggregate (actual rows=N loops=N) + -> Parallel Append (actual rows=N loops=N) + Subplans Removed: 3 + -> Parallel Seq Scan on ab_a2_b1 ab_1 (actual rows=N loops=N) + Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) + -> Parallel Seq Scan on ab_a2_b2 ab_2 (actual rows=N loops=N) + Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) + -> Parallel Seq Scan on ab_a2_b3 ab_3 (actual rows=N loops=N) + Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) + -> Parallel Seq Scan on ab_a3_b1 ab_4 (actual rows=N loops=N) + Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) + -> Parallel Seq Scan on ab_a3_b2 ab_5 (actual rows=N loops=N) + Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) + -> Parallel Seq Scan on ab_a3_b3 ab_6 (actual rows=N loops=N) + Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) +(19 rows) + +-- Try some params whose values do not belong to any partition. +select explain_parallel_append('execute ab_q5 (33, 44, 55)'); + explain_parallel_append +----------------------------------------------------------- + Finalize Aggregate (actual rows=N loops=N) + -> Gather (actual rows=N loops=N) + Workers Planned: 2 + Workers Launched: N + -> Partial Aggregate (actual rows=N loops=N) + -> Parallel Append (actual rows=N loops=N) + Subplans Removed: 9 +(7 rows) + +-- Test Parallel Append with PARAM_EXEC Params +select explain_parallel_append('select count(*) from ab where (a = (select 1) or a = (select 3)) and b = 2'); + explain_parallel_append +------------------------------------------------------------------------------ + Aggregate (actual rows=N loops=N) + InitPlan 1 (returns $0) + -> Result (actual rows=N loops=N) + InitPlan 2 (returns $1) + -> Result (actual rows=N loops=N) + -> Gather (actual rows=N loops=N) + Workers Planned: 2 + Params Evaluated: $0, $1 + Workers Launched: N + -> Parallel Append (actual rows=N loops=N) + -> Parallel Seq Scan on ab_a1_b2 ab_1 (actual rows=N loops=N) + Filter: ((b = 2) AND ((a = $0) OR (a = $1))) + -> Parallel Seq Scan on ab_a2_b2 ab_2 (never executed) + Filter: ((b = 2) AND ((a = $0) OR (a = $1))) + -> Parallel Seq Scan on ab_a3_b2 ab_3 (actual rows=N loops=N) + Filter: ((b = 2) AND ((a = $0) OR (a = $1))) +(16 rows) + +-- Test pruning during parallel nested loop query +create table lprt_a (a int not null); +-- Insert some values we won't find in ab +insert into lprt_a select 0 from generate_series(1,100); +-- and insert some values that we should find. +insert into lprt_a values(1),(1); +analyze lprt_a; +create index ab_a2_b1_a_idx on ab_a2_b1 (a); +create index ab_a2_b2_a_idx on ab_a2_b2 (a); +create index ab_a2_b3_a_idx on ab_a2_b3 (a); +create index ab_a1_b1_a_idx on ab_a1_b1 (a); +create index ab_a1_b2_a_idx on ab_a1_b2 (a); +create index ab_a1_b3_a_idx on ab_a1_b3 (a); +create index ab_a3_b1_a_idx on ab_a3_b1 (a); +create index ab_a3_b2_a_idx on ab_a3_b2 (a); +create index ab_a3_b3_a_idx on ab_a3_b3 (a); +set enable_hashjoin = 0; +set enable_mergejoin = 0; +set enable_memoize = 0; +select 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)'); + explain_parallel_append +-------------------------------------------------------------------------------------------------------- + Finalize Aggregate (actual rows=N loops=N) + -> Gather (actual rows=N loops=N) + Workers Planned: 1 + Workers Launched: N + -> Partial Aggregate (actual rows=N loops=N) + -> Nested Loop (actual rows=N loops=N) + -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) + Filter: (a = ANY ('{0,0,1}'::integer[])) + -> Append (actual rows=N loops=N) + -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N) + Index Cond: (a = a.a) + -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 ab_2 (actual rows=N loops=N) + Index Cond: (a = a.a) + -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N) + Index Cond: (a = a.a) + -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed) + Index Cond: (a = a.a) + -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed) + Index Cond: (a = a.a) + -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed) + Index Cond: (a = a.a) + -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed) + Index Cond: (a = a.a) + -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed) + Index Cond: (a = a.a) + -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed) + Index Cond: (a = a.a) +(27 rows) + +-- Ensure the same partitions are pruned when we make the nested loop +-- parameter an Expr rather than a plain Param. +select 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)'); + explain_parallel_append +-------------------------------------------------------------------------------------------------------- + Finalize Aggregate (actual rows=N loops=N) + -> Gather (actual rows=N loops=N) + Workers Planned: 1 + Workers Launched: N + -> Partial Aggregate (actual rows=N loops=N) + -> Nested Loop (actual rows=N loops=N) + -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) + Filter: (a = ANY ('{0,0,1}'::integer[])) + -> Append (actual rows=N loops=N) + -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N) + Index Cond: (a = (a.a + 0)) + -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 ab_2 (actual rows=N loops=N) + Index Cond: (a = (a.a + 0)) + -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N) + Index Cond: (a = (a.a + 0)) + -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed) + Index Cond: (a = (a.a + 0)) + -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed) + Index Cond: (a = (a.a + 0)) + -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed) + Index Cond: (a = (a.a + 0)) + -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed) + Index Cond: (a = (a.a + 0)) + -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed) + Index Cond: (a = (a.a + 0)) + -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed) + Index Cond: (a = (a.a + 0)) +(27 rows) + +insert into lprt_a values(3),(3); +select 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)'); + explain_parallel_append +-------------------------------------------------------------------------------------------------------- + Finalize Aggregate (actual rows=N loops=N) + -> Gather (actual rows=N loops=N) + Workers Planned: 1 + Workers Launched: N + -> Partial Aggregate (actual rows=N loops=N) + -> Nested Loop (actual rows=N loops=N) + -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) + Filter: (a = ANY ('{1,0,3}'::integer[])) + -> Append (actual rows=N loops=N) + -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N) + Index Cond: (a = a.a) + -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 ab_2 (actual rows=N loops=N) + Index Cond: (a = a.a) + -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N) + Index Cond: (a = a.a) + -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed) + Index Cond: (a = a.a) + -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed) + Index Cond: (a = a.a) + -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed) + Index Cond: (a = a.a) + -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (actual rows=N loops=N) + Index Cond: (a = a.a) + -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (actual rows=N loops=N) + Index Cond: (a = a.a) + -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (actual rows=N loops=N) + Index Cond: (a = a.a) +(27 rows) + +select 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)'); + explain_parallel_append +-------------------------------------------------------------------------------------------------------- + Finalize Aggregate (actual rows=N loops=N) + -> Gather (actual rows=N loops=N) + Workers Planned: 1 + Workers Launched: N + -> Partial Aggregate (actual rows=N loops=N) + -> Nested Loop (actual rows=N loops=N) + -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) + Filter: (a = ANY ('{1,0,0}'::integer[])) + Rows Removed by Filter: N + -> Append (actual rows=N loops=N) + -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N) + Index Cond: (a = a.a) + -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 ab_2 (actual rows=N loops=N) + Index Cond: (a = a.a) + -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N) + Index Cond: (a = a.a) + -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed) + Index Cond: (a = a.a) + -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed) + Index Cond: (a = a.a) + -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed) + Index Cond: (a = a.a) + -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed) + Index Cond: (a = a.a) + -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed) + Index Cond: (a = a.a) + -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed) + Index Cond: (a = a.a) +(28 rows) + +delete from lprt_a where a = 1; +select 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)'); + explain_parallel_append +------------------------------------------------------------------------------------------------- + Finalize Aggregate (actual rows=N loops=N) + -> Gather (actual rows=N loops=N) + Workers Planned: 1 + Workers Launched: N + -> Partial Aggregate (actual rows=N loops=N) + -> Nested Loop (actual rows=N loops=N) + -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) + Filter: (a = ANY ('{1,0,0}'::integer[])) + Rows Removed by Filter: N + -> Append (actual rows=N loops=N) + -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (never executed) + Index Cond: (a = a.a) + -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 ab_2 (never executed) + Index Cond: (a = a.a) + -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (never executed) + Index Cond: (a = a.a) + -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed) + Index Cond: (a = a.a) + -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed) + Index Cond: (a = a.a) + -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed) + Index Cond: (a = a.a) + -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed) + Index Cond: (a = a.a) + -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed) + Index Cond: (a = a.a) + -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed) + Index Cond: (a = a.a) +(28 rows) + +reset enable_hashjoin; +reset enable_mergejoin; +reset enable_memoize; +reset parallel_setup_cost; +reset parallel_tuple_cost; +reset min_parallel_table_scan_size; +reset max_parallel_workers_per_gather; +-- Test run-time partition pruning with an initplan +explain (analyze, costs off, summary off, timing off) +select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a); + QUERY PLAN +------------------------------------------------------------------------- + Append (actual rows=0 loops=1) + InitPlan 1 (returns $0) + -> Aggregate (actual rows=1 loops=1) + -> Seq Scan on lprt_a (actual rows=102 loops=1) + InitPlan 2 (returns $1) + -> Aggregate (actual rows=1 loops=1) + -> Seq Scan on lprt_a lprt_a_1 (actual rows=102 loops=1) + -> Bitmap Heap Scan on ab_a1_b1 ab_1 (never executed) + Recheck Cond: (a = $0) + Filter: (b = $1) + -> Bitmap Index Scan on ab_a1_b1_a_idx (never executed) + Index Cond: (a = $0) + -> Bitmap Heap Scan on ab_a1_b2 ab_2 (never executed) + Recheck Cond: (a = $0) + Filter: (b = $1) + -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed) + Index Cond: (a = $0) + -> Bitmap Heap Scan on ab_a1_b3 ab_3 (never executed) + Recheck Cond: (a = $0) + Filter: (b = $1) + -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed) + Index Cond: (a = $0) + -> Bitmap Heap Scan on ab_a2_b1 ab_4 (never executed) + Recheck Cond: (a = $0) + Filter: (b = $1) + -> Bitmap Index Scan on ab_a2_b1_a_idx (never executed) + Index Cond: (a = $0) + -> Bitmap Heap Scan on ab_a2_b2 ab_5 (never executed) + Recheck Cond: (a = $0) + Filter: (b = $1) + -> Bitmap Index Scan on ab_a2_b2_a_idx (never executed) + Index Cond: (a = $0) + -> Bitmap Heap Scan on ab_a2_b3 ab_6 (never executed) + Recheck Cond: (a = $0) + Filter: (b = $1) + -> Bitmap Index Scan on ab_a2_b3_a_idx (never executed) + Index Cond: (a = $0) + -> Bitmap Heap Scan on ab_a3_b1 ab_7 (never executed) + Recheck Cond: (a = $0) + Filter: (b = $1) + -> Bitmap Index Scan on ab_a3_b1_a_idx (never executed) + Index Cond: (a = $0) + -> Bitmap Heap Scan on ab_a3_b2 ab_8 (actual rows=0 loops=1) + Recheck Cond: (a = $0) + Filter: (b = $1) + -> Bitmap Index Scan on ab_a3_b2_a_idx (actual rows=0 loops=1) + Index Cond: (a = $0) + -> Bitmap Heap Scan on ab_a3_b3 ab_9 (never executed) + Recheck Cond: (a = $0) + Filter: (b = $1) + -> Bitmap Index Scan on ab_a3_b3_a_idx (never executed) + Index Cond: (a = $0) +(52 rows) + +-- Test run-time partition pruning with UNION ALL parents +explain (analyze, costs off, summary off, timing off) +select * from (select * from ab where a = 1 union all select * from ab) ab where b = (select 1); + QUERY PLAN +------------------------------------------------------------------------------- + Append (actual rows=0 loops=1) + InitPlan 1 (returns $0) + -> Result (actual rows=1 loops=1) + -> Append (actual rows=0 loops=1) + -> Bitmap Heap Scan on ab_a1_b1 ab_11 (actual rows=0 loops=1) + Recheck Cond: (a = 1) + Filter: (b = $0) + -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1) + Index Cond: (a = 1) + -> Bitmap Heap Scan on ab_a1_b2 ab_12 (never executed) + Recheck Cond: (a = 1) + Filter: (b = $0) + -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed) + Index Cond: (a = 1) + -> Bitmap Heap Scan on ab_a1_b3 ab_13 (never executed) + Recheck Cond: (a = 1) + Filter: (b = $0) + -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed) + Index Cond: (a = 1) + -> Seq Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1) + Filter: (b = $0) + -> Seq Scan on ab_a1_b2 ab_2 (never executed) + Filter: (b = $0) + -> Seq Scan on ab_a1_b3 ab_3 (never executed) + Filter: (b = $0) + -> Seq Scan on ab_a2_b1 ab_4 (actual rows=0 loops=1) + Filter: (b = $0) + -> Seq Scan on ab_a2_b2 ab_5 (never executed) + Filter: (b = $0) + -> Seq Scan on ab_a2_b3 ab_6 (never executed) + Filter: (b = $0) + -> Seq Scan on ab_a3_b1 ab_7 (actual rows=0 loops=1) + Filter: (b = $0) + -> Seq Scan on ab_a3_b2 ab_8 (never executed) + Filter: (b = $0) + -> Seq Scan on ab_a3_b3 ab_9 (never executed) + Filter: (b = $0) +(37 rows) + +-- A case containing a UNION ALL with a non-partitioned child. +explain (analyze, costs off, summary off, timing off) +select * from (select * from ab where a = 1 union all (values(10,5)) union all select * from ab) ab where b = (select 1); + QUERY PLAN +------------------------------------------------------------------------------- + Append (actual rows=0 loops=1) + InitPlan 1 (returns $0) + -> Result (actual rows=1 loops=1) + -> Append (actual rows=0 loops=1) + -> Bitmap Heap Scan on ab_a1_b1 ab_11 (actual rows=0 loops=1) + Recheck Cond: (a = 1) + Filter: (b = $0) + -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1) + Index Cond: (a = 1) + -> Bitmap Heap Scan on ab_a1_b2 ab_12 (never executed) + Recheck Cond: (a = 1) + Filter: (b = $0) + -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed) + Index Cond: (a = 1) + -> Bitmap Heap Scan on ab_a1_b3 ab_13 (never executed) + Recheck Cond: (a = 1) + Filter: (b = $0) + -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed) + Index Cond: (a = 1) + -> Result (actual rows=0 loops=1) + One-Time Filter: (5 = $0) + -> Seq Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1) + Filter: (b = $0) + -> Seq Scan on ab_a1_b2 ab_2 (never executed) + Filter: (b = $0) + -> Seq Scan on ab_a1_b3 ab_3 (never executed) + Filter: (b = $0) + -> Seq Scan on ab_a2_b1 ab_4 (actual rows=0 loops=1) + Filter: (b = $0) + -> Seq Scan on ab_a2_b2 ab_5 (never executed) + Filter: (b = $0) + -> Seq Scan on ab_a2_b3 ab_6 (never executed) + Filter: (b = $0) + -> Seq Scan on ab_a3_b1 ab_7 (actual rows=0 loops=1) + Filter: (b = $0) + -> Seq Scan on ab_a3_b2 ab_8 (never executed) + Filter: (b = $0) + -> Seq Scan on ab_a3_b3 ab_9 (never executed) + Filter: (b = $0) +(39 rows) + +-- Another UNION ALL test, but containing a mix of exec init and exec run-time pruning. +create table xy_1 (x int, y int); +insert into xy_1 values(100,-10); +set enable_bitmapscan = 0; +set enable_indexscan = 0; +prepare ab_q6 as +select * from ( + select tableoid::regclass,a,b from ab +union all + select tableoid::regclass,x,y from xy_1 +union all + select tableoid::regclass,a,b from ab +) ab where a = $1 and b = (select -10); +-- Ensure the xy_1 subplan is not pruned. +explain (analyze, costs off, summary off, timing off) execute ab_q6(1); + QUERY PLAN +-------------------------------------------------- + Append (actual rows=0 loops=1) + Subplans Removed: 12 + InitPlan 1 (returns $0) + -> Result (actual rows=1 loops=1) + -> Seq Scan on ab_a1_b1 ab_1 (never executed) + Filter: ((a = $1) AND (b = $0)) + -> Seq Scan on ab_a1_b2 ab_2 (never executed) + Filter: ((a = $1) AND (b = $0)) + -> Seq Scan on ab_a1_b3 ab_3 (never executed) + Filter: ((a = $1) AND (b = $0)) + -> Seq Scan on xy_1 (actual rows=0 loops=1) + Filter: ((x = $1) AND (y = $0)) + Rows Removed by Filter: 1 + -> Seq Scan on ab_a1_b1 ab_4 (never executed) + Filter: ((a = $1) AND (b = $0)) + -> Seq Scan on ab_a1_b2 ab_5 (never executed) + Filter: ((a = $1) AND (b = $0)) + -> Seq Scan on ab_a1_b3 ab_6 (never executed) + Filter: ((a = $1) AND (b = $0)) +(19 rows) + +-- Ensure we see just the xy_1 row. +execute ab_q6(100); + tableoid | a | b +----------+-----+----- + xy_1 | 100 | -10 +(1 row) + +reset enable_bitmapscan; +reset enable_indexscan; +deallocate ab_q1; +deallocate ab_q2; +deallocate ab_q3; +deallocate ab_q4; +deallocate ab_q5; +deallocate ab_q6; +-- UPDATE on a partition subtree has been seen to have problems. +insert into ab values (1,2); +explain (analyze, costs off, summary off, timing off) +update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a; + QUERY PLAN +------------------------------------------------------------------------------------------- + Update on ab_a1 (actual rows=0 loops=1) + Update on ab_a1_b1 ab_a1_1 + Update on ab_a1_b2 ab_a1_2 + Update on ab_a1_b3 ab_a1_3 + -> Nested Loop (actual rows=1 loops=1) + -> Append (actual rows=1 loops=1) + -> Bitmap Heap Scan on ab_a1_b1 ab_a1_1 (actual rows=0 loops=1) + Recheck Cond: (a = 1) + -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1) + Index Cond: (a = 1) + -> Bitmap Heap Scan on ab_a1_b2 ab_a1_2 (actual rows=1 loops=1) + Recheck Cond: (a = 1) + Heap Blocks: exact=1 + -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1) + Index Cond: (a = 1) + -> Bitmap Heap Scan on ab_a1_b3 ab_a1_3 (actual rows=0 loops=1) + Recheck Cond: (a = 1) + -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1) + Index Cond: (a = 1) + -> Materialize (actual rows=1 loops=1) + -> Append (actual rows=1 loops=1) + -> Bitmap Heap Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1) + Recheck Cond: (a = 1) + -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1) + Index Cond: (a = 1) + -> Bitmap Heap Scan on ab_a1_b2 ab_2 (actual rows=1 loops=1) + Recheck Cond: (a = 1) + Heap Blocks: exact=1 + -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1) + Index Cond: (a = 1) + -> Bitmap Heap Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1) + Recheck Cond: (a = 1) + -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1) + Index Cond: (a = 1) +(34 rows) + +table ab; + a | b +---+--- + 1 | 3 +(1 row) + +-- Test UPDATE where source relation has run-time pruning enabled +truncate ab; +insert into ab values (1, 1), (1, 2), (1, 3), (2, 1); +explain (analyze, costs off, summary off, timing off) +update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1); + QUERY PLAN +------------------------------------------------------------------------------ + Update on ab_a1 (actual rows=0 loops=1) + Update on ab_a1_b1 ab_a1_1 + Update on ab_a1_b2 ab_a1_2 + Update on ab_a1_b3 ab_a1_3 + InitPlan 1 (returns $0) + -> Result (actual rows=1 loops=1) + -> Nested Loop (actual rows=3 loops=1) + -> Append (actual rows=3 loops=1) + -> Seq Scan on ab_a1_b1 ab_a1_1 (actual rows=1 loops=1) + -> Seq Scan on ab_a1_b2 ab_a1_2 (actual rows=1 loops=1) + -> Seq Scan on ab_a1_b3 ab_a1_3 (actual rows=1 loops=1) + -> Materialize (actual rows=1 loops=3) + -> Append (actual rows=1 loops=1) + -> Seq Scan on ab_a2_b1 ab_a2_1 (actual rows=1 loops=1) + Filter: (b = $0) + -> Seq Scan on ab_a2_b2 ab_a2_2 (never executed) + Filter: (b = $0) + -> Seq Scan on ab_a2_b3 ab_a2_3 (never executed) + Filter: (b = $0) +(19 rows) + +select tableoid::regclass, * from ab; + tableoid | a | b +----------+---+--- + ab_a1_b3 | 1 | 3 + ab_a1_b3 | 1 | 3 + ab_a1_b3 | 1 | 3 + ab_a2_b1 | 2 | 1 +(4 rows) + +drop table ab, lprt_a; +-- Join +create table tbl1(col1 int); +insert into tbl1 values (501), (505); +-- Basic table +create table tprt (col1 int) partition by range (col1); +create table tprt_1 partition of tprt for values from (1) to (501); +create table tprt_2 partition of tprt for values from (501) to (1001); +create table tprt_3 partition of tprt for values from (1001) to (2001); +create table tprt_4 partition of tprt for values from (2001) to (3001); +create table tprt_5 partition of tprt for values from (3001) to (4001); +create table tprt_6 partition of tprt for values from (4001) to (5001); +create index tprt1_idx on tprt_1 (col1); +create index tprt2_idx on tprt_2 (col1); +create index tprt3_idx on tprt_3 (col1); +create index tprt4_idx on tprt_4 (col1); +create index tprt5_idx on tprt_5 (col1); +create index tprt6_idx on tprt_6 (col1); +insert into tprt values (10), (20), (501), (502), (505), (1001), (4500); +set enable_hashjoin = off; +set enable_mergejoin = off; +explain (analyze, costs off, summary off, timing off) +select * from tbl1 join tprt on tbl1.col1 > tprt.col1; + QUERY PLAN +-------------------------------------------------------------------------- + Nested Loop (actual rows=6 loops=1) + -> Seq Scan on tbl1 (actual rows=2 loops=1) + -> Append (actual rows=3 loops=2) + -> Index Scan using tprt1_idx on tprt_1 (actual rows=2 loops=2) + Index Cond: (col1 < tbl1.col1) + -> Index Scan using tprt2_idx on tprt_2 (actual rows=2 loops=1) + Index Cond: (col1 < tbl1.col1) + -> Index Scan using tprt3_idx on tprt_3 (never executed) + Index Cond: (col1 < tbl1.col1) + -> Index Scan using tprt4_idx on tprt_4 (never executed) + Index Cond: (col1 < tbl1.col1) + -> Index Scan using tprt5_idx on tprt_5 (never executed) + Index Cond: (col1 < tbl1.col1) + -> Index Scan using tprt6_idx on tprt_6 (never executed) + Index Cond: (col1 < tbl1.col1) +(15 rows) + +explain (analyze, costs off, summary off, timing off) +select * from tbl1 join tprt on tbl1.col1 = tprt.col1; + QUERY PLAN +-------------------------------------------------------------------------- + Nested Loop (actual rows=2 loops=1) + -> Seq Scan on tbl1 (actual rows=2 loops=1) + -> Append (actual rows=1 loops=2) + -> Index Scan using tprt1_idx on tprt_1 (never executed) + Index Cond: (col1 = tbl1.col1) + -> Index Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2) + Index Cond: (col1 = tbl1.col1) + -> Index Scan using tprt3_idx on tprt_3 (never executed) + Index Cond: (col1 = tbl1.col1) + -> Index Scan using tprt4_idx on tprt_4 (never executed) + Index Cond: (col1 = tbl1.col1) + -> Index Scan using tprt5_idx on tprt_5 (never executed) + Index Cond: (col1 = tbl1.col1) + -> Index Scan using tprt6_idx on tprt_6 (never executed) + Index Cond: (col1 = tbl1.col1) +(15 rows) + +select tbl1.col1, tprt.col1 from tbl1 +inner join tprt on tbl1.col1 > tprt.col1 +order by tbl1.col1, tprt.col1; + col1 | col1 +------+------ + 501 | 10 + 501 | 20 + 505 | 10 + 505 | 20 + 505 | 501 + 505 | 502 +(6 rows) + +select tbl1.col1, tprt.col1 from tbl1 +inner join tprt on tbl1.col1 = tprt.col1 +order by tbl1.col1, tprt.col1; + col1 | col1 +------+------ + 501 | 501 + 505 | 505 +(2 rows) + +-- Multiple partitions +insert into tbl1 values (1001), (1010), (1011); +explain (analyze, costs off, summary off, timing off) +select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1; + QUERY PLAN +-------------------------------------------------------------------------- + Nested Loop (actual rows=23 loops=1) + -> Seq Scan on tbl1 (actual rows=5 loops=1) + -> Append (actual rows=5 loops=5) + -> Index Scan using tprt1_idx on tprt_1 (actual rows=2 loops=5) + Index Cond: (col1 < tbl1.col1) + -> Index Scan using tprt2_idx on tprt_2 (actual rows=3 loops=4) + Index Cond: (col1 < tbl1.col1) + -> Index Scan using tprt3_idx on tprt_3 (actual rows=1 loops=2) + Index Cond: (col1 < tbl1.col1) + -> Index Scan using tprt4_idx on tprt_4 (never executed) + Index Cond: (col1 < tbl1.col1) + -> Index Scan using tprt5_idx on tprt_5 (never executed) + Index Cond: (col1 < tbl1.col1) + -> Index Scan using tprt6_idx on tprt_6 (never executed) + Index Cond: (col1 < tbl1.col1) +(15 rows) + +explain (analyze, costs off, summary off, timing off) +select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1; + QUERY PLAN +-------------------------------------------------------------------------- + Nested Loop (actual rows=3 loops=1) + -> Seq Scan on tbl1 (actual rows=5 loops=1) + -> Append (actual rows=1 loops=5) + -> Index Scan using tprt1_idx on tprt_1 (never executed) + Index Cond: (col1 = tbl1.col1) + -> Index Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2) + Index Cond: (col1 = tbl1.col1) + -> Index Scan using tprt3_idx on tprt_3 (actual rows=0 loops=3) + Index Cond: (col1 = tbl1.col1) + -> Index Scan using tprt4_idx on tprt_4 (never executed) + Index Cond: (col1 = tbl1.col1) + -> Index Scan using tprt5_idx on tprt_5 (never executed) + Index Cond: (col1 = tbl1.col1) + -> Index Scan using tprt6_idx on tprt_6 (never executed) + Index Cond: (col1 = tbl1.col1) +(15 rows) + +select tbl1.col1, tprt.col1 from tbl1 +inner join tprt on tbl1.col1 > tprt.col1 +order by tbl1.col1, tprt.col1; + col1 | col1 +------+------ + 501 | 10 + 501 | 20 + 505 | 10 + 505 | 20 + 505 | 501 + 505 | 502 + 1001 | 10 + 1001 | 20 + 1001 | 501 + 1001 | 502 + 1001 | 505 + 1010 | 10 + 1010 | 20 + 1010 | 501 + 1010 | 502 + 1010 | 505 + 1010 | 1001 + 1011 | 10 + 1011 | 20 + 1011 | 501 + 1011 | 502 + 1011 | 505 + 1011 | 1001 +(23 rows) + +select tbl1.col1, tprt.col1 from tbl1 +inner join tprt on tbl1.col1 = tprt.col1 +order by tbl1.col1, tprt.col1; + col1 | col1 +------+------ + 501 | 501 + 505 | 505 + 1001 | 1001 +(3 rows) + +-- Last partition +delete from tbl1; +insert into tbl1 values (4400); +explain (analyze, costs off, summary off, timing off) +select * from tbl1 join tprt on tbl1.col1 < tprt.col1; + QUERY PLAN +-------------------------------------------------------------------------- + Nested Loop (actual rows=1 loops=1) + -> Seq Scan on tbl1 (actual rows=1 loops=1) + -> Append (actual rows=1 loops=1) + -> Index Scan using tprt1_idx on tprt_1 (never executed) + Index Cond: (col1 > tbl1.col1) + -> Index Scan using tprt2_idx on tprt_2 (never executed) + Index Cond: (col1 > tbl1.col1) + -> Index Scan using tprt3_idx on tprt_3 (never executed) + Index Cond: (col1 > tbl1.col1) + -> Index Scan using tprt4_idx on tprt_4 (never executed) + Index Cond: (col1 > tbl1.col1) + -> Index Scan using tprt5_idx on tprt_5 (never executed) + Index Cond: (col1 > tbl1.col1) + -> Index Scan using tprt6_idx on tprt_6 (actual rows=1 loops=1) + Index Cond: (col1 > tbl1.col1) +(15 rows) + +select tbl1.col1, tprt.col1 from tbl1 +inner join tprt on tbl1.col1 < tprt.col1 +order by tbl1.col1, tprt.col1; + col1 | col1 +------+------ + 4400 | 4500 +(1 row) + +-- No matching partition +delete from tbl1; +insert into tbl1 values (10000); +explain (analyze, costs off, summary off, timing off) +select * from tbl1 join tprt on tbl1.col1 = tprt.col1; + QUERY PLAN +------------------------------------------------------------------- + Nested Loop (actual rows=0 loops=1) + -> Seq Scan on tbl1 (actual rows=1 loops=1) + -> Append (actual rows=0 loops=1) + -> Index Scan using tprt1_idx on tprt_1 (never executed) + Index Cond: (col1 = tbl1.col1) + -> Index Scan using tprt2_idx on tprt_2 (never executed) + Index Cond: (col1 = tbl1.col1) + -> Index Scan using tprt3_idx on tprt_3 (never executed) + Index Cond: (col1 = tbl1.col1) + -> Index Scan using tprt4_idx on tprt_4 (never executed) + Index Cond: (col1 = tbl1.col1) + -> Index Scan using tprt5_idx on tprt_5 (never executed) + Index Cond: (col1 = tbl1.col1) + -> Index Scan using tprt6_idx on tprt_6 (never executed) + Index Cond: (col1 = tbl1.col1) +(15 rows) + +select tbl1.col1, tprt.col1 from tbl1 +inner join tprt on tbl1.col1 = tprt.col1 +order by tbl1.col1, tprt.col1; + col1 | col1 +------+------ +(0 rows) + +drop table tbl1, tprt; +-- Test with columns defined in varying orders between each level +create table part_abc (a int not null, b int not null, c int not null) partition by list (a); +create table part_bac (b int not null, a int not null, c int not null) partition by list (b); +create table part_cab (c int not null, a int not null, b int not null) partition by list (c); +create table part_abc_p1 (a int not null, b int not null, c int not null); +alter table part_abc attach partition part_bac for values in(1); +alter table part_bac attach partition part_cab for values in(2); +alter table part_cab attach partition part_abc_p1 for values in(3); +prepare part_abc_q1 (int, int, int) as +select * from part_abc where a = $1 and b = $2 and c = $3; +-- Single partition should be scanned. +explain (analyze, costs off, summary off, timing off) execute part_abc_q1 (1, 2, 3); + QUERY PLAN +---------------------------------------------------------- + Seq Scan on part_abc_p1 part_abc (actual rows=0 loops=1) + Filter: ((a = $1) AND (b = $2) AND (c = $3)) +(2 rows) + +deallocate part_abc_q1; +drop table part_abc; +-- Ensure that an Append node properly handles a sub-partitioned table +-- matching without any of its leaf partitions matching the clause. +create table listp (a int, b int) partition by list (a); +create table listp_1 partition of listp for values in(1) partition by list (b); +create table listp_1_1 partition of listp_1 for values in(1); +create table listp_2 partition of listp for values in(2) partition by list (b); +create table listp_2_1 partition of listp_2 for values in(2); +select * from listp where b = 1; + a | b +---+--- +(0 rows) + +-- Ensure that an Append node properly can handle selection of all first level +-- partitions before finally detecting the correct set of 2nd level partitions +-- which match the given parameter. +prepare q1 (int,int) as select * from listp where b in ($1,$2); +explain (analyze, costs off, summary off, timing off) execute q1 (1,1); + QUERY PLAN +------------------------------------------------------------- + Append (actual rows=0 loops=1) + Subplans Removed: 1 + -> Seq Scan on listp_1_1 listp_1 (actual rows=0 loops=1) + Filter: (b = ANY (ARRAY[$1, $2])) +(4 rows) + +explain (analyze, costs off, summary off, timing off) execute q1 (2,2); + QUERY PLAN +------------------------------------------------------------- + Append (actual rows=0 loops=1) + Subplans Removed: 1 + -> Seq Scan on listp_2_1 listp_1 (actual rows=0 loops=1) + Filter: (b = ANY (ARRAY[$1, $2])) +(4 rows) + +-- Try with no matching partitions. +explain (analyze, costs off, summary off, timing off) execute q1 (0,0); + QUERY PLAN +-------------------------------- + Append (actual rows=0 loops=1) + Subplans Removed: 2 +(2 rows) + +deallocate q1; +-- Test more complex cases where a not-equal condition further eliminates partitions. +prepare q1 (int,int,int,int) as select * from listp where b in($1,$2) and $3 <> b and $4 <> b; +-- Both partitions allowed by IN clause, but one disallowed by <> clause +explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,0); + QUERY PLAN +------------------------------------------------------------------------- + Append (actual rows=0 loops=1) + Subplans Removed: 1 + -> Seq Scan on listp_1_1 listp_1 (actual rows=0 loops=1) + Filter: ((b = ANY (ARRAY[$1, $2])) AND ($3 <> b) AND ($4 <> b)) +(4 rows) + +-- Both partitions allowed by IN clause, then both excluded again by <> clauses. +explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,1); + QUERY PLAN +-------------------------------- + Append (actual rows=0 loops=1) + Subplans Removed: 2 +(2 rows) + +-- Ensure Params that evaluate to NULL properly prune away all partitions +explain (analyze, costs off, summary off, timing off) +select * from listp where a = (select null::int); + QUERY PLAN +------------------------------------------------------ + Append (actual rows=0 loops=1) + InitPlan 1 (returns $0) + -> Result (actual rows=1 loops=1) + -> Seq Scan on listp_1_1 listp_1 (never executed) + Filter: (a = $0) + -> Seq Scan on listp_2_1 listp_2 (never executed) + Filter: (a = $0) +(7 rows) + +drop table listp; +-- +-- check that stable query clauses are only used in run-time pruning +-- +create table stable_qual_pruning (a timestamp) partition by range (a); +create table stable_qual_pruning1 partition of stable_qual_pruning + for values from ('2000-01-01') to ('2000-02-01'); +create table stable_qual_pruning2 partition of stable_qual_pruning + for values from ('2000-02-01') to ('2000-03-01'); +create table stable_qual_pruning3 partition of stable_qual_pruning + for values from ('3000-02-01') to ('3000-03-01'); +-- comparison against a stable value requires run-time pruning +explain (analyze, costs off, summary off, timing off) +select * from stable_qual_pruning where a < localtimestamp; + QUERY PLAN +-------------------------------------------------------------------------------------- + Append (actual rows=0 loops=1) + Subplans Removed: 1 + -> Seq Scan on stable_qual_pruning1 stable_qual_pruning_1 (actual rows=0 loops=1) + Filter: (a < LOCALTIMESTAMP) + -> Seq Scan on stable_qual_pruning2 stable_qual_pruning_2 (actual rows=0 loops=1) + Filter: (a < LOCALTIMESTAMP) +(6 rows) + +-- timestamp < timestamptz comparison is only stable, not immutable +explain (analyze, costs off, summary off, timing off) +select * from stable_qual_pruning where a < '2000-02-01'::timestamptz; + QUERY PLAN +-------------------------------------------------------------------------------------- + Append (actual rows=0 loops=1) + Subplans Removed: 2 + -> Seq Scan on stable_qual_pruning1 stable_qual_pruning_1 (actual rows=0 loops=1) + Filter: (a < 'Tue Feb 01 00:00:00 2000 PST'::timestamp with time zone) +(4 rows) + +-- check ScalarArrayOp cases +explain (analyze, costs off, summary off, timing off) +select * from stable_qual_pruning + where a = any(array['2010-02-01', '2020-01-01']::timestamp[]); + QUERY PLAN +-------------------------------- + Result (actual rows=0 loops=1) + One-Time Filter: false +(2 rows) + +explain (analyze, costs off, summary off, timing off) +select * from stable_qual_pruning + where a = any(array['2000-02-01', '2010-01-01']::timestamp[]); + QUERY PLAN +---------------------------------------------------------------------------------------------------------------- + Seq Scan on stable_qual_pruning2 stable_qual_pruning (actual rows=0 loops=1) + Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000","Fri Jan 01 00:00:00 2010"}'::timestamp without time zone[])) +(2 rows) + +explain (analyze, costs off, summary off, timing off) +select * from stable_qual_pruning + where a = any(array['2000-02-01', localtimestamp]::timestamp[]); + QUERY PLAN +------------------------------------------------------------------------------------------------------------ + Append (actual rows=0 loops=1) + Subplans Removed: 2 + -> Seq Scan on stable_qual_pruning2 stable_qual_pruning_1 (actual rows=0 loops=1) + Filter: (a = ANY (ARRAY['Tue Feb 01 00:00:00 2000'::timestamp without time zone, LOCALTIMESTAMP])) +(4 rows) + +explain (analyze, costs off, summary off, timing off) +select * from stable_qual_pruning + where a = any(array['2010-02-01', '2020-01-01']::timestamptz[]); + QUERY PLAN +-------------------------------- + Append (actual rows=0 loops=1) + Subplans Removed: 3 +(2 rows) + +explain (analyze, costs off, summary off, timing off) +select * from stable_qual_pruning + where a = any(array['2000-02-01', '2010-01-01']::timestamptz[]); + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------- + Append (actual rows=0 loops=1) + Subplans Removed: 2 + -> Seq Scan on stable_qual_pruning2 stable_qual_pruning_1 (actual rows=0 loops=1) + Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000 PST","Fri Jan 01 00:00:00 2010 PST"}'::timestamp with time zone[])) +(4 rows) + +explain (analyze, costs off, summary off, timing off) +select * from stable_qual_pruning + where a = any(null::timestamptz[]); + QUERY PLAN +-------------------------------------------------------------------------------------- + Append (actual rows=0 loops=1) + -> Seq Scan on stable_qual_pruning1 stable_qual_pruning_1 (actual rows=0 loops=1) + Filter: (a = ANY (NULL::timestamp with time zone[])) + -> Seq Scan on stable_qual_pruning2 stable_qual_pruning_2 (actual rows=0 loops=1) + Filter: (a = ANY (NULL::timestamp with time zone[])) + -> Seq Scan on stable_qual_pruning3 stable_qual_pruning_3 (actual rows=0 loops=1) + Filter: (a = ANY (NULL::timestamp with time zone[])) +(7 rows) + +drop table stable_qual_pruning; +-- +-- Check that pruning with composite range partitioning works correctly when +-- it must ignore clauses for trailing keys once it has seen a clause with +-- non-inclusive operator for an earlier key +-- +create table mc3p (a int, b int, c int) partition by range (a, abs(b), c); +create table mc3p0 partition of mc3p + for values from (0, 0, 0) to (0, maxvalue, maxvalue); +create table mc3p1 partition of mc3p + for values from (1, 1, 1) to (2, minvalue, minvalue); +create table mc3p2 partition of mc3p + for values from (2, minvalue, minvalue) to (3, maxvalue, maxvalue); +insert into mc3p values (0, 1, 1), (1, 1, 1), (2, 1, 1); +explain (analyze, costs off, summary off, timing off) +select * from mc3p where a < 3 and abs(b) = 1; + QUERY PLAN +-------------------------------------------------------- + Append (actual rows=3 loops=1) + -> Seq Scan on mc3p0 mc3p_1 (actual rows=1 loops=1) + Filter: ((a < 3) AND (abs(b) = 1)) + -> Seq Scan on mc3p1 mc3p_2 (actual rows=1 loops=1) + Filter: ((a < 3) AND (abs(b) = 1)) + -> Seq Scan on mc3p2 mc3p_3 (actual rows=1 loops=1) + Filter: ((a < 3) AND (abs(b) = 1)) +(7 rows) + +-- +-- Check that pruning with composite range partitioning works correctly when +-- a combination of runtime parameters is specified, not all of whose values +-- are available at the same time +-- +prepare ps1 as + select * from mc3p where a = $1 and abs(b) < (select 3); +explain (analyze, costs off, summary off, timing off) +execute ps1(1); + QUERY PLAN +-------------------------------------------------------- + Append (actual rows=1 loops=1) + Subplans Removed: 2 + InitPlan 1 (returns $0) + -> Result (actual rows=1 loops=1) + -> Seq Scan on mc3p1 mc3p_1 (actual rows=1 loops=1) + Filter: ((a = $1) AND (abs(b) < $0)) +(6 rows) + +deallocate ps1; +prepare ps2 as + select * from mc3p where a <= $1 and abs(b) < (select 3); +explain (analyze, costs off, summary off, timing off) +execute ps2(1); + QUERY PLAN +-------------------------------------------------------- + Append (actual rows=2 loops=1) + Subplans Removed: 1 + InitPlan 1 (returns $0) + -> Result (actual rows=1 loops=1) + -> Seq Scan on mc3p0 mc3p_1 (actual rows=1 loops=1) + Filter: ((a <= $1) AND (abs(b) < $0)) + -> Seq Scan on mc3p1 mc3p_2 (actual rows=1 loops=1) + Filter: ((a <= $1) AND (abs(b) < $0)) +(8 rows) + +deallocate ps2; +drop table mc3p; +-- Ensure runtime pruning works with initplans params with boolean types +create table boolvalues (value bool not null); +insert into boolvalues values('t'),('f'); +create table boolp (a bool) partition by list (a); +create table boolp_t partition of boolp for values in('t'); +create table boolp_f partition of boolp for values in('f'); +explain (analyze, costs off, summary off, timing off) +select * from boolp where a = (select value from boolvalues where value); + QUERY PLAN +----------------------------------------------------------- + Append (actual rows=0 loops=1) + InitPlan 1 (returns $0) + -> Seq Scan on boolvalues (actual rows=1 loops=1) + Filter: value + Rows Removed by Filter: 1 + -> Seq Scan on boolp_f boolp_1 (never executed) + Filter: (a = $0) + -> Seq Scan on boolp_t boolp_2 (actual rows=0 loops=1) + Filter: (a = $0) +(9 rows) + +explain (analyze, costs off, summary off, timing off) +select * from boolp where a = (select value from boolvalues where not value); + QUERY PLAN +----------------------------------------------------------- + Append (actual rows=0 loops=1) + InitPlan 1 (returns $0) + -> Seq Scan on boolvalues (actual rows=1 loops=1) + Filter: (NOT value) + Rows Removed by Filter: 1 + -> Seq Scan on boolp_f boolp_1 (actual rows=0 loops=1) + Filter: (a = $0) + -> Seq Scan on boolp_t boolp_2 (never executed) + Filter: (a = $0) +(9 rows) + +drop table boolp; +-- +-- Test run-time pruning of MergeAppend subnodes +-- +set enable_seqscan = off; +set enable_sort = off; +create table ma_test (a int, b int) partition by range (a); +create table ma_test_p1 partition of ma_test for values from (0) to (10); +create table ma_test_p2 partition of ma_test for values from (10) to (20); +create table ma_test_p3 partition of ma_test for values from (20) to (30); +insert into ma_test select x,x from generate_series(0,29) t(x); +create index on ma_test (b); +analyze ma_test; +prepare mt_q1 (int) as select a from ma_test where a >= $1 and a % 10 = 5 order by b; +explain (analyze, costs off, summary off, timing off) execute mt_q1(15); + QUERY PLAN +----------------------------------------------------------------------------------------- + Merge Append (actual rows=2 loops=1) + Sort Key: ma_test.b + Subplans Removed: 1 + -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_1 (actual rows=1 loops=1) + Filter: ((a >= $1) AND ((a % 10) = 5)) + Rows Removed by Filter: 9 + -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_2 (actual rows=1 loops=1) + Filter: ((a >= $1) AND ((a % 10) = 5)) + Rows Removed by Filter: 9 +(9 rows) + +execute mt_q1(15); + a +---- + 15 + 25 +(2 rows) + +explain (analyze, costs off, summary off, timing off) execute mt_q1(25); + QUERY PLAN +----------------------------------------------------------------------------------------- + Merge Append (actual rows=1 loops=1) + Sort Key: ma_test.b + Subplans Removed: 2 + -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_1 (actual rows=1 loops=1) + Filter: ((a >= $1) AND ((a % 10) = 5)) + Rows Removed by Filter: 9 +(6 rows) + +execute mt_q1(25); + a +---- + 25 +(1 row) + +-- Ensure MergeAppend behaves correctly when no subplans match +explain (analyze, costs off, summary off, timing off) execute mt_q1(35); + QUERY PLAN +-------------------------------------- + Merge Append (actual rows=0 loops=1) + Sort Key: ma_test.b + Subplans Removed: 3 +(3 rows) + +execute mt_q1(35); + a +--- +(0 rows) + +deallocate mt_q1; +prepare mt_q2 (int) as select * from ma_test where a >= $1 order by b limit 1; +-- Ensure output list looks sane when the MergeAppend has no subplans. +explain (analyze, verbose, costs off, summary off, timing off) execute mt_q2 (35); + QUERY PLAN +-------------------------------------------- + Limit (actual rows=0 loops=1) + Output: ma_test.a, ma_test.b + -> Merge Append (actual rows=0 loops=1) + Sort Key: ma_test.b + Subplans Removed: 3 +(5 rows) + +deallocate mt_q2; +-- ensure initplan params properly prune partitions +explain (analyze, costs off, summary off, timing off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b; + QUERY PLAN +----------------------------------------------------------------------------------------------- + Merge Append (actual rows=20 loops=1) + Sort Key: ma_test.b + InitPlan 2 (returns $1) + -> Result (actual rows=1 loops=1) + InitPlan 1 (returns $0) + -> Limit (actual rows=1 loops=1) + -> Index Scan using ma_test_p2_b_idx on ma_test_p2 (actual rows=1 loops=1) + Index Cond: (b IS NOT NULL) + -> Index Scan using ma_test_p1_b_idx on ma_test_p1 ma_test_1 (never executed) + Filter: (a >= $1) + -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_2 (actual rows=10 loops=1) + Filter: (a >= $1) + -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_3 (actual rows=10 loops=1) + Filter: (a >= $1) +(14 rows) + +reset enable_seqscan; +reset enable_sort; +drop table ma_test; +reset enable_indexonlyscan; +-- +-- check that pruning works properly when the partition key is of a +-- pseudotype +-- +-- array type list partition key +create table pp_arrpart (a int[]) partition by list (a); +create table pp_arrpart1 partition of pp_arrpart for values in ('{1}'); +create table pp_arrpart2 partition of pp_arrpart for values in ('{2, 3}', '{4, 5}'); +explain (costs off) select * from pp_arrpart where a = '{1}'; + QUERY PLAN +------------------------------------ + Seq Scan on pp_arrpart1 pp_arrpart + Filter: (a = '{1}'::integer[]) +(2 rows) + +explain (costs off) select * from pp_arrpart where a = '{1, 2}'; + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +explain (costs off) select * from pp_arrpart where a in ('{4, 5}', '{1}'); + QUERY PLAN +---------------------------------------------------------------------- + Append + -> Seq Scan on pp_arrpart1 pp_arrpart_1 + Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[])) + -> Seq Scan on pp_arrpart2 pp_arrpart_2 + Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[])) +(5 rows) + +explain (costs off) update pp_arrpart set a = a where a = '{1}'; + QUERY PLAN +-------------------------------------------- + Update on pp_arrpart + Update on pp_arrpart1 pp_arrpart_1 + -> Seq Scan on pp_arrpart1 pp_arrpart_1 + Filter: (a = '{1}'::integer[]) +(4 rows) + +explain (costs off) delete from pp_arrpart where a = '{1}'; + QUERY PLAN +-------------------------------------------- + Delete on pp_arrpart + Delete on pp_arrpart1 pp_arrpart_1 + -> Seq Scan on pp_arrpart1 pp_arrpart_1 + Filter: (a = '{1}'::integer[]) +(4 rows) + +drop table pp_arrpart; +-- array type hash partition key +create table pph_arrpart (a int[]) partition by hash (a); +create table pph_arrpart1 partition of pph_arrpart for values with (modulus 2, remainder 0); +create table pph_arrpart2 partition of pph_arrpart for values with (modulus 2, remainder 1); +insert into pph_arrpart values ('{1}'), ('{1, 2}'), ('{4, 5}'); +select tableoid::regclass, * from pph_arrpart order by 1; + tableoid | a +--------------+------- + pph_arrpart1 | {1,2} + pph_arrpart1 | {4,5} + pph_arrpart2 | {1} +(3 rows) + +explain (costs off) select * from pph_arrpart where a = '{1}'; + QUERY PLAN +-------------------------------------- + Seq Scan on pph_arrpart2 pph_arrpart + Filter: (a = '{1}'::integer[]) +(2 rows) + +explain (costs off) select * from pph_arrpart where a = '{1, 2}'; + QUERY PLAN +-------------------------------------- + Seq Scan on pph_arrpart1 pph_arrpart + Filter: (a = '{1,2}'::integer[]) +(2 rows) + +explain (costs off) select * from pph_arrpart where a in ('{4, 5}', '{1}'); + QUERY PLAN +---------------------------------------------------------------------- + Append + -> Seq Scan on pph_arrpart1 pph_arrpart_1 + Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[])) + -> Seq Scan on pph_arrpart2 pph_arrpart_2 + Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[])) +(5 rows) + +drop table pph_arrpart; +-- enum type list partition key +create type pp_colors as enum ('green', 'blue', 'black'); +create table pp_enumpart (a pp_colors) partition by list (a); +create table pp_enumpart_green partition of pp_enumpart for values in ('green'); +create table pp_enumpart_blue partition of pp_enumpart for values in ('blue'); +explain (costs off) select * from pp_enumpart where a = 'blue'; + QUERY PLAN +------------------------------------------ + Seq Scan on pp_enumpart_blue pp_enumpart + Filter: (a = 'blue'::pp_colors) +(2 rows) + +explain (costs off) select * from pp_enumpart where a = 'black'; + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +drop table pp_enumpart; +drop type pp_colors; +-- record type as partition key +create type pp_rectype as (a int, b int); +create table pp_recpart (a pp_rectype) partition by list (a); +create table pp_recpart_11 partition of pp_recpart for values in ('(1,1)'); +create table pp_recpart_23 partition of pp_recpart for values in ('(2,3)'); +explain (costs off) select * from pp_recpart where a = '(1,1)'::pp_rectype; + QUERY PLAN +-------------------------------------- + Seq Scan on pp_recpart_11 pp_recpart + Filter: (a = '(1,1)'::pp_rectype) +(2 rows) + +explain (costs off) select * from pp_recpart where a = '(1,2)'::pp_rectype; + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +drop table pp_recpart; +drop type pp_rectype; +-- range type partition key +create table pp_intrangepart (a int4range) partition by list (a); +create table pp_intrangepart12 partition of pp_intrangepart for values in ('[1,2]'); +create table pp_intrangepart2inf partition of pp_intrangepart for values in ('[2,)'); +explain (costs off) select * from pp_intrangepart where a = '[1,2]'::int4range; + QUERY PLAN +----------------------------------------------- + Seq Scan on pp_intrangepart12 pp_intrangepart + Filter: (a = '[1,3)'::int4range) +(2 rows) + +explain (costs off) select * from pp_intrangepart where a = '(1,2)'::int4range; + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +drop table pp_intrangepart; +-- +-- Ensure the enable_partition_prune GUC properly disables partition pruning. +-- +create table pp_lp (a int, value int) partition by list (a); +create table pp_lp1 partition of pp_lp for values in(1); +create table pp_lp2 partition of pp_lp for values in(2); +explain (costs off) select * from pp_lp where a = 1; + QUERY PLAN +-------------------------- + Seq Scan on pp_lp1 pp_lp + Filter: (a = 1) +(2 rows) + +explain (costs off) update pp_lp set value = 10 where a = 1; + QUERY PLAN +---------------------------------- + Update on pp_lp + Update on pp_lp1 pp_lp_1 + -> Seq Scan on pp_lp1 pp_lp_1 + Filter: (a = 1) +(4 rows) + +explain (costs off) delete from pp_lp where a = 1; + QUERY PLAN +---------------------------------- + Delete on pp_lp + Delete on pp_lp1 pp_lp_1 + -> Seq Scan on pp_lp1 pp_lp_1 + Filter: (a = 1) +(4 rows) + +set enable_partition_pruning = off; +set constraint_exclusion = 'partition'; -- this should not affect the result. +explain (costs off) select * from pp_lp where a = 1; + QUERY PLAN +---------------------------------- + Append + -> Seq Scan on pp_lp1 pp_lp_1 + Filter: (a = 1) + -> Seq Scan on pp_lp2 pp_lp_2 + Filter: (a = 1) +(5 rows) + +explain (costs off) update pp_lp set value = 10 where a = 1; + QUERY PLAN +---------------------------------------- + Update on pp_lp + Update on pp_lp1 pp_lp_1 + Update on pp_lp2 pp_lp_2 + -> Append + -> Seq Scan on pp_lp1 pp_lp_1 + Filter: (a = 1) + -> Seq Scan on pp_lp2 pp_lp_2 + Filter: (a = 1) +(8 rows) + +explain (costs off) delete from pp_lp where a = 1; + QUERY PLAN +---------------------------------------- + Delete on pp_lp + Delete on pp_lp1 pp_lp_1 + Delete on pp_lp2 pp_lp_2 + -> Append + -> Seq Scan on pp_lp1 pp_lp_1 + Filter: (a = 1) + -> Seq Scan on pp_lp2 pp_lp_2 + Filter: (a = 1) +(8 rows) + +set constraint_exclusion = 'off'; -- this should not affect the result. +explain (costs off) select * from pp_lp where a = 1; + QUERY PLAN +---------------------------------- + Append + -> Seq Scan on pp_lp1 pp_lp_1 + Filter: (a = 1) + -> Seq Scan on pp_lp2 pp_lp_2 + Filter: (a = 1) +(5 rows) + +explain (costs off) update pp_lp set value = 10 where a = 1; + QUERY PLAN +---------------------------------------- + Update on pp_lp + Update on pp_lp1 pp_lp_1 + Update on pp_lp2 pp_lp_2 + -> Append + -> Seq Scan on pp_lp1 pp_lp_1 + Filter: (a = 1) + -> Seq Scan on pp_lp2 pp_lp_2 + Filter: (a = 1) +(8 rows) + +explain (costs off) delete from pp_lp where a = 1; + QUERY PLAN +---------------------------------------- + Delete on pp_lp + Delete on pp_lp1 pp_lp_1 + Delete on pp_lp2 pp_lp_2 + -> Append + -> Seq Scan on pp_lp1 pp_lp_1 + Filter: (a = 1) + -> Seq Scan on pp_lp2 pp_lp_2 + Filter: (a = 1) +(8 rows) + +drop table pp_lp; +-- Ensure enable_partition_prune does not affect non-partitioned tables. +create table inh_lp (a int, value int); +create table inh_lp1 (a int, value int, check(a = 1)) inherits (inh_lp); +NOTICE: merging column "a" with inherited definition +NOTICE: merging column "value" with inherited definition +create table inh_lp2 (a int, value int, check(a = 2)) inherits (inh_lp); +NOTICE: merging column "a" with inherited definition +NOTICE: merging column "value" with inherited definition +set constraint_exclusion = 'partition'; +-- inh_lp2 should be removed in the following 3 cases. +explain (costs off) select * from inh_lp where a = 1; + QUERY PLAN +------------------------------------ + Append + -> Seq Scan on inh_lp inh_lp_1 + Filter: (a = 1) + -> Seq Scan on inh_lp1 inh_lp_2 + Filter: (a = 1) +(5 rows) + +explain (costs off) update inh_lp set value = 10 where a = 1; + QUERY PLAN +------------------------------------------------ + Update on inh_lp + Update on inh_lp inh_lp_1 + Update on inh_lp1 inh_lp_2 + -> Result + -> Append + -> Seq Scan on inh_lp inh_lp_1 + Filter: (a = 1) + -> Seq Scan on inh_lp1 inh_lp_2 + Filter: (a = 1) +(9 rows) + +explain (costs off) delete from inh_lp where a = 1; + QUERY PLAN +------------------------------------------ + Delete on inh_lp + Delete on inh_lp inh_lp_1 + Delete on inh_lp1 inh_lp_2 + -> Append + -> Seq Scan on inh_lp inh_lp_1 + Filter: (a = 1) + -> Seq Scan on inh_lp1 inh_lp_2 + Filter: (a = 1) +(8 rows) + +-- Ensure we don't exclude normal relations when we only expect to exclude +-- inheritance children +explain (costs off) update inh_lp1 set value = 10 where a = 2; + QUERY PLAN +--------------------------- + Update on inh_lp1 + -> Seq Scan on inh_lp1 + Filter: (a = 2) +(3 rows) + +drop table inh_lp cascade; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to table inh_lp1 +drop cascades to table inh_lp2 +reset enable_partition_pruning; +reset constraint_exclusion; +-- Check pruning for a partition tree containing only temporary relations +create temp table pp_temp_parent (a int) partition by list (a); +create temp table pp_temp_part_1 partition of pp_temp_parent for values in (1); +create temp table pp_temp_part_def partition of pp_temp_parent default; +explain (costs off) select * from pp_temp_parent where true; + QUERY PLAN +----------------------------------------------------- + Append + -> Seq Scan on pp_temp_part_1 pp_temp_parent_1 + -> Seq Scan on pp_temp_part_def pp_temp_parent_2 +(3 rows) + +explain (costs off) select * from pp_temp_parent where a = 2; + QUERY PLAN +--------------------------------------------- + Seq Scan on pp_temp_part_def pp_temp_parent + Filter: (a = 2) +(2 rows) + +drop table pp_temp_parent; +-- Stress run-time partition pruning a bit more, per bug reports +create temp table p (a int, b int, c int) partition by list (a); +create temp table p1 partition of p for values in (1); +create temp table p2 partition of p for values in (2); +create temp table q (a int, b int, c int) partition by list (a); +create temp table q1 partition of q for values in (1) partition by list (b); +create temp table q11 partition of q1 for values in (1) partition by list (c); +create temp table q111 partition of q11 for values in (1); +create temp table q2 partition of q for values in (2) partition by list (b); +create temp table q21 partition of q2 for values in (1); +create temp table q22 partition of q2 for values in (2); +insert into q22 values (2, 2, 3); +explain (costs off) +select * +from ( + select * from p + union all + select * from q1 + union all + select 1, 1, 1 + ) s(a, b, c) +where s.a = 1 and s.b = 1 and s.c = (select 1); + QUERY PLAN +---------------------------------------------------- + Append + InitPlan 1 (returns $0) + -> Result + -> Seq Scan on p1 p + Filter: ((a = 1) AND (b = 1) AND (c = $0)) + -> Seq Scan on q111 q1 + Filter: ((a = 1) AND (b = 1) AND (c = $0)) + -> Result + One-Time Filter: (1 = $0) +(9 rows) + +select * +from ( + select * from p + union all + select * from q1 + union all + select 1, 1, 1 + ) s(a, b, c) +where s.a = 1 and s.b = 1 and s.c = (select 1); + a | b | c +---+---+--- + 1 | 1 | 1 +(1 row) + +prepare q (int, int) as +select * +from ( + select * from p + union all + select * from q1 + union all + select 1, 1, 1 + ) s(a, b, c) +where s.a = $1 and s.b = $2 and s.c = (select 1); +explain (costs off) execute q (1, 1); + QUERY PLAN +--------------------------------------------------------------- + Append + Subplans Removed: 1 + InitPlan 1 (returns $0) + -> Result + -> Seq Scan on p1 p + Filter: ((a = $1) AND (b = $2) AND (c = $0)) + -> Seq Scan on q111 q1 + Filter: ((a = $1) AND (b = $2) AND (c = $0)) + -> Result + One-Time Filter: ((1 = $1) AND (1 = $2) AND (1 = $0)) +(10 rows) + +execute q (1, 1); + a | b | c +---+---+--- + 1 | 1 | 1 +(1 row) + +drop table p, q; +-- Ensure run-time pruning works correctly when we match a partitioned table +-- on the first level but find no matching partitions on the second level. +create table listp (a int, b int) partition by list (a); +create table listp1 partition of listp for values in(1); +create table listp2 partition of listp for values in(2) partition by list(b); +create table listp2_10 partition of listp2 for values in (10); +explain (analyze, costs off, summary off, timing off) +select * from listp where a = (select 2) and b <> 10; + QUERY PLAN +-------------------------------------------------- + Seq Scan on listp1 listp (actual rows=0 loops=1) + Filter: ((b <> 10) AND (a = $0)) + InitPlan 1 (returns $0) + -> Result (never executed) +(4 rows) + +-- +-- check that a partition directly accessed in a query is excluded with +-- constraint_exclusion = on +-- +-- turn off partition pruning, so that it doesn't interfere +set enable_partition_pruning to off; +-- setting constraint_exclusion to 'partition' disables exclusion +set constraint_exclusion to 'partition'; +explain (costs off) select * from listp1 where a = 2; + QUERY PLAN +-------------------- + Seq Scan on listp1 + Filter: (a = 2) +(2 rows) + +explain (costs off) update listp1 set a = 1 where a = 2; + QUERY PLAN +-------------------------- + Update on listp1 + -> Seq Scan on listp1 + Filter: (a = 2) +(3 rows) + +-- constraint exclusion enabled +set constraint_exclusion to 'on'; +explain (costs off) select * from listp1 where a = 2; + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +explain (costs off) update listp1 set a = 1 where a = 2; + QUERY PLAN +-------------------------------- + Update on listp1 + -> Result + One-Time Filter: false +(3 rows) + +reset constraint_exclusion; +reset enable_partition_pruning; +drop table listp; +-- Ensure run-time pruning works correctly for nested Append nodes +set parallel_setup_cost to 0; +set parallel_tuple_cost to 0; +create table listp (a int) partition by list(a); +create table listp_12 partition of listp for values in(1,2) partition by list(a); +create table listp_12_1 partition of listp_12 for values in(1); +create table listp_12_2 partition of listp_12 for values in(2); +-- Force the 2nd subnode of the Append to be non-parallel. This results in +-- a nested Append node because the mixed parallel / non-parallel paths cannot +-- be pulled into the top-level Append. +alter table listp_12_1 set (parallel_workers = 0); +-- Ensure that listp_12_2 is not scanned. (The nested Append is not seen in +-- the plan as it's pulled in setref.c due to having just a single subnode). +select explain_parallel_append('select * from listp where a = (select 1);'); + explain_parallel_append +---------------------------------------------------------------------- + Gather (actual rows=N loops=N) + Workers Planned: 2 + Params Evaluated: $0 + Workers Launched: N + InitPlan 1 (returns $0) + -> Result (actual rows=N loops=N) + -> Parallel Append (actual rows=N loops=N) + -> Seq Scan on listp_12_1 listp_1 (actual rows=N loops=N) + Filter: (a = $0) + -> Parallel Seq Scan on listp_12_2 listp_2 (never executed) + Filter: (a = $0) +(11 rows) + +-- Like the above but throw some more complexity at the planner by adding +-- a UNION ALL. We expect both sides of the union not to scan the +-- non-required partitions. +select explain_parallel_append( +'select * from listp where a = (select 1) + union all +select * from listp where a = (select 2);'); + explain_parallel_append +----------------------------------------------------------------------------------- + Append (actual rows=N loops=N) + -> Gather (actual rows=N loops=N) + Workers Planned: 2 + Params Evaluated: $0 + Workers Launched: N + InitPlan 1 (returns $0) + -> Result (actual rows=N loops=N) + -> Parallel Append (actual rows=N loops=N) + -> Seq Scan on listp_12_1 listp_1 (actual rows=N loops=N) + Filter: (a = $0) + -> Parallel Seq Scan on listp_12_2 listp_2 (never executed) + Filter: (a = $0) + -> Gather (actual rows=N loops=N) + Workers Planned: 2 + Params Evaluated: $1 + Workers Launched: N + InitPlan 2 (returns $1) + -> Result (actual rows=N loops=N) + -> Parallel Append (actual rows=N loops=N) + -> Seq Scan on listp_12_1 listp_4 (never executed) + Filter: (a = $1) + -> Parallel Seq Scan on listp_12_2 listp_5 (actual rows=N loops=N) + Filter: (a = $1) +(23 rows) + +drop table listp; +reset parallel_tuple_cost; +reset parallel_setup_cost; +-- Test case for run-time pruning with a nested Merge Append +set enable_sort to 0; +create table rangep (a int, b int) partition by range (a); +create table rangep_0_to_100 partition of rangep for values from (0) to (100) partition by list (b); +-- We need 3 sub-partitions. 1 to validate pruning worked and another two +-- because a single remaining partition would be pulled up to the main Append. +create table rangep_0_to_100_1 partition of rangep_0_to_100 for values in(1); +create table rangep_0_to_100_2 partition of rangep_0_to_100 for values in(2); +create table rangep_0_to_100_3 partition of rangep_0_to_100 for values in(3); +create table rangep_100_to_200 partition of rangep for values from (100) to (200); +create index on rangep (a); +-- Ensure run-time pruning works on the nested Merge Append +explain (analyze on, costs off, timing off, summary off) +select * from rangep where b IN((select 1),(select 2)) order by a; + QUERY PLAN +------------------------------------------------------------------------------------------------------------ + Append (actual rows=0 loops=1) + InitPlan 1 (returns $0) + -> Result (actual rows=1 loops=1) + InitPlan 2 (returns $1) + -> Result (actual rows=1 loops=1) + -> Merge Append (actual rows=0 loops=1) + Sort Key: rangep_2.a + -> Index Scan using rangep_0_to_100_1_a_idx on rangep_0_to_100_1 rangep_2 (actual rows=0 loops=1) + Filter: (b = ANY (ARRAY[$0, $1])) + -> Index Scan using rangep_0_to_100_2_a_idx on rangep_0_to_100_2 rangep_3 (actual rows=0 loops=1) + Filter: (b = ANY (ARRAY[$0, $1])) + -> Index Scan using rangep_0_to_100_3_a_idx on rangep_0_to_100_3 rangep_4 (never executed) + Filter: (b = ANY (ARRAY[$0, $1])) + -> Index Scan using rangep_100_to_200_a_idx on rangep_100_to_200 rangep_5 (actual rows=0 loops=1) + Filter: (b = ANY (ARRAY[$0, $1])) +(15 rows) + +reset enable_sort; +drop table rangep; +-- +-- Check that gen_prune_steps_from_opexps() works well for various cases of +-- clauses for different partition keys +-- +create table rp_prefix_test1 (a int, b varchar) partition by range(a, b); +create table rp_prefix_test1_p1 partition of rp_prefix_test1 for values from (1, 'a') to (1, 'b'); +create table rp_prefix_test1_p2 partition of rp_prefix_test1 for values from (2, 'a') to (2, 'b'); +-- Don't call get_steps_using_prefix() with the last partition key b plus +-- an empty prefix +explain (costs off) select * from rp_prefix_test1 where a <= 1 and b = 'a'; + QUERY PLAN +-------------------------------------------------- + Seq Scan on rp_prefix_test1_p1 rp_prefix_test1 + Filter: ((a <= 1) AND ((b)::text = 'a'::text)) +(2 rows) + +create table rp_prefix_test2 (a int, b int, c int) partition by range(a, b, c); +create table rp_prefix_test2_p1 partition of rp_prefix_test2 for values from (1, 1, 0) to (1, 1, 10); +create table rp_prefix_test2_p2 partition of rp_prefix_test2 for values from (2, 2, 0) to (2, 2, 10); +-- Don't call get_steps_using_prefix() with the last partition key c plus +-- an invalid prefix (ie, b = 1) +explain (costs off) select * from rp_prefix_test2 where a <= 1 and b = 1 and c >= 0; + QUERY PLAN +------------------------------------------------ + Seq Scan on rp_prefix_test2_p1 rp_prefix_test2 + Filter: ((a <= 1) AND (c >= 0) AND (b = 1)) +(2 rows) + +create table rp_prefix_test3 (a int, b int, c int, d int) partition by range(a, b, c, d); +create table rp_prefix_test3_p1 partition of rp_prefix_test3 for values from (1, 1, 1, 0) to (1, 1, 1, 10); +create table rp_prefix_test3_p2 partition of rp_prefix_test3 for values from (2, 2, 2, 0) to (2, 2, 2, 10); +-- Test that get_steps_using_prefix() handles a prefix that contains multiple +-- clauses for the partition key b (ie, b >= 1 and b >= 2) +explain (costs off) select * from rp_prefix_test3 where a >= 1 and b >= 1 and b >= 2 and c >= 2 and d >= 0; + QUERY PLAN +-------------------------------------------------------------------------- + Seq Scan on rp_prefix_test3_p2 rp_prefix_test3 + Filter: ((a >= 1) AND (b >= 1) AND (b >= 2) AND (c >= 2) AND (d >= 0)) +(2 rows) + +-- Test that get_steps_using_prefix() handles a prefix that contains multiple +-- clauses for the partition key b (ie, b >= 1 and b = 2) (This also tests +-- that the caller arranges clauses in that prefix in the required order) +explain (costs off) select * from rp_prefix_test3 where a >= 1 and b >= 1 and b = 2 and c = 2 and d >= 0; + QUERY PLAN +------------------------------------------------------------------------ + Seq Scan on rp_prefix_test3_p2 rp_prefix_test3 + Filter: ((a >= 1) AND (b >= 1) AND (d >= 0) AND (b = 2) AND (c = 2)) +(2 rows) + +drop table rp_prefix_test1; +drop table rp_prefix_test2; +drop table rp_prefix_test3; +-- +-- Test that get_steps_using_prefix() handles IS NULL clauses correctly +-- +create 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); +-- create 8 partitions +select 'create table hp_prefix_test_p' || x::text || ' partition of hp_prefix_test for values with (modulus 8, remainder ' || x::text || ');' +from generate_Series(0,7) x; + ?column? +------------------------------------------------------------------------------------------------------ + create table hp_prefix_test_p0 partition of hp_prefix_test for values with (modulus 8, remainder 0); + create table hp_prefix_test_p1 partition of hp_prefix_test for values with (modulus 8, remainder 1); + create table hp_prefix_test_p2 partition of hp_prefix_test for values with (modulus 8, remainder 2); + create table hp_prefix_test_p3 partition of hp_prefix_test for values with (modulus 8, remainder 3); + create table hp_prefix_test_p4 partition of hp_prefix_test for values with (modulus 8, remainder 4); + create table hp_prefix_test_p5 partition of hp_prefix_test for values with (modulus 8, remainder 5); + create table hp_prefix_test_p6 partition of hp_prefix_test for values with (modulus 8, remainder 6); + create table hp_prefix_test_p7 partition of hp_prefix_test for values with (modulus 8, remainder 7); +(8 rows) + +\gexec +create table hp_prefix_test_p0 partition of hp_prefix_test for values with (modulus 8, remainder 0); +create table hp_prefix_test_p1 partition of hp_prefix_test for values with (modulus 8, remainder 1); +create table hp_prefix_test_p2 partition of hp_prefix_test for values with (modulus 8, remainder 2); +create table hp_prefix_test_p3 partition of hp_prefix_test for values with (modulus 8, remainder 3); +create table hp_prefix_test_p4 partition of hp_prefix_test for values with (modulus 8, remainder 4); +create table hp_prefix_test_p5 partition of hp_prefix_test for values with (modulus 8, remainder 5); +create table hp_prefix_test_p6 partition of hp_prefix_test for values with (modulus 8, remainder 6); +create table hp_prefix_test_p7 partition of hp_prefix_test for values with (modulus 8, remainder 7); +-- insert 16 rows, one row for each test to perform. +insert into hp_prefix_test +select + case a when 0 then null else 1 end, + case b when 0 then null else 2 end, + case c when 0 then null else 3 end, + case d when 0 then null else 4 end +from + generate_series(0,1) a, + generate_series(0,1) b, + generate_Series(0,1) c, + generate_Series(0,1) d; +-- Ensure partition pruning works correctly for each combination of IS NULL +-- and equality quals. This may seem a little excessive, but there have been +-- a number of bugs in this area over the years. We make use of row only +-- output to reduce the size of the expected results. +\t on +select + 'explain (costs off) select tableoid::regclass,* from hp_prefix_test where ' || + string_agg(c.colname || case when g.s & (1 << c.colpos) = 0 then ' is null' else ' = ' || (colpos+1)::text end, ' and ' order by c.colpos) +from (values('a',0),('b',1),('c',2),('d',3)) c(colname, colpos), generate_Series(0,15) g(s) +group by g.s +order by g.s; + explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d is null + explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d is null + explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d is null + explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d is null + explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d is null + explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d is null + explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d is null + explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d is null + explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d = 4 + explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d = 4 + explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d = 4 + explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d = 4 + explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d = 4 + explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d = 4 + explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d = 4 + explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d = 4 + +\gexec +explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d is null + Seq Scan on hp_prefix_test_p0 hp_prefix_test + Filter: ((a IS NULL) AND (b IS NULL) AND (c IS NULL) AND (d IS NULL)) + +explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d is null + Seq Scan on hp_prefix_test_p1 hp_prefix_test + Filter: ((b IS NULL) AND (c IS NULL) AND (d IS NULL) AND (a = 1)) + +explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d is null + Seq Scan on hp_prefix_test_p2 hp_prefix_test + Filter: ((a IS NULL) AND (c IS NULL) AND (d IS NULL) AND (b = 2)) + +explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d is null + Seq Scan on hp_prefix_test_p4 hp_prefix_test + Filter: ((c IS NULL) AND (d IS NULL) AND (a = 1) AND (b = 2)) + +explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d is null + Seq Scan on hp_prefix_test_p3 hp_prefix_test + Filter: ((a IS NULL) AND (b IS NULL) AND (d IS NULL) AND (c = 3)) + +explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d is null + Seq Scan on hp_prefix_test_p7 hp_prefix_test + Filter: ((b IS NULL) AND (d IS NULL) AND (a = 1) AND (c = 3)) + +explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d is null + Seq Scan on hp_prefix_test_p4 hp_prefix_test + Filter: ((a IS NULL) AND (d IS NULL) AND (b = 2) AND (c = 3)) + +explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d is null + Seq Scan on hp_prefix_test_p5 hp_prefix_test + Filter: ((d IS NULL) AND (a = 1) AND (b = 2) AND (c = 3)) + +explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d = 4 + Seq Scan on hp_prefix_test_p4 hp_prefix_test + Filter: ((a IS NULL) AND (b IS NULL) AND (c IS NULL) AND (d = 4)) + +explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d = 4 + Seq Scan on hp_prefix_test_p6 hp_prefix_test + Filter: ((b IS NULL) AND (c IS NULL) AND (a = 1) AND (d = 4)) + +explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d = 4 + Seq Scan on hp_prefix_test_p5 hp_prefix_test + Filter: ((a IS NULL) AND (c IS NULL) AND (b = 2) AND (d = 4)) + +explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d = 4 + Seq Scan on hp_prefix_test_p6 hp_prefix_test + Filter: ((c IS NULL) AND (a = 1) AND (b = 2) AND (d = 4)) + +explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d = 4 + Seq Scan on hp_prefix_test_p4 hp_prefix_test + Filter: ((a IS NULL) AND (b IS NULL) AND (c = 3) AND (d = 4)) + +explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d = 4 + Seq Scan on hp_prefix_test_p5 hp_prefix_test + Filter: ((b IS NULL) AND (a = 1) AND (c = 3) AND (d = 4)) + +explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d = 4 + Seq Scan on hp_prefix_test_p6 hp_prefix_test + Filter: ((a IS NULL) AND (b = 2) AND (c = 3) AND (d = 4)) + +explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d = 4 + Seq Scan on hp_prefix_test_p4 hp_prefix_test + Filter: ((a = 1) AND (b = 2) AND (c = 3) AND (d = 4)) + +-- And ensure we get exactly 1 row from each. Again, all 16 possible combinations. +select + 'select tableoid::regclass,* from hp_prefix_test where ' || + string_agg(c.colname || case when g.s & (1 << c.colpos) = 0 then ' is null' else ' = ' || (colpos+1)::text end, ' and ' order by c.colpos) +from (values('a',0),('b',1),('c',2),('d',3)) c(colname, colpos), generate_Series(0,15) g(s) +group by g.s +order by g.s; + select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d is null + select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d is null + select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d is null + select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d is null + select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d is null + select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d is null + select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d is null + select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d is null + select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d = 4 + select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d = 4 + select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d = 4 + select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d = 4 + select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d = 4 + select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d = 4 + select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d = 4 + select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d = 4 + +\gexec +select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d is null + hp_prefix_test_p0 | | | | + +select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d is null + hp_prefix_test_p1 | 1 | | | + +select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d is null + hp_prefix_test_p2 | | 2 | | + +select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d is null + hp_prefix_test_p4 | 1 | 2 | | + +select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d is null + hp_prefix_test_p3 | | | 3 | + +select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d is null + hp_prefix_test_p7 | 1 | | 3 | + +select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d is null + hp_prefix_test_p4 | | 2 | 3 | + +select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d is null + hp_prefix_test_p5 | 1 | 2 | 3 | + +select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d = 4 + hp_prefix_test_p4 | | | | 4 + +select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d = 4 + hp_prefix_test_p6 | 1 | | | 4 + +select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d = 4 + hp_prefix_test_p5 | | 2 | | 4 + +select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d = 4 + hp_prefix_test_p6 | 1 | 2 | | 4 + +select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d = 4 + hp_prefix_test_p4 | | | 3 | 4 + +select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d = 4 + hp_prefix_test_p5 | 1 | | 3 | 4 + +select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d = 4 + hp_prefix_test_p6 | | 2 | 3 | 4 + +select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d = 4 + hp_prefix_test_p4 | 1 | 2 | 3 | 4 + +\t off +drop table hp_prefix_test; +-- +-- Check that gen_partprune_steps() detects self-contradiction from clauses +-- regardless of the order of the clauses (Here we use a custom operator to +-- prevent the equivclass.c machinery from reordering the clauses) +-- +create operator === ( + leftarg = int4, + rightarg = int4, + procedure = int4eq, + commutator = ===, + hashes +); +create operator class part_test_int4_ops2 +for type int4 +using hash as +operator 1 ===, +function 2 part_hashint4_noop(int4, int8); +create table hp_contradict_test (a int, b int) partition by hash (a part_test_int4_ops2, b part_test_int4_ops2); +create table hp_contradict_test_p1 partition of hp_contradict_test for values with (modulus 2, remainder 0); +create table hp_contradict_test_p2 partition of hp_contradict_test for values with (modulus 2, remainder 1); +explain (costs off) select * from hp_contradict_test where a is null and a === 1 and b === 1; + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +explain (costs off) select * from hp_contradict_test where a === 1 and b === 1 and a is null; + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +drop table hp_contradict_test; +drop operator class part_test_int4_ops2 using hash; +drop operator ===(int4, int4); |