On Mon, Dec 15, 2025 at 10:46 PM Matheus Alcantara
<[email protected]> wrote:
>
> Hi,
>
> 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).
>
hi.
src/include/nodes/primnodes.h CollateExpr comments:
/*----------
* CollateExpr - COLLATE
*
* The planner replaces CollateExpr with RelabelType during expression
* preprocessing, so execution never sees a CollateExpr.
*----------
*/
examine_variable handling RelabelType (transformed from CollateExpr) is wrong, i
think. Roughly speaking it will reduce "t2.c collate case_insensitive" to
"t2.c". see [1].
If examine_variable does not strip the RelabelType(CollateExpr) node, then
estimate_num_groups->add_unique_group_var may need to deal with RelabelType.
The estimate_num_groups function should account for collation settings. "GROUP
BY a" and "GROUP BY a COLLATE case_insensitive" may result in different row
estimates and should be handled distinctly. Therefore exprs_known_equal within
add_unique_group_var must ensure collation is compared before assuming equality.
In this context, while strippping RelabelType, we should ensure
exprCollation(RelabelType->arg) is the same as the RelabelType->resultcollid.
However, it does not affect partitionwise join, because commit [2] already fixed
the collation issue. so we don't have to worry about
have_partkey_equi_join->exprs_known_equal code path for the RelabelType node.
so i change exprs_known_equal to:
+ /* Remove any relabel decorations if collation match */
+ if (IsA(expr, RelabelType))
+ {
+ RelabelType *relabel = castNode(RelabelType, expr);
+ Expr *rexpr = (Expr *) relabel;
+
+ while (rexpr && IsA(rexpr, RelabelType))
+ rexpr = ((RelabelType *) rexpr)->arg;
+
+ if (exprCollation((Node *) rexpr) == relabel->resultcollid)
+ expr = rexpr;
+ }
[1]
https://postgr.es/m/cacjufxglciyhm+p0gxesg2x--ptrmy3pszqsqoq_h4qs_oq...@mail.gmail.com
[2]
https://git.postgresql.org/cgit/postgresql.git/commit/?id=075acdd93388c080c0fb0aca5723144ad7a56dac
--
jian
https://www.enterprisedb.com
From 0bb70258bf5de43bf3b5e049f037d459260eb7fe Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Thu, 15 Jan 2026 11:05:30 +0800
Subject: [PATCH v2 1/1] 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 away any RelabelType
decorations from the EC member's expression (em->em_expr) before
performing the structural equal() check against the input items. This
ensures that the check is performed on the underlying base expression,
allowing ECs to be correctly utilized for partitionwise join planning
with VARCHAR types.
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 | 18 ++++++++++++++--
3 files changed, 53 insertions(+), 2 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 6066510c7c0..6d485601ad8 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10330,6 +10330,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;
@@ -10499,6 +10509,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 4f7ab2ed0ac..c42daa6163c 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3278,6 +3278,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;
@@ -3308,6 +3320,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 a4fcfcc86c8..4a8241fcd53 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -2675,12 +2675,26 @@ exprs_known_equal(PlannerInfo *root, Node *item1, Node *item2, Oid opfamily)
foreach(lc2, ec->ec_members)
{
EquivalenceMember *em = (EquivalenceMember *) lfirst(lc2);
+ Expr *expr = em->em_expr;
+
+ /* Remove any relabel decorations if collation match */
+ if (IsA(expr, RelabelType))
+ {
+ RelabelType *relabel = castNode(RelabelType, expr);
+ Expr *rexpr = (Expr *) relabel;
+
+ while (rexpr && IsA(rexpr, RelabelType))
+ rexpr = ((RelabelType *) rexpr)->arg;
+
+ if (exprCollation((Node *) rexpr) == relabel->resultcollid)
+ expr = rexpr;
+ }
/* 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)
--
2.34.1
