Re: PostgreSQL 10: Call for Quotes

On Fri, Sep 1, 2017 at 7:35 AM, Simon Riggs <[email protected]> wrote:

>
> This is very good work, well done.
>
> 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
>
>
> --
> Sent via pgsql-advocacy mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-advocacy
>



-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more