Re: SELECT slows down on sixth execution

Jonathan Rogers wrote:
> 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.

You are encountering "custom plans", introduced in 9.2.

When a statement with parameters is executed, PostgreSQL will not only generate
a generic plan, but for the first 5 executions it will substitute the arguments
and generate and execute a custom plan for that.

After 5 executions, the cost of the generic plan is compared to the average
of the costs of the custom plans. If the cost is less, the generic plan will
be used from that point on. If the cost is more, a custom plan will be used.

So what you encounter is probably caused by bad estimates for either
the custom plan or the generic plan.

Look at the EXPLAIN ANALYZE output for both the custom plan (one of the
first five executions) and the generic plan (the one used from the sixth
time on) and see if you can find and fix the cause for the misestimate.

Other than that, you could stop using prepared statements, but that is
probably not the optimal solution.

Yours,
Laurenz Albe