Thread: Persistent Plan Cache
Hi, We have a very large, partitioned, table that we often need to query from new connections, but frequently with similar queries. We have constraint exclusion on to take advantage of the partitioning. This also makes query planning more expensive. As a result, the CPU is fully loaded, all the time, preparing queries, many of which have been prepared, identically, by other connections. Is there any way to have a persistent plan cache that remains between connections? If such a mechanism existed, it would give us a great speedup because the CPU's load for planning would be lightened substantially. Thank you, Joshua Rubin
Attachment
Joshua Rubin <jrubin@esoft.com> writes: > We have a very large, partitioned, table that we often need to query > from new connections, but frequently with similar queries. We have > constraint exclusion on to take advantage of the partitioning. This also > makes query planning more expensive. As a result, the CPU is fully > loaded, all the time, preparing queries, many of which have been > prepared, identically, by other connections. If you're depending on constraint exclusion, it's hard to see how plan caching could help you at all. The generated plan needs to vary depending on the actual WHERE-clause parameters. regards, tom lane
Tom Lane wrote: > Joshua Rubin <jrubin@esoft.com> writes: >> We have a very large, partitioned, table that we often need to query >> from new connections, but frequently with similar queries. We have >> constraint exclusion on to take advantage of the partitioning. This also >> makes query planning more expensive. As a result, the CPU is fully >> loaded, all the time, preparing queries, many of which have been >> prepared, identically, by other connections. > > If you're depending on constraint exclusion, it's hard to see how plan > caching could help you at all. The generated plan needs to vary > depending on the actual WHERE-clause parameters. That's what the OP really should've complained about. If we addressed that, so that a generic plan was created that determines which child tables can be excluded at run time, there would be no need for the persistent plan cache. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
* Heikki Linnakangas (heikki.linnakangas@enterprisedb.com) wrote: > That's what the OP really should've complained about. If we addressed > that, so that a generic plan was created that determines which child > tables can be excluded at run time, there would be no need for the > persistent plan cache. This would definitely be nice to have.. I'm not sure what the level of difficulty to do it is though. Stephen
Attachment
Tom, > If you're depending on constraint exclusion, it's hard to see how plan > caching could help you at all. The generated plan needs to vary > depending on the actual WHERE-clause parameters. Thank you for the reply. We "hardcode" the parts of the where clause so that the prepared plan will not vary among the possible partitions of the table. The only values that are bound would not affect the planner's choice of table. Thanks, Joshua
Attachment
Joshua Rubin wrote: > We "hardcode" the parts of the where clause so that the prepared plan > will not vary among the possible partitions of the table. The only > values that are bound would not affect the planner's choice of table. Then you would benefit from using prepared statements in the client, and/or connection pooling to avoid having to re-prepare because of reconnecting. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Hi, Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes: > Joshua Rubin wrote: >> We "hardcode" the parts of the where clause so that the prepared plan >> will not vary among the possible partitions of the table. The only >> values that are bound would not affect the planner's choice of table. > > Then you would benefit from using prepared statements in the client, > and/or connection pooling to avoid having to re-prepare because of > reconnecting. And you can do both in a transparent way (wrt pooling) using preprepare. The problem without it is for the application to know when the statement is already prepared (that depends on whether the pooling software will assign a new fresh connection or not). Using preprepare your application skip the point and simply EXECUTE the already prepared statements. http://preprepare.projects.postgresql.org/README.html http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/preprepare/preprepare/ http://packages.debian.org/search?keywords=preprepare Regards, -- dim
Joshua Rubin wrote: > Hi, > > We have a very large, partitioned, table that we often need to query > from new connections, but frequently with similar queries. We have > constraint exclusion on to take advantage of the partitioning. This also > makes query planning more expensive. As a result, the CPU is fully > loaded, all the time, preparing queries, many of which have been > prepared, identically, by other connections. > > Is there any way to have a persistent plan cache that remains between > connections? If such a mechanism existed, it would give us a great > speedup because the CPU's load for planning would be lightened > substantially. It's not a great solution, but depending on the specific client technology you use, it can done on the client-side. For example, I've done it before in Java and PHP, and the principle extends to any environment that has any possibility of maintaining "persistent" connections to the database, if you create a thin wrapper for the connections. I have open-sourced such a wrapper for PHP, if you're interested.