On Thu Aug 20, 2026 at 10:31 AM -03, Jan Nidzwetzki wrote: > Hello Hackers, > > Thanks for the patch, this is a nice planner improvement. The v3 version > applies to the current master branch and check-world passes. I could > also confirm that the provided test case demonstrates the intended > change of the query plan. >
Hi Jan, Thanks a lot for the review and for sharing the regression. > On 27.01.26 16:42, Matheus Alcantara wrote: >> I think that unwrapping all Relabel types may ignore intermediate >> states. For example, consider Relabel A -> Relabel B -> Base expression: >> This code will unwrap Relabel A and Relabel B and check the expression >> collation directly on Base expression, shouldn't we check every layer, >> e.g Relabel A, Relabel B and Base Expression? Please see the attached v3 >> version with a simplified version of v2 that also check every layer of a >> RelabelType node. > > Checking every layer seems reasonable to me. Independently of that, I > believe I've identified a regression caused by the patch: in > exprs_known_equal(), RelabelType is stripped from 'expr' but not from > item1 and item2. A few lines later, 'equal(item1, expr)' and > 'equal(item2, expr)' are performed. If item1 or item2 is a RelabelType, > these comparisons now return false. > > This can happen when the code is called from have_partkey_equi_join() on > a partitioned table. item1/item2 come from rel->partexprs, and for an > expression partition key like '((c)::text)' on a varchar column, the > stored partition expression is itself a RelabelType node. > > I could reproduce the issue as follows. On master, the query plan below > is a partition-wise nested loop; after applying this patch, the plan no > longer contains a partition-wise join: > > [ ... ] > > I think the RelabelType nodes also need to be stripped from item1/item2 > before the comparison. find_ec_member_matching_expr() performs something > similar and the RelabelType is stripped for both arguments of the > equal() call. > > Best regards > Jan You're right, exprs_known_equal() was stripping the RelabelType only from the EC member's expression, but not from item1/item2. item1/item2 in the have_partkey_equi_join() path come directly from rel->partexprs, which for an expression partition key like ((c::text)) on a varchar column are themselves RelabelType-wrapped, so the raw equal() check happened to succeed by accident (both sides kept the wrapper). Once em_expr got stripped and item1/item2 didn't, that symmetry broke, and I could reproduce the regression you reported. Thanks. The attached v4 patch strips RelabelType from item1 and item2 as well, so both sides of the equal() check are treated consistently. While debbuging this down I found a second, related issue: partition pruning has the same asymmetry. match_clause_to_partition_key() in partprune.c strips RelabelType from the clause's operands but never from the partition key expression itself, so for the same class of RelabelType-wrapped expression keys, pruning silently fails to eliminate any partitions at all. That's actually what was behind an odd behavior I ran into while testing your case with data loaded: without pruning, all partitions stay in the Append, and at small/empty-table row counts the planner's cost estimate for the partitionwise nested loop and the non-partitionwise one are close enough that either can be chosen, which made it look like the join fix wasn't reliable. With pruning fixed too, pb1.c::text = '0002' now correctly prunes to a single partition on each side regardless of scale, so the ambiguity goes away entirely. v4 includes that fix as well. -- Matheus Alcantara EDB: https://www.enterprisedb.com
From facad4a7492cf7b1db1df9d39636e1cacfc540f3 Mon Sep 17 00:00:00 2001 From: Matheus Alcantara <[email protected]> Date: Tue, 27 Jan 2026 11:59:15 -0300 Subject: [PATCH v4] Enable partitionwise join for partition keys wrapped by RelabelType The function exprs_known_equal() is used by the planner to determine if two expressions are semantically equivalent, often by checking if they belong to the same Equivalence Class (EC). When a partitioned table uses a varchar(N) type as a partition key, the partition key expression stored in the equivalence class member (em->em_expr) is often wrapped in a RelabelType node. However, when checking a join condition or a predicate against the EC member, the input expression (item1 or item2) may not contain this RelabelType wrapper, leading to an incorrect equal() comparison and failing to detect a known equivalence. This prevents the planner from recognizing that a join condition matches the partition keys, thereby disabling optimizations like partitionwise joins. This commit modifies exprs_known_equal() to strip RelabelType decorations from both sides of the comparison: the EC member's expression as well as the input expressions (item1 and item2). Stripping only proceeds through layers that don't change the expression's exposed collation, so as not to discard a RelabelType that was added to force a different collation. Partition pruning has the same kind of asymmetry: match_clause_to_partition_key() in partprune.c strips RelabelType from the clause's operands but not from the partition key expression itself, so pruning fails to eliminate any partitions for the same class of RelabelType-wrapped expression keys. This commit fixes that too, by stripping the partition key expression the same way up front. Collation correctness is unaffected, since it's independently verified via PartCollMatchesExprColl(). Co-authored-by: jian he <[email protected]> Reviewed-by: Jan Nidzwetzki <[email protected]> Discussion: https://postgr.es/m/[email protected] --- .../postgres_fdw/expected/postgres_fdw.out | 21 ++++++ contrib/postgres_fdw/sql/postgres_fdw.sql | 16 +++++ src/backend/optimizer/path/equivclass.c | 40 ++++++++++- src/backend/partitioning/partprune.c | 10 +++ src/test/regress/expected/partition_join.out | 68 +++++++++++++++++++ src/test/regress/sql/partition_join.sql | 31 +++++++++ 6 files changed, 184 insertions(+), 2 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 7c09c52b8ea..ccbce46d59d 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -11194,6 +11194,16 @@ CREATE FOREIGN TABLE ftprt2_p2 PARTITION OF fprt2 FOR VALUES FROM (250) TO (500) ANALYZE fprt2; ANALYZE fprt2_p1; ANALYZE fprt2_p2; +CREATE TABLE fprt3 (a int, b int, c varchar(40)) partition by hash(c); +CREATE TABLE fprt3_ft (a int, b int, c varchar(40)); +CREATE TABLE fprt3_p1 partition of fprt3 FOR values WITH (modulus 2, remainder 0); +CREATE FOREIGN TABLE fprt3_p2 partition of fprt3 FOR values WITH (modulus 2, +remainder 1) server loopback options (table_name 'fprt3_ft'); +CREATE TABLE fprt4 (a int, b int, c varchar(40)) partition by hash(c); +CREATE TABLE fprt4_ft (a int, b int, c varchar(40)); +CREATE TABLE fprt4_p1 partition of fprt4 FOR values WITH (modulus 2, remainder 0); +CREATE FOREIGN TABLE fprt4_p2 partition of fprt4 FOR values WITH (modulus 2, +remainder 1) server loopback options (table_name 'fprt4_ft'); -- inner join three tables EXPLAIN (COSTS OFF) SELECT t1.a,t2.b,t3.c FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) INNER JOIN fprt1 t3 ON (t2.b = t3.a) WHERE t1.a % 25 =0 ORDER BY 1,2,3; @@ -11363,6 +11373,17 @@ SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a 400 | 400 (4 rows) +-- varchar types wrapped by a RelabelType is removed to enable partitionwise joins +EXPLAIN(VERBOSE, COSTS OFF) SELECT fprt3.a, fprt4.a FROM fprt3 JOIN fprt4 ON fprt3.c = +fprt4.c WHERE fprt3.c = '0002'; + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------- + Foreign Scan + Output: fprt3.a, fprt4.a + Relations: (public.fprt3_p2 fprt3) INNER JOIN (public.fprt4_p2 fprt4) + Remote SQL: SELECT r4.a, r5.a FROM (public.fprt3_ft r4 INNER JOIN public.fprt4_ft r5 ON (((r5.c = '0002')) AND ((r4.c = '0002')))) +(4 rows) + RESET enable_partitionwise_join; -- =================================================================== -- test partitionwise aggregates diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 6571a18ba0c..21d01b249de 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -3693,6 +3693,18 @@ ANALYZE fprt2; ANALYZE fprt2_p1; ANALYZE fprt2_p2; +CREATE TABLE fprt3 (a int, b int, c varchar(40)) partition by hash(c); +CREATE TABLE fprt3_ft (a int, b int, c varchar(40)); +CREATE TABLE fprt3_p1 partition of fprt3 FOR values WITH (modulus 2, remainder 0); +CREATE FOREIGN TABLE fprt3_p2 partition of fprt3 FOR values WITH (modulus 2, +remainder 1) server loopback options (table_name 'fprt3_ft'); + +CREATE TABLE fprt4 (a int, b int, c varchar(40)) partition by hash(c); +CREATE TABLE fprt4_ft (a int, b int, c varchar(40)); +CREATE TABLE fprt4_p1 partition of fprt4 FOR values WITH (modulus 2, remainder 0); +CREATE FOREIGN TABLE fprt4_p2 partition of fprt4 FOR values WITH (modulus 2, +remainder 1) server loopback options (table_name 'fprt4_ft'); + -- inner join three tables EXPLAIN (COSTS OFF) SELECT t1.a,t2.b,t3.c FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) INNER JOIN fprt1 t3 ON (t2.b = t3.a) WHERE t1.a % 25 =0 ORDER BY 1,2,3; @@ -3723,6 +3735,10 @@ EXPLAIN (COSTS OFF) SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1; SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1; +-- varchar types wrapped by a RelabelType is removed to enable partitionwise joins +EXPLAIN(VERBOSE, COSTS OFF) SELECT fprt3.a, fprt4.a FROM fprt3 JOIN fprt4 ON fprt3.c = +fprt4.c WHERE fprt3.c = '0002'; + RESET enable_partitionwise_join; diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c index 0dcc2ed4266..d5e0ea1fafd 100644 --- a/src/backend/optimizer/path/equivclass.c +++ b/src/backend/optimizer/path/equivclass.c @@ -2628,6 +2628,30 @@ find_join_domain(PlannerInfo *root, Relids relids) } +/* + * strip_collation_preserving_relabel + * Remove any leading RelabelType decorations from "node", but only the + * layers that don't change the expression's exposed collation. + * + * This lets us match expressions that differ only by a binary-compatible + * cast (e.g. varchar to text), while not discarding a RelabelType that was + * added specifically to force a different collation, since that would + * change the expression's semantics. + */ +static Node * +strip_collation_preserving_relabel(Node *node) +{ + while (node && IsA(node, RelabelType)) + { + RelabelType *re = (RelabelType *) node; + + if (re->resultcollid != exprCollation((Node *) re->arg)) + break; + node = (Node *) re->arg; + } + return node; +} + /* * exprs_known_equal * Detect whether two expressions are known equal due to equivalence @@ -2647,6 +2671,17 @@ exprs_known_equal(PlannerInfo *root, Node *item1, Node *item2, Oid opfamily) { ListCell *lc1; + /* + * Strip any collation-preserving RelabelType decorations from the input + * expressions, so that they can still be matched below against EC members + * that get the same treatment. This is needed because, e.g., partition + * key expressions stored in an EC member may be wrapped in a RelabelType + * (binary-compatible cast) that the caller-supplied item may or may not + * also carry. + */ + item1 = strip_collation_preserving_relabel(item1); + item2 = strip_collation_preserving_relabel(item2); + foreach(lc1, root->eq_classes) { EquivalenceClass *ec = (EquivalenceClass *) lfirst(lc1); @@ -2673,12 +2708,13 @@ exprs_known_equal(PlannerInfo *root, Node *item1, Node *item2, Oid opfamily) foreach(lc2, ec->ec_members) { EquivalenceMember *em = (EquivalenceMember *) lfirst(lc2); + Node *expr = strip_collation_preserving_relabel((Node *) em->em_expr); /* Child members should not exist in ec_members */ Assert(!em->em_is_child); - if (equal(item1, em->em_expr)) + if (equal(item1, expr)) item1member = true; - else if (equal(item2, em->em_expr)) + else if (equal(item2, expr)) item2member = true; /* Exit as soon as equality is proven */ if (item1member && item2member) diff --git a/src/backend/partitioning/partprune.c b/src/backend/partitioning/partprune.c index 06566c8ce8a..7fc2a53c2cc 100644 --- a/src/backend/partitioning/partprune.c +++ b/src/backend/partitioning/partprune.c @@ -1836,6 +1836,16 @@ match_clause_to_partition_key(GeneratePruningStepsContext *context, Expr *expr; bool notclause; + /* + * Strip any RelabelType from the partition key expression itself, to + * match the stripping already done below on the clause operands. + * Partition key expressions can be wrapped in a RelabelType. Collation + * correctness doesn't depend on keeping the RelabelType here, since it's + * separately verified below via PartCollMatchesExprColl(). + */ + while (IsA(partkey, RelabelType)) + partkey = (const Expr *) ((const RelabelType *) partkey)->arg; + /* * Recognize specially shaped clauses that match a Boolean partition key. */ diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out index 38643d41fd7..45537d54167 100644 --- a/src/test/regress/expected/partition_join.out +++ b/src/test/regress/expected/partition_join.out @@ -1750,6 +1750,74 @@ SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, ph 273.0000000000000000 | 273.0000000000000000 | 548.0000000000000000 | 0005 | 0005 | A0005 (6 rows) +-- +-- hash partitioned by an expression that's a binary-compatible cast +-- (varchar to text); the partition key expression is stored wrapped in a +-- RelabelType, which needs to still match join/restriction clauses for +-- partitionwise join to kick in +-- +CREATE TABLE pht3 (a int, b int, c varchar(40)) PARTITION BY HASH((c::text)); +CREATE TABLE pht3_p1 PARTITION OF pht3 FOR VALUES WITH (MODULUS 3, REMAINDER 0); +CREATE TABLE pht3_p2 PARTITION OF pht3 FOR VALUES WITH (MODULUS 3, REMAINDER 1); +CREATE TABLE pht3_p3 PARTITION OF pht3 FOR VALUES WITH (MODULUS 3, REMAINDER 2); +INSERT INTO pht3 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 2) i; +ANALYZE pht3; +CREATE TABLE pht4 (a int, b int, c varchar(40)) PARTITION BY HASH((c::text)); +CREATE TABLE pht4_p1 PARTITION OF pht4 FOR VALUES WITH (MODULUS 3, REMAINDER 0); +CREATE TABLE pht4_p2 PARTITION OF pht4 FOR VALUES WITH (MODULUS 3, REMAINDER 1); +CREATE TABLE pht4_p3 PARTITION OF pht4 FOR VALUES WITH (MODULUS 3, REMAINDER 2); +INSERT INTO pht4 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 3) i; +ANALYZE pht4; +-- Force nested loop, since hash/merge join costs tend to be close to the +-- partitionwise join's cost at this scale, which would make the test +-- output machine-dependent. +SET enable_hashjoin = off; +SET enable_mergejoin = off; +EXPLAIN (COSTS OFF) +SELECT avg(t1.a), avg(t2.a), t1.c, t2.c FROM pht3 t1, pht4 t2 WHERE t1.c::text = t2.c::text GROUP BY t1.c, t2.c ORDER BY t1.c, t2.c; + QUERY PLAN +-------------------------------------------------------------------- + Sort + Sort Key: t1.c + -> HashAggregate + Group Key: t1.c + -> Append + -> Nested Loop + Join Filter: ((t1_1.c)::text = (t2_1.c)::text) + -> Seq Scan on pht3_p1 t1_1 + -> Materialize + -> Seq Scan on pht4_p1 t2_1 + -> Nested Loop + Join Filter: ((t1_2.c)::text = (t2_2.c)::text) + -> Seq Scan on pht3_p2 t1_2 + -> Materialize + -> Seq Scan on pht4_p2 t2_2 + -> Nested Loop + Join Filter: ((t1_3.c)::text = (t2_3.c)::text) + -> Seq Scan on pht3_p3 t1_3 + -> Materialize + -> Seq Scan on pht4_p3 t2_3 +(20 rows) + +SELECT avg(t1.a), avg(t2.a), t1.c, t2.c FROM pht3 t1, pht4 t2 WHERE t1.c::text = t2.c::text GROUP BY t1.c, t2.c ORDER BY t1.c, t2.c; + avg | avg | c | c +----------------------+----------------------+------+------ + 24.0000000000000000 | 24.0000000000000000 | 0000 | 0000 + 74.0000000000000000 | 75.0000000000000000 | 0001 | 0001 + 124.0000000000000000 | 124.5000000000000000 | 0002 | 0002 + 174.0000000000000000 | 174.0000000000000000 | 0003 | 0003 + 224.0000000000000000 | 225.0000000000000000 | 0004 | 0004 + 274.0000000000000000 | 274.5000000000000000 | 0005 | 0005 + 324.0000000000000000 | 324.0000000000000000 | 0006 | 0006 + 374.0000000000000000 | 375.0000000000000000 | 0007 | 0007 + 424.0000000000000000 | 424.5000000000000000 | 0008 | 0008 + 474.0000000000000000 | 474.0000000000000000 | 0009 | 0009 + 524.0000000000000000 | 525.0000000000000000 | 0010 | 0010 + 574.0000000000000000 | 574.5000000000000000 | 0011 | 0011 +(12 rows) + +RESET enable_hashjoin; +RESET enable_mergejoin; -- test default partition behavior for range ALTER TABLE prt1 DETACH PARTITION prt1_p3; ALTER TABLE prt1 ATTACH PARTITION prt1_p3 DEFAULT; diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql index c4549fc1ad8..f8bac4df2dd 100644 --- a/src/test/regress/sql/partition_join.sql +++ b/src/test/regress/sql/partition_join.sql @@ -350,6 +350,37 @@ EXPLAIN (COSTS OFF) SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c; SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c; +-- +-- hash partitioned by an expression that's a binary-compatible cast +-- (varchar to text); the partition key expression is stored wrapped in a +-- RelabelType, which needs to still match join/restriction clauses for +-- partitionwise join to kick in +-- +CREATE TABLE pht3 (a int, b int, c varchar(40)) PARTITION BY HASH((c::text)); +CREATE TABLE pht3_p1 PARTITION OF pht3 FOR VALUES WITH (MODULUS 3, REMAINDER 0); +CREATE TABLE pht3_p2 PARTITION OF pht3 FOR VALUES WITH (MODULUS 3, REMAINDER 1); +CREATE TABLE pht3_p3 PARTITION OF pht3 FOR VALUES WITH (MODULUS 3, REMAINDER 2); +INSERT INTO pht3 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 2) i; +ANALYZE pht3; + +CREATE TABLE pht4 (a int, b int, c varchar(40)) PARTITION BY HASH((c::text)); +CREATE TABLE pht4_p1 PARTITION OF pht4 FOR VALUES WITH (MODULUS 3, REMAINDER 0); +CREATE TABLE pht4_p2 PARTITION OF pht4 FOR VALUES WITH (MODULUS 3, REMAINDER 1); +CREATE TABLE pht4_p3 PARTITION OF pht4 FOR VALUES WITH (MODULUS 3, REMAINDER 2); +INSERT INTO pht4 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 3) i; +ANALYZE pht4; + +-- Force nested loop, since hash/merge join costs tend to be close to the +-- partitionwise join's cost at this scale, which would make the test +-- output machine-dependent. +SET enable_hashjoin = off; +SET enable_mergejoin = off; +EXPLAIN (COSTS OFF) +SELECT avg(t1.a), avg(t2.a), t1.c, t2.c FROM pht3 t1, pht4 t2 WHERE t1.c::text = t2.c::text GROUP BY t1.c, t2.c ORDER BY t1.c, t2.c; +SELECT avg(t1.a), avg(t2.a), t1.c, t2.c FROM pht3 t1, pht4 t2 WHERE t1.c::text = t2.c::text GROUP BY t1.c, t2.c ORDER BY t1.c, t2.c; +RESET enable_hashjoin; +RESET enable_mergejoin; + -- test default partition behavior for range ALTER TABLE prt1 DETACH PARTITION prt1_p3; ALTER TABLE prt1 ATTACH PARTITION prt1_p3 DEFAULT; -- 2.50.1 (Apple Git-155)
