Re: Enable partitionwise join for partition keys wrapped by RelabelType

Hello Hackers,

Thanks for the patch, this is a nice planner improvement. The v3 version
applies to the current master branch and check-world passes. I could
also confirm that the provided test case demonstrates the intended
change of the query plan.

-- Master at d78040a469b
jan=# SET enable_partitionwise_join=on;
SET
jan=# 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 applied
jan=# SET enable_partitionwise_join=on;
SET
jan=# 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)

On 27.01.26 16:42, Matheus Alcantara wrote:

> 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.

Checking every layer seems reasonable to me. Independently of that, I
believe I've identified a regression caused by the patch: in
exprs_known_equal(), RelabelType is stripped from 'expr' but not from
item1 and item2. A few lines later, 'equal(item1, expr)' and
'equal(item2, expr)' are performed. If item1 or item2 is a RelabelType,
these comparisons now return false.

This can happen when the code is called from have_partkey_equi_join() on
a partitioned table. item1/item2 come from rel->partexprs, and for an
expression partition key like '((c)::text)' on a varchar column, the
stored partition expression is itself a RelabelType node.

I could reproduce the issue as follows. On master, the query plan below
is a partition-wise nested loop; after applying this patch, the plan no
longer contains a partition-wise join:

SET enable_partitionwise_join = on;

CREATE TABLE pb1 (a int, c varchar(40)) PARTITION BY HASH ((c::text));
CREATE TABLE pb1_p0 PARTITION OF pb1 FOR VALUES WITH (MODULUS 2,
REMAINDER 0);
CREATE TABLE pb1_p1 PARTITION OF pb1 FOR VALUES WITH (MODULUS 2,
REMAINDER 1);

CREATE TABLE pb2 (a int, c varchar(40)) PARTITION BY HASH ((c::text));
CREATE TABLE pb2_p0 PARTITION OF pb2 FOR VALUES WITH (MODULUS 2,
REMAINDER 0);
CREATE TABLE pb2_p1 PARTITION OF pb2 FOR VALUES WITH (MODULUS 2,
REMAINDER 1);

-- Master branch
jan=# EXPLAIN (COSTS OFF) SELECT * FROM pb1 JOIN pb2 ON pb1.c::text =
pb2.c::text WHERE pb1.c::text = '0002';
QUERY PLAN
--------------------------------------------------------
Append
-> Nested Loop
-> Seq Scan on pb1_p0 pb1_1
Filter: ((c)::text = '0002'::text)
-> Materialize
-> Seq Scan on pb2_p0 pb2_1
Filter: ((c)::text = '0002'::text)
-> Nested Loop
-> Seq Scan on pb1_p1 pb1_2
Filter: ((c)::text = '0002'::text)
-> Materialize
-> Seq Scan on pb2_p1 pb2_2
Filter: ((c)::text = '0002'::text)
(13 rows)

-- With patch applied
jan=# EXPLAIN (COSTS OFF) SELECT * FROM pb1 JOIN pb2 ON pb1.c::text =
pb2.c::text WHERE pb1.c::text = '0002';
QUERY PLAN
--------------------------------------------------------
Nested Loop
-> Append
-> Seq Scan on pb1_p0 pb1_1
Filter: ((c)::text = '0002'::text)
-> Seq Scan on pb1_p1 pb1_2
Filter: ((c)::text = '0002'::text)
-> Materialize
-> Append
-> Seq Scan on pb2_p0 pb2_1
Filter: ((c)::text = '0002'::text)
-> Seq Scan on pb2_p1 pb2_2
Filter: ((c)::text = '0002'::text)
(12 rows)

I think the RelabelType nodes also need to be stripped from item1/item2
before the comparison. find_ec_member_matching_expr() performs something
similar and the RelabelType is stripped for both arguments of the
equal() call.

Best regards
Jan

--
Jan Nidzwetzki
PlanetScale Postgres Core Team