Re: Asymmetric partition-wise JOIN

On Mon, Oct 16, 2023 at 10:24 AM Andrei Lepikhov
<a(dot)lepikhov(at)postgrespro(dot)ru> wrote:
>
> >
> > Great! I'm looking forward to the revised patch
> Before preparing a new patch, it would be better to find the common
> ground in the next issue:
> So far, this optimization stays aside, proposing an alternative path for
> a join RelOptInfo if we have an underlying append path in the outer.
> My back burner is redesigning the approach: asymmetric join doesn't
> change the partitioning scheme and bounds of the partitioned side. So,
> it looks consecutive to make it a part of partitionwise_join machinery
> and implement it as a part of the try_partitionwise_join /
> generate_partitionwise_join_paths routines.
>

I think we need an example where such a join will be faster than
non-partitioned join when both the sides are local. It might be
possible to come up with such an example without writing any code. The
idea would be to rewrite SQL as union of joins.

Whenever I visited this idea, I hit one issue prominently - how would
we differentiate different scans of the non-partitioned relation.
Normally we do that using different Relids but in this case we
wouldn't be able to know the number of such relations involved in the
query unless we start planning such a join. It's late to add new base
relations and assign them new Relids. Of course I haven't thought hard
about it. I haven't looked at the patch to see whether this problem is
solved and how.

--
Best Wishes,
Ashutosh Bapat