| Quellcodebibliothek Statistik Leitseite products/Sources/formale Sprachen/C/Postgres/src/test/regress/expected/ (Postgres Database Version 18.4©) Datei vom 11.4.2026 mit Größe 198 kB |
|
Impressum partition_prune.out Sprache: unbekannt |
|
|
Spracherkennung für: .out vermutete Sprache: Unknown {[0] [0] [0]} [Methode: Schwerpunktbildung, einfache Gewichte, sechs Dimensionen] --
-- Test partitioning planner code -- -- Helper function which can be used for masking out portions of EXPLAIN -- ANALYZE which could contain information that's not consistent on all -- platforms. create function explain_analyze(query text) returns setof text language plpgsql as $$ declare ln text; begin for ln in execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', query) loop ln := regexp_replace(ln, 'Maximum Storage: \d+', 'Maximum Storage: N'); return next ln; end loop; end; $$; -- 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, maxva 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 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 ( -> 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 ( -> 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 ( -> 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 ( (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 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 ( -> 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 ( -> 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 ( -> 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 ( -> 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 ( (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 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 ( -> 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 ( -> 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 ( -> 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 ( -> 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 ( (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 --------------------------------------- Seq Scan on boolpart_default boolpart Filter: (a IS UNKNOWN) (2 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) -- try some other permutations with a NULL partition instead of a DEFAULT delete from boolpart where a is null; create table boolpart_null partition of boolpart for values in (null); insert into boolpart values(null); 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_null 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_null boolpart Filter: ((a IS NOT TRUE) AND (a IS NOT FALSE)) (2 rows) explain (costs off) select * from boolpart where a is not false; QUERY PLAN -------------------------------------------- Append -> Seq Scan on boolpart_t boolpart_1 Filter: (a IS NOT FALSE) -> Seq Scan on boolpart_null boolpart_2 Filter: (a IS NOT FALSE) (5 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 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 not false; a --- t (2 rows) select * from boolpart where a is not unknown; a --- f t (2 rows) -- check that all partitions are pruned when faced with conflicting clauses explain (costs off) select * from boolpart where a is not unknown and a is unknown; QUERY PLAN -------------------------- Result One-Time Filter: false (2 rows) explain (costs off) select * from boolpart where a is false and a is unknown; QUERY PLAN -------------------------- Result One-Time Filter: false (2 rows) explain (costs off) select * from boolpart where a is true and a is unknown; QUERY PLAN -------------------------- Result One-Time Filter: false (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) -- Try some other permutations with a NULL partition instead of a DEFAULT delete from iboolpart where a is null; create table iboolpart_null partition of iboolpart for values in (null); insert into iboolpart values(null); -- Pruning shouldn't take place for these. Just check the result is correct 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 not false; a --- t (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 (' create table boolrangep_ft partition of boolrangep for values from ('false', 'true', 0) to (' create table boolrangep_ff1 partition of boolrangep for values from ('false', 'false', 0) to create table boolrangep_ff2 partition of boolrangep for values from ('false', 'false', 50) t create table boolrangep_null partition of boolrangep default; -- 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) -- ensure we prune boolrangep_tf explain (costs off) select * from boolrangep where a is not true and not b and c = 25; QUERY PLAN ------------------------------------------------------------ Append -> Seq Scan on boolrangep_ff1 boolrangep_1 Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25)) -> Seq Scan on boolrangep_ff2 boolrangep_2 Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25)) -> Seq Scan on boolrangep_ft boolrangep_3 Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25)) -> Seq Scan on boolrangep_null boolrangep_4 Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25)) (9 rows) -- ensure we prune everything apart from boolrangep_tf and boolrangep_null explain (costs off) select * from boolrangep where a is not false and not b and c = 25; QUERY PLAN ------------------------------------------------------------- Append -> Seq Scan on boolrangep_tf boolrangep_1 Filter: ((a IS NOT FALSE) AND (NOT b) AND (c = 25)) -> Seq Scan on boolrangep_null boolrangep_2 Filter: ((a IS NOT FALSE) AND (NOT b) AND (c = 25)) (5 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)):: -> Seq Scan on coercepart_bc coercepart_2 Filter: ((a)::text = ANY ((ARRAY['ab'::character varying, (to_char(125, '999'::text)):: -> Seq Scan on coercepart_cd coercepart_3 Filter: ((a)::text = ANY ((ARRAY['ab'::character varying, (to_char(125, '999'::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, 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. 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. 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 an 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)::tex -> Seq Scan on rlp3_default rlp_2 Filter: ((b IS NOT NULL) AND ((b)::text <> 'ab'::text) AND ((b)::text <> 'cd'::text) AND ((b)::tex (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", s create table coll_pruning_multi1 partition of coll_pruning_multi for values from ('a', 'a' create table coll_pruning_multi2 partition of coll_pruning_multi for values from ('a', 'e' create table coll_pruning_multi3 partition of coll_pruning_multi for values from ('b', 'a' -- 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 s 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 (16 -- 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 -- 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_pru -- check that AlternativeSubPlan within a pruning expression gets cleaned up create table asptab (id int primary key) partition by range (id); create table asptab0 partition of asptab for values from (0) to (1); create table asptab1 partition of asptab for values from (1) to (2); explain (costs off) select * from (select exists (select 1 from int4_tbl tinner where f1 = touter.f1) as b from int4_tbl touter) ss, asptab where asptab.id > ss.b::int; QUERY PLAN -------------------------------------------------------------------- Nested Loop -> Seq Scan on int4_tbl touter -> Append -> Index Only Scan using asptab0_pkey on asptab0 asptab_1 Index Cond: (id > (EXISTS(SubPlan 3))::integer) SubPlan 4 -> Seq Scan on int4_tbl tinner_2 -> Index Only Scan using asptab1_pkey on asptab1 asptab_2 Index Cond: (id > (EXISTS(SubPlan 3))::integer) SubPlan 3 -> Seq Scan on int4_tbl tinner_1 Filter: (f1 = touter.f1) SubPlan 2 -> Seq Scan on int4_tbl tinner (14 rows) drop table asptab; -- -- 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_test_int4_ops and part_test_text_ops in test_setup.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 a 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 I -> 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 I -> 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 I (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, buffers off) execute ab_q1 (2, 2, 3); QUERY PLAN ------------------------------------------------------------ Append (actual rows=0.00 loops=1) Subplans Removed: 6 -> Seq Scan on ab_a2_b1 ab_1 (actual rows=0.00 loops=1) Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) -> Seq Scan on ab_a2_b2 ab_2 (actual rows=0.00 loops=1) Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) -> Seq Scan on ab_a2_b3 ab_3 (actual rows=0.00 loops=1) Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) (8 rows) explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (1, 2, 3); QUERY PLAN ------------------------------------------------------------ Append (actual rows=0.00 loops=1) Subplans Removed: 3 -> Seq Scan on ab_a1_b1 ab_1 (actual rows=0.00 loops=1) Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) -> Seq Scan on ab_a1_b2 ab_2 (actual rows=0.00 loops=1) Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) -> Seq Scan on ab_a1_b3 ab_3 (actual rows=0.00 loops=1) Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) -> Seq Scan on ab_a2_b1 ab_4 (actual rows=0.00 loops=1) Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) -> Seq Scan on ab_a2_b2 ab_5 (actual rows=0.00 loops=1) Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) -> Seq Scan on ab_a2_b3 ab_6 (actual rows=0.00 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, buffers off) execute ab_q1 (2, 2); QUERY PLAN ------------------------------------------------------------ Append (actual rows=0.00 loops=1) Subplans Removed: 4 -> Seq Scan on ab_a2_b1 ab_1 (actual rows=0.00 loops=1) Filter: ((a >= $1) AND (a <= $2) AND (b < 3)) -> Seq Scan on ab_a2_b2 ab_2 (actual rows=0.00 loops=1) Filter: ((a >= $1) AND (a <= $2) AND (b < 3)) (6 rows) explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 4); QUERY PLAN ------------------------------------------------------------ Append (actual rows=0.00 loops=1) Subplans Removed: 2 -> Seq Scan on ab_a2_b1 ab_1 (actual rows=0.00 loops=1) Filter: ((a >= $1) AND (a <= $2) AND (b < 3)) -> Seq Scan on ab_a2_b2 ab_2 (actual rows=0.00 loops=1) Filter: ((a >= $1) AND (a <= $2) AND (b < 3)) -> Seq Scan on ab_a3_b1 ab_3 (actual rows=0.00 loops=1) Filter: ((a >= $1) AND (a <= $2) AND (b < 3)) -> Seq Scan on ab_a3_b2 ab_4 (actual rows=0.00 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, buffers off) execute ab_q2 (2, 2); QUERY PLAN ----------------------------------------------------------------------- Append (actual rows=0.00 loops=1) Subplans Removed: 6 InitPlan 1 -> Result (actual rows=1.00 loops=1) -> Seq Scan on ab_a2_b1 ab_1 (actual rows=0.00 loops=1) Filter: ((a >= $1) AND (a <= $2) AND (b < (InitPlan 1).col1)) -> Seq Scan on ab_a2_b2 ab_2 (actual rows=0.00 loops=1) Filter: ((a >= $1) AND (a <= $2) AND (b < (InitPlan 1).col1)) -> Seq Scan on ab_a2_b3 ab_3 (never executed) Filter: ((a >= $1) AND (a <= $2) AND (b < (InitPlan 1).col1)) (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, buffers off) execute ab_q3 (2, 2); QUERY PLAN ----------------------------------------------------------------------- Append (actual rows=0.00 loops=1) Subplans Removed: 6 InitPlan 1 -> Result (actual rows=1.00 loops=1) -> Seq Scan on ab_a1_b2 ab_1 (actual rows=0.00 loops=1) Filter: ((b >= $1) AND (b <= $2) AND (a < (InitPlan 1).col1)) -> Seq Scan on ab_a2_b2 ab_2 (actual rows=0.00 loops=1) Filter: ((b >= $1) AND (b <= $2) AND (a < (InitPlan 1).col1)) -> Seq Scan on ab_a3_b2 ab_3 (never executed) Filter: ((b >= $1) AND (b <= $2) AND (a < (InitPlan 1).col1)) (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 -- Ensure pruning works using a stable function containing no Vars explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part whe QUERY PLAN --------------------------------------------------------------------- Append (actual rows=1.00 loops=1) Subplans Removed: 3 -> Seq Scan on list_part1 list_part_1 (actual rows=1.00 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, buffers off) select * from list_part whe QUERY PLAN --------------------------------------------------------------------- Append (actual rows=4.00 loops=1) -> Seq Scan on list_part1 list_part_1 (actual rows=1.00 loops=1) Filter: (a = list_part_fn(a)) -> Seq Scan on list_part2 list_part_2 (actual rows=1.00 loops=1) Filter: (a = list_part_fn(a)) -> Seq Scan on list_part3 list_part_3 (actual rows=1.00 loops=1) Filter: (a = list_part_fn(a)) -> Seq Scan on list_part4 list_part_4 (actual rows=1.00 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, buffers off) select * from list_part whe QUERY PLAN --------------------------------------------------------------------- Append (actual rows=0.00 loops=1) -> Seq Scan on list_part1 list_part_1 (actual rows=0.00 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.00 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.00 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.00 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, buffers off) %s', $1) loop ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); ln := regexp_replace(ln, 'actual rows=\d+(?:\.\d+)? loops=\d+', 'actual rows=N loops=N') ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N'); perform regexp_matches(ln, 'Index Searches: \d+'); if found then continue; end if; 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 explain_parallel_append -------------------------------------------------------------------------------- Aggregate (actual rows=N loops=N) InitPlan 1 -> Result (actual rows=N loops=N) InitPlan 2 -> Result (actual rows=N loops=N) -> Gather (actual rows=N loops=N) Workers Planned: 2 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 = (InitPlan 1).col1) OR (a = (InitPlan 2).col1))) -> Parallel Seq Scan on ab_a2_b2 ab_2 (never executed) Filter: ((b = 2) AND ((a = (InitPlan 1).col1) OR (a = (InitPlan 2).col1))) -> Parallel Seq Scan on ab_a3_b2 ab_3 (actual rows=N loops=N) Filter: ((b = 2) AND ((a = (InitPlan 1).col1) OR (a = (InitPlan 2).col1))) (15 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 wh 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 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 wh 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 wh 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 wh 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, buffers 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.00 loops=1) InitPlan 1 -> Aggregate (actual rows=1.00 loops=1) -> Seq Scan on lprt_a (actual rows=102.00 loops=1) InitPlan 2 -> Aggregate (actual rows=1.00 loops=1) -> Seq Scan on lprt_a lprt_a_1 (actual rows=102.00 loops=1) -> Bitmap Heap Scan on ab_a1_b1 ab_1 (never executed) Recheck Cond: (a = (InitPlan 1).col1) Filter: (b = (InitPlan 2).col1) -> Bitmap Index Scan on ab_a1_b1_a_idx (never executed) Index Cond: (a = (InitPlan 1).col1) Index Searches: 0 -> Bitmap Heap Scan on ab_a1_b2 ab_2 (never executed) Recheck Cond: (a = (InitPlan 1).col1) Filter: (b = (InitPlan 2).col1) -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed) Index Cond: (a = (InitPlan 1).col1) Index Searches: 0 -> Bitmap Heap Scan on ab_a1_b3 ab_3 (never executed) Recheck Cond: (a = (InitPlan 1).col1) Filter: (b = (InitPlan 2).col1) -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed) Index Cond: (a = (InitPlan 1).col1) Index Searches: 0 -> Bitmap Heap Scan on ab_a2_b1 ab_4 (never executed) Recheck Cond: (a = (InitPlan 1).col1) Filter: (b = (InitPlan 2).col1) -> Bitmap Index Scan on ab_a2_b1_a_idx (never executed) Index Cond: (a = (InitPlan 1).col1) Index Searches: 0 -> Bitmap Heap Scan on ab_a2_b2 ab_5 (never executed) Recheck Cond: (a = (InitPlan 1).col1) Filter: (b = (InitPlan 2).col1) -> Bitmap Index Scan on ab_a2_b2_a_idx (never executed) Index Cond: (a = (InitPlan 1).col1) Index Searches: 0 -> Bitmap Heap Scan on ab_a2_b3 ab_6 (never executed) Recheck Cond: (a = (InitPlan 1).col1) Filter: (b = (InitPlan 2).col1) -> Bitmap Index Scan on ab_a2_b3_a_idx (never executed) Index Cond: (a = (InitPlan 1).col1) Index Searches: 0 -> Bitmap Heap Scan on ab_a3_b1 ab_7 (never executed) Recheck Cond: (a = (InitPlan 1).col1) Filter: (b = (InitPlan 2).col1) -> Bitmap Index Scan on ab_a3_b1_a_idx (never executed) Index Cond: (a = (InitPlan 1).col1) Index Searches: 0 -> Bitmap Heap Scan on ab_a3_b2 ab_8 (actual rows=0.00 loops=1) Recheck Cond: (a = (InitPlan 1).col1) Filter: (b = (InitPlan 2).col1) -> Bitmap Index Scan on ab_a3_b2_a_idx (actual rows=0.00 loops=1) Index Cond: (a = (InitPlan 1).col1) Index Searches: 1 -> Bitmap Heap Scan on ab_a3_b3 ab_9 (never executed) Recheck Cond: (a = (InitPlan 1).col1) Filter: (b = (InitPlan 2).col1) -> Bitmap Index Scan on ab_a3_b3_a_idx (never executed) Index Cond: (a = (InitPlan 1).col1) Index Searches: 0 (61 rows) -- Test run-time partition pruning with UNION ALL parents explain (analyze, costs off, summary off, timing off, buffers 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.00 loops=1) InitPlan 1 -> Result (actual rows=1.00 loops=1) -> Append (actual rows=0.00 loops=1) -> Bitmap Heap Scan on ab_a1_b1 ab_11 (actual rows=0.00 loops=1) Recheck Cond: (a = 1) Filter: (b = (InitPlan 1).col1) -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0.00 loops=1) Index Cond: (a = 1) Index Searches: 1 -> Bitmap Heap Scan on ab_a1_b2 ab_12 (never executed) Recheck Cond: (a = 1) Filter: (b = (InitPlan 1).col1) -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed) Index Cond: (a = 1) Index Searches: 0 -> Bitmap Heap Scan on ab_a1_b3 ab_13 (never executed) Recheck Cond: (a = 1) Filter: (b = (InitPlan 1).col1) -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed) Index Cond: (a = 1) Index Searches: 0 -> Seq Scan on ab_a1_b1 ab_1 (actual rows=0.00 loops=1) Filter: (b = (InitPlan 1).col1) -> Seq Scan on ab_a1_b2 ab_2 (never executed) Filter: (b = (InitPlan 1).col1) -> Seq Scan on ab_a1_b3 ab_3 (never executed) Filter: (b = (InitPlan 1).col1) -> Seq Scan on ab_a2_b1 ab_4 (actual rows=0.00 loops=1) Filter: (b = (InitPlan 1).col1) -> Seq Scan on ab_a2_b2 ab_5 (never executed) Filter: (b = (InitPlan 1).col1) -> Seq Scan on ab_a2_b3 ab_6 (never executed) Filter: (b = (InitPlan 1).col1) -> Seq Scan on ab_a3_b1 ab_7 (actual rows=0.00 loops=1) Filter: (b = (InitPlan 1).col1) -> Seq Scan on ab_a3_b2 ab_8 (never executed) Filter: (b = (InitPlan 1).col1) -> Seq Scan on ab_a3_b3 ab_9 (never executed) Filter: (b = (InitPlan 1).col1) (40 rows) -- A case containing a UNION ALL with a non-partitioned child. explain (analyze, costs off, summary off, timing off, buffers off) select * from (select * from ab where a = 1 union all (values(10,5)) union all select * from ab) ab w QUERY PLAN -------------------------------------------------------------------------------- Append (actual rows=0.00 loops=1) InitPlan 1 -> Result (actual rows=1.00 loops=1) -> Append (actual rows=0.00 loops=1) -> Bitmap Heap Scan on ab_a1_b1 ab_11 (actual rows=0.00 loops=1) Recheck Cond: (a = 1) Filter: (b = (InitPlan 1).col1) -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0.00 loops=1) Index Cond: (a = 1) Index Searches: 1 -> Bitmap Heap Scan on ab_a1_b2 ab_12 (never executed) Recheck Cond: (a = 1) Filter: (b = (InitPlan 1).col1) -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed) Index Cond: (a = 1) Index Searches: 0 -> Bitmap Heap Scan on ab_a1_b3 ab_13 (never executed) Recheck Cond: (a = 1) Filter: (b = (InitPlan 1).col1) -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed) Index Cond: (a = 1) Index Searches: 0 -> Result (actual rows=0.00 loops=1) One-Time Filter: (5 = (InitPlan 1).col1) -> Seq Scan on ab_a1_b1 ab_1 (actual rows=0.00 loops=1) Filter: (b = (InitPlan 1).col1) -> Seq Scan on ab_a1_b2 ab_2 (never executed) Filter: (b = (InitPlan 1).col1) -> Seq Scan on ab_a1_b3 ab_3 (never executed) Filter: (b = (InitPlan 1).col1) -> Seq Scan on ab_a2_b1 ab_4 (actual rows=0.00 loops=1) Filter: (b = (InitPlan 1).col1) -> Seq Scan on ab_a2_b2 ab_5 (never executed) Filter: (b = (InitPlan 1).col1) -> Seq Scan on ab_a2_b3 ab_6 (never executed) Filter: (b = (InitPlan 1).col1) -> Seq Scan on ab_a3_b1 ab_7 (actual rows=0.00 loops=1) Filter: (b = (InitPlan 1).col1) -> Seq Scan on ab_a3_b2 ab_8 (never executed) Filter: (b = (InitPlan 1).col1) -> Seq Scan on ab_a3_b3 ab_9 (never executed) Filter: (b = (InitPlan 1).col1) (42 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, buffers off) execute ab_q6(1); QUERY PLAN -------------------------------------------------------- Append (actual rows=0.00 loops=1) Subplans Removed: 12 InitPlan 1 -> Result (actual rows=1.00 loops=1) -> Seq Scan on ab_a1_b1 ab_1 (never executed) Filter: ((a = $1) AND (b = (InitPlan 1).col1)) -> Seq Scan on ab_a1_b2 ab_2 (never executed) Filter: ((a = $1) AND (b = (InitPlan 1).col1)) -> Seq Scan on ab_a1_b3 ab_3 (never executed) Filter: ((a = $1) AND (b = (InitPlan 1).col1)) -> Seq Scan on xy_1 (actual rows=0.00 loops=1) Filter: ((x = $1) AND (y = (InitPlan 1).col1)) Rows Removed by Filter: 1 -> Seq Scan on ab_a1_b1 ab_4 (never executed) Filter: ((a = $1) AND (b = (InitPlan 1).col1)) -> Seq Scan on ab_a1_b2 ab_5 (never executed) Filter: ((a = $1) AND (b = (InitPlan 1).col1)) -> Seq Scan on ab_a1_b3 ab_6 (never executed) Filter: ((a = $1) AND (b = (InitPlan 1).col1)) (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); select explain_analyze(' update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;'); explain_analyze -------------------------------------------------------------------------------- Update on ab_a1 (actual rows=0.00 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.00 loops=1) -> Append (actual rows=1.00 loops=1) -> Bitmap Heap Scan on ab_a1_b1 ab_a1_1 (actual rows=0.00 loops=1) Recheck Cond: (a = 1) -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0.00 loops=1) Index Cond: (a = 1) Index Searches: 1 -> Bitmap Heap Scan on ab_a1_b2 ab_a1_2 (actual rows=1.00 loops=1) Recheck Cond: (a = 1) Heap Blocks: exact=1 -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1.00 loops=1) Index Cond: (a = 1) Index Searches: 1 -> Bitmap Heap Scan on ab_a1_b3 ab_a1_3 (actual rows=0.00 loops=1) Recheck Cond: (a = 1) Heap Blocks: exact=1 -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1.00 loops=1) Index Cond: (a = 1) Index Searches: 1 -> Materialize (actual rows=1.00 loops=1) Storage: Memory Maximum Storage: NkB -> Append (actual rows=1.00 loops=1) -> Bitmap Heap Scan on ab_a1_b1 ab_1 (actual rows=0.00 loops=1) Recheck Cond: (a = 1) -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0.00 loops=1) Index Cond: (a = 1) Index Searches: 1 -> Bitmap Heap Scan on ab_a1_b2 ab_2 (actual rows=1.00 loops=1) Recheck Cond: (a = 1) Heap Blocks: exact=1 -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1.00 loops=1) Index Cond: (a = 1) Index Searches: 1 -> Bitmap Heap Scan on ab_a1_b3 ab_3 (actual rows=0.00 loops=1) Recheck Cond: (a = 1) Heap Blocks: exact=1 -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1.00 loops=1) Index Cond: (a = 1) Index Searches: 1 (43 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); select explain_analyze(' update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1);'); explain_analyze -------------------------------------------------------------------------------- Update on ab_a1 (actual rows=0.00 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 -> Result (actual rows=1.00 loops=1) -> Nested Loop (actual rows=3.00 loops=1) -> Append (actual rows=3.00 loops=1) -> Seq Scan on ab_a1_b1 ab_a1_1 (actual rows=1.00 loops=1) -> Seq Scan on ab_a1_b2 ab_a1_2 (actual rows=1.00 loops=1) -> Seq Scan on ab_a1_b3 ab_a1_3 (actual rows=1.00 loops=1) -> Materialize (actual rows=1.00 loops=3) Storage: Memory Maximum Storage: NkB -> Append (actual rows=1.00 loops=1) -> Seq Scan on ab_a2_b1 ab_a2_1 (actual rows=1.00 loops=1) Filter: (b = (InitPlan 1).col1) -> Seq Scan on ab_a2_b2 ab_a2_2 (never executed) Filter: (b = (InitPlan 1).col1) -> Seq Scan on ab_a2_b3 ab_a2_3 (never executed) Filter: (b = (InitPlan 1).col1) (20 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, buffers off) select * from tbl1 join tprt on tbl1.col1 > tprt.col1; QUERY PLAN ----------------------------------------------------------------------------- Nested Loop (actual rows=6.00 loops=1) -> Seq Scan on tbl1 (actual rows=2.00 loops=1) -> Append (actual rows=3.00 loops=2) -> Index Scan using tprt1_idx on tprt_1 (actual rows=2.00 loops=2) Index Cond: (col1 < tbl1.col1) Index Searches: 2 -> Index Scan using tprt2_idx on tprt_2 (actual rows=2.00 loops=1) Index Cond: (col1 < tbl1.col1) Index Searches: 1 -> Index Scan using tprt3_idx on tprt_3 (never executed) Index Cond: (col1 < tbl1.col1) Index Searches: 0 -> Index Scan using tprt4_idx on tprt_4 (never executed) Index Cond: (col1 < tbl1.col1) Index Searches: 0 -> Index Scan using tprt5_idx on tprt_5 (never executed) Index Cond: (col1 < tbl1.col1) Index Searches: 0 -> Index Scan using tprt6_idx on tprt_6 (never executed) Index Cond: (col1 < tbl1.col1) Index Searches: 0 (21 rows) explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; QUERY PLAN ----------------------------------------------------------------------------- Nested Loop (actual rows=2.00 loops=1) -> Seq Scan on tbl1 (actual rows=2.00 loops=1) -> Append (actual rows=1.00 loops=2) -> Index Scan using tprt1_idx on tprt_1 (never executed) Index Cond: (col1 = tbl1.col1) Index Searches: 0 -> Index Scan using tprt2_idx on tprt_2 (actual rows=1.00 loops=2) Index Cond: (col1 = tbl1.col1) Index Searches: 2 -> Index Scan using tprt3_idx on tprt_3 (never executed) Index Cond: (col1 = tbl1.col1) Index Searches: 0 -> Index Scan using tprt4_idx on tprt_4 (never executed) Index Cond: (col1 = tbl1.col1) Index Searches: 0 -> Index Scan using tprt5_idx on tprt_5 (never executed) Index Cond: (col1 = tbl1.col1) Index Searches: 0 -> Index Scan using tprt6_idx on tprt_6 (never executed) Index Cond: (col1 = tbl1.col1) Index Searches: 0 (21 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, buffers off) select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1; QUERY PLAN ----------------------------------------------------------------------------- Nested Loop (actual rows=23.00 loops=1) -> Seq Scan on tbl1 (actual rows=5.00 loops=1) -> Append (actual rows=4.60 loops=5) -> Index Scan using tprt1_idx on tprt_1 (actual rows=2.00 loops=5) Index Cond: (col1 < tbl1.col1) Index Searches: 5 -> Index Scan using tprt2_idx on tprt_2 (actual rows=2.75 loops=4) Index Cond: (col1 < tbl1.col1) Index Searches: 4 -> Index Scan using tprt3_idx on tprt_3 (actual rows=1.00 loops=2) Index Cond: (col1 < tbl1.col1) Index Searches: 2 -> Index Scan using tprt4_idx on tprt_4 (never executed) Index Cond: (col1 < tbl1.col1) Index Searches: 0 -> Index Scan using tprt5_idx on tprt_5 (never executed) Index Cond: (col1 < tbl1.col1) Index Searches: 0 -> Index Scan using tprt6_idx on tprt_6 (never executed) Index Cond: (col1 < tbl1.col1) Index Searches: 0 (21 rows) explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1; QUERY PLAN ----------------------------------------------------------------------------- Nested Loop (actual rows=3.00 loops=1) -> Seq Scan on tbl1 (actual rows=5.00 loops=1) -> Append (actual rows=0.60 loops=5) -> Index Scan using tprt1_idx on tprt_1 (never executed) Index Cond: (col1 = tbl1.col1) Index Searches: 0 -> Index Scan using tprt2_idx on tprt_2 (actual rows=1.00 loops=2) Index Cond: (col1 = tbl1.col1) Index Searches: 2 -> Index Scan using tprt3_idx on tprt_3 (actual rows=0.33 loops=3) Index Cond: (col1 = tbl1.col1) Index Searches: 3 -> Index Scan using tprt4_idx on tprt_4 (never executed) Index Cond: (col1 = tbl1.col1) Index Searches: 0 -> Index Scan using tprt5_idx on tprt_5 (never executed) Index Cond: (col1 = tbl1.col1) Index Searches: 0 -> Index Scan using tprt6_idx on tprt_6 (never executed) Index Cond: (col1 = tbl1.col1) Index Searches: 0 (21 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, buffers off) select * from tbl1 join tprt on tbl1.col1 < tprt.col1; QUERY PLAN ----------------------------------------------------------------------------- Nested Loop (actual rows=1.00 loops=1) -> Seq Scan on tbl1 (actual rows=1.00 loops=1) -> Append (actual rows=1.00 loops=1) -> Index Scan using tprt1_idx on tprt_1 (never executed) Index Cond: (col1 > tbl1.col1) Index Searches: 0 -> Index Scan using tprt2_idx on tprt_2 (never executed) Index Cond: (col1 > tbl1.col1) Index Searches: 0 -> Index Scan using tprt3_idx on tprt_3 (never executed) Index Cond: (col1 > tbl1.col1) Index Searches: 0 -> Index Scan using tprt4_idx on tprt_4 (never executed) Index Cond: (col1 > tbl1.col1) Index Searches: 0 -> Index Scan using tprt5_idx on tprt_5 (never executed) Index Cond: (col1 > tbl1.col1) Index Searches: 0 -> Index Scan using tprt6_idx on tprt_6 (actual rows=1.00 loops=1) Index Cond: (col1 > tbl1.col1) Index Searches: 1 (21 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, buffers off) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; QUERY PLAN ------------------------------------------------------------------- Nested Loop (actual rows=0.00 loops=1) -> Seq Scan on tbl1 (actual rows=1.00 loops=1) -> Append (actual rows=0.00 loops=1) -> Index Scan using tprt1_idx on tprt_1 (never executed) Index Cond: (col1 = tbl1.col1) Index Searches: 0 -> Index Scan using tprt2_idx on tprt_2 (never executed) Index Cond: (col1 = tbl1.col1) Index Searches: 0 -> Index Scan using tprt3_idx on tprt_3 (never executed) Index Cond: (col1 = tbl1.col1) Index Searches: 0 -> Index Scan using tprt4_idx on tprt_4 (never executed) Index Cond: (col1 = tbl1.col1) Index Searches: 0 -> Index Scan using tprt5_idx on tprt_5 (never executed) Index Cond: (col1 = tbl1.col1) Index Searches: 0 -> Index Scan using tprt6_idx on tprt_6 (never executed) Index Cond: (col1 = tbl1.col1) Index Searches: 0 (21 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, buffers off) execute part_abc_q1 (1, 2, QUERY PLAN ------------------------------------------------------------- Seq Scan on part_abc_p1 part_abc (actual rows=0.00 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, buffers off) execute q1 (1,1); QUERY PLAN ---------------------------------------------------------------- Append (actual rows=0.00 loops=1) Subplans Removed: 1 -> Seq Scan on listp_1_1 listp_1 (actual rows=0.00 loops=1) Filter: (b = ANY (ARRAY[$1, $2])) (4 rows) explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (2,2); QUERY PLAN ---------------------------------------------------------------- Append (actual rows=0.00 loops=1) Subplans Removed: 1 -> Seq Scan on listp_2_1 listp_1 (actual rows=0.00 loops=1) Filter: (b = ANY (ARRAY[$1, $2])) (4 rows) -- Try with no matching partitions. explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (0,0); QUERY PLAN ----------------------------------- Append (actual rows=0.00 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, buffers off) execute q1 (1,2,2,0); QUERY PLAN ------------------------------------------------------------------------- Append (actual rows=0.00 loops=1) Subplans Removed: 1 -> Seq Scan on listp_1_1 listp_1 (actual rows=0.00 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, buffers off) execute q1 (1,2,2,1); QUERY PLAN ----------------------------------- Append (actual rows=0.00 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, buffers off) select * from listp where a = (select null::int); QUERY PLAN ------------------------------------------------------ Append (actual rows=0.00 loops=1) InitPlan 1 -> Result (actual rows=1.00 loops=1) -> Seq Scan on listp_1_1 listp_1 (never executed) Filter: (a = (InitPlan 1).col1) -> Seq Scan on listp_2_1 listp_2 (never executed) Filter: (a = (InitPlan 1).col1) (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, buffers off) select * from stable_qual_pruning where a < localtimestamp; QUERY PLAN -------------------------------------------------------------------------------- Append (actual rows=0.00 loops=1) Subplans Removed: 1 -> Seq Scan on stable_qual_pruning1 stable_qual_pruning_1 (actual rows=0.00 loops=1) Filter: (a < LOCALTIMESTAMP) -> Seq Scan on stable_qual_pruning2 stable_qual_pruning_2 (actual rows=0.00 loops=1) Filter: (a < LOCALTIMESTAMP) (6 rows) -- timestamp < timestamptz comparison is only stable, not immutable explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a < '2000-02-01'::timestamptz; QUERY PLAN -------------------------------------------------------------------------------- Append (actual rows=0.00 loops=1) Subplans Removed: 2 -> Seq Scan on stable_qual_pruning1 stable_qual_pruning_1 (actual rows=0.00 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, buffers off) select * from stable_qual_pruning where a = any(array['2010-02-01', '2020-01-01']::timestamp[]); QUERY PLAN ----------------------------------- Result (actual rows=0.00 loops=1) One-Time Filter: false (2 rows) explain (analyze, costs off, summary off, timing off, buffers 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.00 loops=1) Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000","Fri Jan 01 00:00:00 2010"}'::timestamp withou (2 rows) explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2000-02-01', localtimestamp]::timestamp[]); QUERY PLAN -------------------------------------------------------------------------------- Append (actual rows=0.00 loops=1) Subplans Removed: 2 -> Seq Scan on stable_qual_pruning2 stable_qual_pruning_1 (actual rows=0.00 loops=1) Filter: (a = ANY (ARRAY['Tue Feb 01 00:00:00 2000'::timestamp without time zone, LOCALTIMEST (4 rows) explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2010-02-01', '2020-01-01']::timestamptz[]); QUERY PLAN ----------------------------------- Append (actual rows=0.00 loops=1) Subplans Removed: 3 (2 rows) explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2000-02-01', '2010-01-01']::timestamptz[]); QUERY PLAN -------------------------------------------------------------------------------- Append (actual rows=0.00 loops=1) Subplans Removed: 2 -> Seq Scan on stable_qual_pruning2 stable_qual_pruning_1 (actual rows=0.00 loops=1) Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000 PST","Fri Jan 01 00:00:00 2010 PST"}'::timestamp (4 rows) explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(null::timestamptz[]); QUERY PLAN -------------------------------------------------------------------------------- Append (actual rows=0.00 loops=1) -> Seq Scan on stable_qual_pruning1 stable_qual_pruning_1 (actual rows=0.00 loops=1) Filter: (a = ANY (NULL::timestamp with time zone[])) -> Seq Scan on stable_qual_pruning2 stable_qual_pruning_2 (actual rows=0.00 loops=1) Filter: (a = ANY (NULL::timestamp with time zone[])) -> Seq Scan on stable_qual_pruning3 stable_qual_pruning_3 (actual rows=0.00 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, buffers off) select * from mc3p where a < 3 and abs(b) = 1; QUERY PLAN ----------------------------------------------------------- Append (actual rows=3.00 loops=1) -> Seq Scan on mc3p0 mc3p_1 (actual rows=1.00 loops=1) Filter: ((a < 3) AND (abs(b) = 1)) -> Seq Scan on mc3p1 mc3p_2 (actual rows=1.00 loops=1) Filter: ((a < 3) AND (abs(b) = 1)) -> Seq Scan on mc3p2 mc3p_3 (actual rows=1.00 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, buffers off) execute ps1(1); QUERY PLAN ------------------------------------------------------------- Append (actual rows=1.00 loops=1) Subplans Removed: 2 InitPlan 1 -> Result (actual rows=1.00 loops=1) -> Seq Scan on mc3p1 mc3p_1 (actual rows=1.00 loops=1) Filter: ((a = $1) AND (abs(b) < (InitPlan 1).col1)) (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, buffers off) execute ps2(1); QUERY PLAN -------------------------------------------------------------- Append (actual rows=2.00 loops=1) Subplans Removed: 1 InitPlan 1 -> Result (actual rows=1.00 loops=1) -> Seq Scan on mc3p0 mc3p_1 (actual rows=1.00 loops=1) Filter: ((a <= $1) AND (abs(b) < (InitPlan 1).col1)) -> Seq Scan on mc3p1 mc3p_2 (actual rows=1.00 loops=1) Filter: ((a <= $1) AND (abs(b) < (InitPlan 1).col1)) (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, buffers off) select * from boolp where a = (select value from boolvalues where value); QUERY PLAN -------------------------------------------------------------- Append (actual rows=0.00 loops=1) InitPlan 1 -> Seq Scan on boolvalues (actual rows=1.00 loops=1) Filter: value Rows Removed by Filter: 1 -> Seq Scan on boolp_f boolp_1 (never executed) Filter: (a = (InitPlan 1).col1) -> Seq Scan on boolp_t boolp_2 (actual rows=0.00 loops=1) Filter: (a = (InitPlan 1).col1) (9 rows) explain (analyze, costs off, summary off, timing off, buffers off) select * from boolp where a = (select value from boolvalues where not value); QUERY PLAN -------------------------------------------------------------- Append (actual rows=0.00 loops=1) InitPlan 1 -> Seq Scan on boolvalues (actual rows=1.00 loops=1) Filter: (NOT value) Rows Removed by Filter: 1 -> Seq Scan on boolp_f boolp_1 (actual rows=0.00 loops=1) Filter: (a = (InitPlan 1).col1) -> Seq Scan on boolp_t boolp_2 (never executed) Filter: (a = (InitPlan 1).col1) (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, buffers off) execute mt_q1(15); QUERY PLAN -------------------------------------------------------------------------------- Merge Append (actual rows=2.00 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.00 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) Rows Removed by Filter: 9 Index Searches: 1 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_2 (actual rows=1.00 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) Rows Removed by Filter: 9 Index Searches: 1 (11 rows) execute mt_q1(15); a ---- 15 25 (2 rows) explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(25); QUERY PLAN -------------------------------------------------------------------------------- Merge Append (actual rows=1.00 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.00 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) Rows Removed by Filter: 9 Index Searches: 1 (7 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, buffers off) execute mt_q1(35); QUERY PLAN ----------------------------------------- Merge Append (actual rows=0.00 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, buffers off) execute mt_q2 (35 QUERY PLAN ----------------------------------------------- Limit (actual rows=0.00 loops=1) Output: ma_test.a, ma_test.b -> Merge Append (actual rows=0.00 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, buffers off) select * from ma_test where QUERY PLAN -------------------------------------------------------------------------------- Merge Append (actual rows=20.00 loops=1) Sort Key: ma_test.b InitPlan 2 -> Result (actual rows=1.00 loops=1) InitPlan 1 -> Limit (actual rows=1.00 loops=1) -> Index Scan using ma_test_p2_b_idx on ma_test_p2 (actual rows=1.00 loops=1) Index Cond: (b IS NOT NULL) Index Searches: 1 -> Index Scan using ma_test_p1_b_idx on ma_test_p1 ma_test_1 (never executed) Filter: (a >= (InitPlan 2).col1) Index Searches: 0 -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_2 (actual rows=10.00 loops=1) Filter: (a >= (InitPlan 2).col1) Index Searches: 1 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_3 (actual rows=10.00 loops=1) Filter: (a >= (InitPlan 2).col1) Index Searches: 1 (18 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 -> Result -> Seq Scan on p1 p Filter: ((a = 1) AND (b = 1) AND (c = (InitPlan 1).col1)) -> Seq Scan on q111 q1 Filter: ((a = 1) AND (b = 1) AND (c = (InitPlan 1).col1)) -> Result One-Time Filter: (1 = (InitPlan 1).col1) (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 -> Result -> Seq Scan on p1 p Filter: ((a = $1) AND (b = $2) AND (c = (InitPlan 1).col1)) -> Seq Scan on q111 q1 Filter: ((a = $1) AND (b = $2) AND (c = (InitPlan 1).col1)) -> Result One-Time Filter: ((1 = $1) AND (1 = $2) AND (1 = (InitPlan 1).col1)) (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, buffers off) select * from listp where a = (select 2) and b <> 10; QUERY PLAN ----------------------------------------------------- Seq Scan on listp1 listp (actual rows=0.00 loops=1) Filter: ((b <> 10) AND (a = (InitPlan 1).col1)) InitPlan 1 -> 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 Workers Launched: N InitPlan 1 -> 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 = (InitPlan 1).col1) -> Parallel Seq Scan on listp_12_2 listp_2 (never executed) Filter: (a = (InitPlan 1).col1) (10 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 -------------------------------------------------------------------------------- Gather (actual rows=N loops=N) Workers Planned: 2 Workers Launched: N -> Parallel Append (actual rows=N loops=N) -> Parallel Append (actual rows=N loops=N) InitPlan 2 -> Result (actual rows=N loops=N) -> Seq Scan on listp_12_1 listp_1 (never executed) Filter: (a = (InitPlan 2).col1) -> Parallel Seq Scan on listp_12_2 listp_2 (actual rows=N loops=N) Filter: (a = (InitPlan 2).col1) -> Parallel Append (actual rows=N loops=N) InitPlan 1 -> Result (actual rows=N loops=N) -> Seq Scan on listp_12_1 listp_4 (actual rows=N loops=N) Filter: (a = (InitPlan 1).col1) -> Parallel Seq Scan on listp_12_2 listp_5 (never executed) Filter: (a = (InitPlan 1).col1) (18 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 lis -- 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, buffers off) select * from rangep where b IN((select 1),(select 2)) order by a; QUERY PLAN -------------------------------------------------------------------------------- Append (actual rows=0.00 loops=1) InitPlan 1 -> Result (actual rows=1.00 loops=1) InitPlan 2 -> Result (actual rows=1.00 loops=1) -> Merge Append (actual rows=0.00 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.0 Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1])) Index Searches: 1 -> Index Scan using rangep_0_to_100_2_a_idx on rangep_0_to_100_2 rangep_3 (actual rows=0.0 Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1])) Index Searches: 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[(InitPlan 1).col1, (InitPlan 2).col1])) Index Searches: 0 -> Index Scan using rangep_100_to_200_a_idx on rangep_100_to_200 rangep_5 (actual rows=0.0 Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1])) Index Searches: 1 (19 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, create table rp_prefix_test1_p2 partition of rp_prefix_test1 for values from (2, 'a') to (2, -- 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, create table rp_prefix_test2_p2 partition of rp_prefix_test2 for values from (2, 2, 0) to (2, -- 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 ( create table rp_prefix_test3_p2 partition of rp_prefix_test3 for values from (2, 2, 2, 0) to ( -- 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 par -- create 8 partitions select 'create table hp_prefix_test_p' || x::text || ' partition of hp_prefix_test for value from generate_Series(0,7) x; ?column? -------------------------------------------------------------------------------- create table hp_prefix_test_p0 partition of hp_prefix_test for values with (modulus 8, rema create table hp_prefix_test_p1 partition of hp_prefix_test for values with (modulus 8, rema create table hp_prefix_test_p2 partition of hp_prefix_test for values with (modulus 8, rema create table hp_prefix_test_p3 partition of hp_prefix_test for values with (modulus 8, rema create table hp_prefix_test_p4 partition of hp_prefix_test for values with (modulus 8, rema create table hp_prefix_test_p5 partition of hp_prefix_test for values with (modulus 8, rema create table hp_prefix_test_p6 partition of hp_prefix_test for values with (modulus 8, rema create table hp_prefix_test_p7 partition of hp_prefix_test for values with (modulus 8, rema (8 rows) \gexec create table hp_prefix_test_p0 partition of hp_prefix_test for values with (modulus 8, rema create table hp_prefix_test_p1 partition of hp_prefix_test for values with (modulus 8, rema create table hp_prefix_test_p2 partition of hp_prefix_test for values with (modulus 8, rema create table hp_prefix_test_p3 partition of hp_prefix_test for values with (modulus 8, rema create table hp_prefix_test_p4 partition of hp_prefix_test for values with (modulus 8, rema create table hp_prefix_test_p5 partition of hp_prefix_test for values with (modulus 8, rema create table hp_prefix_test_p6 partition of hp_prefix_test for values with (modulus 8, rema create table hp_prefix_test_p7 partition of hp_prefix_test for values with (modulus 8, rema -- 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+ from (values('a',0),('b',1),('c',2),('d',3)) c(colname, colpos), generate_Series(0,1 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 n explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null a explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 a explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c i explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is n explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null a explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 a explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is n explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null a explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 a explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c i explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is n explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null a explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 a explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = \gexec explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is n 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 a 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 a 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 i 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 n 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 a 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 a 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 = 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 n 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 a 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 a 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 i 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 n 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 a 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 a 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 = 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+ from (values('a',0),('b',1),('c',2),('d',3)) c(colname, colpos), generate_Series(0,1 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 select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d is nu select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d is nu 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 nu 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 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 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 nu 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 nu 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 nu 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 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 pa create table hp_contradict_test_p1 partition of hp_contradict_test for values with (modul create table hp_contradict_test_p2 partition of hp_contradict_test for values with (modul 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); drop function explain_analyze(text); -- Runtime pruning on UPDATE using WITH CHECK OPTIONS and RETURNING create table part_abc (a int, b text, c bool) partition by list (a); create table part_abc_1 (b text, a int, c bool); create table part_abc_2 (a int, c bool, b text); alter table part_abc attach partition part_abc_1 for values in (1); alter table part_abc attach partition part_abc_2 for values in (2); insert into part_abc values (1, 'b', true); insert into part_abc values (2, 'c', true); create view part_abc_view as select * from part_abc where b <> 'a' with check option; prepare update_part_abc_view as update part_abc_view set b = $2 where a = $1 returning *; -- Only the unpruned partition should be shown in the list of relations to be -- updated explain (verbose, costs off) execute update_part_abc_view (1, 'd'); QUERY PLAN ----------------------------------------------------------------------------- Update on public.part_abc Output: part_abc_1.a, part_abc_1.b, part_abc_1.c Update on public.part_abc_1 -> Append Subplans Removed: 1 -> Seq Scan on public.part_abc_1 Output: $2, part_abc_1.tableoid, part_abc_1.ctid Filter: ((part_abc_1.b <> 'a'::text) AND (part_abc_1.a = $1)) (8 rows) execute update_part_abc_view (1, 'd'); a | b | c ---+---+--- 1 | d | t (1 row) explain (verbose, costs off) execute update_part_abc_view (2, 'a'); QUERY PLAN ----------------------------------------------------------------------------- Update on public.part_abc Output: part_abc_1.a, part_abc_1.b, part_abc_1.c Update on public.part_abc_2 -> Append Subplans Removed: 1 -> Seq Scan on public.part_abc_2 Output: $2, part_abc_2.tableoid, part_abc_2.ctid Filter: ((part_abc_2.b <> 'a'::text) AND (part_abc_2.a = $1)) (8 rows) execute update_part_abc_view (2, 'a'); ERROR: new row violates check option for view "part_abc_view" DETAIL: Failing row contains (2, a, t). -- All pruned. explain (verbose, costs off) execute update_part_abc_view (3, 'a'); QUERY PLAN ---------------------------------------------------- Update on public.part_abc Output: part_abc_1.a, part_abc_1.b, part_abc_1.c -> Append Subplans Removed: 2 (4 rows) execute update_part_abc_view (3, 'a'); a | b | c ---+---+--- (0 rows) deallocate update_part_abc_view; -- Runtime pruning on MERGE using a stable function create function stable_one() returns int as $$ begin return 1; end; $$ language plpgsql stable explain (costs off) merge into part_abc_view pt using (select stable_one() as pid) as q join part_abc_1 pt1 on (q.pid = pt1.a) on pt.a = pt1.a when matched then delete returning pt.a; QUERY PLAN ----------------------------------------------------------------------- Merge on part_abc Merge on part_abc_1 -> Nested Loop -> Append Subplans Removed: 1 -> Seq Scan on part_abc_1 Filter: ((b <> 'a'::text) AND (a = stable_one())) -> Materialize -> Seq Scan on part_abc_1 pt1 Filter: (a = stable_one()) (10 rows) merge into part_abc_view pt using (select stable_one() as pid) as q join part_abc_1 pt1 on (q.pid = pt1.a) on pt.a = pt1.a when matched then delete returning pt.a; a --- 1 (1 row) table part_abc_view; a | b | c ---+---+--- 2 | c | t (1 row) -- All pruned. explain (costs off) merge into part_abc_view pt using (select stable_one() + 2 as pid) as q join part_abc_1 pt1 on (q.pid = pt1.a) on pt.a = pt1.a when matched then delete returning pt.a; QUERY PLAN ------------------------------------------------------ Merge on part_abc -> Nested Loop -> Append Subplans Removed: 2 -> Materialize -> Seq Scan on part_abc_1 pt1 Filter: (a = (stable_one() + 2)) (7 rows) merge into part_abc_view pt using (select stable_one() + 2 as pid) as q join part_abc_1 pt1 on (q.pid = pt1.a) on pt.a = pt1.a when matched then delete returning pt.a; a --- (0 rows) table part_abc_view; a | b | c ---+---+--- 2 | c | t (1 row) -- MERGE ... INSERT when all pruned from MERGE source. begin; explain (costs off) merge into part_abc_view pt using (select stable_one() + 1 as pid) as q join part_abc_2 pt2 on (q.pid = pt2.a) on pt.a = stable when not matched then insert values (1, 'd', false) returning pt.a; QUERY PLAN ------------------------------------------------ Merge on part_abc -> Nested Loop Left Join -> Seq Scan on part_abc_2 pt2 Filter: ((stable_one() + 1) = a) -> Materialize -> Append Subplans Removed: 2 (7 rows) merge into part_abc_view pt using (select stable_one() + 1 as pid) as q join part_abc_2 pt2 on (q.pid = pt2.a) on pt.a = stable when not matched then insert values (1, 'd', false) returning pt.a; a --- 1 (1 row) table part_abc_view; a | b | c ---+---+--- 1 | d | f 2 | c | t (2 rows) rollback; -- A case with multiple ModifyTable nodes. begin; create table part_abc_log (action text, a int, b text, c bool); explain (costs off) with t as ( merge into part_abc_view pt using (select stable_one() + 1 as pid) as q join part_abc_2 pt2 on (q.pid = pt2.a) on pt.a = stable when not matched then insert values (1, 'd', false) returning merge_action(), pt.* ) insert into part_abc_log select * from t returning *; QUERY PLAN -------------------------------------------------------- Insert on part_abc_log CTE t -> Merge on part_abc -> Nested Loop Left Join -> Seq Scan on part_abc_2 pt2 Filter: ((stable_one() + 1) = a) -> Materialize -> Append Subplans Removed: 2 -> CTE Scan on t (10 rows) with t as ( merge into part_abc_view pt using (select stable_one() + 1 as pid) as q join part_abc_2 pt2 on (q.pid = pt2.a) on pt.a = stable when not matched then insert values (1, 'd', false) returning merge_action(), pt.* ) insert into part_abc_log select * from t returning *; action | a | b | c --------+---+---+--- INSERT | 1 | d | f (1 row) table part_abc_view; a | b | c ---+---+--- 1 | d | f 2 | c | t (2 rows) table part_abc_log; action | a | b | c --------+---+---+--- INSERT | 1 | d | f (1 row) rollback; -- A case with nested MergeAppend with its own PartitionPruneInfo. create index on part_abc (a); alter table part_abc add d int; create table part_abc_3 partition of part_abc for values in (3, 4) partition by range (d); create table part_abc_3_1 partition of part_abc_3 for values from (minvalue) to (1); create table part_abc_3_2 partition of part_abc_3 for values from (1) to (100); create table part_abc_3_3 partition of part_abc_3 for values from (100) to (maxvalue); explain (costs off) select min(a) over (partition by a order by a) from part_abc where a >= stable_one() + 1 and d <= stab union all select min(a) over (partition by a order by a) from part_abc where a >= stable_one() + 1 and d >= stab QUERY PLAN -------------------------------------------------------------------------------- Append -> Subquery Scan on "*SELECT* 1_1" -> WindowAgg Window: w1 AS (PARTITION BY part_abc.a ORDER BY part_abc.a) -> Append Subplans Removed: 1 -> Index Scan using part_abc_2_a_idx on part_abc_2 part_abc_1 Index Cond: (a >= (stable_one() + 1)) Filter: (d <= stable_one()) -> Merge Append Sort Key: part_abc_3.a Subplans Removed: 1 -> Index Scan using part_abc_3_1_a_idx on part_abc_3_1 part_abc_3 Index Cond: (a >= (stable_one() + 1)) Filter: (d <= stable_one()) -> Index Scan using part_abc_3_2_a_idx on part_abc_3_2 part_abc_4 Index Cond: (a >= (stable_one() + 1)) Filter: (d <= stable_one()) -> Subquery Scan on "*SELECT* 2" -> WindowAgg Window: w1 AS (PARTITION BY part_abc_5.a ORDER BY part_abc_5.a) -> Append Subplans Removed: 1 -> Index Scan using part_abc_2_a_idx on part_abc_2 part_abc_6 Index Cond: (a >= (stable_one() + 1)) Filter: (d >= stable_one()) -> Merge Append Sort Key: a Subplans Removed: 1 -> Index Scan using part_abc_3_2_a_idx on part_abc_3_2 part_abc_8 Index Cond: (a >= (stable_one() + 1)) Filter: (d >= stable_one()) -> Index Scan using part_abc_3_3_a_idx on part_abc_3_3 part_abc_9 Index Cond: (a >= (stable_one() + 1)) Filter: (d >= stable_one()) (35 rows) drop view part_abc_view; drop table part_abc; -- -- Check that operands wrapped in PlaceHolderVars are matched to partition -- keys, allowing partition pruning to occur. PlaceHolderVars can be -- introduced when a subquery's output is used with grouping sets. -- create table phv_part (a int, b text) partition by list (a); create table phv_part_1 partition of phv_part for values in (1); create table phv_part_2 partition of phv_part for values in (2); create table phv_part_null partition of phv_part for values in (null); insert into phv_part values (1, 'one'), (2, 'two'), (null, 'null'); -- OpExpr: PHV-wrapped operand matched via equal() explain (costs off) select * from (select a, b from phv_part) t where a = 1 group by grouping sets (a, b); QUERY PLAN --------------------------------------- MixedAggregate Hash Key: phv_part.b Group Key: phv_part.a -> Seq Scan on phv_part_1 phv_part Filter: (a = 1) (5 rows) select * from (select a, b from phv_part) t where a = 1 group by grouping sets (a, b); a | b ---+----- 1 | | one (2 rows) -- OpExpr with RelabelType: PHV wrapped around a casted column explain (costs off) select * from (select a::oid as x, b from phv_part) t where x::int = 1 group by grouping sets (x, b); QUERY PLAN ------------------------------------------- HashAggregate Hash Key: (phv_part.a)::oid Hash Key: phv_part.b -> Seq Scan on phv_part_1 phv_part Filter: (((a)::oid)::integer = 1) (5 rows) select * from (select a::oid as x, b from phv_part) t where x::int = 1 group by grouping sets (x, b); x | b ---+----- 1 | | one (2 rows) -- ScalarArrayOpExpr: IN clause with PHV-wrapped operand explain (costs off) select * from (select a, b from phv_part) t where a in (1, null) group by grouping sets (a, b); QUERY PLAN --------------------------------------------------- HashAggregate Hash Key: phv_part.a Hash Key: phv_part.b -> Seq Scan on phv_part_1 phv_part Filter: (a = ANY ('{1,NULL}'::integer[])) (5 rows) select * from (select a, b from phv_part) t where a in (1, null) group by grouping sets (a, b); a | b ---+----- 1 | | one (2 rows) -- NullTest: IS NULL with PHV-wrapped operand explain (costs off) select * from (select a, b from phv_part) t where a is null group by grouping sets (a, b); QUERY PLAN ------------------------------------------ HashAggregate Hash Key: phv_part.a Hash Key: phv_part.b -> Seq Scan on phv_part_null phv_part Filter: (a IS NULL) (5 rows) select * from (select a, b from phv_part) t where a is null group by grouping sets (a, b); a | b ---+------ | | null (2 rows) drop table phv_part; -- BooleanTest: IS TRUE with PHV-wrapped boolean partition key create table phv_boolpart (a bool, b text) partition by list (a); create table phv_boolpart_t partition of phv_boolpart for values in (true); create table phv_boolpart_f partition of phv_boolpart for values in (false); create table phv_boolpart_null partition of phv_boolpart default; insert into phv_boolpart values (true, 'yes'), (false, 'no'), (null, 'unknown'); explain (costs off) select * from (select a, b from phv_boolpart) t where a is true group by grouping sets (a, b); QUERY PLAN ----------------------------------------------- HashAggregate Hash Key: phv_boolpart.a Hash Key: phv_boolpart.b -> Seq Scan on phv_boolpart_t phv_boolpart Filter: (a IS TRUE) (5 rows) select * from (select a, b from phv_boolpart) t where a is true group by grouping sets (a, b); a | b ---+----- t | | yes (2 rows) drop table phv_boolpart; [Seitenstruktur0.220Druckenetwas mehr zur Ethik2026-08-08] |
2026-08-25