Обсуждение: prepared statement: are they pre-compiled?
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.
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
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
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
(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