Date: Tue, 02 Dec 2008 14:48:36 +0100
To: Andrus <kobruleht2@hot.ee>, pgsql-performance@postgresql.org
Subject: Re: analyzing intermediate query
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <gh38cg$1544$1@news.hub.org>
Content-Transfer-Encoding: 7bit
Message-ID: <op.uljfbad8cigqcu@soyouz>
In-Reply-To: <gh38cg$1544$1@news.hub.org>
User-Agent: Opera Mail/9.62 (Linux)

> I noticed that query
>
> SELECT dok.*
>  FROM dok
> JOIN  (SELECT DISTINCT dokumnr FROM  temptbl ) x USING(dokumnr);
>
> is slow in 8.1.4
> I cannot use explain analyze since this query uses results from  
> temporary table temptbl which is not available.

	Generally if you know your temptbl will always contains a few rows (say,  
generally a few and never more than a few thousands) it is better to use  
something like that :

	- get list of items
	- SELECT * FROM table WHERE id IN (...)

	Of course you must be pretty damn sure that the list isn't gonna contain  
10 million items. Or else you'll have a little problem. But it generally  
works pretty well. The overhead of generating and parsing the IN() is  
lower than the overhead of temptables...


	By the way, sometime ago there was talk about adding estimation of number  
of rows returned to set-returning functions. What's the status of this ?  
It doesn't seem to have survived...

8.3> EXPLAIN SELECT * FROM generate_series( 1,10 );
                                QUERY PLAN
------------------------------------------------------------------------
  Function Scan on generate_series  (cost=0.00..12.50 rows=1000 width=4)


> Sometimes innter table returns only 1 row so maybe seq scan is selected  
> instead of single row index access becauses expected count is 1000
>
> As I understand, PostgreSql requires manually running ANALYZE for  
> temporary tables if their row count is different from 1000
>
> How to force PostgreSql to analyze inner table in this query or use  
> other way to get index using query plan if inner query returns single  
> row ?
>
> How