Message-ID: <b4e48621-88ef-4c45-94c5-af6796f5074c@gmail.com>
Date: Fri, 4 Apr 2025 15:49:56 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Memoize ANTI and SEMI JOIN inner
To: Richard Guo <guofenglinux@gmail.com>, David Rowley <dgrowleyml@gmail.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
References: <60bf8d26-7c7e-4915-b544-afdb9020011d@gmail.com>
 <95586c5d-2632-4f1c-bfb9-4c391f260de4@gmail.com>
 <CAApHDvr2NRbjLXEUXvvKNCDcHfhC6UBL6ZmJzXZ29MBPi3+y8g@mail.gmail.com>
 <CAMbWs4_qE=j=KQ3bs8ryLCe4OPYbSaT+2RAVUaoj6vvxKrQDiQ@mail.gmail.com>
 <1b356b61-3a2a-490a-bf9a-6340a1ec004a@gmail.com>
 <CAMbWs49G-w9UMEBeMVj_ZT6-W-3iv=1xN8QUy_gLSiGf_VXSUw@mail.gmail.com>
 <d700804d-2bac-4264-b831-6508d22419f6@gmail.com>
 <CAMbWs49F9oR30P3AfQor_i-xLBEVuUOJvTxvzpy_wf-11PUYCw@mail.gmail.com>
Content-Language: en-AU
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: <CAMbWs49F9oR30P3AfQor_i-xLBEVuUOJvTxvzpy_wf-11PUYCw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/b4e48621-88ef-4c45-94c5-af6796f5074c%40gmail.com>
Precedence: bulk

On 4/1/25 09:18, Richard Guo wrote:
> On Mon, Mar 31, 2025 at 7:33 PM Andrei Lepikhov <lepihov@gmail.com> wrote:
>> and I don't get the case. As I see, ANTI/SEMI join just transforms to
>> the regular join and it is still not the case. May you be more specific?
> 
> Upthread, you said that a qual contained in ppi_clauses will also be
> included in extra->restrictlist.  I provided this example to show that
> this is not true.  And it seems to me that this discrepancy makes the
> check I mentioned earlier not reliable in all cases.  As I explained
> earlier, this check could pass even if a restriction clause isn't
> parameterized, as long as another join clause, which doesn't belong to
> the current join, is included in ppi_clauses.  This is not correct.
Ok, keep aside the question of why we discuss it here.
After second thought, I think the current check is correct at the 
moment. Looking into the inner_unique proving machinery, I see that the 
inner may be decided to be unique if:
1. It has a unique index on joining columns - in the case of NestLoop, 
these clauses inevitably go down as parameters.
2. degenerate case like x=1 where x is unique column - not our case, 
because x=1 goes down
3. DISTINCT clause - in this case, we can't transform the sublink at 
early stages, and using the already planned subquery --> upper rel 
clause will not be pushed to the unique inner.

So, I see nothing criminal in this clause - it may be a little more 
specific to be sure we will be safe in case of improvements in the 
inner_unique proof logic or subquery parameterisation, but that's it. If 
my analysis is correct, we only need to change the comment.
As usual, it would be nice to see an example of incorrect behaviour if 
I'm wrong.

-- 
regards, Andrei Lepikhov