Thread: Re: [postgis-devel] JDBC & WKB - 400% overhead

[X-Post to better place for discussion]

Hi, David,

dblasby@openplans.org schrieb:
> I was checking to see if the WKB (postgis 1.0) was actually transiting
> "in binary" from the server (postgresql 8) to a JDBC (also postgresql
> 8) client.
>
> ..
> ResultSet rs2 = st.executeQuery("select 5::float8,asBinary('POINT(0 0)')");
> rs2.next();
> byte[] bs = rs2.getBytes(2);
> assertTrue(bs.length == 21);
>
> The WKB representation of a 'POINT(0 0)' is 21 bytes long (1 byte for
> xdr/ndr flag, 4 bytes for type, and 2 8-byte doubles = 21 bytes).
>
> If you step through the executeQuery() command in the postgresql driver,
> you'll eventually end up in PGStream#ReceiveTupleV3().  This is where
> the actual data is received from the database.
>
> It clearly receives 84 bytes of data from the server.  This is the bytea
> text representation:
> \001\001\000\000\000\000\000\000\000\000\000\000\000\000\000\000\000\000\000\000\000
>
> The ResultSet#getBytes() function converts this to a byte[].

Maybe your postgresql driver only uses text transfer up to now.
asBinary() returns a byte array, so this should be transferred more
efficiently.

Can you try the following query:

select 5::float8,asBinary('POINT(0 0)')::bytea

Maybe the current 8.0-310 build has fixed this. But the pgsql-jdbc list
could be the better place to ask this question, IMHO, so I forward it to
there.


Markus

--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com

On Wed, 23 Feb 2005, Markus Schaber wrote:

> > ResultSet rs2 = st.executeQuery("select 5::float8,asBinary('POINT(0 0)')");
> > rs2.next();
> > byte[] bs = rs2.getBytes(2);
> > assertTrue(bs.length == 21);
> >
> > The WKB representation of a 'POINT(0 0)' is 21 bytes long (1 byte for
> > xdr/ndr flag, 4 bytes for type, and 2 8-byte doubles = 21 bytes).
> >
> > It clearly receives 84 bytes of data from the server.  This is the bytea
> > text representation:
> > \001\001\000\000\000\000\000\000\000\000\000\000\000\000\000\000\000\000\000\000\000
> >
> > The ResultSet#getBytes() function converts this to a byte[].
>
> Maybe your postgresql driver only uses text transfer up to now.
> asBinary() returns a byte array, so this should be transferred more
> efficiently.
>

The driver receives all data as text.  To be able to receive binary data
we must support binary data for all types because we do not know what type
we will be getting back from the SELECT before it is run.  Getting that
information would require an extra network round trip so we don't want to
do that.  Doing all binary transfer is on the 8.1 todo list, but I've yet
to really get started on it.

Kris Jurka

Hi, Kris,

Kris Jurka schrieb:

>>Maybe your postgresql driver only uses text transfer up to now.
>>asBinary() returns a byte array, so this should be transferred more
>>efficiently.
>
> The driver receives all data as text.  To be able to receive binary data
> we must support binary data for all types because we do not know what type
> we will be getting back from the SELECT before it is run.  Getting that
> information would require an extra network round trip so we don't want to
> do that.  Doing all binary transfer is on the 8.1 todo list, but I've yet
> to really get started on it.

Oh, I thought that binary transmit was implemented in 8.0 :-(

Then, currently, the better way to receive geometries may be to use the
new canonical text rep for geometries (hex-encoded extended WKB) which
is only two times as large compared to pure binary rep.

As there are at least plans to use caching of prepared statements, it
also should be possible to cache this information together with the
prepared statement. This would reduce the number of roundtrips.

Markus

--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com

On Thu, 24 Feb 2005, Markus Schaber wrote:

> Oh, I thought that binary transmit was implemented in 8.0 :-(
>

Binary transmission was done for sending bytea data, which was a big win,
but that's something we can do because we know we're sending binary data.

> As there are at least plans to use caching of prepared statements, it
> also should be possible to cache this information together with the
> prepared statement. This would reduce the number of roundtrips.

That's true, this information could be received on the first query or say
a PreparedStatement.getMetaData() call.  This would definitely be a
simpler thing to do, but still 8.1 material I think.


Kris Jurka

Hi, Kris,

Kris Jurka schrieb:

>>As there are at least plans to use caching of prepared statements, it
>>also should be possible to cache this information together with the
>>prepared statement. This would reduce the number of roundtrips.
> That's true, this information could be received on the first query or say
> a PreparedStatement.getMetaData() call.  This would definitely be a
> simpler thing to do, but still 8.1 material I think.

Yes, I agree that this is not material for 8.0.

Markus

--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com