Re: ResultSet performance question

Craig Servin wrote:
> I'm having a performance issue with Postgresql, but only when using the JDBC
> driver. I'm looking for any suggestions.
>
>
> I have a very simple query that returns about 7000 rows from a temp table.
> Using psql the query returns in under 3 seconds. Using the JDBC driver it
> takes around 30 seconds to run the same query.
>
> I have looked at the query plan using both methods of execution and they are
> identical.

Did you turn on statement tracing on the server an look at the actual query
strings that arrive at the server? Else, how do you know what exactly is executed?

Is there a server side cursor involved in one of this cases? This can change
the execution plan.

> I have played around with setFetchSize() and used
> con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
> But, since I am trying to get all of the results and it is total runtime I am
> worried about server side cursors don't seem applicable.
>
> I have also created a Java class that does nothing but issue rs.next() calls
> through the ResultSet to time the execution without any other work being
> done.

How is the time distributed? Does it take a long time until the first row is
fetched, and the rest comes in almost no time? Or are the 30 seconds more evenly
distributed?

> I can't believe that I am not doing something wrong as the difference in
> execution time is extreme. Is this normal?

If the execution plans are really identical, that would seem out of the
ordinary, even considering Java's comparative slowness.

Yours,
Laurenz Albe