pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: Laurent Martelli <laurent@aopsys.com>
To: pgsql-sql@postgresql.org
Subject: Re: optimizing select ... not in (select ...)
Date: Mon, 13 Aug 2001 08:16:43 -0700
Message-ID: <web-101179@davinci.ethosmedia.com> (raw)
In-Reply-To: <87bslkayl8.fsf@lolo.aopsys>

Laurent,

> select distinct on (Pictures.PictureID) * from Pictures where
> Pictures.PictureID not in (select distinct PictureID from Keywords);
> 
> and I find it a bit slow. Does anybody have suggestions to run this
> faster ? (I have indexes on PictureID on both Pictures and Keywords) 

NOT IN is always slow on all RDBMS that I know, unless the subselect has
a very small (<100) return set.  This is because the engine has to
compare each row in the master query against every value returned by NOT
IN, one row at a time.

Try the WHERE NOT EXISTS construction instead:
SELECT * FROM Pictures
WHERE NOT EXISTS ( SELECT pictureID FROM keywords
		   WHERE keywords.pictureID = Pictures.pictureID );

This uses the DB engine's JOIN functionality and thus runs considerably
faster.  

BTW, all those "DISTINCT" in the query example you gave, assuming that
PictureID is the unique index of Pictures, are completely superfluous
and will only slow the query down.  Particularly the use of DISTINCT in
a subquery should only be used if the contents of the subquery will be
displayed as part of the result set.

-Josh Berkus





______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco







view thread (5+ messages)  latest in thread

Message-ID: <web-101179@davinci.ethosmedia.com>
Permalink:  ../web-101179@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-101179@davinci.ethosmedia.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: josh@agliodbs.com, laurent@aopsys.com
  Subject: Re: optimizing select ... not in (select ...)
  In-Reply-To: <web-101179@davinci.ethosmedia.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox