From: Kris Jurka <books@ejurka.com>
To: Dave Crooke <dcrooke@gmail.com>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-jdbc@postgresql.org, pgsql-performance <pgsql-performance@postgresql.org>
Cc: Scott Carey <scott@richrelevance.com>
Subject: Re: SOLVED ... Re: Getting rid of a cursor from JDBC .... Re: [PERFORM] Re: HELP: How to tame the 8.3.x JDBC driver with a biq guery result set
Date: Tue, 20 Apr 2010 17:05:54 -0400 (EDT)
Message-ID: <alpine.BSO.2.00.1004201644280.6736@leary.csoft.net> (raw)
In-Reply-To: <w2oca24673e1004201340p703b0121iaf852aa28695be65@mail.gmail.com>
References: <q2sca24673e1004191633j10b1124amf1eb51f3bd84f19a@mail.gmail.com>
<4BCD73AF0200002500030BC2@gw.wicourts.gov>
<w2kca24673e1004200847k77429a1cra0a6f3560d407d8e@mail.gmail.com>
<4BCD90D80200002500030BDD@gw.wicourts.gov>
<x2zca24673e1004201229n653f4e69vd274af4a4aa22d09@mail.gmail.com>
<4BCDC6B90200002500030C09@gw.wicourts.gov>
<w2oca24673e1004201340p703b0121iaf852aa28695be65@mail.gmail.com>
On Tue, 20 Apr 2010, Dave Crooke wrote:
> a. Make setFetchSize(10000) the default
The reason this is not done is that the mechanism used for fetching a
piece of the results at a time can change the query plan used if using a
PreparedStatement. There are three ways to plan a PreparedStatement:
a) Using the exact parameter values by substituting them directly into the
query. This isn't really "planned" as you can't re-use it at all. This
is only available using the V2 protocol.
b) Using the parameter values for statistics, but not making any stronger
guarantees about them. So the parameters will be used for evaluating the
selectivity, but not to perform other optimizations like
contraint_exclusion or transforming a LIKE operation to a range query.
This is the default plan type the JDBC driver uses.
c) Planning the query with no regard for the parameters passed to it.
This is the plan type the JDBC driver uses when it sees the same
PreparedStatement being re-used multiple times or when it is respecting
setFetchSize and allowing for partial results.
We must use (c) for partial results instead of (b) because of some
limitations of the server. Currently you cannot have two statements of
type (b) open on the same connection. So since the driver can't know if
the user will issue another query before fetching the remainder of the
first query's results, it must setup the first query to be of type (c) so
that multiple statements can exist simultaneously.
Switching the default plan type to (c) will cause a significant number of
complaints as performance on some queries will go into the tank. Perhaps
we could have a default fetchSize for plain Statements as it won't affect
the plan. I could also see making this a URL parameter though so it could
be set as the default with only a configuration, not a code change.
> b. If someone does call rs.close() before the end of the ResultSet, and has
> not created an explicit cursor at the JDBC level, flag the query / lock /
> virtual transaction in some way in the JDBC driver that tells it that it can
> just dump the cursor on a subsequent stmt.close(), conn.commit() or
> conn.close() call without sucking down the rest of the data.
This is already true. The JDBC driver only asks the server for more of
the ResultSet when a next() call requires it. So the server isn't
constantly spewing out rows that the driver must deal with, the driver
only gets the rows it asks for. Once the ResultSet is closed, it won't
ask for any more.
Kris Jurka
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: books@ejurka.com, dcrooke@gmail.com, Kevin.Grittner@wicourts.gov, scott@richrelevance.com
Subject: Re: SOLVED ... Re: Getting rid of a cursor from JDBC .... Re: [PERFORM] Re: HELP: How to tame the 8.3.x JDBC driver with a biq guery result set
In-Reply-To: <alpine.BSO.2.00.1004201644280.6736@leary.csoft.net>
* 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