Обсуждение: Insert values() per-statement overhead

Hi,

There is a finding that insert(x) values(y);insert(x) values(z);  is
2-4 times slower than insert(..) values(y),(z);
see [1], [2].

In other words, there is a significant per-statement overhead even
though server-prepared statements are properly used.
The issue is reproducible in 9.5rc1.

Is it something that was discussed previously? (I was not able to find
that in archives)
Is it something that can be improved? (e.g. "insert node state"
caching across execute calls, improve performance of "INSERT %u %u"
generation, etc)

Even though I understand there will always be _some_ per-statement
overhead, such a hight overhead plays against common case of using
ORMs.
End-users are just stuck with insert(...) values(...);

1) Java's standard way of batching statements is
"PreparedStatement#addBatch()". Unfortunately, backend protocol does
not support statement batching.
One does not simply teach Hibernate/EclipseLink,etc etc to use
PostgreSQL's-specific COPY.
Note: I am not talking about network roundtrips here. I'm just
highlighting that there is no way to execute "bind bind bind
executebatch" sequence at the protocol level.

2) One might consider "transparent rewrite of insert()  batches into a
single insert() values(),(),() statement" at JDBC driver level, but it
is hard to get right as there is no easy way to parse a query. It is
really expected that every PostgreSQL connector would implement SQL
parser & insert rewriter?

3) Transparent rewrites (including "rewrite inserts to COPY") would
fail to provide "number of modified rows" for each row. Error
semantics is different as well.

4) COPY does not support UPSERT, does it?

My profiler (Instruments in Mac OS) shows that significant time is
spent in standard_ExecutorStart: see [3]
In fact, the time spent in standard_ExecutorStart even exceeds the
time spent in standard_ExecutorRun.

[1]: http://www.postgresql.org/message-id/55130DC8.2070508@redhat.com
[2]: https://github.com/pgjdbc/pgjdbc/pull/491#issuecomment-171780054
[3]: https://github.com/pgjdbc/pgjdbc/pull/491#issuecomment-171908974

Vladimir Sitnikov

On 2016-01-15 13:17:12 +0300, Vladimir Sitnikov wrote:
> There is a finding that insert(x) values(y);insert(x) values(z);  is
> 2-4 times slower than insert(..) values(y),(z);
> see [1], [2].

If you indeed just mean statements like above, without begin/commit, a
large portion of the overhead will be transactional overhead. But
I guess you mean there's a transaction surrounding it?

If not, quite possibly what you're describing is client round trip
latency? How exactly are these issued? There'll be some overhead of
running two statements rather than one, but if you have neither added
transactional overhead, nor latency issues, the difference isn't *that*
big in my experience.

> Is it something that was discussed previously?

Yes.

I think the biggestoverhead here is that the executor startup includes
too many indirect (linked lists) datastructured, that allocated each
round. And that some datastructures are computed at execution time
(ExecTypeFromTL I'm looking at you). Unfortunately changing that is not
a small project.

Andres

>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.postgresql.jdbc.BatchResultHandler@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.postgresql.jdbc.BatchResultHandler@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