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: 20030808224301.Q92407-100000@megazone.bigpanda.com (view raw or flat)
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)



In response to

Responses

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-2014 The PostgreSQL Global Development Group