Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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: binary_arrays.patch
Description: text/plain (16.2 KB)

Responses

pgsql-jdbc by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group