Re: IN list processing performance (yet again)

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(at)postgresql(dot)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