Re: SELECT slows down on sixth execution

On Fri, Oct 16, 2015 at 9:14 PM, Jonathan Rogers
<jrogers(at)socialserve(dot)com> wrote:
> On 10/16/2015 08:37 AM, Albe Laurenz wrote:
>> Jonathan Rogers wrote:
>>>> 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?
>>
>> PostgreSQL does not consider the actual query execution time, it only
>> compares its estimates for there general and the custom plan.
>> Also, it does not keep track of the parameter values you supply,
>> only of the average custom plan query cost estimate.
>
> OK, that makes more sense then. It's somewhat tedious for the purpose of
> testing to execute a prepared statement six times to see the plan which
> needs to be optimized. Unfortunately, there doesn't seem to be any way
> to force use of a generic plan in SQL based on Pavel Stehule's reply.

Yeah. In the worst case, a query can fail in the generic plan because
it depends on the arguments for dubious things like

SELECT
CASE WHEN _arg = 'TEXT' THEN foo::text ...

I'm ok with why those things must fail, but it'd sure be nice to be
able to control the switch to the generic plan.

merlin