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 6FF23925001 for ; Wed, 28 May 2003 21:47:48 -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 94279-02 for ; Wed, 28 May 2003 21:47:47 -0400 (EDT) Received: from houston.familyhealth.com.au (unknown [203.59.48.253]) by developer.postgresql.org (Postfix) with ESMTP id 8C971924FE9 for ; Wed, 28 May 2003 21:47:45 -0400 (EDT) Received: (from root@localhost) by houston.familyhealth.com.au (8.11.6p2/8.11.6) id h4T1lii55474 for pgsql-performance@postgresql.org; Thu, 29 May 2003 09:47:44 +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 h4T1ldV55338; Thu, 29 May 2003 09:47:39 +0800 (WST) Message-ID: <0b2901c32584$528dee90$6500a8c0@fhp.internal> From: "Christopher Kings-Lynne" To: "Dave Tenny" , "Shridhar Daithankar" Cc: References: <3ED4B0E5.3050703@attbi.com> <200305281846.38928.shridhar_daithankar@nospam.persistent.co.in> <3ED4F8B6.7030905@attbi.com> Subject: Re: IN list processing performance (yet again) Date: Thu, 29 May 2003 09:47:53 +0800 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0B26_01C325C7.5FF9EC80" 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/257 X-Sequence-Number: 2036 This is a multi-part message in MIME format. ------=_NextPart_000_0B26_01C325C7.5FF9EC80 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable The IN list processing has been fixed in 7.4CVS. It now uses a hash based = lookup rather than a list, so it's vastly faster. Chris ----- Original Message -----=20 From: Dave Tenny=20 To: Shridhar Daithankar=20 Cc: pgsql-performance@postgresql.org=20 Sent: Thursday, May 29, 2003 1:58 AM Subject: Re: [PERFORM] IN list processing performance (yet again) 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.=20=20 Consider it something like this, say there's a database that represents t= he entire file system content of a set of machines, hundreds of thousands of files. A single user want= s 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 dat= a 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, ...) =20=20=20=20 How do you derive this list of number? If it is from same database, can you= =20 rewrite the query using a join statement? HTH Shridhar ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster =20=20 ------=_NextPart_000_0B26_01C325C7.5FF9EC80 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
The IN list processing has been fixed in= =20 7.4CVS.  It now uses a hash based lookup rather than a list, so it's v= astly=20 faster.
 
Chris
----- Original Message -----
Fro= m:=20 Dave Tenny=
To: Shridhar Daithankar<= /A>=20
Cc: pgsql-performance@postgr= esql.org=20
Sent: Thursday, May 29, 2003 1:58= =20 AM
Subject: Re: [PERFORM] IN list pro= cessing=20 performance (yet again)

A join isn't an option, these elements come a a selection = of=20 entity ID's that are specific to some client context.
Some other peopl= e=20 suggested joins too. 

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

Dave

Shridhar Daithanka= r=20 wrote:
On Wednesday 28 May 2003 18:21, Dave Tenny w=
rote:
  
Having grepped the web, it's c=
lear 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=
=20
rewrite the query using a join statement?

HTH

 Shridhar


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

  
------=_NextPart_000_0B26_01C325C7.5FF9EC80--