Re: 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