Re: NOT IN and NOT EXIST

On 07/03/2014 08:35 AM, Sameer Kumar wrote:
>
> Hi,
>
> Postgres optimizer automatically tries to convert an IN clause to Hash
> Join (something similar to EXISTS equivalent of that query).
>
> Does a similar translation happen for NOT IN as well? Given that the
> column used is NOT NUL.
>
> Select * from emp where deptid not in (select deptid from dept where
> deptLocation='New York');
>
> Will this above statement be automatically converted to a plan which
> would match below statement?
>
> Select * from emp where not exists (select 1 from dept where
> deptLocation='New York' and dept.deptid=emp.deptid);
>
>
Caveat: I am *not* a planner guru... However:

You can easily test this for yourself using explain.

But note that the planner doesn't just blindly and consistently map from
one plan to another. It also evaluates things like distribution of
values, availability of indexes, size of tables, etc. So the planner
will likely choose one plan if dept is very small and another if it is
huge. Similarly, it might choose to use an index on deptid if available
but again use of that index will depend on table size and distributions
for deptid. In other words, what you see on a small test data set may
differ from what you get on a large live one.

Also note that depending on the version of PostgreSQL and the nature of
your data it could be preferable to use WHERE IN... or to use EXISTS.
Fortunately as the planner has been imbued with ever increasing smarts,
the need to test and choose between the two seems to have diminished.

Cheers,
Steve

Cheers,
Steve