Re: A problem about partitionwise join - Mailing list pgsql-hackers

On Sat, Aug 10, 2024 at 6:22 AM Alexey Dvoichenkov
<alexey@hyperplane.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