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