From: Jonathan Rogers <jrogers@socialserve.com>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: SELECT slows down on sixth execution
Date: Fri, 16 Oct 2015 22:29:56 -0400
Message-ID: <5621B2A4.9020608@socialserve.com> (raw)
In-Reply-To: <CAFj8pRBpRCUxJYY6K+FGe85WO+DhKs_7dsGC7NTg-aFb2syeJw@mail.gmail.com>
References: <561E068F.1040702@socialserve.com>
<CAFj8pRBpRCUxJYY6K+FGe85WO+DhKs_7dsGC7NTg-aFb2syeJw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 10/14/2015 05:01 AM, Pavel Stehule wrote:
> Hi
>
> 2015-10-14 9:38 GMT+02:00 Jonathan Rogers <jrogers@socialserve.com
> <mailto:jrogers@socialserve.com>>:
>
> 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?
>
>
> yes, there is. PostgreSQL try to run custom plans five times (optimized
> for specific parameters) and then compare average cost with cost of
> generic plan. If generic plan is cheaper, then PostgreSQL will use
> generic plan (that is optimized for most common value (not for currently
> used value)).
>
> see
> https://github.com/postgres/postgres/blob/master/src/backend/utils/cache/plancache.c
> , function choose_custom_plan
>
> What I know, this behave isn't possible to change from outside.
> Shouldn't be hard to write a extension for own PREPARE function, that
> set CURSOR_OPT_CUSTOM_PLAN option
Thanks for the link. I can see the hard-coded "5" right there. I looked
in the docs a bit and found the server C function "SPI_prepare_cursor"
which allows explicit selection of a custom or generic plan. However, if
I understand you correctly, there is currently no SQL interface to
explicitly control what type of plan is used.
So, the solution for my particular query is to avoid preparing it,
ensuring it gets a custom plan every time. The decision to prepare it
came from a client-side layer which defaults to preparing everything
rather than any specific reason and we're now reconsidering that policy.
--
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, pavel.stehule@gmail.com
Subject: Re: SELECT slows down on sixth execution
In-Reply-To: <5621B2A4.9020608@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