Re: Memoize ANTI and SEMI JOIN inner - Mailing list pgsql-hackers

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