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

>>>>> "Tomas" == Tomas Berndtsson <tomas(at)nocrew(dot)org> writes:

Tomas> Laurent Martelli <laurent(at)aopsys(dot)com> 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 Pictures and
>> Keywords)

Tomas> Try this instead:

Tomas> select distinct on (Pictures.PictureID) * from Pictures where
Tomas> not exists (select distinct PictureID from Keywords where
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
laurent(at)aopsys(dot)com http://www.bearteam.org/~laurent/