Skip site navigation (1) Skip section navigation (2)

Re: prepared statement: are they pre-compiled?

From: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
To: Flipper <flipper(at)gammadue(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: prepared statement: are they pre-compiled?
Date: 2007-07-05 08:53:51
Message-ID: 468CB19F.6080108@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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

In response to

Responses

pgsql-jdbc by date

Next:From: FlipperDate: 2007-07-05 10:08:59
Subject: Re: prepared statement: are they pre-compiled?
Previous:From: FlipperDate: 2007-07-05 08:23:25
Subject: prepared statement: are they pre-compiled?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group