pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
To: Dave Tenny <tenny@attbi.com>
To: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
Cc: pgsql-performance@postgresql.org
Subject: Re: IN list processing performance (yet again)
Date: Thu, 29 May 2003 09:47:53 +0800
Message-ID: <0b2901c32584$528dee90$6500a8c0@fhp.internal> (raw)
References: <3ED4B0E5.3050703@attbi.com>
	<200305281846.38928.shridhar_daithankar@nospam.persistent.co.in>
	<3ED4F8B6.7030905@attbi.com>

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 ----- 
  From: Dave Tenny 
  To: Shridhar Daithankar 
  Cc: pgsql-performance@postgresql.org 
  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.  

  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

  

view thread (23+ messages)  latest in thread

Message-ID: <0b2901c32584$528dee90$6500a8c0@fhp.internal>
Permalink:  ../0b2901c32584$528dee90$6500a8c0@fhp.internal/
Also on:    postgresql.org/message-id/0b2901c32584$528dee90$6500a8c0@fhp.internal

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: chriskl@familyhealth.com.au, tenny@attbi.com, shridhar_daithankar@persistent.co.in
  Subject: Re: IN list processing performance (yet again)
  In-Reply-To: <0b2901c32584$528dee90$6500a8c0@fhp.internal>
  References: <3ED4B0E5.3050703@attbi.com> <200305281846.38928.shridhar_daithankar@nospam.persistent.co.in> <3ED4F8B6.7030905@attbi.com> <0b2901c32584$528dee90$6500a8c0@fhp.internal>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox