Re: Varchar and binary protocol

On Sat, Feb 05, 2011 at 10:59:45PM +0100, Rados??aw Smogura wrote:
> I do performance tests against orignal JDBC driver and my version in binary
> and in text mode. I saw strange results when I was reading varchar values.
> Here is some output from simple benchmark
>
> Plain strings speed Execution: 8316582 , local: 2116608 , all:
> 10433190
> Binary strings speed Execution: 9354613 , local: 2755949 , all:
> 12110562
> Text NG strings speed Execution: 8346902 , local: 2704242 , all:
> 11051144
>
> Plain is standard JDBC driver, Binary is my version with binary transfer, Text
> is my version with normal transfer. 1st column, "Execution" is time spend on
> query execution this includes send, recivie proto message, store it, etc, no
> conversion to output format. Values are in nanoseconds.
>
> In new version I added some functionality, but routines to read parts in
> "Execution" block are almost same for binary and text.
>
> But as you see the binary version is 10-20% slower then orginal, and my text
> version, if I increase number of read records this proportion will not change.
> I done many checks, against even "skip proto message content" driver, end
> results was same 10-20% slower.

Comparing "COPY tbl(varchar_col) TO '/dev/null'" to "COPY tbl(varchar_col) TO
'/dev/null' WITH BINARY" gives a better sense of the situation. Your data could
have reflected a backend performance problem, but it could just as well have
arisen from your client-side changes. (This thread also really belongs on
pgsql-performance. See http://wiki.postgresql.org/wiki/SlowQueryQuestions)

I can reproduce a 20% slowdown using the test case I mentioned above. I didn't
investigate much further.

Thanks,
nm