From: | Miernik <public(at)public(dot)miernik(dot)name> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: why "WHERE uid NOT IN" is so slow, and EXCEPT in the same situtation is fast? |
Date: | 2008-07-31 03:18:22 |
Message-ID: | 20080731031822.GA813@tarnica |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Wed, Jul 30, 2008 at 11:08:06PM -0400, Tom Lane wrote:
> 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.
work_mem = 1024kB
The machine has 48 MB total RAM and is a Xen host.
> 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 if use EXCEPT ALL?
> Another issue is that the NOT IN will probably not do what you
> expected if the subquery yields any NULLs.
In this specific query I think it is not possible for the subquery to
have NULLs, because its an INNER JOIN USING (the_only_column_in_the
_result, some_other_column_also). If any "uid" column of any row would
have been NULL, it wouldn't appear in that INNER JOIN, no?
--
Miernik
http://miernik.name/
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2008-07-31 03:55:07 | Re: why "WHERE uid NOT IN" is so slow, and EXCEPT in the same situtation is fast? |
Previous Message | Tom Lane | 2008-07-31 03:08:06 | Re: why "WHERE uid NOT IN" is so slow, and EXCEPT in the same situtation is fast? |