Hello Matheus,
On 27.08.26 19:08, Matheus Alcantara wrote:
> Thanks for the review. v5 is attached, now split into two patches: 0001 is
> the partprune fix and 0002 is the exprs_known_equal() fix.
Thank you for the updated, split version 5 of the patch. Both patches
apply cleanly to the main branch (de56594), and I verified that only the
added tests fail when the change is not applied. So, the split and the
tests look good to me. I also appreciate the tests added to
'collate.icu.utf8.sql'.
While testing the patch, I noticed an asymmetry between inner and outer
joins. The patch handles the inner join case, while the outer join case
produces a non-partitionwise query plan.
>From my understanding, outer-join clauses don't form an equivalence
class. So, have_partkey_equi_join() can only prove the keys equal by
matching the join clause through match_expr_to_partition_keys(), which
strips the clause operand but compares it against rel->partexprs unstripped.
This can be reproduced as follows:
CREATE TABLE e1 (a int, c varchar(40)) PARTITION BY HASH ((c::text));
CREATE TABLE e1_p1 PARTITION OF e1 FOR VALUES WITH (MODULUS 2, REMAINDER 0);
CREATE TABLE e1_p2 PARTITION OF e1 FOR VALUES WITH (MODULUS 2, REMAINDER 1);
CREATE TABLE e2 (a int, c varchar(40)) PARTITION BY HASH ((c::text));
CREATE TABLE e2_p1 PARTITION OF e2 FOR VALUES WITH (MODULUS 2, REMAINDER 0);
CREATE TABLE e2_p2 PARTITION OF e2 FOR VALUES WITH (MODULUS 2, REMAINDER 1);
SET max_parallel_workers_per_gather = 0;
SET enable_hashjoin = off;
SET enable_mergejoin = off;
SET enable_partitionwise_join = on;
-- inner join: partitionwise
jan=# EXPLAIN (COSTS OFF) SELECT * FROM e1 JOIN e2 ON e1.c::text =
e2.c::text;
QUERY PLAN
--------------------------------------------------------
Append
-> Nested Loop
Join Filter: ((e1_1.c)::text = (e2_1.c)::text)
-> Seq Scan on e1_p1 e1_1
-> Materialize
-> Seq Scan on e2_p1 e2_1
-> Nested Loop
Join Filter: ((e1_2.c)::text = (e2_2.c)::text)
-> Seq Scan on e1_p2 e1_2
-> Materialize
-> Seq Scan on e2_p2 e2_2
(11 rows)
-- outer join: not partitionwise
jan=# EXPLAIN (COSTS OFF) SELECT * FROM e1 LEFT JOIN e2 ON e1.c::text =
e2.c::text;
QUERY PLAN
----------------------------------------------
Nested Loop Left Join
Join Filter: ((e1.c)::text = (e2.c)::text)
-> Append
-> Seq Scan on e1_p1 e1_1
-> Seq Scan on e1_p2 e1_2
-> Materialize
-> Append
-> Seq Scan on e2_p1 e2_1
-> Seq Scan on e2_p2 e2_2
(9 rows)
I did a few tests, and I think we need to do the stripping in
match_expr_to_partition_keys() as well. By applying this change, the
query plan changes as follows:
-- Now partitionwise
jan=# EXPLAIN (COSTS OFF) SELECT * FROM e1 LEFT JOIN e2 ON e1.c::text =
e2.c::text;
QUERY PLAN
--------------------------------------------------------
Append
-> Nested Loop Left Join
Join Filter: ((e1_1.c)::text = (e2_1.c)::text)
-> Seq Scan on e1_p1 e1_1
-> Materialize
-> Seq Scan on e2_p1 e2_1
-> Nested Loop Left Join
Join Filter: ((e1_2.c)::text = (e2_2.c)::text)
-> Seq Scan on e1_p2 e1_2
-> Materialize
-> Seq Scan on e2_p2 e2_2
(11 rows)
I drafted a possible fix in my tree, added a test case that fails
without the modification, and exported it as a new patch. Attached is a
new series. Patches 1 and 2 are yours in an unmodified form. Patch 3 is
the new one, which could potentially be squashed into patch 2 if you
find the modification useful.
Best regards
Jan
--
Jan Nidzwetzki
PlanetScale Postgres Core Team
From d75c022e8a33e8353b320e1b9031adb02b90b918 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email protected]>
Date: Wed, 26 Aug 2026 15:45:56 -0300
Subject: [PATCH v6 1/3] Fix partition pruning for partition keys wrapped by
RelabelType
match_clause_to_partition_key() strips RelabelType decorations from the
operands of the clause being matched, but not from the partition key
expression itself. When the partition key is an expression involving a
binary-compatible cast, for example
PARTITION BY LIST ((col::text))
on a varchar column, the stored partition key expression is itself a
RelabelType. The equal() comparisons against the stripped clause
operands therefore never match, so no pruning steps are generated at all
and every partition is scanned regardless of the clause.
Fix by stripping the partition key expression the same way, once, up
front. This covers all of the clause shapes handled by the function
(OpExpr, ScalarArrayOpExpr, NullTest, and the Boolean clause forms
recognized by match_boolean_partition_clause()), since they all receive
the same partkey.
Collation correctness does not depend on the partition key expression's
exposed collation: for the operator clause forms it is established by
checking the clause's input collation against the partition collation via
PartCollMatchesExprColl(), and nullness and Boolean tests do not depend
on collation at all.
Reviewed-by: Jan Nidzwetzki <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
src/backend/partitioning/partprune.c | 10 ++++
src/test/regress/expected/partition_prune.out | 51 +++++++++++++++++++
src/test/regress/sql/partition_prune.sql | 17 +++++++
3 files changed, 78 insertions(+)
diff --git a/src/backend/partitioning/partprune.c
b/src/backend/partitioning/partprune.c
index 5e98f503244..dc87b9b9329 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 RelabelType *) partkey)->arg;
+
/*
* Recognize specially shaped clauses that match a Boolean partition
key.
*/
diff --git a/src/test/regress/expected/partition_prune.out
b/src/test/regress/expected/partition_prune.out
index 8f1119a026d..899df49fbc1 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -186,6 +186,57 @@ explain (costs off) select * from coll_pruning where a
collate "POSIX" = 'a' col
Filter: ((a)::text = 'a'::text COLLATE "POSIX")
(7 rows)
+-- the partition key expression involves a binary-compatible cast, so it is
+-- stored wrapped in a RelabelType; that has to be ignored when matching
+-- clauses to the partition key, else no pruning occurs
+create table cast_pruning (a int, c varchar(40)) partition by list ((c::text));
+create table cast_pruning_a partition of cast_pruning for values in ('a');
+create table cast_pruning_b partition of cast_pruning for values in ('b');
+create table cast_pruning_null partition of cast_pruning for values in (null);
+insert into cast_pruning values (1, 'a'), (2, 'b'), (3, null);
+explain (costs off) select * from cast_pruning where c = 'a';
+ QUERY PLAN
+-----------------------------------------
+ Seq Scan on cast_pruning_a cast_pruning
+ Filter: ((c)::text = 'a'::text)
+(2 rows)
+
+explain (costs off) select * from cast_pruning where c in ('a', 'b');
+ QUERY PLAN
+-----------------------------------------------------
+ Append
+ -> Seq Scan on cast_pruning_a cast_pruning_1
+ Filter: ((c)::text = ANY ('{a,b}'::text[]))
+ -> Seq Scan on cast_pruning_b cast_pruning_2
+ Filter: ((c)::text = ANY ('{a,b}'::text[]))
+(5 rows)
+
+explain (costs off) select * from cast_pruning where c is null;
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on cast_pruning_null cast_pruning
+ Filter: (c IS NULL)
+(2 rows)
+
+select * from cast_pruning where c = 'a';
+ a | c
+---+---
+ 1 | a
+(1 row)
+
+select * from cast_pruning where c in ('a', 'b') order by a;
+ a | c
+---+---
+ 1 | a
+ 2 | b
+(2 rows)
+
+select * from cast_pruning where c is null;
+ a | c
+---+---
+ 3 |
+(1 row)
+
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;
diff --git a/src/test/regress/sql/partition_prune.sql
b/src/test/regress/sql/partition_prune.sql
index f967658d4b5..918d24a5b11 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -53,6 +53,23 @@ explain (costs off) select * from coll_pruning where a
collate "C" = 'a' collate
-- collation doesn't match the partitioning collation, no pruning occurs
explain (costs off) select * from coll_pruning where a collate "POSIX" = 'a'
collate "POSIX";
+-- the partition key expression involves a binary-compatible cast, so it is
+-- stored wrapped in a RelabelType; that has to be ignored when matching
+-- clauses to the partition key, else no pruning occurs
+create table cast_pruning (a int, c varchar(40)) partition by list ((c::text));
+create table cast_pruning_a partition of cast_pruning for values in ('a');
+create table cast_pruning_b partition of cast_pruning for values in ('b');
+create table cast_pruning_null partition of cast_pruning for values in (null);
+insert into cast_pruning values (1, 'a'), (2, 'b'), (3, null);
+
+explain (costs off) select * from cast_pruning where c = 'a';
+explain (costs off) select * from cast_pruning where c in ('a', 'b');
+explain (costs off) select * from cast_pruning where c is null;
+
+select * from cast_pruning where c = 'a';
+select * from cast_pruning where c in ('a', 'b') order by a;
+select * from cast_pruning where c is null;
+
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;
--
2.47.3
From 281c3fd08f7caf18367b8c2590be7d8be6fc1dd6 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email protected]>
Date: Wed, 26 Aug 2026 15:49:42 -0300
Subject: [PATCH v6 2/3] 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).
Either side of that comparison may be decorated with a binary-compatible
RelabelType, and which side carries it varies. When a partitioned table
uses a varchar(N) type as a partition key, the expression stored in the
equivalence class member (em->em_expr) is wrapped in a RelabelType while
the input expression (item1 or item2) is a plain Var; when the partition
key is itself an expression involving a binary-compatible cast, such as
PARTITION BY HASH ((col::text)), it is the input expression that is
wrapped. Comparing a wrapped expression against an unwrapped one leads
to an incorrect equal() comparison and fails 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. canonicalize_ec_expression()
deliberately wraps equivalence class members in a collation-changing
RelabelType so that every member of a class exposes that class's
collation, and discarding one is not safe: under a non-deterministic
collation, equality in the EC's collation differs from equality in the
partition collation, so a table's partitions are not join-closed and
reporting such keys as equal would let have_partkey_equi_join() choose a
partitionwise join that silently omits matching rows.
Co-authored-by: Jian He
Reviewed-by: Jan Nidzwetzki <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
.../postgres_fdw/expected/postgres_fdw.out | 22 +++++++
contrib/postgres_fdw/sql/postgres_fdw.sql | 17 ++++++
src/backend/optimizer/path/equivclass.c | 52 ++++++++++++++++-
.../regress/expected/collate.icu.utf8.out | 46 +++++++++++++++
src/test/regress/expected/partition_join.out | 58 +++++++++++++++++++
src/test/regress/sql/collate.icu.utf8.sql | 26 +++++++++
src/test/regress/sql/partition_join.sql | 37 ++++++++++++
7 files changed, 256 insertions(+), 2 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out
b/contrib/postgres_fdw/expected/postgres_fdw.out
index 517d15cf1fa..24c3cf31588 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -11192,6 +11192,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;
@@ -11361,6 +11371,18 @@ SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON
(t1.a = t2.b) WHERE t1.a
400 | 400
(4 rows)
+-- with a varchar partition key, the partitionwise join lets the whole join be
+-- pushed down to the remote server
+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 ec766e2b28a..041b81d6885 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,11 @@ 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;
+-- with a varchar partition key, the partitionwise join lets the whole join be
+-- pushed down to the remote server
+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..f981cc30fa0 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -2628,6 +2628,40 @@ 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, such as varchar to text.
+ *
+ * Layers that do change the exposed collation must be kept. Note that
+ * canonicalize_ec_expression() deliberately wraps equivalence class members
+ * in such a RelabelType, so that every member of a class exposes that
+ * class's collation; that wrapper is not mere decoration. Under a
+ * non-deterministic collation, equality in the equivalence class's collation
+ * differs from equality in the partition collation, so a table's partitions
+ * are not join-closed and reporting such keys as equal would let
+ * have_partkey_equi_join() choose a partitionwise join that silently omits
+ * matching rows. We can't leave that check to the caller, since
+ * exprs_known_equal() returns only a boolean and its callers never learn the
+ * collation of the equivalence class that proved the equality.
+ */
+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 +2681,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 +2718,15 @@ exprs_known_equal(PlannerInfo *root, Node *item1, Node
*item2, Oid opfamily)
foreach(lc2, ec->ec_members)
{
EquivalenceMember *em = (EquivalenceMember *)
lfirst(lc2);
+ Node *expr;
/* Child members should not exist in ec_members */
Assert(!em->em_is_child);
- if (equal(item1, em->em_expr))
+
+ expr = strip_collation_preserving_relabel((Node *)
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/test/regress/expected/collate.icu.utf8.out
b/src/test/regress/expected/collate.icu.utf8.out
index fcfcc658bea..130997f9928 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -3574,11 +3574,57 @@ SELECT t1.c, count(t2.c) FROM pagg_tab5 t1 JOIN
pagg_tab6 t2 ON t1.c = t2.c AND
d | 9
(4 rows)
+-- Here the partition key collation is the same as the collation of the
+-- partitioned column, so a partitionwise join can't be rejected by comparing
+-- those two. The equality of the partition keys is proven only by an
+-- equivalence class, whose collation (case_insensitive) is not the partition
+-- key collation. Under a non-deterministic collation the partitions are not
+-- join-closed, so a partitionwise join would give a wrong answer here (it
+-- would return 5 rather than 9).
+SET enable_partitionwise_join TO true;
+CREATE TABLE pwj_tab1 (a int, b text) PARTITION BY LIST (b);
+CREATE TABLE pwj_tab1_p1 PARTITION OF pwj_tab1 FOR VALUES IN ('AbC');
+CREATE TABLE pwj_tab1_p2 PARTITION OF pwj_tab1 FOR VALUES IN ('abc', 'ABC');
+CREATE TABLE pwj_tab2 (a int, b text) PARTITION BY LIST (b);
+CREATE TABLE pwj_tab2_p1 PARTITION OF pwj_tab2 FOR VALUES IN ('AbC');
+CREATE TABLE pwj_tab2_p2 PARTITION OF pwj_tab2 FOR VALUES IN ('abc', 'ABC');
+INSERT INTO pwj_tab1 VALUES (1, 'abc'), (2, 'ABC'), (3, 'AbC');
+INSERT INTO pwj_tab2 VALUES (1, 'abc'), (2, 'ABC'), (3, 'AbC');
+ANALYZE pwj_tab1;
+ANALYZE pwj_tab2;
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM pwj_tab1 t1, pwj_tab2 t2 WHERE t1.b = 'abc' COLLATE
case_insensitive AND t2.b = 'abc' COLLATE case_insensitive;
+ QUERY PLAN
+--------------------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Append
+ -> Seq Scan on pwj_tab1_p2 t1_1
+ Filter: ((b)::text = 'abc'::text COLLATE case_insensitive)
+ -> Seq Scan on pwj_tab1_p1 t1_2
+ Filter: ((b)::text = 'abc'::text COLLATE case_insensitive)
+ -> Materialize
+ -> Append
+ -> Seq Scan on pwj_tab2_p2 t2_1
+ Filter: ((b)::text = 'abc'::text COLLATE
case_insensitive)
+ -> Seq Scan on pwj_tab2_p1 t2_2
+ Filter: ((b)::text = 'abc'::text COLLATE
case_insensitive)
+(13 rows)
+
+SELECT count(*) FROM pwj_tab1 t1, pwj_tab2 t2 WHERE t1.b = 'abc' COLLATE
case_insensitive AND t2.b = 'abc' COLLATE case_insensitive;
+ count
+-------
+ 9
+(1 row)
+
DROP TABLE pagg_tab3;
DROP TABLE pagg_tab4;
DROP TABLE pagg_tab5;
DROP TABLE pagg_tab6;
+DROP TABLE pwj_tab1;
+DROP TABLE pwj_tab2;
RESET enable_partitionwise_aggregate;
+RESET enable_partitionwise_join;
RESET max_parallel_workers_per_gather;
RESET enable_incremental_sort;
--
diff --git a/src/test/regress/expected/partition_join.out
b/src/test/regress/expected/partition_join.out
index 38643d41fd7..8a122a6cc24 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -1750,6 +1750,64 @@ 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 a varchar column and an int column
+--
+-- The partition key for c is a plain Var, but an equivalence class member
+-- derived from comparing it against a text value is wrapped in a RelabelType.
+-- Proving the partition keys equal therefore requires ignoring
+-- binary-compatible relabeling on both sides of the comparison.
+--
+-- Below, c's equality is proven by the equivalence class rather than by a join
+-- clause, which is why both sides are compared to a constant instead of to
+-- each other; d's equality comes from the join clause. Hash pruning needs
+-- every partition key column to be constrained, so constraining only c prunes
+-- nothing and all partitions remain on both sides, which is what makes the
+-- partitionwise join visible here.
+--
+CREATE TABLE pht3 (a int, c varchar(40), d int) PARTITION BY HASH (c, d);
+CREATE TABLE pht3_p1 PARTITION OF pht3 FOR VALUES WITH (MODULUS 2, REMAINDER
0);
+CREATE TABLE pht3_p2 PARTITION OF pht3 FOR VALUES WITH (MODULUS 2, REMAINDER
1);
+INSERT INTO pht3 SELECT i, to_char(i/50, 'FM0000'), i % 10 FROM
generate_series(0, 599, 2) i;
+ANALYZE pht3;
+CREATE TABLE pht4 (a int, c varchar(40), d int) PARTITION BY HASH (c, d);
+CREATE TABLE pht4_p1 PARTITION OF pht4 FOR VALUES WITH (MODULUS 2, REMAINDER
0);
+CREATE TABLE pht4_p2 PARTITION OF pht4 FOR VALUES WITH (MODULUS 2, REMAINDER
1);
+INSERT INTO pht4 SELECT i, to_char(i/50, 'FM0000'), i % 10 FROM
generate_series(0, 599, 3) i;
+ANALYZE pht4;
+-- avoid hash and merge joins, whose costs here are close enough to the
+-- partitionwise nested loop's to make the test output unstable
+SET enable_hashjoin = off;
+SET enable_mergejoin = off;
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t2.a FROM pht3 t1 JOIN pht4 t2 ON t1.d = t2.d WHERE t1.c = '0002'
AND t2.c = '0002';
+ QUERY PLAN
+--------------------------------------------------------
+ Append
+ -> Nested Loop
+ Join Filter: (t1_1.d = t2_1.d)
+ -> Seq Scan on pht3_p1 t1_1
+ Filter: ((c)::text = '0002'::text)
+ -> Materialize
+ -> Seq Scan on pht4_p1 t2_1
+ Filter: ((c)::text = '0002'::text)
+ -> Nested Loop
+ Join Filter: (t1_2.d = t2_2.d)
+ -> Seq Scan on pht3_p2 t1_2
+ Filter: ((c)::text = '0002'::text)
+ -> Materialize
+ -> Seq Scan on pht4_p2 t2_2
+ Filter: ((c)::text = '0002'::text)
+(15 rows)
+
+SELECT count(*) FROM pht3 t1 JOIN pht4 t2 ON t1.d = t2.d WHERE t1.c = '0002'
AND t2.c = '0002';
+ count
+-------
+ 40
+(1 row)
+
+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/collate.icu.utf8.sql
b/src/test/regress/sql/collate.icu.utf8.sql
index ce4e2bb3ffd..ea7321f22d7 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -1301,12 +1301,38 @@ EXPLAIN (COSTS OFF)
SELECT t1.c, count(t2.c) FROM pagg_tab5 t1 JOIN pagg_tab6 t2 ON t1.c = t2.c
AND t1.c = t2.b GROUP BY 1 ORDER BY t1.c COLLATE "C";
SELECT t1.c, count(t2.c) FROM pagg_tab5 t1 JOIN pagg_tab6 t2 ON t1.c = t2.c
AND t1.c = t2.b GROUP BY 1 ORDER BY t1.c COLLATE "C";
+-- Here the partition key collation is the same as the collation of the
+-- partitioned column, so a partitionwise join can't be rejected by comparing
+-- those two. The equality of the partition keys is proven only by an
+-- equivalence class, whose collation (case_insensitive) is not the partition
+-- key collation. Under a non-deterministic collation the partitions are not
+-- join-closed, so a partitionwise join would give a wrong answer here (it
+-- would return 5 rather than 9).
+SET enable_partitionwise_join TO true;
+CREATE TABLE pwj_tab1 (a int, b text) PARTITION BY LIST (b);
+CREATE TABLE pwj_tab1_p1 PARTITION OF pwj_tab1 FOR VALUES IN ('AbC');
+CREATE TABLE pwj_tab1_p2 PARTITION OF pwj_tab1 FOR VALUES IN ('abc', 'ABC');
+CREATE TABLE pwj_tab2 (a int, b text) PARTITION BY LIST (b);
+CREATE TABLE pwj_tab2_p1 PARTITION OF pwj_tab2 FOR VALUES IN ('AbC');
+CREATE TABLE pwj_tab2_p2 PARTITION OF pwj_tab2 FOR VALUES IN ('abc', 'ABC');
+INSERT INTO pwj_tab1 VALUES (1, 'abc'), (2, 'ABC'), (3, 'AbC');
+INSERT INTO pwj_tab2 VALUES (1, 'abc'), (2, 'ABC'), (3, 'AbC');
+ANALYZE pwj_tab1;
+ANALYZE pwj_tab2;
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM pwj_tab1 t1, pwj_tab2 t2 WHERE t1.b = 'abc' COLLATE
case_insensitive AND t2.b = 'abc' COLLATE case_insensitive;
+SELECT count(*) FROM pwj_tab1 t1, pwj_tab2 t2 WHERE t1.b = 'abc' COLLATE
case_insensitive AND t2.b = 'abc' COLLATE case_insensitive;
+
DROP TABLE pagg_tab3;
DROP TABLE pagg_tab4;
DROP TABLE pagg_tab5;
DROP TABLE pagg_tab6;
+DROP TABLE pwj_tab1;
+DROP TABLE pwj_tab2;
RESET enable_partitionwise_aggregate;
+RESET enable_partitionwise_join;
RESET max_parallel_workers_per_gather;
RESET enable_incremental_sort;
diff --git a/src/test/regress/sql/partition_join.sql
b/src/test/regress/sql/partition_join.sql
index c4549fc1ad8..2a423f14190 100644
--- a/src/test/regress/sql/partition_join.sql
+++ b/src/test/regress/sql/partition_join.sql
@@ -350,6 +350,43 @@ 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 a varchar column and an int column
+--
+-- The partition key for c is a plain Var, but an equivalence class member
+-- derived from comparing it against a text value is wrapped in a RelabelType.
+-- Proving the partition keys equal therefore requires ignoring
+-- binary-compatible relabeling on both sides of the comparison.
+--
+-- Below, c's equality is proven by the equivalence class rather than by a join
+-- clause, which is why both sides are compared to a constant instead of to
+-- each other; d's equality comes from the join clause. Hash pruning needs
+-- every partition key column to be constrained, so constraining only c prunes
+-- nothing and all partitions remain on both sides, which is what makes the
+-- partitionwise join visible here.
+--
+CREATE TABLE pht3 (a int, c varchar(40), d int) PARTITION BY HASH (c, d);
+CREATE TABLE pht3_p1 PARTITION OF pht3 FOR VALUES WITH (MODULUS 2, REMAINDER
0);
+CREATE TABLE pht3_p2 PARTITION OF pht3 FOR VALUES WITH (MODULUS 2, REMAINDER
1);
+INSERT INTO pht3 SELECT i, to_char(i/50, 'FM0000'), i % 10 FROM
generate_series(0, 599, 2) i;
+ANALYZE pht3;
+
+CREATE TABLE pht4 (a int, c varchar(40), d int) PARTITION BY HASH (c, d);
+CREATE TABLE pht4_p1 PARTITION OF pht4 FOR VALUES WITH (MODULUS 2, REMAINDER
0);
+CREATE TABLE pht4_p2 PARTITION OF pht4 FOR VALUES WITH (MODULUS 2, REMAINDER
1);
+INSERT INTO pht4 SELECT i, to_char(i/50, 'FM0000'), i % 10 FROM
generate_series(0, 599, 3) i;
+ANALYZE pht4;
+
+-- avoid hash and merge joins, whose costs here are close enough to the
+-- partitionwise nested loop's to make the test output unstable
+SET enable_hashjoin = off;
+SET enable_mergejoin = off;
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t2.a FROM pht3 t1 JOIN pht4 t2 ON t1.d = t2.d WHERE t1.c = '0002'
AND t2.c = '0002';
+SELECT count(*) FROM pht3 t1 JOIN pht4 t2 ON t1.d = t2.d WHERE t1.c = '0002'
AND t2.c = '0002';
+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.47.3
From 6bcd3f5840fdd7a9e613f140a3da8750ae595636 Mon Sep 17 00:00:00 2001
From: Jan Nidzwetzki <[email protected]>
Date: Fri, 28 Aug 2026 15:03:59 +0200
Subject: [PATCH v6 3/3] Enable partitionwise join for outer joins on
RelabelType-wrapped keys
Strip RelabelType decorations in match_expr_to_partition_keys() to
support partitionwise join for outer joins on RelabelType-wrapped keys.
---
src/backend/optimizer/util/relnode.c | 27 ++++++++++---
src/test/regress/expected/partition_join.out | 41 ++++++++++++++++++++
src/test/regress/sql/partition_join.sql | 23 +++++++++++
3 files changed, 86 insertions(+), 5 deletions(-)
diff --git a/src/backend/optimizer/util/relnode.c
b/src/backend/optimizer/util/relnode.c
index ee69f81945f..1d45a013acf 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -2480,6 +2480,18 @@ have_partkey_equi_join(PlannerInfo *root, RelOptInfo
*joinrel,
return false;
}
+/*
+ * strip_relabel_decorations
+ * Remove any RelabelType decorations from "expr".
+ */
+static Expr *
+strip_relabel_decorations(Expr *expr)
+{
+ while (expr && IsA(expr, RelabelType))
+ expr = ((RelabelType *) expr)->arg;
+ return expr;
+}
+
/*
* match_expr_to_partition_keys
*
@@ -2501,9 +2513,14 @@ match_expr_to_partition_keys(Expr *expr, RelOptInfo
*rel, bool strict_op)
Assert(rel->partexprs);
Assert(rel->nullable_partexprs);
- /* Remove any relabel decorations. */
- while (IsA(expr, RelabelType))
- expr = (Expr *) (castNode(RelabelType, expr))->arg;
+ /*
+ * Remove any relabel decorations, from the clause expression here and
+ * from each partition key expression below. A key involving a
+ * binary-compatible cast is itself stored wrapped in a RelabelType. The
+ * collation is not lost, as the caller compares the clause's
inputcollid
+ * against the partition collation.
+ */
+ expr = strip_relabel_decorations(expr);
for (cnt = 0; cnt < rel->part_scheme->partnatts; cnt++)
{
@@ -2512,7 +2529,7 @@ match_expr_to_partition_keys(Expr *expr, RelOptInfo *rel,
bool strict_op)
/* We can always match to the non-nullable partition keys. */
foreach(lc, rel->partexprs[cnt])
{
- if (equal(lfirst(lc), expr))
+ if (equal(strip_relabel_decorations(lfirst(lc)), expr))
return cnt;
}
@@ -2528,7 +2545,7 @@ match_expr_to_partition_keys(Expr *expr, RelOptInfo *rel,
bool strict_op)
*/
foreach(lc, rel->nullable_partexprs[cnt])
{
- if (equal(lfirst(lc), expr))
+ if (equal(strip_relabel_decorations(lfirst(lc)), expr))
return cnt;
}
}
diff --git a/src/test/regress/expected/partition_join.out
b/src/test/regress/expected/partition_join.out
index 8a122a6cc24..304de6e1b49 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -1806,6 +1806,47 @@ SELECT count(*) FROM pht3 t1 JOIN pht4 t2 ON t1.d = t2.d
WHERE t1.c = '0002' AND
40
(1 row)
+RESET enable_hashjoin;
+RESET enable_mergejoin;
+-- outer join on a partition key that is an expression wrapped in a RelabelType
+CREATE TABLE pht5 (a int, c varchar(40)) PARTITION BY HASH ((c::text));
+CREATE TABLE pht5_p1 PARTITION OF pht5 FOR VALUES WITH (MODULUS 2, REMAINDER
0);
+CREATE TABLE pht5_p2 PARTITION OF pht5 FOR VALUES WITH (MODULUS 2, REMAINDER
1);
+INSERT INTO pht5 SELECT i, to_char(i/50, 'FM0000') FROM generate_series(0,
599, 2) i;
+ANALYZE pht5;
+CREATE TABLE pht6 (a int, c varchar(40)) PARTITION BY HASH ((c::text));
+CREATE TABLE pht6_p1 PARTITION OF pht6 FOR VALUES WITH (MODULUS 2, REMAINDER
0);
+CREATE TABLE pht6_p2 PARTITION OF pht6 FOR VALUES WITH (MODULUS 2, REMAINDER
1);
+INSERT INTO pht6 SELECT i, to_char(i/50, 'FM0000') FROM generate_series(0,
599, 3) i;
+ANALYZE pht6;
+-- avoid hash and merge joins, whose costs here are close enough to the
+-- partitionwise nested loop's to make the test output unstable
+SET enable_hashjoin = off;
+SET enable_mergejoin = off;
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM pht5 t1 LEFT JOIN pht6 t2 ON t1.c::text = t2.c::text;
+ QUERY PLAN
+--------------------------------------------------------------
+ Aggregate
+ -> Append
+ -> Nested Loop Left Join
+ Join Filter: ((t1_1.c)::text = (t2_1.c)::text)
+ -> Seq Scan on pht5_p1 t1_1
+ -> Materialize
+ -> Seq Scan on pht6_p1 t2_1
+ -> Nested Loop Left Join
+ Join Filter: ((t1_2.c)::text = (t2_2.c)::text)
+ -> Seq Scan on pht5_p2 t1_2
+ -> Materialize
+ -> Seq Scan on pht6_p2 t2_2
+(12 rows)
+
+SELECT count(*) FROM pht5 t1 LEFT JOIN pht6 t2 ON t1.c::text = t2.c::text;
+ count
+-------
+ 5000
+(1 row)
+
RESET enable_hashjoin;
RESET enable_mergejoin;
-- test default partition behavior for range
diff --git a/src/test/regress/sql/partition_join.sql
b/src/test/regress/sql/partition_join.sql
index 2a423f14190..009cfc2452d 100644
--- a/src/test/regress/sql/partition_join.sql
+++ b/src/test/regress/sql/partition_join.sql
@@ -387,6 +387,29 @@ SELECT count(*) FROM pht3 t1 JOIN pht4 t2 ON t1.d = t2.d
WHERE t1.c = '0002' AND
RESET enable_hashjoin;
RESET enable_mergejoin;
+-- outer join on a partition key that is an expression wrapped in a RelabelType
+CREATE TABLE pht5 (a int, c varchar(40)) PARTITION BY HASH ((c::text));
+CREATE TABLE pht5_p1 PARTITION OF pht5 FOR VALUES WITH (MODULUS 2, REMAINDER
0);
+CREATE TABLE pht5_p2 PARTITION OF pht5 FOR VALUES WITH (MODULUS 2, REMAINDER
1);
+INSERT INTO pht5 SELECT i, to_char(i/50, 'FM0000') FROM generate_series(0,
599, 2) i;
+ANALYZE pht5;
+
+CREATE TABLE pht6 (a int, c varchar(40)) PARTITION BY HASH ((c::text));
+CREATE TABLE pht6_p1 PARTITION OF pht6 FOR VALUES WITH (MODULUS 2, REMAINDER
0);
+CREATE TABLE pht6_p2 PARTITION OF pht6 FOR VALUES WITH (MODULUS 2, REMAINDER
1);
+INSERT INTO pht6 SELECT i, to_char(i/50, 'FM0000') FROM generate_series(0,
599, 3) i;
+ANALYZE pht6;
+
+-- avoid hash and merge joins, whose costs here are close enough to the
+-- partitionwise nested loop's to make the test output unstable
+SET enable_hashjoin = off;
+SET enable_mergejoin = off;
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM pht5 t1 LEFT JOIN pht6 t2 ON t1.c::text = t2.c::text;
+SELECT count(*) FROM pht5 t1 LEFT JOIN pht6 t2 ON t1.c::text = t2.c::text;
+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.47.3
