"Alvaro Herrera" <[EMAIL PROTECTED]> writes: > PFC wrote: > >> So, where to go from that ? I don't see a way to implement this without >> a (backwards-compatible) change to the wire protocol, because the clients >> will want to specify when a plan should be cached or not. Since the user >> should not have to name each and every one of the statements they want to >> use plan caching, I see the following choices : > > I don't understand the point here. We already have cached plans: you > send a Parse. You can then Bind/Execute many times.
I think what he's referring to is persistently caching plans so that new connections can use them. That makes a lot more sense if you have lots of short-lived connections like a stock php server without persistent connections turned on or a connection pooler. You can prepare queries but they only live for a single web page so you don't get any benefit. Personally I would like to see this, not primarily for the performance gains, but for the possibility of managing when plans change -- ie, plan stability. But there is resistance from other quarters about the reliability hit of having the plan data structures in shared memory. A bug in one backend could cause other backends to crash or corrupt their memory. The possibility exists with other shared data structures but, arguably, plans are much more complex data structures than PGPROC entries and buffers. I still don't see why you would need a wire protocol change. You would just have clients prepare plans normally and stash them in shared memory for other backends in a hash table keyed by, well, something, perhaps the original query text. Then whenever you're asked to prepare a query you go check if someone else has already done it for you and find an already generated plan in the shared memory hash table. The contention on the shared cache is likely to negate much of the planning savings but I think it would still be a win. But what's really interesting to me is then providing an interface to see and manipulate that cache. Then you could see what plans other backends are using for queries, mark plans as being acceptable or not, and even revoke users' permissions to execute queries which aren't already present and marked as being acceptable. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
