bytea, jdbc, i/o ...

Subject: [GENERAL] bytea, jdbc, i/o ...
From: Kenneth Been <kennethb(at)telocity(dot)com>
===
I am seeing some strange behavior, and I have a guess for what is
causing it. Maybe someone will know for sure.

Here is the situation. Database A contains mostly data of type
integer[], text, and boolean[]. Database B contains mostly data
of type bytea.

I wrote a program to send a bunch of select queries and time the
results. This test program is in Java, and connects using JDBC.
The data types in A are retrieved with the
ResultSet.getString() method. That includes the arrays, which I
then parse myself. The bytea fields in B are retrieved with the
ResultSet.getBytes() method, which returns byte[].

I ran this test program on the same machine that has the database
engine, and on a different machine on the local network.

Here is the strange behavior:

For database A, the difference in performance between testing
locally and testing over the network is negligible; about 5%
slower over the network. About what I would expect. But for
database B, the difference is huge; about 35 times slower over
the network.

Here is my guess for what is going on:

Maybe for a local connection the db engine delivers the bytea as
is, whereas for a remote connection it converts the bytea to a
string representation, and then Java would have to convert it
back for the getBytes() method. If the queries are usually
answered from the cache, then I would think converting to string
and back could take 35 times longer than just pulling the data
out of the cache.

For the other data types, on the other hand, maybe the engine
always converts them to string, whether it is local or not. Or
anyway, even if it doesn't, Java would convert to string for the
getString() method.

Does that sound right? If not, any ideas on what is going on?

Thanks.

Ken

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org