Re: wondering how prepared statements work?

On Sep 27, 2007, at 17:58, Ben Tilly wrote:

> I don't know how it is commonly used, but I've seen it be useful with
> databases that do prepare statements on the server side.

It's actually *most* useful for database s that support server-side  
prepared statements.

> It takes resources to check whether or not you've prepared a query
> before.  Those resources include unnecessary round trips to the
> database, CPU time on the checks, internal latching within the
> database and so on.  While individually these are cheap, they add up
> for a busy transactional database.  If you use prepare_cached rather
> than prepare, you can get rid of this overhead.

I believe that the biggest cost is query planning. Simply put, hen  
the database compiles your query, it takes it apart, generates a tree  
of possible execution plans, and chooses the cheapest one. Such a  
decision of cost is based on the statistics stored by the database,  
which for PostgreSQL is why it's so important to keep your database  
well vacuumed and analyzed. Now, the overhead of the query planning  
is *usually* cheaper than executing the query itself (though not  
always), but it still makes up a substantial cost of a query. But  
with server-side prepared queries, the query planning happens only  
the first time you execute a query. After that, the query plan is  
simply stored and reused for the duration of your database  
connection. For frequently-executed queries, this can be a big win  
overall.

Best,

David