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: <20080731031822.GA813@tarnica> 
References: <20080731024508.53F6.0.NOFFLE@turbacz.local>
	<27820.1217473686@sss.pgh.pa.us> <20080731031822.GA813@tarnica>
Comments: In-reply-to Miernik <public@public.miernik.name>
	message dated "Thu, 31 Jul 2008 05:18:22 +0200"
Date: Wed, 30 Jul 2008 23:55:07 -0400
Message-ID: <28448.1217476507@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Miernik <public@public.miernik.name> writes:
> 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

Try increasing that ... I don't recall the exact per-row overhead
but I'm quite sure it's more than 8 bytes.  Ten times that would
likely get you to a hash subquery plan.

> The machine has 48 MB total RAM and is a Xen host.

48MB is really not a sane amount of memory to run a modern database
in.  Maybe you could make it go with sqlite or some other tiny-footprint
DBMS, but Postgres isn't focused on that case.

>> 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?

Fraid not, EXCEPT ALL has yet other rules for how it deals with
duplicates.

>> 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,

Okay, just wanted to point out a common gotcha.

			regards, tom lane