On Thu Jan 15, 2026 at 12:30 AM -03, jian he wrote:
> On Mon, Dec 15, 2025 at 10:46 PM Matheus Alcantara
> <[email protected]> wrote:
>> 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).
>>
> 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
>
Thanks for taking a look at this.
Although this make sense to me I see difference in row estimation using
your v2 patch for the following example:
CREATE COLLATION case_insensitive (provider = icu, locale =
'@colStrength=secondary', deterministic = false);
CREATE DOMAIN d_txt1 AS text collate case_insensitive;
CREATE TABLE t1 (a int, b int, c text) PARTITION BY LIST(c);
CREATE TABLE t1_p1 PARTITION OF t1 FOR VALUES IN ('0000', '0003', '0004',
'0010');
CREATE TABLE t1_p2 PARTITION OF t1 FOR VALUES IN ('0001', '0005', '0002',
'0009');
CREATE TABLE t1_p3 PARTITION OF t1 FOR VALUES IN ('0006', '0007', '0008',
'0011');
INSERT INTO t1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0,
599, 2) i;
ANALYZE t1;
CREATE TABLE t2 (a int, b int, c d_txt1) PARTITION BY LIST(c);
CREATE TABLE t2_p1 PARTITION OF t2 FOR VALUES IN ('0000', '0003', '0004',
'0010');
CREATE TABLE t2_p2 PARTITION OF t2 FOR VALUES IN ('0001', '0005', '0002',
'0009');
CREATE TABLE t2_p3 PARTITION OF t2 FOR VALUES IN ('0006', '0007', '0008',
'0011');
INSERT INTO t2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0,
599, 3) i;
ANALYZE t2;
SET enable_partitionwise_join TO true;
V1 patch:
postgres=# select * from check_estimated_rows($$ SELECT FROM t1, t2 WHERE t1.c
= t2.c GROUP BY t1.c, t2.c $$);
estimated | actual
-----------+--------
12 | 12
V2 patch:
postgres=# select * from check_estimated_rows($$ SELECT FROM t1, t2 WHERE t1.c
= t2.c GROUP BY t1.c, t2.c $$);
estimated | actual
-----------+--------
144 | 12
I've also tried to make the partitions of t1 and t2 as foreign tables
and I got the same row estimation difference.
I'm just wondering if we are missing something?
About the v2 patch:
+ 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;
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.
--
Matheus Alcantara
EDB: https://www.enterprisedb.com
From cbd6cf8c70f62a4b7ca1782d4858d90652c5c97f Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email protected]>
Date: Tue, 27 Jan 2026 11:59:15 -0300
Subject: [PATCH v3] 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.
Co-authored-by: Jian He
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 | 15 +++++++++++--
3 files changed, 50 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 e3697df51a2..3a5671862dc 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -2674,12 +2674,23 @@ 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 */
+ while (IsA(expr, RelabelType))
+ {
+ RelabelType *re = castNode(RelabelType, expr);
+
+ if (re->resultcollid != exprCollation((Node *)
re->arg))
+ break;
+ expr = re->arg;
+ }
/* 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.51.2
