On 2014-04-30 11:10:22 -0700, Jeff Janes wrote: > 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.
To quite some degree that's an implementation deficiency of our lwlocks though. I've seen *massive* improvements with my lwlock patch for that problem. Additionally we need to get rid of the spinlock around pin/unpin. That said, even after those optimizations, there remains a significant amount of cacheline bouncing. That's much easier to avoid for something like hash indexes than btrees. I think another advantage is that hash indexes can be *much* smaller than btree when the individual rows are wide. I wonder though if we couldn't solve that better by introducing "transforms" around the looked up data. E.g. allow to *transparently* use a hash(indexed_column) to be used. If you currently do that a lot of work has to be done in every query... Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
