From: Heikki Linnakangas <hlinnakangas@vmware.com>
To: Markus Schulz <msc@antzsystem.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: BUG: endless lseek(.., SEEK_END) from select queries on x64 builds
Date: Fri, 22 Feb 2013 15:35:25 +0200
Message-ID: <5127741D.6090907@vmware.com> (raw)
In-Reply-To: <201302220925.26460.msc@antzsystem.de>
References: <201302220925.26460.msc@antzsystem.de>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: hlinnakangas@vmware.com, msc@antzsystem.de
Subject: Re: BUG: endless lseek(.., SEEK_END) from select queries on x64 builds
In-Reply-To: <5127741D.6090907@vmware.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox