Re: Enable partitionwise join for partition keys wrapped by RelabelType

Hello Matheus,

On 24.08.26 19:04, Matheus Alcantara wrote:
[...]
> You're right, exprs_known_equal() was stripping the RelabelType only
> from the EC member's expression, but not from item1/item2. item1/item2
> in the have_partkey_equi_join() path come directly from rel->partexprs,
> which for an expression partition key like ((c::text)) on a varchar
> column are themselves RelabelType-wrapped, so the raw equal() check
> happened to succeed by accident (both sides kept the wrapper). Once
> em_expr got stripped and item1/item2 didn't, that symmetry broke, and I
> could reproduce the regression you reported. Thanks.

[...]

> The attached v4 patch strips RelabelType from item1 and item2 as well,
> so both sides of the equal() check are treated consistently.
>
> While debbuging this down I found a second, related issue: partition
> pruning has the same asymmetry.

Thank you for the updated version 4 of the patch. I can confirm it
applies cleanly to master (5e1e32c2), check-world passes, and the
regression observed in v3 is gone. I looked into the patch and I have
the following comments:

=========
Partprune
=========

The new change in partprune.c might be something that deserves its own
patch since it touches the partitioning code; it's a good improvement,
and it is testable on its own (and not covered by tests so far).

The improvement is easy to demonstrate:

CREATE TABLE vprune (c varchar(40)) PARTITION BY LIST ((c::text));
CREATE TABLE vprune_a PARTITION OF vprune FOR VALUES IN ('a');
CREATE TABLE vprune_b PARTITION OF vprune FOR VALUES IN ('b');

On master
=========
jan=# EXPLAIN (COSTS OFF) SELECT * FROM vprune WHERE c = 'a';
QUERY PLAN
-----------------------------------------
Append
-> Seq Scan on vprune_a vprune_1
Filter: ((c)::text = 'a'::text)
-> Seq Scan on vprune_b vprune_2
Filter: ((c)::text = 'a'::text)
(5 rows)

With the partprune.c part of the patch applied
===============================================
jan=# EXPLAIN (COSTS OFF) SELECT * FROM vprune WHERE c = 'a';
QUERY PLAN
-----------------------------------
Seq Scan on vprune_a vprune
Filter: ((c)::text = 'a'::text)
(2 rows)

===============
Remaining Patch
===============

The new test in partition_join.sql addresses the v3 regression, but it
does not cover the behavior this patch changes: as you note above, with
an expression partition key like ((c::text)), both sides keep the
wrapper. So, the test produces identical output whether the patch is
applied or not.

The mismatch the patch fixes requires exactly one side decorated. That
happens with a plain varchar column key, PARTITION BY HASH (c), where
rel->partexprs holds a plain Var while the equivalence class member is
RelabelType-wrapped.

Making that visible requires two more things: (1) the equality has to
come from the equivalence class rather than from the join clause, so a
constant is required; and (2) the constant must not be prunable at plan
time, otherwise both sides collapse to a single partition. The function
current_setting() satisfies both.

With pht3 and pht4 redefined in this manner (PARTITION BY HASH (c)),
there is no partition-wise join on the master branch for the following
query. However, with the patch applied, this changes:

On master
=========
jan=# SET enable_partitionwise_join = on;
jan=# EXPLAIN (COSTS OFF) SELECT t1.a, t2.a FROM pht3 t1 JOIN pht4 t2
ON t1.c = t2.c WHERE t1.c = current_setting('timezone');
QUERY PLAN

-----------------------------------------------------------------------------
Nested Loop
-> Append
Subplans Removed: 2
-> Seq Scan on pht3_p3 t1_1
Filter: ((c)::text = current_setting('timezone'::text))
-> Materialize
-> Append
Subplans Removed: 2
-> Seq Scan on pht4_p3 t2_1
Filter: ((c)::text = current_setting('timezone'::text))
(10 rows)

With the patch applied
======================
jan=# SET enable_partitionwise_join = on;
jan=# EXPLAIN (COSTS OFF) SELECT t1.a, t2.a FROM pht3 t1 JOIN pht4 t2
ON t1.c = t2.c WHERE t1.c = current_setting('timezone');
QUERY PLAN
-----------------------------------------------------------------------
Append
-> Nested Loop
-> Seq Scan on pht3_p1 t1_1
Filter: ((c)::text = current_setting('timezone'::text))
-> Seq Scan on pht4_p1 t2_1
Filter: ((c)::text = current_setting('timezone'::text))
-> Nested Loop
-> Seq Scan on pht3_p2 t1_2
Filter: ((c)::text = current_setting('timezone'::text))
-> Seq Scan on pht4_p2 t2_2
Filter: ((c)::text = current_setting('timezone'::text))
-> Nested Loop
-> Seq Scan on pht3_p3 t1_3
Filter: ((c)::text = current_setting('timezone'::text))
-> Seq Scan on pht4_p3 t2_3
Filter: ((c)::text = current_setting('timezone'::text))
(16 rows)

I think this would make a good test for partition_join.sql, since it
fails without the patch.

However, the new plan is not necessarily faster than the old one. On
master, both Appends can use run-time pruning (Subplans Removed: 2), so
only one partition per side is scanned. The partition-wise plan lacks
any pruning, so the outer side of every child join is fully scanned.
This seems to be because the pruning information is built from
rel->baserestrictinfo (see create_append_plan()), which is NIL for the
joinrel that a partition-wise Append sits on (see build_join_rel()).
However, plan-time pruning is not affected.

I don't believe the patch is to blame here. The planner opts for the
partition-wise plan based on estimated costs, but these cost estimates
do not account for run-time pruning. As a result, it fails to recognize
that the other plan would eliminate two-thirds of the scans during
execution. The patch simply makes partition-wise joins available for
more queries.

Additionally, the query in question is somewhat artificial, designed to
highlight the EC path; with a simple constant, the partitions are pruned
at planning time regardless. However, enabling partition-wise joins for
these EC-derived equalities could negatively impact queries that
previously benefited from run-time pruning. Was this interaction taken
into account?

================
Further comments
================

1) I couldn't find a test case that proves we need the collation guard
in the introduced strip_collation_preserving_relabel() function. Do you
have a test case that would break without the guard?

2) I am wondering if the following cast in
match_clause_to_partition_key() is actually needed, since
RelabelType.arg is already an (Expr *):

partkey = (const Expr *) ((const RelabelType *) partkey)->arg;

Apart from that, the patch looks good to me.

Best regards
Jan

--
Jan Nidzwetzki
PlanetScale Postgres Core Team