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]/messages/by-id/55130DC8.2070508@redhat.com, [2]https://github.com/pgjdbc/pgjdbc/pull/491#issuecomment-171780054.
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]https://github.com/pgjdbc/pgjdbc/pull/491#issuecomment-171908974
In fact, the time spent in standard_ExecutorStart even exceeds the
time spent in standard_ExecutorRun.
[1]: /messages/by-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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Andres>I think the biggestoverhead here is that the executor startup
includes
Andres>too many indirect (linked lists) datastructured, that allocated each
Andres>round
The case is very common: batch inserts are popular in Java, and ORMs use
batch API automatically.
However, there's high per-backend-message overhead, and that overhead is
very noticeable.
What is the approach to handle this?
Folding multiple DML statements into one with a help of CTE does not work
either (see https://github.com/pgjdbc/pgjdbc/issues/1165 ):
CTE doc>Trying to update the same row twice in a single statement is not
supported. Only one of the modifications takes place, but it is not easy
(and sometimes not possible) to reliably predict which one
Vladimir