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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

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