Re: prepared statement: are they pre-compiled?

Flipper wrote:
> Hi all,
> a little question about prepared statement. I know that one advantage of using
> prepared statement is the fact that the statament could be "precompiled" by
> the server before the binding of parameters, is it true?
> But while observing the messages sent from the FE to the BE by the postgresql
> driver, I noticed that the query is sent along with the bind of the
> parameters, that is the when the executeQuery() method is called on the
> prepared statement, both the parse and bind messages are sent. I thought the
> bind message have to be sent before the bind one, but it seems to me it's
> not. Anyone can please help me understand this?

It depends on the prepareThreshold parameter. The default is 5, which
means that the query is parsed and planned separately for each call to
executeQuery, until the 5th call. After that, a generic plan is
generated and used thereafter.

For queries that are executed few times, planning the query each time is
more efficient because the parameter values can be used in the planning,
which can give you a better plan. For example, if you have a partial
index on X > 10, and you run a query with WHERE X > ?, you can only use
the partial index if the parameter > 10. When the query is planned using
the parameter given, the planner can check its value and use the partial
index when possible, but when the plan is made for use with arbitrary
parameters later on, the partial index can't be used. Partial indexes
are an obvious example, but there's many other cases where the parameter
values can make a big difference in the plan.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com