On 12/01/16 13:00, Dave Cramer wrote:
> We have an interesting problem, and the reporter has been kind enough to
> provide logs for which we can't explain.
>
> I'd be interested to hear any plausible explanations for a prepared plan
> suddenly going from 2ms to 60ms for the same input values ?
This is a new feature in 9.2, where on the fifth (or sixth, not sure)
execution the planner might choose to use a generic plan. From the 9.2
release notes (though I'm fairly certain this is documented somewhere in
the manual as well):
In the past, a prepared statement always had a single "generic" plan
that was used for all parameter values, which was frequently much
inferior to the plans used for non-prepared statements containing
explicit constant values. Now, the planner attempts to generate custom
plans for specific parameter values. A generic plan will only be used
after custom plans have repeatedly proven to provide no benefit. This
change should eliminate the performance penalties formerly seen from use
of prepared statements (including non-dynamic statements in PL/pgSQL).
.m