Re: HASH: Out of overflow pages. Out of luck

"Gene Selkov, Jr." <selkovjr(at)xnet(dot)com> writes:
> I saw Tom answer a similar question a year ago, by saying that the
> hash access method is poorly supported and that there is no advantage
> to using it. I am not sure about the former, but the latter is not
> entirely true: we saw at least 20% gain in performance when we
> switched from btree to hash, and my boss considers 20% a big enough
> improvement. Besides, he knows the database theory and he is a
> long-time BerkelyDB user, and in his world, hash is greatly superior
> to btree, so he is wondering why are the postgres implementations so
> close. Besides, it's a tough challenge to explain it to a Libertarian
> that he'd better not do something.

Hey, if he wants to fix the hash code, more power to him ;-). Patches
will be gladly accepted.

The real problem with the PG hash index code is that approximately zero
effort has been put into it since the code left Berkeley, while quite
a lot of work has been put into the btree code. Thus, for most purposes
the btree index type leaves hash in the dust, no matter what theoretical
concerns may say.

If you or he would like to expend the effort to bring hash indexing up
to speed, I'll surely not stand in your way. But be advised that
there's a lot of work to be done there (concurrency issues and WAL
support being at the top of my list) ... are you sure you believe that
hash is worth the effort?

regards, tom lane