Re: Memoize ANTI and SEMI JOIN inner

On 3/31/25 12:18, Richard Guo wrote:
> On Mon, Mar 31, 2025 at 6:46 PM Andrei Lepikhov <lepihov(at)gmail(dot)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