On Mon, Oct 16, 2023 at 10:24 AM Andrei Lepikhov <a.lepik...@postgrespro.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
