Thread: prepared statement: are they pre-compiled?

prepared statement: are they pre-compiled?

From
Flipper
Date:
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?

Thanks.

Re: prepared statement: are they pre-compiled?

From
Heikki Linnakangas
Date:
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

Re: prepared statement: are they pre-compiled?

From
Flipper
Date:
On Thursday 5 July 2007 Heikki Linnakangas's cat, walking on the keyboard,
wrote:
> 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.

Thanks for you explaination!
Where is this default parameter set? I cannot find it.
By the way, I cannot see the real difference in the code, and in fact the
QueryExecutor goes into the sendParse with oneShot = false (since the flag
has not been set). The sendParse creates a statement name and then sends the
parse message. So the doubt is: the parse message is always sent and the
server ignores it when a statement name is provided? Or is something in the
Java code that I'm not getting?

Thanks

Re: prepared statement: are they pre-compiled?

From
Heikki Linnakangas
Date:
Flipper wrote:
> On Thursday 5 July 2007 Heikki Linnakangas's cat, walking on the keyboard,
> wrote:
>> 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.
>
> Thanks for you explaination!
> Where is this default parameter set? I cannot find it.

In the connection string, see
http://jdbc.postgresql.org/documentation/82/connect.html#connection-parameters.

Or you can set it programmatically with PGConnection.setPrepareThreshold.

> By the way, I cannot see the real difference in the code, and in fact the
> QueryExecutor goes into the sendParse with oneShot = false (since the flag
> has not been set). The sendParse creates a statement name and then sends the
> parse message. So the doubt is: the parse message is always sent and the
> server ignores it when a statement name is provided? Or is something in the
> Java code that I'm not getting?

I'm not familiar with that code myself either, but at the beginning of
sendParse, we check if we've already sent a PARSE for the given query
and parameters:

>         // Already parsed, or we have a Parse pending and the types are right?
>         int[] typeOIDs = params.getTypeOIDs();
>         if (query.isPreparedFor(typeOIDs))
>             return;


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

Re: prepared statement: are they pre-compiled?

From
Heikki Linnakangas
Date:
(please keep the list cc'd so that others can answer and benefit from
the discussions as well)

Flipper wrote:
> thank you for the quick reply. May I ask something more in order to understand
> if I'm right? When you use a Statement you should use  a simple query
> protocol (i.e., without a bind) but from the driver code I see that the
> sequence of messages is always the same, that is parse, bind, execute....
> I thought the simple query protocol does not include the parse+bind steps,
> since they should be in the extended query protocol.....am I wrong?

I think we use the extended query protocol for all queries, including
those issued from a Statement. That means we still send a Bind message,
there just isn't any parameter values in it. Note that we don't wait for
a response after each message, so that doesn't cause extra round trips
to the server.

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