On Wed, Apr 30, 2014 at 11:02 AM, Peter Geoghegan <pg@heroku.com> wrote:
On Wed, Apr 30, 2014 at 10:11 AM, Robert Haas <robertmhaas@gmail.com> wrote: > I thought the theoretical advantage of hash indexes wasn't that they > were smaller but that you avoided a central contention point (the > btree root).
The B-Tree root isn't really a central contention point at all. The locking/latching protocol that nbtree uses is remarkably concurrency-friendly. In the real world, there is pretty much no exclusive locking of the root page's buffer.
I've seen the simple pinning and unpinning of the root page (or the fast root, whatever the first page we bother to pin on a regular basis is called) be a point of contention. When one index dominates the entire system workload, that one page also drives contention on the spin lock that protects the lwlock that share-protects whichever buffer mapping partition happens to contain it.