Re: SELECT slows down on sixth execution

Hi

2015-10-14 9:38 GMT+02:00 Jonathan Rogers <jrogers(at)socialserve(dot)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

Regards

Pavel

>
> 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(at)emphasys-software(dot)com
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>