Skip site navigation (1) Skip section navigation (2)

Inconsistant query plan

From: "Daniel Gish" <dan(at)centrifugesolutions(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Inconsistant query plan
Date: 2006-01-24 23:15:57
Message-ID: GJEAKHIALCNFPPGBGEKNEEBDDGAA.dan@centrifugesolutions.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.

Best Regards,
Dan



Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2006-01-24 23:30:18
Subject: Re: Inconsistant query plan
Previous:From: Andrew SullivanDate: 2006-01-24 21:00:37
Subject: Re: Investigating IO Saturation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group