> 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