[SQL] optimizing select ... not in (select ...)
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) -- Laurent Martelli [EMAIL PROTECTED] http://www.bearteam.org/~laurent/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Re: Donations?
>>>>> "Josh" == "Josh Berkus" <[EMAIL PROTECTED]> writes: Josh> Core team, Josh> I may be able to build a small donation towards postgresql Josh> development into my next project fee. However, I am unsure of Josh> where I can send a check so that it will go directly towards Josh> PostgreSQL development, and not for any company's overhead. Josh> Suggestions? Maybe you could do that through www.cosource.com ? (the site is down today because they're moving the servers). Here's how it works : You post a request on the site, telling how much you are willing to spend to see it developped. Other users can join you so that's more money for the developpers. Developpers can submit proposals. You choose one, commit money on it, the developper does the work, you pay him and everybody's happy : the developpers got money and the users got features. -- Laurent Martelli [EMAIL PROTECTED] http://www.bearteam.org/~laurent/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] 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 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 [EMAIL PROTECTED] http://www.bearteam.org/~laurent/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
