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

From: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
To: Merlin Moncure <mmoncure(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>, Peter Geoghegan <peter(at)2ndquadrant(dot)com>, Stefan Keller <sfkeller(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Hash index use presently(?) discouraged since 2005: revive or bury it?
Date: 2011-09-17 22:29:16
Message-ID: CAMkU=1zj0yJgXK2r0-eG=kG2hsVE_N4nGH8+4y3j+dN-_9yDMQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Stefan Keller 2011-09-18 14:59:10 Re: Hash index use presently(?) discouraged since 2005: revive or bury it?
Previous Message Merlin Moncure 2011-09-17 22:14:55 Re: Hash index use presently(?) discouraged since 2005: revive or bury it?