Date: Tue, 9 May 2006 11:01:00 +0200 (CEST)
From: Christian Kratzer <ck-lists@cksoft.de>
Reply-To: Christian Kratzer <ck@cksoft.de>
To: PFC <lists@peufeu.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org,
	pgsql-hackers@postgresql.org
Subject: Re: Big IN() clauses etc : feature proposal
In-Reply-To: <op.s89zl30wcigqcu@apollo13>
Message-ID: <20060509105214.P90693@vesihiisi.cksoft.de>
References: <445F7FF8.6070707@comcast.net>
	<1147110142.32369.7.camel@archimedes>
	<20060508175013.GY99570@pervasive.com> <28288.1147111196@sss.pgh.pa.us>
	<op.s89zl30wcigqcu@apollo13>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

Hi,

On Tue, 9 May 2006, PFC wrote:
<snipp/>
> 	Back to the point : I can't use the temp table method, because temp 
> tables are too slow.
> 	Creating a temp table, filling it, analyzing it and then dropping it 
> takes about 100 ms. The search query, on average, takes 10 ms.

just some thoughts:

You might consider just selecting your primary key or a set of
primary keys to involved relations in your search query.  If you
currently use "select *" this can make your result set very large.

Copying all the result set to the temp. costs you additional IO
that you propably dont need.

Also you might try:

 	SELECT * FROM somewhere JOIN result USING (id)

Instead of:

 	SELECT * FROM somewhere WHERE id IN (SELECT id FROM result)

Joins should be a lot faster than large IN clauses.

Here it will also help if result only contains the primary keys 
and not all the other data. The join will be much faster.

On the other hand if your search query runs in 10ms it seems to be fast 
enough for you to run it multiple times.  Theres propably no point in 
optimizing anything in such case.

Greetings
Christian

-- 
Christian Kratzer                       ck@cksoft.de
CK Software GmbH                        http://www.cksoft.de/
Phone: +49 7452 889 135                 Fax: +49 7452 889 136