From: | Jonathan Rogers <jrogers(at)socialserve(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: SELECT slows down on sixth execution |
Date: | 2015-10-14 15:28:29 |
Message-ID: | 561E749D.4090301@socialserve.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On 10/14/2015 05:00 AM, Albe Laurenz wrote:
> 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.
Thanks. That does explain what I've seen.
>
> 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.
Yes, I have been looking at both plans and can see where they diverge.
How could I go about figuring out why Postgres fails to see the large
difference in plan execution time? I use exactly the same parameters
every time I execute the prepared statement, so how would Postgres come
to think that those are not the norm?
>
> Other than that, you could stop using prepared statements, but that is
> probably not the optimal solution.
This is probably what I'll end up doing. The statement preparation is
the result of a custom layer that does so universally and I'll probably
just turn that feature off.
--
Jonathan Rogers
Socialserve.com by Emphasys Software
jrogers(at)emphasys-software(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Albe Laurenz | 2015-10-16 12:37:56 | Re: SELECT slows down on sixth execution |
Previous Message | Pavel Stehule | 2015-10-14 09:01:43 | Re: SELECT slows down on sixth execution |