Re: Statement Pooling

On Tue, May 25, 2010 at 05:28:10PM +0200, Janning wrote:
> Our hibernate stack uses prepared statements. Postgresql is caching the
> execution plan. Next time the same statement is used, postgresql reuses the
> execution plan. This saves time planning statements inside DB.

It only uses the cached plan if you prepare the statement and run that
prepared statement. Running "SELECT foo FROM bar" twice in a row without any
preparing will result in the query being parsed, planned, and executed twice.
On the other hand, doing something like this:

p = conn.prepareStatement("SELECT foo FROM bar");

...and then repeatedly executed p, parsing and planning for the query would
occur only once, at the time of the prepareStatement call.

> Additionally c3p0 can cache java instances of "java.sql.PreparedStatement"
> which means it is caching the java object. So when using
> c3p0.maxStatementsPerConnection = 100 it caches at most 100 different
> objects. It saves time on creating objects, but this has nothing to do with
> the postgresql database and its prepared statements.
>
> Right?

That's the idea.

> As we use about 100 different statements I would set
> c3p0.maxStatementsPerConnection = 100
>
> Is this reasonable? Is there a real benefit activating it?

Answering that question for your situation really requires benchmarking with
and without statement caching turned on. Your best bet is probably to set it
to a value that seems decent, and revisit it if you find a performance
bottleneck you need to resolve which looks like it's related to statement
caching.

> I remember postgresql 8.4 is replanning prepared statements when statistics
> change occur, but I didn't find it in the release notes. It is just saying
> "Invalidate cached plans when referenced schemas, functions, operators, or
> operator classes are modified". Does PG replans prepared statements from time
> to time if underlying data statistics change?

I don't think so, though I may be wrong. The change you refer to replans such
things when the actual objects change, such as when you remove a column or
something that would make the plan fail to execute.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com