Re: PostgreSQL 10: Call for Quotes

On Fri, Sep 1, 2017 at 7:35 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> 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 (pgsql-advocacy(at)postgresql(dot)org)
> 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