Re: Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

Interesting so why would it choose a worse plan at that point ? Why would it change at all if the current plan is working well ?

On 12 January 2016 at 07:15, Marko Tiikkaja <marko@joh.to> wrote:

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