From: Tom Lane <tgl@sss.pgh.pa.us>
To: David W Noon <dwnoon@ntlworld.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Interesting Query Performance Question
Date: Sat, 06 Nov 2010 13:12:18 -0400
Message-ID: <21222.1289063538@sss.pgh.pa.us> (raw)
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>
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
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-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, dwnoon@ntlworld.com
Subject: Re: Interesting Query Performance Question
In-Reply-To: <21222.1289063538@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