SAVEPOINTs and COMMIT performance

As part of a performance investigation for a customer I've noticed an
O(N^2) performance issue on COMMITs of transactions that contain many
SAVEPOINTs. I've consistently measured COMMIT times of around 9 seconds,
with 49% CPU, mostly in LockReassignCurrentOwner().

BEGIN;
INSERT...
SAVEPOINT ...
INSERT...
SAVEPOINT ...
... (repeat 10,000 times)
COMMIT;

The way SAVEPOINTs work is that each is nested within the previous one,
so that at COMMIT time we must recursively commit all the
subtransactions before we issue final commit.

That's a shame because ResourceOwnerReleaseInternal() contains an
optimisation to speed up final commit, by calling ProcReleaseLocks().

What we actually do is recursively call LockReassignCurrentOwner() which
sequentially scans LockMethodLocalHash at each level of transaction. The
comments refer to this as "retail" rather than the wholesale method,
which never gets to execute anything worthwhile in this case.

This issue does NOT occur in PLpgSQL functions that contain many
EXCEPTION clauses in a loop, since in that case the subtransactions are
started and committed from the top level so that the subxact nesting
never goes too deep.

Fix looks like we need special handling for the depth-first case, rather
than just a recursion loop in CommitTransactionCommand().

Issues looks like it goes all the way back, no fix for 9.0.

I notice also that the nesting model of SAVEPOINTs also means that
read-only subtransactions will still generate an xid when followed by a
DML statement. That's unnecessary, but required given current design.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services