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 52F089259B5 for ; Wed, 28 May 2003 14:17:27 -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 00212-08 for ; Wed, 28 May 2003 14:17:23 -0400 (EDT) Received: from sccrmhc03.attbi.com (sccrmhc03.attbi.com [204.127.202.63]) by developer.postgresql.org (Postfix) with ESMTP id 4BBE49259D9 for ; Wed, 28 May 2003 14:17:23 -0400 (EDT) Received: from attbi.com (dtenny.ne.client2.attbi.com[24.60.113.221]) by attbi.com (sccrmhc03) with SMTP id <2003052818172200300e9v7ae>; Wed, 28 May 2003 18:17:22 +0000 Message-ID: <3ED4FD31.5090804@attbi.com> Date: Wed, 28 May 2003 14:17:21 -0400 From: Dave Tenny User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.3.1) Gecko/20030425 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Mario Weilguni Cc: pgsql-performance@postgresql.org Subject: Re: IN list processing performance (yet again) References: <3ED4B0E5.3050703@attbi.com> <002401c32525$d92368e0$1e01c00a@icomedias.com> In-Reply-To: <002401c32525$d92368e0$1e01c00a@icomedias.com> Content-Type: multipart/alternative; boundary="------------050507090400080101010005" X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/242 X-Sequence-Number: 2021 This is a multi-part message in MIME format. --------------050507090400080101010005 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Mario Weilguni wrote: >>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 > > Very interesting! I tried it out, but it didn't appreciably change the thresholds in my results for going by for IN list sizes 100 - 1000. It's also likely to be of use only if the range for the between is fairly restricted, which isn't necessarily characteristic of my data. Dave --------------050507090400080101010005 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Mario Weilguni wrote:
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
  
Very interesting!  I tried it out, but it didn't appreciably change the thresholds in my results for going by for IN list
sizes 100 - 1000.  It's also likely to be of use only if the range for the between is fairly restricted,
which isn't necessarily characteristic of my data.

Dave
--------------050507090400080101010005--