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

Actually around 9.2 Tom Lane added the ability to have multiple plans for
prepared statements, specifically

Allow the planner to generate custom plans for specific parameter values
even when using prepared statement

What version of the server are you using ?

Dave Cramer

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

On 12 January 2016 at 06:26, Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com>
wrote:

> >My guess is that the plan that is generated using the prepared statement
> only
> works for some input values, but not for all (a problem that Oracle has
> suffered
> from for ages as well).
>
> I think the problem is as follows:
> 1) During initial runs in server-prepared mode PG still uses exact
> values to plan the query
> 2) It turns out that for certain conditions PG properly understands
> that certain conditions are bad.
> I bet $1 = '180', $2 = '1' returns lots of rows, thus backend chooses
> to start with $3 = '7728'.
> 3) Later backend creates generic plan. That one does not include
> knowledge of exact $1 and &2 values. Thus backend estimates the
> cardinality differently.
> That results a plan flip.
>
> Note: in Oracle (9-10) bind peeking worked in another way.
> There, a plan built for the first bind values was reused for all the
> subsequent executions.
> That makes more sense for me since that gives at least some stability
> (it avoids sudden plan flips).
>
> I'll try to file a case to hackers list to check what they say on the plan
> flip.
>
> PS. The sad thing is JDBC does have room for "SQL-injection-safe,
> non-server-prepared query". In other words, plain java.sql.Statement
> does not have "setXXX" methods, and PreparedStatement has no
> user-provided API to convey "please, do not use server-prepared
> statement as the plan is very data-dependent".
>
> PPS. I do not think the plan is data-dependent in the particular case.
> I bet the same plan works for all the values that particular
> application uses. It is just PG's fault that plan flip happens.
>
> Vladimir
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>