Thread

Enable partitionwise join for partition keys wrapped by RelabelType

Matheus Alcantara <matheusssilv97@gmail.com> — 2025-12-15T14:46:31Z

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