Re: Hash index use presently(?) discouraged since 2005: revive or bury it?

On Fri, Sep 16, 2011 at 12:38 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I'm not entirely following this eagerness to junk that AM, anyway.
> We've put a lot of sweat into it over the years, in the hopes that
> it would eventually be good for something.  It's on the edge of
> being good for something now, and there's doubtless room for more
> improvements, so why are the knives out?

There are lots of issues with hash tables. I'm not going to enumerate
them, you probably know them better than I.

But the reality of it is that btree on hash values is a very useful
index kind. It has stable performance, is very compact, and supports
any type, even user defined, if the hashing function can be
customized. They're better than hashes in all my tests for my use case
(which is indexing over a column with long strings), and the only
drawback is that they have to be supported by application code.

If PG could have a native implementation, I'm sure lots of people
would find it useful.

Maybe scrapping the hash index is too much, but support for indexing
with btree with hashing would be very neat.

I read recently hash removed the need to store the value in the index,
so I don't expect such a wrapper to be difficult to write.