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

>>>>> "Tomas" == Tomas Berndtsson <[email protected]> writes:
 Tomas> Laurent Martelli <[email protected]> writes: >> I have this query : >>  >> 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
Picturesand >> Keywords)
 
 Tomas> Try this instead:
 Tomas> select distinct on (Pictures.PictureID) * from Pictures where Tomas> not exists (select distinct PictureID from
Keywordswhere Tomas> Pictures.PictureID=Keywords.PictureID);
 
 Tomas> I've found that it's usually faster, probably because it can Tomas> use indices better.

It is indeed much faster. Thanks to all of you who answered so fast. 

-- 
Laurent Martelli
[email protected]              http://www.bearteam.org/~laurent/