Re: Enable partitionwise join for partition keys wrapped by RelabelType

Hello Matheus,

On 27.08.26 19:08, Matheus Alcantara wrote:
> Thanks for the review. v5 is attached, now split into two patches: 0001 is
> the partprune fix and 0002 is the exprs_known_equal() fix.

Thank you for the updated, split version 5 of the patch. Both patches
apply cleanly to the main branch (de56594), and I verified that only the
added tests fail when the change is not applied. So, the split and the
tests look good to me. I also appreciate the tests added to
'collate.icu.utf8.sql'.

While testing the patch, I noticed an asymmetry between inner and outer
joins. The patch handles the inner join case, while the outer join case
produces a non-partitionwise query plan.

From my understanding, outer-join clauses don't form an equivalence
class. So, have_partkey_equi_join() can only prove the keys equal by
matching the join clause through match_expr_to_partition_keys(), which
strips the clause operand but compares it against rel->partexprs unstripped.

This can be reproduced as follows:

CREATE TABLE e1 (a int, c varchar(40)) PARTITION BY HASH ((c::text));
CREATE TABLE e1_p1 PARTITION OF e1 FOR VALUES WITH (MODULUS 2, REMAINDER 0);
CREATE TABLE e1_p2 PARTITION OF e1 FOR VALUES WITH (MODULUS 2, REMAINDER 1);
CREATE TABLE e2 (a int, c varchar(40)) PARTITION BY HASH ((c::text));
CREATE TABLE e2_p1 PARTITION OF e2 FOR VALUES WITH (MODULUS 2, REMAINDER 0);
CREATE TABLE e2_p2 PARTITION OF e2 FOR VALUES WITH (MODULUS 2, REMAINDER 1);

SET max_parallel_workers_per_gather = 0;
SET enable_hashjoin = off;
SET enable_mergejoin = off;
SET enable_partitionwise_join = on;

-- inner join: partitionwise
jan=# EXPLAIN (COSTS OFF) SELECT * FROM e1 JOIN e2 ON e1.c::text =
e2.c::text;
QUERY PLAN
--------------------------------------------------------
Append
-> Nested Loop
Join Filter: ((e1_1.c)::text = (e2_1.c)::text)
-> Seq Scan on e1_p1 e1_1
-> Materialize
-> Seq Scan on e2_p1 e2_1
-> Nested Loop
Join Filter: ((e1_2.c)::text = (e2_2.c)::text)
-> Seq Scan on e1_p2 e1_2
-> Materialize
-> Seq Scan on e2_p2 e2_2
(11 rows)

-- outer join: not partitionwise
jan=# EXPLAIN (COSTS OFF) SELECT * FROM e1 LEFT JOIN e2 ON e1.c::text =
e2.c::text;
QUERY PLAN
----------------------------------------------
Nested Loop Left Join
Join Filter: ((e1.c)::text = (e2.c)::text)
-> Append
-> Seq Scan on e1_p1 e1_1
-> Seq Scan on e1_p2 e1_2
-> Materialize
-> Append
-> Seq Scan on e2_p1 e2_1
-> Seq Scan on e2_p2 e2_2
(9 rows)

I did a few tests, and I think we need to do the stripping in
match_expr_to_partition_keys() as well. By applying this change, the
query plan changes as follows:

-- Now partitionwise
jan=# EXPLAIN (COSTS OFF) SELECT * FROM e1 LEFT JOIN e2 ON e1.c::text =
e2.c::text;
QUERY PLAN
--------------------------------------------------------
Append
-> Nested Loop Left Join
Join Filter: ((e1_1.c)::text = (e2_1.c)::text)
-> Seq Scan on e1_p1 e1_1
-> Materialize
-> Seq Scan on e2_p1 e2_1
-> Nested Loop Left Join
Join Filter: ((e1_2.c)::text = (e2_2.c)::text)
-> Seq Scan on e1_p2 e1_2
-> Materialize
-> Seq Scan on e2_p2 e2_2
(11 rows)

I drafted a possible fix in my tree, added a test case that fails
without the modification, and exported it as a new patch. Attached is a
new series. Patches 1 and 2 are yours in an unmodified form. Patch 3 is
the new one, which could potentially be squashed into patch 2 if you
find the modification useful.

Best regards
Jan

--
Jan Nidzwetzki
PlanetScale Postgres Core Team