Re: performance decrease after reboot

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