To: James Cloos <cloos@jhcloos.com>
cc: pgsql-sql@postgresql.org
Subject: Re: A more efficient way? 
In-reply-to: <m3sjzm4zkv.fsf@jhcloos.com> 
References: <m3sjzm4zkv.fsf@jhcloos.com>
Comments: In-reply-to James Cloos <cloos@jhcloos.com>
	message dated "Sun, 31 Oct 2010 02:59:20 -0400"
Date: Sun, 31 Oct 2010 10:10:47 -0400
Message-ID: <19769.1288534247@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

James Cloos <cloos@jhcloos.com> writes:
> I've a third-party app which is slower than it ought to be.
> It does one operation in particular by doing a query to get a list of
> rows, and then iterates though them to confirm whether it actually wants
> that row.  As you can imagine that is very slow.

> This query gets the correct data set in one go, but is also slow:

>  select p from m where s = 7 and p not in (select p from m where s != 7);

See if you can recast it as a NOT EXISTS.  NOT IN is hard to optimize
because of its weird behavior with nulls.

If you're working with a less-than-current version of PG, you may
instead have to resort to a left-join-with-is-null locution, ie
	select m.p from m left join
	(select p from m where s != 7) m2
	on (m.p=m2.p)
	where m2.p is null and m.s = 7;
but it's hard to wrap your head around that sort of thing, so I'd
advise against using it if you can get decent performance with EXISTS.

			regards, tom lane