Date: Tue, 09 May 2006 12:10:37 +0200
To: "Christian Kratzer" <ck@cksoft.de>
Subject: Re: [HACKERS] Big IN() clauses etc : feature proposal
Cc: pgsql-performance@postgresql.org, pgsql-hackers@postgresql.org
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>
	<op.s8916gm6cigqcu@apollo13>
	<20060509113337.M90693@vesihiisi.cksoft.de>
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-ID: <op.s893vzcpcigqcu@apollo13>
In-Reply-To: <20060509113337.M90693@vesihiisi.cksoft.de>
User-Agent: Opera M2/8.51 (Linux, build 1462)


> Additionally to your query you are already transferring the whole result  
> set multiple times.  First you copy it to the result table. Then you
> read it again.   Your subsequent queries will also have to read over
> all the unneeded tuples just to get your primary key.

	Considering that the result set is not very large and will be cached in  
RAM, this shouldn't be a problem.

> then why useth thy not the DISTINCT clause when building thy result  
> table and thou shalt have no duplicates.

	Because the result table contains no duplicates ;)
	I need to remove duplicates in this type of queries :

-- get object owners info
SELECT * FROM users WHERE id IN (SELECT user_id FROM results);

	And in this case I find IN() easier to read than DISTINCT (what I posted  
was a simplification of my real use case...)

> which is a perfect reason to use a temp table.  Another variation on the  
> temp table scheme is use a result table and add a query_id.

	True. Doesn't solve my problem though : it's still complex, doesn't have  
good rowcount estimation, bloats a table (I only need these records for  
the duration of the transaction), etc.
	
> We do something like this in our web application when users submit  
> complex queries.  For each query we store tuples of (query_id,result_id)
> in a result table.  It's then easy for the web application to page the
> result set.

	Yes, that is about the only sane way to page big result sets.

> A cleaner solution usually pays off in the long run whereas a hackish
> or overly complex solution will bite you in the behind for sure as
> time goes by.

	Yes, but in this case temp tables add too much overhead. I wish there  
were RAM based temp tables like in mysql. However I guess the current temp  
table slowness comes from the need to mark their existence in the system  
catalogs or something. That's why I proposed using cursors...