PostgreSQL Performance – Single Statement vs Multiple Row Inserts

I think @a_horse_with_no_name is right about that the index is updated once per statement, because if the statement has not completed its execution the data will not be visible since it is in transaction. And the definition of a statement includes having multiple values

And accoriding to the docs here index creation/update works more efficient with batches than single statements.

There is also a nice article from Peter Manis about the performance of various Insert methods which I can recommend.

Another thing to keep in mind is the FILLFACTOR of the index as it does have an impact on performance as Fabien Coelho describes here.

The next to last major step was to find out that it is, unfortunately for MySQL users, only MySQL that joins slowly, so these write intensive tables were all given unique integer identifiers to join upon. That took off 1/3 of the remaining average time consumption reduction.

The last step was taking CTE optimization fencing into account which took off the last 2/3 of reduction.

In the case of the above set of queries, the average time consumed is now between 1 and 2ms on an i7 laptop tuned for SSDs even though I'm using the included hard drive. On a server with a single SSD, the time consumed averages less than 1ms.

To get the last performance boost, almost all queries were condensed, so for example if a table needed to be updated, it's best to do it in one query no matter how strange or performance reducing it might appear.

Reading to assemble relevant primary key values required more testing. If it was a combination of RETURNINGs, it was best to allow each sub-statement to recalculate it rather than depend upon another sub-statement to do the calculation. If the read had to go to disk, even if it was small, it was best to do that once and reference it across the multiple writing queries.

Copying was another grey area. If a table required inserts, updates, and deletes based upon data from another table, it was slower to pre-select the data. It was faster to simply reference the primary key data.

In general, aside from reads that go to disk to assemble primary key values to reference, it is best to compress a CTE as much as possible though it may appear strange to keep the chains as short and narrow as possible.

Multi-prepared statement transactions that previously consumed 10s of ms in my application now consume max 5ms.

This approach should probably be limited to small amounts of redundant data.