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

On Fri, Sep 1, 2017 at 11:05 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On 1 September 2017 at 05:08, Amit Kapila <amit.kapila16@gmail.com> wrote:
>> On Thu, Aug 31, 2017 at 10:46 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
>>> On 31 August 2017 at 17:48, Robert Haas <robertmhaas@gmail.com> wrote:
>>>
>>>> I would argue for
>>>> adding Andres's executor speedups and Amit's hash index work, too
>>>
>>> Do we have accepted/verified perf results we can quote?
>>>
>>
>> The main benefit (as mentioned by Robert) of hash indexes is that now
>> they are durable, but they do better than btree indexes in some use
>> cases (in terms of size and raw TPS) as has been demonstrated with the
>> help of real world workload and by doing performance testing using
>> benchmark pgbench. Just to give some references, please see the posts
>> of some of the users on hackers.
>>
>> Reference about size:
>> https://www.postgresql.org/message-id/20170704105728.mwb72jebfmok2nm2%40zip.com.au
>>
>> Reference about the performance of Select:
>> https://www.postgresql.org/message-id/4575a870-1315-18ac-0516-c21a83a7afdf%40redhat.com
>>
>> Apart from above, I and some of my colleagues have also done
>> performance testing, the results of which are summarized in my PGCon
>> presentation:
>> https://www.pgcon.org/2017/schedule/events/1039.en.html
>>
>> Now, I don't want to say that hash indexes became better in terms of
>> size and performance only because of work in PG-10.  However, there
>> has been substantial work done in PG-10 in both areas and the numbers
>> have been shared on hackers (in case you or others want to see, I can
>> dig down those emails and share it here).
>>
>> I am not sure whether the work done on hash indexes in PG-10 makes it
>> a candidate for the major feature, but I think it is worth
>> considering.
>
> 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.
>

Yes, expansion of hash indexes was a problem and we have done some
work to make them expand more gradually.  See
commit ea69a0dead5128c421140dc53fac165ba4af8520
Author: Robert Haas <rhaas@postgresql.org>
Date:   Mon Apr 3 23:46:33 2017 -0400

    Expand hash indexes more gradually.

> Some concern over write
> performance, especially since no published measurements.
>

Yes, writes/updates on hash indexes are not good and we have done some
work to improve it like in commit 6977b8b7, but I think that needs
more work.

commit 6977b8b7f4dfb40896ff5e2175cad7fdbda862eb
Author: Robert Haas <rhaas@postgresql.org>
Date:   Wed Mar 15 22:18:56 2017 -0400

    Port single-page btree vacuum logic to hash indexes.

> 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?
>

It will be good for cases where the size of index matters and the
index is on columns like UUID, bytea, varchar (basically, the key
length is somewhat bigger say 32 or 64 bytes) and the index data is
not updated frequently.  It should perform well on Read workloads
especially at higher client counts.

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