Received: from junk.nocrew.org (junk.nocrew.org [212.73.17.42]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f7DEtoP87561 for ; Mon, 13 Aug 2001 10:55:50 -0400 (EDT) (envelope-from tomas@junk.nocrew.org) Received: from tomas by junk.nocrew.org with local (Exim 3.12 #1 (Debian)) id 15WJ7i-0003vx-00; Mon, 13 Aug 2001 16:55:22 +0200 To: Laurent Martelli Cc: pgsql-sql@postgresql.org Subject: Re: optimizing select ... not in (select ...) References: <87bslkayl8.fsf@lolo.aopsys> From: Tomas Berndtsson Date: 13 Aug 2001 16:55:22 +0200 In-Reply-To: <87bslkayl8.fsf@lolo.aopsys> Message-ID: <80pua0ypph.fsf@junk.nocrew.org> Lines: 20 User-Agent: Gnus/5.0808 (Gnus v5.8.8) Emacs/20.7 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200108/168 X-Sequence-Number: 4390 Laurent Martelli 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) Try this instead: select distinct on (Pictures.PictureID) * from Pictures where 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