Re: [PERFORM] Improving PostgreSQL insert performance
Vladimir Sitnikov wrote:
> Alvaro>Something like
> INSERT INTO .. VALUES ('col1', 'col2'), ('col1', 'col2'), ('col1', 'col2')>I
> did not
> Frits>try that, to be honest.
>
> pgjdbc does automatically rewrite insert values(); into insert ...
> values(),(),(),() when reWriteBatchedInserts=true. I don't expect manual
> multivalues to be noticeably faster there.
Ahh, so that's what that option does :-) Nice to know -- great feature.
--
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Improving PostgreSQL insert performance
Alvaro>Something like
INSERT INTO .. VALUES ('col1', 'col2'), ('col1', 'col2'), ('col1', 'col2')>I
did not
Frits>try that, to be honest.
pgjdbc does automatically rewrite insert values(); into insert ...
values(),(),(),() when reWriteBatchedInserts=true. I don't expect manual
multivalues to be noticeably faster there.
Frits>https://etc.to/confluence/display/~admjal/PostgreSQL+performance+tests
Do you really intend to measure just a single insert operation?
It looks odd, as typical applications would execute inserts for quite a
while before they terminate.
You are including lots of warmup overheads (e.g. JIT-compilation), so your
approach does not measure peak performance.
On the other hand, you are not measuring enough time to catch things like
"DB log switch".
Would you please use JMH as a load driver?
Here's an example:
https://github.com/pgjdbc/pgjdbc/blob/master/ubenchmark/src/main/java/org/postgresql/benchmark/statement/InsertBatch.java
Vladimir
>
Re: [PERFORM] Improving PostgreSQL insert performance
Hi Alvaro, I did not try that, to be honest. I am using a single prepared statement so that the database needs to parse it only once. All executes then use the batched parameters. I will try this later on, but I wonder whether having to reparse the statement every time compared to one prepared statement would actually be faster. But thanks for the tip; I will take a look. Regards, Frits
