Re: Why Is pgSQL's JDBC Slow?

LSanchez(at)ameritrade(dot)com wrote:

> I’m a member of the pgsql-jdbc mailing list but it seems that my email
> (which I sent multiple times since July 8) just can’t get posted onto
> the site, so I thought of going to you directly because I see your names
> very often in the list.

The list can be unreliable at times, or it may be that your email is
getting delayed for approval (e.g. because you are not subscribed to the
list with the address you are sending from).

> We’ve been doing our pgsql testing and the biggest problem that we’ve
> encountered so far is the JDBC slowness, which could be 1000x slower in
> some cases in comparison to native sql and could be 500x slower compared
> to Oracle.
>
> Are there any newer drivers around that had fixed this problem?

You can try the latest development drivers from jdbc.postgresql.org --
they may help but without knowing what exactly is causing the slowness I
can't say for sure.

> *Sql
> statement
> psql jdbc*
>
> select * from eventlog where eventdate between '1/14/1998' and
> '1/15/1998' 8 ms 720 ms
>
> Select * from eventlog a, eventcodes b, statuschange c Where a.eventid =
> c.eventid and a.code = b.code 22 ms 2500 ms

Are you comparing the same times here?

I notice in your JDBC code you are timing the execution time but not the
time to iterate through the resultset. With the way you are setting up
your statements, JDBC will be pulling the entire resultset into memory
immediately on execute, so you're actually measuring both the query
execution time and the time taken to pull the results over to the Java
side. Are you also measuring the time to retrieve the results when using
psql?

Are you "warming up" the JVM at all before taking measurements?
Depending on the JVM you use, it may take some time before the critical
bits of code are JIT-ed.

I'd also suggest checking your heap settings under JDBC, You appear to
be moving a lot of data and it may be that some of the slowness you see
is actually time spent doing GC. Try running with '-verbose:gc' and see
how long collection is taking.

If it *is* heap/GC, you have a couple of options:

1) tune your heap/GC settings (probably means a much bigger heap)
2) make JDBC use a cursor to incrementally fetch the resultset; this
will decrease JDBC's working heap size which may help GC.

2) requires (under the current driver):

- a 7.4 or later server
- setAutoCommit(false)
- TYPE_FORWARD_ONLY, not TYPE_SCROLLABLE_INSENSITIVE
- calling Statement.setFetchSize() with a positive value before
executing the query (this controls the number of rows fetched at once)

Earlier drivers (pre build 302) didn't have the need-a-7.4-server
restriction. If there's demand, I could reimplement the
cursors-via-DECLARE support for 7.3.

-O