From: Alessandro Baretta <a.baretta@barettadeit.com>
To: Daniel Gish <dan@centrifugesolutions.com>
Cc: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Inconsistant query plan
Date: Wed, 25 Jan 2006 10:05:44 +0100
Message-ID: <43D73F68.3040306@barettadeit.com> (raw)
In-Reply-To: <GJEAKHIALCNFPPGBGEKNCEBGDGAA.dan@centrifugesolutions.com>
References: <GJEAKHIALCNFPPGBGEKNCEBGDGAA.dan@centrifugesolutions.com>
Daniel Gish wrote:
> Hi,
> Thanks for your response. The actual query is below; the joins are only 4
> deep. Adjusting the stats target did help, but not dramatically.
>
> QUERY PLAN
> ----------------------------------------------------------------------------
> ----------------------------------------------------------------------------
> ---------------------------------------
> Nested Loop (cost=978.19..37161.81 rows=133 width=4) (actual
> time=2511.676..2511.676 rows=0 loops=1)
> -> Merge Join (cost=978.19..22854.00 rows=4244 width=4) (actual
> time=1718.420..2510.128 rows=92 loops=1)
> ...
> -> Nested Loop (cost=0.00..974.33 rows=113 width=8) (actual
time=0.078..2.305 rows=19 loops=1)
I have a similar problem recently. An importat diagnostic tool for these issues
is the pg_stats view. Let me suggest that you post the relevant lines from
pg_stats, so that with some help you will be able to discover what data advises
the query planner to overestimate the cardinality of some joins and
underestimate others.
Alex
--
*********************************************************************
http://www.barettadeit.com/
Baretta DE&IT
A division of Baretta SRL
tel. +39 02 370 111 55
fax. +39 02 370 111 54
Our technology:
The Application System/Xcaml (AS/Xcaml)
<http://www.asxcaml.org/;
The FreerP Project
<http://www.freerp.org/;
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: a.baretta@barettadeit.com, dan@centrifugesolutions.com, mike@fuhr.org
Subject: Re: Inconsistant query plan
In-Reply-To: <43D73F68.3040306@barettadeit.com>
* 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