Re: BUG #6293: JDBC driver performance

On Tue, 15 Nov 2011, Teun Hoogendoorn wrote:

>
> The following bug has been logged online:
>
> Bug reference: 6293
> PostgreSQL version: 9.1
> Description: JDBC driver performance
> Details:
>
> Using the postgresql-9.1-901.jdbc3.jar driver instead of
> postgresql-9.0-801.jdbc3.jar drops performance dramatically.
>
> I think it has something to do with using ResultSetMetaData in Java. The
> postgres log shows me hundreds of identical query's when retrieving the
> ResultSetMetaData for a single query. I'm not using an ORM framework, just
> simple JDBC calls.

The 9.1 JDBC driver was changed to try and fetch all metadata for the
entire resultset in one query instead of potentially issuing multiple
queries for each column. So this change was supposed to improve things.

Looking at the code, the caching pattern has changed slightly, so now it's
important to hold onto the same ResultSetMetaData instance. That is you
need to do:

ResultSet rs = ...
ResultSetMetaData rsmd = rs.getMetaData();
for (int i=1; i<rsmd.getColumnCount(); i++) {
// good
System.out.println(rsmd.getAutoIncrement());
// bad
System.out.println(rs.getMetaData().getAutoIncrement());
}

The driver should probably be changed to hand back the same
ResultSetMetaData instance each time instead of a new one for each
MetaData call.

Does this explain your problem? If not, can you provide more details on
how you access and use ResultSetMetaData?

Kris Jurka