To: David W Noon <dwnoon@ntlworld.com>
cc: pgsql-admin@postgresql.org
Subject: Re: Interesting Query Performance Question 
In-reply-to: <20101106165354.67ed9532@karnak.local> 
References: 
 <68C107706843A54BA7D9CAEF123ADF05010346CAE6@SP2-EX07VS03.ds.corp.yahoo.com>
	<20024.1289059188@sss.pgh.pa.us>
	<20101106165354.67ed9532@karnak.local>
Comments: In-reply-to David W Noon <dwnoon@ntlworld.com>
	message dated "Sat, 06 Nov 2010 16:53:54 -0000"
Date: Sat, 06 Nov 2010 13:12:18 -0400
Message-ID: <21222.1289063538@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

David W Noon <dwnoon@ntlworld.com> writes:
> On Sat, 06 Nov 2010 11:59:48 -0400, Tom Lane wrote about Re: [ADMIN]
>> In general, NOT IN is hard to optimize because of its weird behavior
>> for NULLs.  I'd suggest looking into converting the query to use an
>> EXISTS instead.

> I have know about that issue for some years, so I always use a column
> that has been declared NOT NULL for the sub-query.  Does PostgreSQL not
> use that property to bypass the vagaries of NULL in an IN predicate?

No.  It's a reasonable TODO item though.  Up till fairly recently, we
didn't have infrastructure that would allow building plans that depended
for correctness on the existence of a constraint.  (In particular,
I'd want such a plan to get thrown away and redone if someone dropped
the NOT NULL constraint.)  We have most of that now, although it only
works for constraints that have entries in pg_constraint, which NOT NULL
doesn't yet.  Maybe in another few months ...

			regards, tom lane