Re: optimizing select ... not in (select ...)

Laurent Martelli <laurent@aopsys.com> writes:

> I have this query :
> 
> select distinct on (Pictures.PictureID) * from Pictures where Pictures.PictureID not in (select distinct PictureID
fromKeywords);
 
> 
> 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) 

Try this instead:

select distinct on (Pictures.PictureID) *from Pictureswhere not exists (select distinct PictureID from Keywords where
Pictures.PictureID=Keywords.PictureID);

I've found that it's usually faster, probably because it can use
indices better.


Tomas