From: Laurent Martelli <laurent@aopsys.com>
To: pgsql-sql@postgresql.org
Subject: Re: optimizing select ... not in (select ...)
Date: 13 Aug 2001 18:35:43 +0200
Message-ID: <87zo93apeo.fsf@lolo.aopsys> (raw)
References: <80pua0ypph.fsf@junk.nocrew.org>
>>>>> "Tomas" == Tomas Berndtsson <tomas@nocrew.org> writes:
Tomas> 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 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@aopsys.com http://www.bearteam.org/~laurent/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: laurent@aopsys.com
Subject: Re: optimizing select ... not in (select ...)
In-Reply-To: <87zo93apeo.fsf@lolo.aopsys>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox