Re: [pgsql-advocacy] PostgreSQL 10: Call for Quotes

On Fri, Sep 1, 2017 at 11:13 AM, Chris Travers <chris.travers@gmail.com> wrote:
>
>
> On Fri, Sep 1, 2017 at 7:35 AM, Simon Riggs <simon@2ndquadrant.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
>

Did you notice the size of indexes and what happens if you double the data?

> What I found was that in my tests, hash indexes were marginally faster for
> lookups.
>

At what client count (how many simultaneous clients were accessing
hash index), did you tried this on higher client counts?

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com