Обсуждение: NOT IN and NOT EXIST

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);

Regards
Sameer

PS: Sent from my Mobile device. Pls ignore typo n abb

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

On 07/04/2014 06:12 AM, Sameer Kumar wrote:
> NOT EXISTS translates to HASH ANTI JOIN and NOT IN translates to NOT
> (HASHED) operation.
>
> Given that the columns used in NOT IN clause (for outer as well as
> inner) are NOT NULL, should not it translate a NOT IN plan similar to
> NOT EXISTS plan?

It will, hopefully in 9.5.

https://commitfest.postgresql.org/action/patch_view?id=1487
--
Vik