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 567DB925174 for ; Wed, 28 May 2003 10:31:45 -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 48577-06 for ; Wed, 28 May 2003 10:31:41 -0400 (EDT) Received: from relay12.austria.eu.net (relay12.Austria.eu.net [193.154.160.116]) by developer.postgresql.org (Postfix) with ESMTP id EF45A925124 for ; Wed, 28 May 2003 10:31:40 -0400 (EDT) Received: from relay2.austria.eu.net (endjinn.austria.eu.net [193.81.13.2]) by relay12.austria.eu.net (Postfix) with ESMTP id 0571DC5611; Wed, 28 May 2003 16:31:40 +0200 (CEST) Received: from ICOMWE (office.icomedias.com [62.99.232.80] (may be forged)) by relay2.austria.eu.net (8.12.9/8.12.6) with SMTP id h4SEVbUg010347; Wed, 28 May 2003 16:31:39 +0200 (MET DST) Message-ID: <002401c32525$d92368e0$1e01c00a@icomedias.com> From: "Mario Weilguni" To: "Dave Tenny" , References: <3ED4B0E5.3050703@attbi.com> Subject: Re: IN list processing performance (yet again) Date: Wed, 28 May 2003 16:31:37 +0200 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-Spam-Status: NO ; -10 X-Spam-Level: X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/233 X-Sequence-Number: 2012 > > My application relies heavily on IN lists. The lists are primarily > constant integers, so queries look like: > > SELECT val FROM table WHERE id IN (43, 49, 1001, 100002, ...) > > Performance is critical, and the size of these lists depends a lot on > how the larger 3-tier applicaiton is used, > but it wouldn't be out of the question to retrieve 3000-10000 items. > > PostgreSQL 7.3.2 seems to have a lot of trouble with large lists. you should rewrite your query if the query is created from an applition: SELECT val FROM table WHERE id between 43 and 100002 AND id IN (43, 49, 1001, 100002, ...) where 43 is the min and 100002 the max of all values. I had this case with postgresql 7.2 and the planner made much smarter choices in my case. Regards, Mario Weilguni