Message-ID: <d700804d-2bac-4264-b831-6508d22419f6@gmail.com>
Date: Mon, 31 Mar 2025 12:33:12 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Memoize ANTI and SEMI JOIN inner
To: Richard Guo <guofenglinux@gmail.com>
Cc: David Rowley <dgrowleyml@gmail.com>,
 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>
Content-Language: en-AU
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: 
 <CAMbWs49G-w9UMEBeMVj_ZT6-W-3iv=1xN8QUy_gLSiGf_VXSUw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/d700804d-2bac-4264-b831-6508d22419f6%40gmail.com>
Precedence: bulk

On 3/31/25 12:18, Richard Guo wrote:
> On Mon, Mar 31, 2025 at 6:46 PM Andrei Lepikhov <lepihov@gmail.com> wrote:
>   Nested Loop
>     ->  Seq Scan on t t2
>     ->  Nested Loop
>           ->  Seq Scan on t t1
>           ->  Subquery Scan on t3
>                 Filter: ((t2.a = t3.a) AND (t1.b = t3.b))
>                 ->  Seq Scan on t t3_1
> (7 rows)
> 
> t3's ppi_clauses includes "t2.a = t3.a" and "t1.b = t3.b", while t1/t3
> join's restrictlist only includes "t1.b = t3.b".
I attempted to make your query ab it closer to our case:

SET enable_mergejoin = f;
SET enable_hashjoin = f;
SET enable_material = f;
CREATE INDEX ON t(a);

explain (costs off)
select * from t t1 join t t2
   on EXISTS (select *, t1.a+t2.a as x from t t3
     WHERE t2.a = t3.a AND t1.b = t3.b);

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?

-- 
regards, Andrei Lepikhov