Re: Why is Hash index not transaction safe. - Mailing list pgsql-novice

On Wed, May  6, 2015 at 02:02:23PM +0800, Wei Shan wrote:
> Hi Sameer,
>
> Nice to see you around!
>
> The developer wanted to use HASH indexes as the query hitting some columns are
> only using equality comparison (=). They said that HASH has a speed of O(1)
> whereas B-Tree has a speed of logarithm. I believe it's true if you look
> fundamentally at hashtable and B-Tree algorithm.
>
> However, I did my research and found that HASH index has minimal perfomance
> improvement over B-Tree in equality comparison.
>
> Thus, we dropped the idea already :)

Yes, we have heard this many times, and PG 9.5 will issue a warning if
you create a hash index:

    WARNING:  hash indexes are not WAL-logged and their use is discouraged

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +