Re: A problem about partitionwise join

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

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