2

Which one is generally more efficient for batch updates between these two forms for the same operation?

Option 1:

UPDATE table SET a = val.b FROM (VALUES (0,1), (2,3)) AS val(b,id) WHERE table.id = val.id

Option 2:

UPDATE table SET a = 0 WHERE table.id = 1;
UPDATE table SET a = 2 WHERE table.id = 3;

I found this in the doc:

When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the from_item list, and each output row of the join represents an update operation for the target table.

Does it mean that the two options are equivalent under the hood?

For option 1, I wonder if Postgres will only do index updates only once at the end, after all the rows are updated, or once for each row being updated. The former should be more efficient than the latter, which would make option 1 better than option 2.

1
  • The main difference is: the first one will always be run as a single transaction. The second might not
    – user1822
    Jul 9, 2022 at 10:28

1 Answer 1

1

Merely delaying the index maintenance to the end is unlikely to increase the efficiency--that would require an algorithm change, not just a delay. The same work still has to be done. For GIN indexes, there is a "fast update" mechanism but if it is turned on then it will get applied to both cases. If more/better "fast update"-like features get implemented, they will likely be implemented in a similar way--so that it improves both cases.

The big advantages of using the first method is that it doesn't need to flush WAL between each update, doesn't need to parse/analyze/plan each update, and doesn't need to do IPC/context swap/network round trip to the client for each update. But the client could ameliorate these issues by using explicit transactions, prepared statements, or pipelining.

If the values list is quite long, it might be faster to put it into a hash table and then read all of the target table and probe the hash table with it, rather than doing a loop over the values list with a nested index scan on the target table. With the 2nd method, it is effectively forced to do the nested loop even if that is slower.

1
  • Thanks. So if we assume the 2nd option is run in a single transaction, then there's negligible difference between the two.
    – Alvin
    Jul 10, 2022 at 22:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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