On 9/27/07, David E. Wheeler <[EMAIL PROTECTED]> wrote: > On Sep 27, 2007, at 17:58, Ben Tilly wrote: > > 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.
You're right that query planning is a big cost. And a big problem for database implementations is finding the right tradeoff between effort spent optimizing the query and effort spent executing the query. Now I have *no* idea how PostgreSQL handles things internally. But I know Oracle pretty well. In Oracle they keep a library of prepared statements. And each connection remembers what queries it has encountered. When you go to prepare a query, Oracle will first check to see if that query has already been prepared in your connection. If so, that's a session cursor cache hit. Then it checks the global library to see if it was parsed by anyone else. If it has, then Oracle just uses the existing query plan and that is a soft parse. If it has not, then Oracle does a hard parse. (Technical aside. Why do they bother with a session cache if they're just going to look in the library cache anyways? The reason is that access to the global cache has to be serialized through a latch, which significantly reduces how much concurrency you'll get in the database.) Given this caching of query plans, Oracle expects to parse very little. So they can put more energy into parsing. But now it is very, very important to not make Oracle do unnecessary hard parses. Therefore you have to always use placeholders, DBAs measure things like the ratios of hard and soft parses, etc. So in Oracle, the performance difference between prepare and prepare_cached is very small. It just means losing the session cursor cache hits. But small is still real. If Postgres does not have the elaborate caching scheme that Oracle does, then I'd imagine that the performance benefit is much, much bigger. Cheers, Ben
