From: | Markus Schulz <msc(at)antzsystem(dot)de> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org, Heikki Linnakangas <hlinnakangas(at)vmware(dot)com> |
Subject: | Re: BUG: endless lseek(.., SEEK_END) from select queries on x64 builds |
Date: | 2013-02-22 18:10:25 |
Message-ID: | 201302221910.25271.msc@antzsystem.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Staubo | 2013-02-22 18:22:48 | Bad query plan with high-cardinality column |
Previous Message | Vitalii Tymchyshyn | 2013-02-22 17:59:40 | Re: Avoiding Recheck Cond when using Select Distinct |