To: Miernik <public@public.miernik.name>
cc: pgsql-performance@postgresql.org
Subject: Re: why "WHERE uid NOT IN" is so slow,
	and EXCEPT in the same situtation is fast?
In-reply-to: <20080731024508.53F6.0.NOFFLE@turbacz.local> 
References: <20080731024508.53F6.0.NOFFLE@turbacz.local>
Comments: In-reply-to Miernik <public@public.miernik.name>
	message dated "Thu, 31 Jul 2008 04:45:08 +0200"
Date: Wed, 30 Jul 2008 23:08:06 -0400
Message-ID: <27820.1217473686@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Miernik <public@public.miernik.name> writes:
> Two queries which do the same thing, first one takes ages to complete
> (did wait several minutes and cancelled it), while the second one took
> 9 seconds? Don't they do the same thing?

Hmm, what have you got work_mem set to?  The first one would likely
have been a lot faster if it had hashed the subplan; which I'd have
thought would happen with only 80K rows in the subplan result,
except it didn't.

The queries are in fact not exactly equivalent, because EXCEPT
involves some duplicate-elimination behavior that won't happen
in the NOT IN formulation.  So I don't apologize for your having
gotten different plans.  But you should have gotten a plan less
awful than that one for the NOT IN.

Another issue is that the NOT IN will probably not do what you
expected if the subquery yields any NULLs.

			regards, tom lane