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

On Mon, Aug 12, 2024 at 8:50 AM Richard Guo <guofenglinux@gmail.com> wrote:
>
> 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.

This will be fine if the number of surviving partitions is only 1 (or
at most a couple), but in case the number of surviving partitions
after pruning are more than a handful, partitionwise join + runtime
partition pruning will be required.

> 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].

Right.

--
Best Wishes,
Ashutosh Bapat