X-Original-To: pgsql-performance@postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id 09AE4925263 for ; Wed, 28 May 2003 21:53:52 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 93900-09 for ; Wed, 28 May 2003 21:53:51 -0400 (EDT) Received: from houston.familyhealth.com.au (unknown [203.59.48.253]) by developer.postgresql.org (Postfix) with ESMTP id D944092511F for ; Wed, 28 May 2003 21:53:49 -0400 (EDT) Received: (from root@localhost) by houston.familyhealth.com.au (8.11.6p2/8.11.6) id h4T1rnW57234 for pgsql-performance@postgresql.org; Thu, 29 May 2003 09:53:49 +0800 (WST) (envelope-from chriskl@familyhealth.com.au) Received: from mariner (mariner.internal [192.168.0.101]) by houston.familyhealth.com.au (8.11.6p2/8.9.3) with SMTP id h4T1rfV57052; Thu, 29 May 2003 09:53:41 +0800 (WST) Message-ID: <0b9a01c32585$2a8e6900$6500a8c0@fhp.internal> From: "Christopher Kings-Lynne" To: "Dave Tenny" , "Bruno Wolff III" Cc: "Andreas Pflug" , References: <3ED4B0E5.3050703@attbi.com> <3ED4B75A.2050106@web.de> <3ED4FB02.1010908@attbi.com> <20030528183914.GA6118@wolff.to> <3ED5159E.2050407@attbi.com> Subject: Re: IN list processing performance (yet again) Date: Thu, 29 May 2003 09:53:55 +0800 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-scanner: scanned by Inflex 0.1.5c - (http://www.inflex.co.za/) X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/258 X-Sequence-Number: 2037 > Also, IN (subquery) is a known performance problem in PGSQL, at least if the subquery is going to return > many rows. > It's too bad, since I'm rather fond of subqueries, but I avoid them like the plague in PostgreSQL. You're not really using a subquery - really just a long list of integers. Subqueries are lightning fast, so long as you conver to the EXISTS form: SELECT * FROM tab WHERE id IN (SELECT id2 FROM tab2); converts to: SELECT * FROM tab WHERE EXISTS (SELECT id2 FROM tab2 WHERE id2=id); Chris