From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Daniel Gish <dan@centrifugesolutions.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Inconsistant query plan
Date: Tue, 24 Jan 2006 17:30:18 -0600
Message-ID: <1138145417.25819.39.camel@state.g2switchworks.com> (raw)
In-Reply-To: <GJEAKHIALCNFPPGBGEKNEEBDDGAA.dan@centrifugesolutions.com>
References: <GJEAKHIALCNFPPGBGEKNEEBDDGAA.dan@centrifugesolutions.com>
On Tue, 2006-01-24 at 17:15, Daniel Gish wrote:
> Hi,
> We are running Postgresql 8.1, and getting dramatically inconsistant results
> after running VACUUM ANALYZE. Sometimes after analyzing the database, the
> query planner chooses a very efficient plan (15 rows, 4.744 ms), and
> sometimes a terrible one (24 rows, 3536.995 ms). Here's the abbreviated
> query:
>
> SELECT * FROM t1 INNER JOIN (t2 INNER JOIN (t3 INNER JOIN t4 ON t3.gid =
> t4.gid) ON t3.gid = t2.gid) ON t2.eid = t1.eid WHERE ...
>
> In the efficient plan, t2 is joined to t3 & t4 before being joined to t1.
> The inefficient plan joins t1 to t2 before joining to the other tables.
>
> We've experimented with different settings, such as shared_buffers &
> max_fsm_pages, to no avail. Anybody have a suggestion for getting the
> efficient plan to execute consistantly? If you'd like to see the actual
> query & query plans let me know.
Have you adjusted the stats target for that column? See \h alter table
in psql for the syntax for that. Then run analyze again.
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: smarlowe@g2switchworks.com, dan@centrifugesolutions.com
Subject: Re: Inconsistant query plan
In-Reply-To: <1138145417.25819.39.camel@state.g2switchworks.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