Re: Asymmetric partition-wise JOIN

On 15/10/2023 17:25, Alexander Korotkov wrote:
> On Sun, Oct 15, 2023 at 8:40 AM Andrei Lepikhov
> <a.lepikhov@postgrespro.ru> wrote:
>> Thanks for such detailed feedback!
>> The rationale for this patch was to give the optimizer additional ways
>> to push down more joins into foreign servers. And, because of
>> asynchronous append, the benefit of that optimization was obvious.
>> Unfortunately, we hadn't found other applications for this feature,
>> which was why this patch was postponed in the core.
>> You have brought new ideas about applying this idea locally. Moreover,
>> the main issue of the patch was massive memory consumption in the case
>> of many joins and partitions - because of reparameterization. But now,
>> postponing the reparameterization proposed in the thread [1] resolves
>> that problem and gives some insights into the reparameterization
>> technique of some fields, like lateral references.
>> Hence, I think we can restart this work.
>> The first thing here (after rebase, of course) is to figure out and
>> implement in the cost model cases of effectiveness when asymmetric join
>> would give significant performance.
> 
> 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.

-- 
regards,
Andrey Lepikhov
Postgres Professional