Re: NOT IN and NOT EXIST - Mailing list pgsql-general
On Thu, Jul 3, 2014 at 11:52 PM, Steve Crawford <scrawford@pinpointresearch.com> wrote:
You can easily test this for yourself using explain.
I tried it out.
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?
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.
Postgres already does that for IN and EXISTS. Both would try to use HASH. But does not seem to be the case with NOT IN and NOT EXISTS.
Best Regards,
Sameer Kumar | Database Consultant
ASHNIK PTE. LTD.
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533
M: +65 8110 0350 T: +65 6438 3504 | www.ashnik.com
This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).
Attachment
pgsql-general by date: