On Tue, Sep 20, 2016 at 7:55 PM, Bruce Momjian <br...@momjian.us> wrote: >> If it turns out that it has little benefit, then we don't really need >> to step up our user education. People can just keep using btree like > > The big problem is people coming from other databases and assuming our > hash indexes have the same benefits over btree that exist in some other > database software. The 9.5 warning at least helps with that.
I'd be curious what benefits people expect to get. For example, I searched for "Oracle hash indexes" using Google and found this page: http://logicalread.solarwinds.com/oracle-11g-hash-indexes-mc02/ It implies that their hash indexes are actually clustered indexes; that is, the table data is physically organized into contiguous chunks by hash bucket. Also, they can't split buckets on the fly. I think the DB2 implementation is similar. So our hash indexes, even once we add write-ahead logging and better concurrency, will be somewhat different from those products. However, I'm not actually sure how widely-used those index types are. I wonder if people who use hash indexes in PostgreSQL are even likely to be familiar with those technologies, and what expectations they might have. For PostgreSQL, I expect the benefits of improving hash indexes to be (1) slightly better raw performance for equality comparisons and (2) better concurrency. The details aren't very clear at this stage. We know that write performance is bad right now, even with Amit's patches, but that's without the kill_prior_tuple optimization which is probably extremely important but which has never been implemented for hash indexes. Read performance is good, but there are still further optimizations that haven't been done there, too, so it may be even better by the time Amit gets done working in this area. Of course, if we want to implement clustered indexes, that's going to require significant changes to the heap format ... or the ability to support multiple heap storage formats. I'm not opposed to that, but I think it makes sense to fix the existing implementation first. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers