We had a lot of problems with the NOT IN. It is indeed horrible as far as
performance goes. I rewrote all our NOT IN queries to LEFT OUTER JOINS. this
boosted performance and does not change the basic structure of the query.
SELECT . . .
FROM a LEFT OUTER JOIN b
WHERE b.pk IS NOT NULL
Sincerely,
Leon Oosterwijk
Dave Ramsey Inc.
leono@daveramsey.com
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
> Sent: Wednesday, January 29, 2003 1:14 AM
> To: Andy
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] need much better query perfomance
>
>
> andy@mixonic.com (Andy) writes:
> > select track_id from track where track_id not in (
> > select at.track_id from album_track at, album alb
> > where at.album_id = alb.album_id and alb.deleted is null
> > )
>
> > The above query should work, but it takes too long to execute.
>
> The performance of NOT IN pretty much sucks :-(. (7.4 will be
> better, but that doesn't help you today.) I'd suggest rewriting
> to avoid that. Perhaps
>
> CREATE TEMP TABLE keepers AS
> select at.track_id from album_track at, album alb
> where at.album_id = alb.album_id and alb.deleted is null;
>
> CREATE TEMP TABLE zappers AS
> SELECT track_id FROM track EXCEPT SELECT track_id FROM keepers;
>
> DELETE FROM track WHERE track_id = zappers.track_id;
>
> Untested, use at your own risk, etc.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly