From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | David Wheeler <david(at)wheeler(dot)net> |
Cc: | <sfpug(at)postgresql(dot)org> |
Subject: | Re: NOT IN Optmization |
Date: | 2003-08-09 05:44:25 |
Message-ID: | 20030808224301.Q92407-100000@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | sfpug |
On Fri, 8 Aug 2003, David Wheeler wrote:
> Fellow PostgreSQLers.
>
> Today I found that I need a NOT IN subquery for the first time. It
> looks like this:
>
> SELECT r.id, r.parent_id, r.path, r.uri, r.size, r.mod_time
> FROM resource r, media_type t
> WHERE r.media_type__id = t.id
> AND r.id NOT IN
> (SELECT resource__id
> FROM job__resource
> WHERE job__id = 1027)
> ORDER BY path
>
> Now, I know that this has been optimized in 7.4, which is great, but I
> still need to support users of 7.1 and later. So I'd like to optimize
> this. I've gotten used to changinb IN queries to inner joins, but that
> doesn't work for NOT IN, of course. Am I missing something, or is this
> as optimal as it gets?
Maybe something of the form
AND NOT EXISTS (SELECT 1 FROM job__resource
WHERE job__id = 1027 and r.id = resource__id)
will work better for the older systems (it sometimes does, it sometimes
doesn't)
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2003-08-09 18:20:06 | Re: NOT IN Optmization |
Previous Message | David Wheeler | 2003-08-09 05:18:27 | NOT IN Optmization |