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

NOT IN Optimization

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 (view raw or flat)
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]


sfpug by date

Next:From: David WheelerDate: 2003-08-09 05:18:27
Subject: NOT IN Optmization
Previous:From: David WheelerDate: 2003-08-08 00:06:32
Subject: Re: Error Messages

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