From: Jonathan Rogers <jrogers@socialserve.com>
To: pgsql-performance@postgresql.org
Subject: SELECT slows down on sixth execution
Date: Wed, 14 Oct 2015 03:38:55 -0400
Message-ID: <561E068F.1040702@socialserve.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
I have a very complex SELECT for which I use PREPARE and then EXECUTE.
The first five times I run "explain (analyze, buffers) execute ..." in
psql, it takes about 1s. Starting with the sixth execution, the plan
changes and execution time doubles or more. The slower plan is used from
then on. If I DEALLOCATE the prepared statement and PREPARE again, the
cycle is reset and I get five good executions again.
This behavior is utterly mystifying to me since I can see no reason for
Postgres to change its plan after an arbitrary number of executions,
especially for the worse. When I did the experiment on a development
system, Postgres was doing nothing apart from the interactively executed
statements. No data were inserted, no settings were changed and no other
clients were active in any way. Is there some threshold for five or six
executions of the same query?
Without delving into the plans themselves yet, what could possibly cause
the prepared statement to be re-planned? I have seen the same behavior
on Postgres 9.2.10 and 9.4.1.
--
Jonathan Rogers
Socialserve.com by Emphasys Software
jrogers@emphasys-software.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: jrogers@socialserve.com
Subject: Re: SELECT slows down on sixth execution
In-Reply-To: <561E068F.1040702@socialserve.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