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).
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.
On match_expr_to_partition_keys() we already strip away any RelabelType
that is present on OpExpr, so I think that can also modify
exprs_known_equal() to do the same and enable partitionwise joins for
more cases. Please see the attached patch.
On master for queries like the following we have this plan:
EXPLAIN(VERBOSE, COSTS OFF) SELECT fprt3.a, fprt4.a FROM fprt3 JOIN
fprt4 ON fprt3.c = fprt4.c WHERE fprt3.c = '0002';
QUERY PLAN
---------------------------------------------------------------------------------
Nested Loop
Output: fprt3.a, fprt4.a
-> Foreign Scan on public.fprt3_p2 fprt3
Output: fprt3.a, fprt3.c
Remote SQL: SELECT a, c FROM public.fprt3_ft WHERE ((c = '0002'))
-> Materialize
Output: fprt4.a, fprt4.c
-> Foreign Scan on public.fprt4_p2 fprt4
Output: fprt4.a, fprt4.c
Remote SQL: SELECT a, c FROM public.fprt4_ft WHERE ((c = '0002'))
(10 rows)
With the patch and "enable_partitionwise_join" set to on we get this query plan
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)
--
Matheus Alcantara
EDB: http://www.enterprisedb.com
From 230ede3b3a7af5cf7ad28c786530d6f5bd5d69c6 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email protected]>
Date: Fri, 12 Dec 2025 14:49:54 -0300
Subject: [PATCH v1] 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.
---
.../postgres_fdw/expected/postgres_fdw.out | 21 +++++++++++++++++++
contrib/postgres_fdw/sql/postgres_fdw.sql | 16 ++++++++++++++
src/backend/optimizer/path/equivclass.c | 9 ++++++--
3 files changed, 44 insertions(+), 2 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out
b/contrib/postgres_fdw/expected/postgres_fdw.out
index 48e3185b227..1fd227976d5 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 9a8f9e28135..9142acf735b 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3270,6 +3270,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;
@@ -3300,6 +3312,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 441f12f6c50..ce75e3d70df 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -2675,12 +2675,17 @@ 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. */
+ while (IsA(expr, RelabelType))
+ expr = (Expr *) (castNode(RelabelType,
expr))->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
