Re: Enable partitionwise join for partition keys wrapped by RelabelType

On Fri Aug 28, 2026, Jan Nidzwetzki wrote:
> 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.

I think that's right, for a non-degenerate outer-join qual,
distribute_qual_to_rels() sets maybe_equivalence = false , so
process_equivalence() is never called, the comment there gives the
reason, that the two sides may be unequal above the join once one of
them has gone to NULL. So loop 2 has nothing to work with and loop 1 is
the only path, which is exactly where the unstripped partexprs
comparison bites.

Your 0003 patch looks correct to me, I've just made two additions to its
test:

1. The fix strips both rel->partexprs and rel->nullable_partexprs, but a
two-way LEFT JOIN only ever consults the non-nullable list, so half the
change had no coverage. I added a three-way case:

SELECT count(*) FROM (pht5 t1 LEFT JOIN pht6 t2 ON t1.c::text = t2.c::text)
LEFT JOIN pht7 t3 ON t2.c::text = t3.c::text;

Here the upper clause references t2.c, which can only be in the (pht5,
pht6) joinrel's nullable_partexprs, t1.c is the non-nullable entry and
has a different varno. This was fully non-partitionwise before 0003 and
is fully partitionwise after it, so it does cover the nullable half.

2. Every c value was present in both tables, so the LEFT JOIN produced
no null-extended rows at all. I added one key with no match in pht6 and
changed the query to report count(*) and count(t2.a), so the difference
makes the null-extended row visible and the test checks outer join
semantics rather than just the plan shape.

I also set max_parallel_workers_per_gather = 0 for these, since the
three-way query otherwise picked up a Gather and a Parallel Append whose
child order varied.

> Patch 3 is the new one, which could potentially be squashed into patch 2
> if you find the modification useful.

I've left it separate for now although I think that make sense to
squash it into 0002. Let's see if a committer have any thoughts on this.

While checking whether match_expr_to_partition_keys(), I found one more
case, and I've fixed on 0004. group_by_has_partkey() strips RelabelType
from the grouping expressions but not from the partition key expression.
The effect is that full partitionwise aggregation is never chosen for a
partition key involving a binary-compatible cast, and the plan falls
back to partial aggregation with a finalize step. Both spellings fail,
for slightly different reasons: GROUP BY c compares a bare Var against a
RelabelType, and GROUP BY c::text strips the grouping side to a bare Var
while the partition key side stays wrapped.

v7 attached. 0001 and 0002 remains the same. 0003 is your patch with new
test cases added and 0004 is the new one.

--
Matheus Alcantara
EDB: https://www.enterprisedb.com