On Tue, 19 Jul 2005, John Mendenhall wrote:
> I tuned a query last week to obtain acceptable performance.
> Here is my recorded explain analyze results:
>
> LOG: duration: 826.505 ms statement: explain analyze
> [cut for brevity]
>
> I rebooted the database machine later that night.
> Now, when I run the same query, I get the following
> results:
>
> LOG: duration: 6931.701 ms statement: explain analyze
> [cut for brevity]
I just ran my query again, no changes from yesterday
and it is back to normal:
LOG: duration: 795.839 ms statement: explain analyze
What could have been the problem?
The major differences in the query plan are as follows:
(1) The one that runs faster uses a Hash Join at the
very top of the query plan. It does a Hash Cond on
the country and code fields.
(2) The one that runs slower uses a Materialize with
the subplan, with no Hash items. The Materialize does
Seq Scan of the countries table, and above it, a Join
Filter is run.
(3) The partners_pkey index on the partners table is
in a different place in the query.
Does anyone know what would cause the query plan to be
different like this, for the same server, same query?
I run vacuum analyze every night. Is this perhaps the
problem?
What setting do I need to tweak to make sure the faster
plan is always found?
Thanks for any pointers in this dilemma.
JohnM
--
John Mendenhall
john@surfutopia.net
surf utopia
internet services
