MIME-Version: 1.0
References: 
 <CA+i5JwYoxnYGHXn41AgnN9iJP8FCMDMozgzsn5hc0tvhSSMNVg@mail.gmail.com>
 <CAApHDvox+8heOrYyD5Y8gpz6+wcj0w4Th2gT9+msOaH__4eFGQ@mail.gmail.com>
In-Reply-To: 
 <CAApHDvox+8heOrYyD5Y8gpz6+wcj0w4Th2gT9+msOaH__4eFGQ@mail.gmail.com>
From: Shaozhong SHI <shishaozhong@gmail.com>
Date: Tue, 11 Apr 2023 10:44:02 +0100
Message-ID: 
 <CA+i5Jwa6evCRWGZ8C2oW4jRdXKBu0ot_rjqKCq7sVepBAkCzoA@mail.gmail.com>
Subject: Re: Looking for an efficient way to replace efficient NOT IN when
 landling very large data
To: David Rowley <dgrowleyml@gmail.com>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000915b7405f90c5524"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2Bi5Jwa6evCRWGZ8C2oW4jRdXKBu0ot_rjqKCq7sVepBAkCzoA%40mail.gmail.com>
Precedence: bulk

--000000000000915b7405f90c5524
Content-Type: text/plain; charset="UTF-8"

On Tue, 11 Apr 2023 at 10:33, David Rowley <dgrowleyml@gmail.com> wrote:

> On Tue, 11 Apr 2023 at 21:28, Shaozhong SHI <shishaozhong@gmail.com>
> wrote:
> >
> > Select a.id, a.name, b.id, b.name from a_large_table a, definitive b
> where (a.id, b.name) not in
> > (select b.id, b.name from definitive b)
> >
> > is very slow.
> >
> > Is there a faster way to do so?
>
> It depends on what your exact requirements are for the NULL handling
> that NOT IN provides.  Do you need the query to return 0 rows if b.id
> and b.name are null?  This question is moot if none of the columns or
> either table allow NULLs.
>
> If you don't require that, then you'll give the planner more
> flexibility to choose a more efficient plan if you use NOT EXISTS
> instead.
>
> David
>

I would like to try out an example of NOT EXISTS way and see how the
replacement works.

Regards,

David

--000000000000915b7405f90c5524
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Tue, 11 Apr 2023 at 10:33, David R=
owley &lt;<a href=3D"mailto:dgrowleyml@gmail.com">dgrowleyml@gmail.com</a>&=
gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0=
px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">On Tu=
e, 11 Apr 2023 at 21:28, Shaozhong SHI &lt;<a href=3D"mailto:shishaozhong@g=
mail.com" target=3D"_blank">shishaozhong@gmail.com</a>&gt; wrote:<br>
&gt;<br>
&gt; Select <a href=3D"http://a.id" rel=3D"noreferrer" target=3D"_blank">a.=
id</a>, <a href=3D"http://a.name" rel=3D"noreferrer" target=3D"_blank">a.na=
me</a>, <a href=3D"http://b.id" rel=3D"noreferrer" target=3D"_blank">b.id</=
a>, <a href=3D"http://b.name" rel=3D"noreferrer" target=3D"_blank">b.name</=
a> from a_large_table a, definitive b where (<a href=3D"http://a.id" rel=3D=
"noreferrer" target=3D"_blank">a.id</a>, <a href=3D"http://b.name" rel=3D"n=
oreferrer" target=3D"_blank">b.name</a>) not in<br>
&gt; (select <a href=3D"http://b.id" rel=3D"noreferrer" target=3D"_blank">b=
.id</a>, <a href=3D"http://b.name" rel=3D"noreferrer" target=3D"_blank">b.n=
ame</a> from definitive b)<br>
&gt;<br>
&gt; is very slow.<br>
&gt;<br>
&gt; Is there a faster way to do so?<br>
<br>
It depends on what your exact requirements are for the NULL handling<br>
that NOT IN provides.=C2=A0 Do you need the query to return 0 rows if <a hr=
ef=3D"http://b.id" rel=3D"noreferrer" target=3D"_blank">b.id</a><br>
and <a href=3D"http://b.name" rel=3D"noreferrer" target=3D"_blank">b.name</=
a> are null?=C2=A0 This question is moot if none of the columns or<br>
either table allow NULLs.<br>
<br>
If you don&#39;t require that, then you&#39;ll give the planner more<br>
flexibility to choose a more efficient plan if you use NOT EXISTS<br>
instead.<br>
<br>
David<br></blockquote><div><br></div><div>I would like to try out an exampl=
e of NOT EXISTS way and see how the replacement works.</div><div><br></div>=
<div>Regards,</div><div><br></div><div>David=C2=A0</div></div></div>

--000000000000915b7405f90c5524--