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

On 12/01/16 11:26, Vladimir Sitnikov 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
Most types in the driver map directly but date time parameters are
special. There may be others treated as a special case.

I think you'll find initial executions (Bind) use the paramater type
org.postgresql.core.Oid.UNSPECIFIED for the date time.
https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/jdbc/PgPreparedStatement.java#L1426
> 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'.
At the threshold a ParameterDescribe is issued by the front end. The
back end returns the actual type used to store the parameter. The front
end stores the updated parameter type information.
> 3) Later backend creates generic plan. That one does not include
> knowledge of exact $1 and &2 values. Thus backend estimates the
> cardinality differently.
Subsequent Bind messages use the replaced parameter type. The back end
switches to a new plan using the updated type information.

I could be wrong on this as the codebase has changed dramatically in
recent weeks.

Jeremy
> 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
>
>

--
Jeremy Whiting
Senior Software Engineer, JBoss Performance Team
Red Hat

------------------------------------------------------------
Registered Address: RED HAT UK LIMITED, 64 Baker Street, 4th Floor, Paddington. London. United Kingdom W1U 7DF
Registered in UK and Wales under Company Registration No. 3798903 Directors: Michael Cunningham (US), Charles Peters (US), Matt Parson (US) and Michael O'Neill(Ireland)