From: Tom Lane <tgl@sss.pgh.pa.us>
To: James Cloos <cloos@jhcloos.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: A more efficient way?
Date: Sun, 31 Oct 2010 10:10:47 -0400
Message-ID: <19769.1288534247@sss.pgh.pa.us> (raw)
In-Reply-To: <m3sjzm4zkv.fsf@jhcloos.com>
References: <m3sjzm4zkv.fsf@jhcloos.com>
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
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-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, cloos@jhcloos.com
Subject: Re: A more efficient way?
In-Reply-To: <19769.1288534247@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