From: Dave Dutcher <dave@tridecap.com>
To: 'Tim Truman' <tim@advam.com>
To: pgsql-performance@postgresql.org
Subject: Re: Forcing the use of particular execution plans
Date: Wed, 27 Sep 2006 10:51:26 -0500
Message-ID: <007b01c6e24c$ccfc8190$8300a8c0@tridecap.com> (raw)
In-Reply-To: <000501c6e1ff$c80022c0$0340050a@sdg>
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tim Truman
>
> Hi,
>
> I have the following query which has been running very slowly
> and after a
> lot of testing/trial and error I found an execution plan that
> ran the query
> in a fraction of the time (and then lost the statistics that
> produced it).
> What I wish to know is how to force the query to use the
> faster execution
> plan.
It would be a bit easier to diagnose the problem if you posted EXPLAIN
ANALYZE rather than just EXPLAIN. The two plans you posted looked very
similar except for the order of the nested loop in subquery 1 and an index
scan rather than a seq scan in subquery 2.
My guess would be that the order of the nested loop is determined mostly by
estimates of matching rows. If you ran an EXPLAIN ANALYZE you could tell if
the planner is estimating correctly. If it is not, you could try increasing
your statistics target and running ANALYZE.
To make the planner prefer an index scan over a seq scan, I would first
check the statistics again, and then you can try setting enable_seqscan to
false (enable_seqscan is meant more for testing than production) or, you
could try reducing random_page_cost, but you should test that against a
range of queries before putting it in production.
Dave
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: dave@tridecap.com, tim@advam.com
Subject: Re: Forcing the use of particular execution plans
In-Reply-To: <007b01c6e24c$ccfc8190$8300a8c0@tridecap.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