Re: Asymmetric partition-wise JOIN

On 17/10/2023 17:09, Ashutosh Bapat wrote:
> On Tue, Oct 17, 2023 at 2:05 PM Andrei Lepikhov
> <a(dot)lepikhov(at)postgrespro(dot)ru> wrote:
>>
>> On 16/10/2023 23:21, Ashutosh Bapat wrote:
>>> On Mon, Oct 16, 2023 at 10:24 AM Andrei Lepikhov
>>> 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.
>>>
>> I'm curious, which type of problems do you afraid here? Why we need a
>> range table entry for each scan of non-partitioned relation?
>>
>
> Not RTE but RelOptInfo.
>
> Using the same example as Alexander Korotkov, let's say A is the
> nonpartitioned table and P is partitioned table with partitions P1,
> P2, ... Pn. The partitionwise join would need to compute AP1, AP2, ...
> APn. Each of these joins may have different properties and thus will
> require creating paths. In order to save these paths, we need
> RelOptInfos which are indentified by relids. Let's assume that the
> relids of these join RelOptInfos are created by union of relid of A
> and relid of Px (the partition being joined). This is notionally
> misleading but doable.

Ok, now I see your disquiet. In current patch we have built RelOptInfo
for each JOIN(A, Pi) by the build_child_join_rel() routine. And of
course, they all have different sets of cheapest paths (it is one more
point of optimality). At this point the RelOptInfo of relation A is
fully formed and upper joins use the pathlist "as is", without changes.

> But the clauses of A parameterized by P will produce different
> translations for each of the partitions. I think we will need
> different RelOptInfos (for A) to store these translations.

Does the answer above resolved this issue?

> The relid is also used to track the scans at executor level. Since we
> have so many scans on A, each may be using different plan, we will
> need different ids for those.

I don't understand this sentence. Which way executor uses this index of
RelOptInfo ?

--
regards,
Andrey Lepikhov
Postgres Professional