Date: Wed, 20 Jul 2005 13:28:32 -0700
From: John Mendenhall <john@surfutopia.net>
To: pgsql-performance list <pgsql-performance@postgresql.org>
Subject: Re: performance decrease after reboot
Message-ID: <20050720202832.GA22109@calvin.surfutopia.net>
References: <20050719210557.GA30794@calvin.surfutopia.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20050719210557.GA30794@calvin.surfutopia.net>
User-Agent: Mutt/1.5.6i

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