Hello,
The attached patch speeds up transaction completion when any prior transaction accessed many relations in the same
session.
The transaction records its own acquired lock information in the LOCALLOCK structure (a pair of lock object and lock
mode). It stores LOCALLOCKs in a hash table in its local memory. The hash table automatically expands when the
transactionacquires many relations. The hash table doesn't shrink. When the transaction commits or aborts, it scans
thehash table to find LOCALLOCKs to release locks.
The problem is that once some transaction accesses many relations, even subsequent transactions in the same session
thatonly access a few relations take unreasonably long time to complete, because it has to scan the expanded hash
table.
The attached patch links LOCALLOCKS to PGPROC, so that releasing locks should only scan the list instead of the hash
table. The hash table is kept because some functions want to find a particular LOCALLOCK quickly based on its hash
value.
This problem was uncovered while evaluating partitioning performance. When the application PREPAREs a statement once
andthen EXECUTE-COMMIT repeatedly, the server creates a generic plan on the 6th EXECUTE. Unfortunately, creation of
thegeneric plan of UPDATE/DELETE currently accesses all partitions of the target table (this itself needs improvement),
expandingthe LOCALLOCK hash table. As a result, 7th and later EXECUTEs get slower.
Imai-san confirmed performance improvement with this patch:
https://commitfest.postgresql.org/22/1993/
Regards
Takayuki Tsunakawa
