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

NOT IN Optmization

From: David Wheeler <david(at)wheeler(dot)net>
To: sfpug(at)postgresql(dot)org
Subject: NOT IN Optmization
Date: 2003-08-09 05:18:27
Message-ID: (view raw, whole thread or download thread mbox)
Lists: sfpug
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?



David Wheeler                                     AIM: dwTheory
david(at)kineticode(dot)com                              ICQ: 15726394                     Yahoo!: dew7e
                                                Jabber: Theory(at)jabber(dot)org
Kineticode. Setting knowledge in motion.[sm]


sfpug by date

Next:From: Stephan SzaboDate: 2003-08-09 05:44:25
Subject: Re: NOT IN Optmization
Previous:From: David WheelerDate: 2003-08-09 05:14:40
Subject: NOT IN Optimization

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