From: PFC <lists@peufeu.com>
To: Andrus <kobruleht2@hot.ee>
To: pgsql-performance@postgresql.org
Subject: Re: analyzing intermediate query
Date: Tue, 02 Dec 2008 14:48:36 +0100
Message-ID: <op.uljfbad8cigqcu@soyouz> (raw)
In-Reply-To: <gh38cg$1544$1@news.hub.org>
References: <gh38cg$1544$1@news.hub.org>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com, kobruleht2@hot.ee
Subject: Re: analyzing intermediate query
In-Reply-To: <op.uljfbad8cigqcu@soyouz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox