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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
