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. 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.
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.
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.
- Heikki -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
