Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f7DFGmP94386 for ; Mon, 13 Aug 2001 11:16:48 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 101179; Mon, 13 Aug 2001 08:16:43 -0700 From: "Josh Berkus" Subject: Re: optimizing select ... not in (select ...) To: Laurent Martelli , pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Mon, 13 Aug 2001 08:16:43 -0700 Message-ID: In-Reply-To: <87bslkayl8.fsf@lolo.aopsys> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="_===101179====davinci.ethosmedia.com===_" X-Archive-Number: 200108/167 X-Sequence-Number: 4389 This is a multi-part MIME message --_===101179====davinci.ethosmedia.com===_ Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit Laurent, > 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) NOT IN is always slow on all RDBMS that I know, unless the subselect has a very small (<100) return set. This is because the engine has to compare each row in the master query against every value returned by NOT IN, one row at a time. Try the WHERE NOT EXISTS construction instead: SELECT * FROM Pictures WHERE NOT EXISTS ( SELECT pictureID FROM keywords WHERE keywords.pictureID = Pictures.pictureID ); This uses the DB engine's JOIN functionality and thus runs considerably faster. BTW, all those "DISTINCT" in the query example you gave, assuming that PictureID is the unique index of Pictures, are completely superfluous and will only slow the query down. Particularly the use of DISTINCT in a subquery should only be used if the contents of the subquery will be displayed as part of the result set. -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco --_===101179====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===101179====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===101179====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===101179====davinci.ethosmedia.com===_--