To: "Christian Kratzer" <ck@cksoft.de>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org,
	pgsql-hackers@postgresql.org
Subject: Re: Big IN() clauses etc : feature proposal
References: <445F7FF8.6070707@comcast.net>
	<1147110142.32369.7.camel@archimedes>
	<20060508175013.GY99570@pervasive.com>
	<28288.1147111196@sss.pgh.pa.us> <op.s89zl30wcigqcu@apollo13>
	<20060509105214.P90693@vesihiisi.cksoft.de>
Message-ID: <op.s8916gm6cigqcu@apollo13>
Date: Tue, 09 May 2006 11:33:42 +0200
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
In-Reply-To: <20060509105214.P90693@vesihiisi.cksoft.de>
User-Agent: Opera M2/8.51 (Linux, build 1462)


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

	It is a bit of a catch : I need this information, because the purpose of  
the query is to retrieve these objects. I can first store the ids, then  
retrieve the objects, but it's one more query.

> Also you might try:
>  	SELECT * FROM somewhere JOIN result USING (id)
> Instead of:
>  	SELECT * FROM somewhere WHERE id IN (SELECT id FROM result)

	Yes you're right in this case ; however the query to retrieve the owners  
needs to eliminate duplicates, which IN() does.

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

	I don't think so :
	- 10 ms is a mean time, sometimes it can take much more time, sometimes  
it's faster.
	- Repeating the query might yield different results if records were added  
or deleted in the meantime.
	- Complex search queries have imprecise rowcount estimates ; hence the  
joins that I would add to them will get suboptimal plans.

	Using a temp table is really the cleanest solution now ; but it's too  
slow so I reverted to generating big IN() clauses in the application.