Skip site navigation (1) Skip section navigation (2)

Re: Behavior of hash index on a text field

From: "David Monarchi" <david(dot)e(dot)monarchi(at)gmail(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Behavior of hash index on a text field
Date: 2007-04-01 13:56:34
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
Thanks for the insight, Tom.  I had read that Postgres hash indexes didn't
work too well, but thought that had been remedied in 8.2.  Do you have any
thoughts about btree versus GiST or GIN for text data with very few
duplicate values?


On 3/31/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "David Monarchi" <david(dot)e(dot)monarchi(at)gmail(dot)com> writes:
> > I believe (but don't know) that a hash index would be better for this
> than a
> > btree.
> A fairly reliable rule of thumb is that there isn't *any* situation
> where a Postgres hash index outperforms a btree.
> Why this is so is not entirely clear, and various people keep poking
> at the code in hopes of making it better.  Sooner or later we'll either
> succeed in getting hash indexes to be a win for specific use cases,
> or give up and drop them entirely.
> But at present it is undeniable that Postgres hash indexes are not
> production quality.  (Even if they had a performance win, their
> current lack of WAL backup makes them unfit for production use...)
>                         regards, tom lane

In response to

pgsql-novice by date

Next:From: Bob McConnellDate: 2007-04-01 20:25:32
Subject: Re: Updating old code for new engine
Previous:From: Tom LaneDate: 2007-04-01 06:45:30
Subject: Re: Updating old code for new engine

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group