Re: Insert values() per-statement overhead

>I guess you mean there's a transaction surrounding it?

Sure there is a transaction.
I measure the latency from the first Bind message to the ReadyForQuery response.
The database is at localhost.

The flow is as follows (I've use 4 queries in batch for brevity,
however the test above is executed for 1024 statements in single
batch):

create table batch_perf_test(a int4, b varchar(100), c int4)

insert into batch_perf_test(a, b, c) values($1, $2, $3)

Typical JDBC batch look like the following:

13:53:17.815 (1) batch execute 4 queries,
handler=org(dot)postgresql(dot)jdbc(dot)BatchResultHandler(at)38d611f4, maxRows=0,
fetchSize=0, flags=532
13:53:17.816 (1) FE=> Bind(stmt=S_1,portal=null,$1=<0>,$2=<'s0'>,$3=<0>)
13:53:17.816 (1) FE=> Execute(portal=null,limit=1)
13:53:17.816 (1) FE=> Bind(stmt=S_1,portal=null,$1=<1>,$2=<'s1'>,$3=<1>)
13:53:17.816 (1) FE=> Execute(portal=null,limit=1)
13:53:17.816 (1) FE=> Bind(stmt=S_1,portal=null,$1=<2>,$2=<'s2'>,$3=<2>)
13:53:17.816 (1) FE=> Execute(portal=null,limit=1)
13:53:17.816 (1) FE=> Bind(stmt=S_1,portal=null,$1=<3>,$2=<'s3'>,$3=<3>)
13:53:17.816 (1) FE=> Execute(portal=null,limit=1)
13:53:17.816 (1) FE=> Sync
13:53:17.817 (1) <=BE BindComplete [unnamed]
13:53:17.817 (1) <=BE CommandStatus(INSERT 0 1)
13:53:17.817 (1) <=BE BindComplete [unnamed]
13:53:17.817 (1) <=BE CommandStatus(INSERT 0 1)
13:53:17.817 (1) <=BE BindComplete [unnamed]
13:53:17.817 (1) <=BE CommandStatus(INSERT 0 1)
13:53:17.817 (1) <=BE BindComplete [unnamed]
13:53:17.817 (1) <=BE CommandStatus(INSERT 0 1)
13:53:17.817 (1) <=BE ReadyForQuery(I)

"Rewritten" batch looks like the following (inserting pairs gives 1.5
times improvement when testing 1024 row inserts):

insert into batch_perf_test(a, b, c) values($1, $2, $3), ($4, $5, $6)

13:53:41.048 (1) batch execute 2 queries,
handler=org(dot)postgresql(dot)jdbc(dot)BatchResultHandler(at)38d611f4, maxRows=0,
fetchSize=0, flags=532
13:53:41.048 (1) FE=>
Bind(stmt=S_1,portal=null,$1=<0>,$2=<'s0'>,$3=<0>,$4=<1>,$5=<'s1'>,$6=<1>)
13:53:41.049 (1) FE=> Execute(portal=null,limit=1)
13:53:41.049 (1) FE=>
Bind(stmt=S_1,portal=null,$1=<2>,$2=<'s2'>,$3=<2>,$4=<3>,$5=<'s3'>,$6=<3>)
13:53:41.049 (1) FE=> Execute(portal=null,limit=1)
13:53:41.049 (1) FE=> Sync
13:53:41.049 (1) <=BE BindComplete [unnamed]
13:53:41.049 (1) <=BE CommandStatus(INSERT 0 2)
13:53:41.049 (1) <=BE BindComplete [unnamed]
13:53:41.049 (1) <=BE CommandStatus(INSERT 0 2)
13:53:41.049 (1) <=BE ReadyForQuery(I)

Vladimir