Message-ID: <5127741D.6090907@vmware.com>
Date: Fri, 22 Feb 2013 15:35:25 +0200
From: Heikki Linnakangas <hlinnakangas@vmware.com>
Organization: VMware
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:10.0.12) Gecko/20130116 Icedove/10.0.12
MIME-Version: 1.0
To: Markus Schulz <msc@antzsystem.de>
CC: pgsql-performance@postgresql.org
Subject: Re: BUG: endless lseek(..,
 SEEK_END) from select queries on x64 builds
References: <201302220925.26460.msc@antzsystem.de>
In-Reply-To: <201302220925.26460.msc@antzsystem.de>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance