Re: Status of binary protocol usage?

Hi,
I'm wondering what's the status of binary protocol usage patches. I've seen
7 of them
posted to the mailing list up to January 2007, and then, nothing (patches
are here:
http://mokki.dyndns.org/~mtiihone/postgresql/binarytransfer/).

I'm asking because I suspect the text protocol is biting me with slow
performance quite
a lot. I'm getting geometries out of a Postgis database. Well, guess what,
the following query:

SELECT revision, gid, encode(AsBinary(force_2d(the_geom), 'XDR'),'base64')
FROM world

happens to be 25% faster than:

SELECT revision, gid, AsBinary(force_2d(the_geom), 'XDR') FROM world

even if in the former case the backend has to do more work during the base64
encoding, and
the client has to do base64 decoding. This is sounds counter intuitive, a
profiler
informs me that quite a big of time is spent in the PGBytea.toBytes(byte[]s)
method, which
is used only if the transfer occurrs in text mode.

I have other experiences where Postgres resulted to be quite a bit slower
than other databases
when gathering big amounts of data (not necessarily geometries). I always
had the gut feeling the text protocol was to blame, but never asked... well,
now I do :)
Any chance we'll see the binary protocol used by default?

--
View this message in context: http://www.nabble.com/Status-of-binary-protocol-usage--tf3972236.html#a11275147
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

aaime74 <andrea(dot)aime(at)gmail(dot)com> writes:
> A profiler informs me that quite a big of time is spent in the
> PGBytea.toBytes(byte[]s) method, which is used only if the transfer
> occurrs in text mode.

That hardly seems like a killer argument for switching to binary
(which has got a boatload of disadvantages of its own). Surely a
bit of code-optimization work can fix that.

regards, tom lane

Tom Lane-2 wrote:
>
> aaime74 <andrea(dot)aime(at)gmail(dot)com> writes:
>> A profiler informs me that quite a big of time is spent in the
>> PGBytea.toBytes(byte[]s) method, which is used only if the transfer
>> occurrs in text mode.
>
> That hardly seems like a killer argument for switching to binary
> (which has got a boatload of disadvantages of its own). Surely a
> bit of code-optimization work can fix that.
>

Hum, interesting. What would be the boatload of disadvantages? Is there
any reference to those?
Do you have any idea why doing more processing (Base64 encoding/decoding)
leads to significant better performance? :)

Cheers
Andrea

--
View this message in context: http://www.nabble.com/Status-of-binary-protocol-usage--tf3972236.html#a11276153
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

aaime74 <andrea(dot)aime(at)gmail(dot)com> writes:
> Hum, interesting. What would be the boatload of disadvantages?

Portability and cross-machine compatibility, or lack of same.

It's tough enough trying to make things work for relatively primitive
datatypes like float. I can hardly imagine that anyone would want to
support binary representations across platforms for geometric types.

regards, tom lane