Re: Looking for an efficient way to replace efficient NOT IN when landling very large data

On Tue, 11 Apr 2023 at 10:33, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Tue, 11 Apr 2023 at 21:28, Shaozhong SHI <shishaozhong(at)gmail(dot)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