From: Tom Lane <tgl@sss.pgh.pa.us>
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?
Date: Wed, 30 Jul 2008 23:08:06 -0400
Message-ID: <27820.1217473686@sss.pgh.pa.us> (raw)
In-Reply-To: <20080731024508.53F6.0.NOFFLE@turbacz.local>
References: <20080731024508.53F6.0.NOFFLE@turbacz.local>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, public@public.miernik.name
Subject: Re: why "WHERE uid NOT IN" is so slow, and EXCEPT in the same situtation is fast?
In-Reply-To: <27820.1217473686@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox