[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: Re: [SQL] A more efficient way?
From: Tom Lane <tgl () sss ! pgh ! pa ! us>
Date: 2010-10-31 14:10:47
Message-ID: 19769.1288534247 () sss ! pgh ! pa ! us
[Download RAW message or body]
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic