9

In my app I do multi-row inserts when I can just because it reduces the number of round trips between the db and the app.

However, I was curious, is there any other advantages? For example, if multiple rows are inserted at once like this:

insert into tbl (c1, c2) values
(v1, v2)
(v3, v4)

versus:

insert into tbl (c1, c2) values (v1, v2)
insert into tbl (c1, c2) values (v3, v4)

and the table has an index, is the index calculated once in the first case, and twice in the second case? Or is it always once per insert? Assume that both queries are in the same transaction.

I'm using PostgreSQL.

  • 2
    I think the index is updated it's once per statement, not once per row. So the single statement should be more efficient than the two statements. But I am not sure (hence no answer, just a comment) – a_horse_with_no_name Sep 11 '14 at 17:57
  • 1
    I think the right question is, is it encapsuled in the same transaction. Out of the stomach, if there are both in a transaction, there wont be a difference. – user1363989 Sep 11 '14 at 19:21
  • @user1363989, I've updated my question to indicate that both queries would be in the same transaction – cdmckay Sep 11 '14 at 20:56
5

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.

  • If the queries are batched in a transaction, will the index update happen once-per-statement? Will there be a performance difference due to anything else? I'm unable to access either article: could you please re-link? – batbrat Feb 12 '17 at 18:02
1

I don't be believe there are currently any optimization differences in the area of index maintenance in this regard.

In addition to network overhead, the other advantages of the multi-value statement are in the parsing, locking, etc. (Even though the table lock on tbl is taken by the first insert statement and then maintained throughout the rest of the transaction, each insert statement still has to verify that the lock is held, and this verification is not free)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.