Re: A problem about partitionwise join

On Sat, Aug 10, 2024 at 6:22 AM Alexey Dvoichenkov
<alexey(at)hyperplane(dot)net> wrote:
> I haven't read the entire thread so I might be missing something, but
> one interesting consequence of this patch is that it kind of breaks
> the initial pruning of generic plans. Given a query such as SELECT
> ... WHERE A.PK = B.PK AND A.PK = $1 the planner will do the right
> thing for custom plans, but not for GPs since the existing logic is
> not capable of pruning anything more complex than a scan. See the
> attached example.

Thanks for the report! I see what the problem is. Previously, for a
join with filter 'WHERE A.PK = B.PK AND A.PK = $1', the planner was
unable to generate partitionwise join, because it failed to realize
that there exists an equi-join condition between A.PK and B.PK. As a
result, the prepared statement 'ps' was planned as a join of two
Appends in generic mode:

Nested Loop
-> Append
-> Seq Scan on a0 a_1
Filter: (x = $1)
-> Seq Scan on a1 a_2
Filter: (x = $1)
-> Materialize
-> Append
-> Seq Scan on b0 b_1
Filter: (x = $1)
-> Seq Scan on b1 b_2
Filter: (x = $1)

... and then one of the subpaths for each Append node would be pruned
during initial pruning phase, so you'd get:

Nested Loop
-> Append
Subplans Removed: 1
-> Seq Scan on a0 a_1
Filter: (x = $1)
-> Materialize
-> Append
Subplans Removed: 1
-> Seq Scan on b0 b_1
Filter: (x = $1)

With this patch, the planner is able to generate partitionwise join,
as it can recognize the equi-join condition between A.PK and B.PK from
ECs. So the prepared statement 'ps' is planned as an Append of two
joins in generic mode:

Append
-> Nested Loop
-> Seq Scan on a0 a_1
Filter: (x = $1)
-> Seq Scan on b0 b_1
Filter: (x = $1)
-> Nested Loop
-> Seq Scan on a1 a_2
Filter: (x = $1)
-> Seq Scan on b1 b_2
Filter: (x = $1)

... and neither subpath of this Append can be pruned during the
initial pruning phase.

It seems to me that this is not the fault of this patch: it fixes the
partitionwise join as expected. The ideal fix to this issue is, IMO,
to take initial pruning into account when calculating costs, so we can
pick the non-partitionwise-join path and then apply the initial
pruning if that is cheaper. Of course we also need to fix
apply_scanjoin_target_to_paths to not drop old paths of partitioned
joinrels so that we can retain non-partitionwise-join paths if
the cheapest path happens to be among them. This work is being
discussed in [1].

For now, I think you can work around this issue by setting
enable_partitionwise_join to off for this query, if that works for
you.

[1] https://postgr.es/m/CAExHW5toze58+jL-454J3ty11sqJyU13Sz5rJPQZDmASwZgWiA@mail.gmail.com

Thanks
Richard