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