From: Markus Schulz <msc@antzsystem.de>
Organization: AntzSystem GmbH
To: pgsql-performance@postgresql.org,
	Heikki Linnakangas <hlinnakangas@vmware.com>
Subject: Re: BUG: endless lseek(..,
 SEEK_END) from select queries on x64 builds
Date: Fri, 22 Feb 2013 19:10:25 +0100
User-Agent: KMail/1.13.7 (Linux/3.6.2.nias; KDE/4.8.4; x86_64; ; )
References: <201302220925.26460.msc@antzsystem.de>
	<5127741D.6090907@vmware.com>
In-Reply-To: <5127741D.6090907@vmware.com>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <201302221910.25271.msc@antzsystem.de>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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