Re: Very strange performance decrease when reusing a PreparedStatement

Dave Cramer wrote:
>
> It appears that the Postgres "server-prepared statement" cannot
> handle
> parameters to the statement. This is really unfortunate, because 99%
> of real-life applications will want to re-use the same statement
> (template) with different parameters.
>
> The term "server-prepared statement" itself already indicates that
> there may be something skewed about the "local" semantics of
> java.sql.PreparedStatements in the Postgres JDBC driver. There is no
> notion of "client-prepared statement" in the JDBC API, which
> conceives
> PreparedStatement instances as mere handles to server side objects.
> And indeed, Postgres JDBC users have historically been using
> java.sql.PreparedStatements for its side-effect of preventing SQL
> injection rather than for the purpose the JDBC API designers had in
> mind with this class.
>
>
> I'm not sure where this hypothesis is coming from. Postgresql server
> prepared statements can certainly handle parameters.
>
> What makes you think it can't ?
>
Possibly the post refers to problems in generating optimal query plans
with prepared statements, for example with respect to index choice. Some
cases i can understand:
For example if you have a partial index on say (val=3) and you do
something like "select ... where val=?" if the server knows the value is
3 it can use the better index.

However it seems that other optimisations can't be made for example it
doesn't seem possible to tell the server that parameter 1 is always
going to be an int and therefore it should be using index A. The current
implementation may not use index A as it is unaware as to the type of
the supplied parameter. Perhaps in this case the execution planner
should pick the parameters for the most optimal plan and return the
types during the parse and let the driver convert the data to what the
server requires...

Certainly it is a (minor) problem the prepared statements may not be the
most optimal with any benefits saved during parse lost due to bad
execution plans.

JOHN