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 B89379259AA for ; Wed, 28 May 2003 13:58:17 -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 95164-02 for ; Wed, 28 May 2003 13:58:16 -0400 (EDT) Received: from sccrmhc02.attbi.com (sccrmhc02.attbi.com [204.127.202.62]) by developer.postgresql.org (Postfix) with ESMTP id E75989259A6 for ; Wed, 28 May 2003 13:58:15 -0400 (EDT) Received: from attbi.com (dtenny.ne.client2.attbi.com[24.60.113.221]) by attbi.com (sccrmhc02) with SMTP id <2003052817581500200r93g8e>; Wed, 28 May 2003 17:58:15 +0000 Message-ID: <3ED4F8B6.7030905@attbi.com> Date: Wed, 28 May 2003 13:58:14 -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: Shridhar Daithankar Cc: pgsql-performance@postgresql.org Subject: Re: IN list processing performance (yet again) References: <3ED4B0E5.3050703@attbi.com> <200305281846.38928.shridhar_daithankar@nospam.persistent.co.in> In-Reply-To: <200305281846.38928.shridhar_daithankar@nospam.persistent.co.in> Content-Type: multipart/alternative; boundary="------------040509090107020304090108" X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/238 X-Sequence-Number: 2017 This is a multi-part message in MIME format. --------------040509090107020304090108 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit A join isn't an option, these elements come a a selection of entity ID's that are specific to some client context. Some other people suggested joins too. Consider it something like this, say there's a database that represents the entire file system content of a set of machines, hundreds of thousands of files. A single user wants to do something related to the ID's of 3000 files. The requests for those 3000 files can be built up in a number of ways, not all of which rely on data in the database. So I need to retrieve data on those 3000 files using IN lists or some alternative. Dave Shridhar Daithankar wrote: >On Wednesday 28 May 2003 18:21, Dave Tenny wrote: > > >>Having grepped the web, it's clear that this isn't the first or last >>time this issue will be raised. >> >>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, ...) >> >> > >How do you derive this list of number? If it is from same database, can you >rewrite the query using a join statement? > >HTH > > Shridhar > > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster > > > --------------040509090107020304090108 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit A join isn't an option, these elements come a a selection of entity ID's that are specific to some client context.
Some other people suggested joins too. 

Consider it something like this, say there's a database that represents the entire file system content
of a set of machines, hundreds of thousands of files.  A single user wants to do something
related to the ID's of 3000 files.  The requests for those 3000 files can be built up in a number of ways,
not all of which rely on data in the database.  So I need to retrieve data on those 3000 files using IN lists or some alternative.

Dave

Shridhar Daithankar wrote:
On Wednesday 28 May 2003 18:21, Dave Tenny wrote:
  
Having grepped the web, it's clear that this isn't the first or last
time this issue will be raised.

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, ...)
    

How do you derive this list of number? If it is from same database, can you 
rewrite the query using a join statement?

HTH

 Shridhar


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

  
--------------040509090107020304090108--