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

Hi Marko,

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 ?

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 12 January 2016 at 07:15, Marko Tiikkaja <marko(at)joh(dot)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
>