public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andrei Lepikhov <[email protected]>
To: Richard Guo <[email protected]>
Cc: David Rowley <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Subject: Re: Memoize ANTI and SEMI JOIN inner
Date: Mon, 31 Mar 2025 12:33:12 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAMbWs49G-w9UMEBeMVj_ZT6-W-3iv=1xN8QUy_gLSiGf_VXSUw@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<CAApHDvr2NRbjLXEUXvvKNCDcHfhC6UBL6ZmJzXZ29MBPi3+y8g@mail.gmail.com>
<CAMbWs4_qE=j=KQ3bs8ryLCe4OPYbSaT+2RAVUaoj6vvxKrQDiQ@mail.gmail.com>
<[email protected]>
<CAMbWs49G-w9UMEBeMVj_ZT6-W-3iv=1xN8QUy_gLSiGf_VXSUw@mail.gmail.com>
On 3/31/25 12:18, Richard Guo wrote:
> On Mon, Mar 31, 2025 at 6:46 PM Andrei Lepikhov <[email protected]> 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
view thread (26+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Memoize ANTI and SEMI JOIN inner
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox