On Thursday 27 May 2010 16:56:28 you wrote: > > 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. Really? Today I think c3p0 statement pooling it is not only about saving time in object creation, but to let postgresql reuse an already prwpeared statement. > > 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. Ok thanks for your detailed answer. I did some benchmarking and it seems to be faster with c3p0 statement cache. > > 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. Ok, so i should be aware of this. if the execution plan postgresql is caching gets old, i can run into trouble. So i should close my connections in the pool from time to time to force replanning of my prepared statements. i have asked this question on stackoverflow too, because i thought it was to jdbc/hibernate specific for this list after sending and not getting an answer. So for further reference i post a link to this question, where you can find an excellent answer, too: http://stackoverflow.com/questions/2920740/should-i-activate-c3p0-statement- pooling/2921340#2921340 kind regards Janning http://www.kicktipp.de/ > > -- > Joshua Tolley / eggyknap > End Point Corporation > http://www.endpoint.com -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
