Skip site navigation (1) Skip section navigation (2)

Re: NOT IN Optmization

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: (view raw, whole thread or download thread mbox)
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.parent_id, r.path, r.uri, r.size, r.mod_time
>          FROM   resource r, media_type t
>          WHERE  r.media_type__id =
>                 AND 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
 WHERE job__id = 1027 and = resource__id)
will work better for the older systems (it sometimes does, it sometimes

In response to


sfpug by date

Next:From: Josh BerkusDate: 2003-08-09 18:20:06
Subject: Re: NOT IN Optmization
Previous:From: David WheelerDate: 2003-08-09 05:18:27
Subject: NOT IN Optmization

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group