Patch for binary receive of basic array types

From: Mikko Tiihonen <mikko(dot)tiihonen(at)nitorcreations(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Patch for binary receive of basic array types
Date: 2011-09-23 21:31:24
Message-ID: 4E7CFAAC.9000507@nitorcreations.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Hi,

I coded a patch that allows to receive binary coded int2,int4,int8,float4,float8,text arrays.

I tried a small read-only benchmark that loads repeatedly same rows from database and records the speed.
The test uses the resultSet.getArray(1).getArray() API, not the resultSet.getArray(1).getResultSet() which is slower.
I tried with 4 different types of data to better observe the speed differences.

column data bytes speed bytes speed data inc speed inc
int[] '{1,2,3}' text: rowlen=7 4338.39 ops/s, bin: rowlen=44 4477.75 ops/s => 528.57% 3.21% ops/s
int[][] '{{1000,2},{NULL,-4000}}' text: rowlen=23 3315.53 ops/s, bin: rowlen=56 4149.75 ops/s => 143.48% 25.16% ops/s
float4[] '{3.141,-523.23,1e5,0}' text: rowlen=32 2803.35 ops/s, bin: rowlen=52 4410.70 ops/s => 62.50% 57.34% ops/s
text[] '{abba,\"fa\\\"b\"}' text: rowlen=14 3987.68 ops/s, bin: rowlen=36 4462.47 ops/s => 157.14% 11.91% ops/s

summary: speed-up 3-57%, row-length on the wire increases typically between 50% and 200% (smaller increases for larger arrays or complex types)

I think the speed-up is enough to justify using of binary transfers for arrays by default even though the data amount on the wire is increased.

-Mikko

Attachment Content-Type Size
binary_arrays.patch text/plain 16.2 KB

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Kevin Grittner 2011-09-23 21:49:23 Re: Patch for binary receive of basic array types
Previous Message Maciek Sakrejda 2011-09-23 19:25:19 Re: Prepared statement with function as argument: how to bind values?