Am Freitag, 22. Februar 2013, 14:35:25 schrieb Heikki Linnakangas: > On 22.02.2013 10:25, Markus Schulz wrote: > > i can run the query four times with good performance and after that > > postgresql starts with the strange lseek() behavior. > > By default, the JDBC driver re-plans the prepared statement for the > first 4 invocations of the query. On the fifth invocation, it switches > to using a generic plan, which will be reused on subsequent invocations.
that sounds really interesting and i would try to change my java-jdbc-test-cli program according to that, but ... > See http://jdbc.postgresql.org/documentation/head/server-prepare.html. > The generic plan seems to perform much worse in this case. You can > disable that mechanism and force re-planning the query every time by > setting the "prepareThreshold=0" parameter on the data source. it wouldn't explain why the same jboss runs fine with a 32bit postgresql server (i switched only the datasource to another server with exactly the same database). > You could check what the generic plan looks like by taking the query > used in the java program, with the parameter markers, and running > EXPLAIN on that. how can i do this? I've tried the following in my ejb-test-function to: String query = "..." entitymanager.createNativeQuery(query)...; entitymanager.createNativeQuery("EXPLAIN ANALYZE " + query)...; but the second createNativeQuery call runs fast every time and will show the same plan and the first hangs after the fourth call to this function. > PostgreSQL version 9.2 might work better in this case. It has some > smarts in the server to generate parameter-specific plans even when > prepared statements are used, if the planner thinks a specific plan will > be faster. this wouldn't help: > - Debian Wheezy 64bit with EnterpriseDB 9.2 64bit -> Bad behavior we tried postgresql 9.2 too > - Heikki regards, msc -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
