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