On 04/08/2011 04:59 AM, Szymon Guz wrote:
Hi, this is maybe a stupid question, but I don't know how to explain to my coworkers why performing many inserts in autocommit mode is so much slower than making all of them in one transaction. Why is that so slow?
Unless you have synchronous_commit disabled and/or a commit_delay, each commit requires synchronization of all outstanding writes to the disk before the commit can return success. That stops the OS intelligently reordering and batching the writes of multiple statements for better disk I/O efficiency. It also adds pauses before each statement can return, effectively adding latency between statements.
The trade-off you get is that after each statement, you know for certain that the results of that statement are on-disk and safe. That's not the case when you do them all in one transaction, or with synchronous_commit off.
BTW, the other area to look at with this is query batching. If the client isn't on the same host as the server or at least on a very low latency network, it can be much more efficient to batch queries together to minimize the number of network round trips required.
-- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
