Re: NOT IN Optmization

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)