From: | David Wheeler <david(at)kineticode(dot)com> |
---|---|
To: | sfpug(at)postgresql(dot)org |
Subject: | NOT IN Optimization |
Date: | 2003-08-09 05:14:40 |
Message-ID: | 60B78CA4-CA28-11D7-B37F-0003931A964A@kineticode.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | sfpug |
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?
TIA,
David
--
David Wheeler AIM: dwTheory
david(at)kineticode(dot)com ICQ: 15726394
http://www.kineticode.com/ Yahoo!: dew7e
Jabber: Theory(at)jabber(dot)org
Kineticode. Setting knowledge in motion.[sm]
From | Date | Subject | |
---|---|---|---|
Next Message | David Wheeler | 2003-08-09 05:18:27 | NOT IN Optmization |
Previous Message | David Wheeler | 2003-08-08 00:06:32 | Re: Error Messages |