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

On Thu, Sep 15, 2011 at 9:20 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>
> odd: I was pondering Claudio's point about maintenance of hash indexes
> vs btree and decided to do some more tests.  Something very strange is
> happening:  I decided to compare 'update v set x=x+1', historically
> one of postgres's weaker points, on the 10M table indexed hash vs
> btree.  The btree typically muddled through in about 5 minutes:
>
> postgres=# update v set x=x+1;
> UPDATE 10000000
> Time: 302341.466 ms
>
> recreating the table and hash index, I ran it again. 47 minutes into
> the query, I started to get curious and noticed that cpu time disk
> usage are hovering near zero but nothing is blocked. disk space on the
> index is *slowly* increasing, now at:
> 09/15/2011  11:08 PM       541,024,256 16531

The way you created the table, I think the rows are basically going to be
in order in the table, which means the btree index accesses are going to
visit the same block over and over again before going to the next block.

With hash indexes, it will jump all over the place.

Cheers,

Jeff