Re: need much better query perfomance

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(at)daveramsey(dot)com

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Tom Lane
> Sent: Wednesday, January 29, 2003 1:14 AM
> To: Andy
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] need much better query perfomance
>
>
> andy(at)mixonic(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly