From: John Mendenhall <john@surfutopia.net>
To: pgsql-performance list <pgsql-performance@postgresql.org>
Subject: Re: performance decrease after reboot
Date: Wed, 20 Jul 2005 13:28:32 -0700
Message-ID: <20050720202832.GA22109@calvin.surfutopia.net> (raw)
In-Reply-To: <20050719210557.GA30794@calvin.surfutopia.net>
References: <20050719210557.GA30794@calvin.surfutopia.net>
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
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: john@surfutopia.net
Subject: Re: performance decrease after reboot
In-Reply-To: <20050720202832.GA22109@calvin.surfutopia.net>
* 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