Обсуждение: 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

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

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

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.