Message-ID: <d99ed0cb-c1b0-4da0-a9f7-b7061cb497bb@postgrespro.ru>
Date: Wed, 18 Oct 2023 12:25:23 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Asymmetric partition-wise JOIN
Content-Language: en-US
To: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Cc: Alexander Korotkov <aekorotkov@gmail.com>,
 Alexander Pyhalov <a.pyhalov@postgrespro.ru>,
 Jaime Casanova <jcasanov@systemguards.com.ec>,
 Aleksander Alekseev <afiskon@gmail.com>, pgsql-hackers@lists.postgresql.org,
 KaiGai Kohei <kaigai@heterodb.com>, "a.rybakina"
 <a.rybakina@postgrespro.ru>,
 =?UTF-8?B?0JHQtdC70Y/Qu9C+0LIg0JTQsNC80LjRgCDQndCw0LjQu9C10LLQuNGH?=
 <d.belyalov@postgrespro.ru>
References: 
 <CAOP8fzaVL_2SCJayLL9kj5pCA46PJOXXjuei6-3aFUV45j4LJQ@mail.gmail.com>
 <CALtqXTfhC=FfHe0+_RP84w=X13d+z+akyN_ROCEQAyQigW7PDA@mail.gmail.com>
 <163118104689.1167.8241611097516113795.pgcf@coridan.postgresql.org>
 <20210909153833.GA6514@ahch-to>
 <2c9caede-55c0-2042-e421-dd0021f28837@postgrespro.ru>
 <792d60f4-37bc-e6ad-68ca-c2af5cbb2d9b@postgrespro.ru>
 <88bc3c051d285653215393a56bdf3056@postgrespro.ru>
 <CAPpHfdtm2_xM1WP1=Jyj9vRQ5jNu-kSqaoazvUp_s_7Cq0906A@mail.gmail.com>
 <5c0e38e3-7ab5-4b10-a1bb-70ca69771ff0@postgrespro.ru>
 <CAPpHfdvwp7cBWs=iBv7zc7e9NOWQf0TmcwNNzw=--H1qFoBcNA@mail.gmail.com>
 <521c2a71-49dd-4ab5-a585-569d0af3a647@postgrespro.ru>
 <CAExHW5vOGLD5MUW2tMTYR8pSjcT67+RVRyDy99fUSCKsdBELaA@mail.gmail.com>
 <a4fe3652-a0a0-4482-8a7e-15024671b53e@postgrespro.ru>
 <CAExHW5uOSp9LZKAsb2ejn+PRNshyAruGppAmAef=wdhRrQ=rqQ@mail.gmail.com>
From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: 
 <CAExHW5uOSp9LZKAsb2ejn+PRNshyAruGppAmAef=wdhRrQ=rqQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/d99ed0cb-c1b0-4da0-a9f7-b7061cb497bb%40postgrespro.ru>
Precedence: bulk

On 17/10/2023 17:09, Ashutosh Bapat wrote:
> On Tue, Oct 17, 2023 at 2:05 PM Andrei Lepikhov
> <a.lepikhov@postgrespro.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