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

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

--
Simon Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services