Date: Thu, 31 Jul 2008 05:18:22 +0200
From: Miernik <public@public.miernik.name>
To: pgsql-performance@postgresql.org
Subject: Re: why "WHERE uid NOT IN" is so slow, and EXCEPT in the
	same situtation is fast?
Message-ID: <20080731031822.GA813@tarnica>
References: <20080731024508.53F6.0.NOFFLE@turbacz.local>
	<27820.1217473686@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <27820.1217473686@sss.pgh.pa.us>
User-Agent: Mutt/1.5.18 (2008-05-17)

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/