hash index concurrency

I ran a SELECT-only pgbench test today on the IBM POWER7 box with 64
concurrent clients and got roughly 305,000 tps.  Then, I created a
hash index on pgbench_accounts (aid), dropped the primary key, and
reran the test.  I got roughly 104,000 tps. 'perf -g -e cs' suggested
lock contention in _hash_first(), so I whacked out the calls to
_hash_getlock(rel, 0, HASH_SHARE) and _hash_droplock(rel, 0,
HASH_SHARE).  With that change, I got roughly 270,000 TPS.  Taking a
little further, I then changed the definition of USELOCKING() to 0,
effectively removing all the heavyweight page locks.  With that
change, I got 324,000 tps.  Also, with this change, the CPU is fully
saturated - about 77% user time, 23% system time.

I don't immediately have a proposal to deal with this, but it seems
that if we want good hash index performance under high concurrency, we
need to work a bit harder.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company