On 3/31/25 12:18, Richard Guo wrote:
On Mon, Mar 31, 2025 at 6:46 PM Andrei Lepikhov <lepi...@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
