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
