Message-ID: <e2544a0e-7d84-4ba7-b796-99b0f1d9f4a1@postgrespro.ru>
Date: Tue, 2 Apr 2024 10:07:35 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Asymmetric partition-wise JOIN
To: Alexander Korotkov <aekorotkov@gmail.com>,
 Thomas Munro <thomas.munro@gmail.com>, KaiGai Kohei <kaigai@heterodb.com>,
 sulamul@gmail.com
Cc: Alexander Pyhalov <a.pyhalov@postgrespro.ru>,
 Jaime Casanova <jcasanov@systemguards.com.ec>,
 Aleksander Alekseev <afiskon@gmail.com>, pgsql-hackers@lists.postgresql.org,
 "a.rybakina" <a.rybakina@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>
Content-Language: en-US
From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: <CAPpHfdvwp7cBWs=iBv7zc7e9NOWQf0TmcwNNzw=--H1qFoBcNA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/e2544a0e-7d84-4ba7-b796-99b0f1d9f4a1%40postgrespro.ru>
Precedence: bulk

On 15/10/2023 13:25, Alexander Korotkov wrote:
> Great!  I'm looking forward to the revised patch.
Revising the code and opinions before restarting this work, I found two 
different possible strategies mentioned in the thread:
1. 'Common Resources' shares the materialised result of the inner table 
scan (a hash table in the case of HashJoin) to join each partition one 
by one. It gives us a profit in the case of parallel append and possibly 
other cases, like the one shown in the initial message.
2. 'Individual strategies' - By limiting the AJ feature to cases when 
the JOIN clause contains a partitioning expression, we can push an 
additional scan clause into each copy of the inner table scan, reduce 
the number of tuples scanned, and even prune something because of proven 
zero input.

I see the pros and cons of both approaches. The first option is more 
straightforward, and its outcome is obvious in the case of parallel 
append. But how can we guarantee the same join type for each join? Why 
should we ignore the positive effect of different strategies for 
different partitions?
The second strategy is more expensive for the optimiser, especially in 
the multipartition case. But as I can predict, it is easier to implement 
and looks more natural for the architecture. What do you think about that?

-- 
regards,
Andrei Lepikhov
Postgres Professional