We've discussed my misgivings about hash indexes face to face, so forgive me if I repeat some of them here.
Hash indexes work well for equality lookups on unique data, yet do not yet themselves enforce uniqueness, so you are forced to have a btree anyway. Expanding the hash index gives operational issues and we have no measurements of the effects of that - not something we should be letting people discover in production. Some concern over write performance, especially since no published measurements.
BRIN suffered from people misunderstanding its use case, so perhaps we can avoid a repeat of that.
Are we safe to draw attention to these indexes, for a particular use case? Can we get a clear statement of what that is? If we can, I would incline towards adding them to the major items list.
I would like to second this and add a note.
I ran a small benchmark myself on tables inserting large numbers of uuids (5 million). These went first into a holding table. Then in the benchmark I did an insert .... select....;
Three tables:
1. Unindexed (control)
2. Btree
3. Hash
What I found was that in my tests, hash indexes were marginally faster for lookups.
Btrees handled inserts far better (20% improvement *worst case* and 300% improvement *best case*)
So from this I concluded that this was not the use case for hash indexes.
But I would be very interested in where the use cases are.
Best Wishes,
Chris Travers
-- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
