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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
