Re: What is wrong with hashed index usage?

> -----Original Message-----
> From: Neil Conway [mailto:nconway(at)klamath(dot)dyndns(dot)org]
> Sent: Monday, April 22, 2002 2:59 PM
> To: Dann Corbit
> Cc: pgsql-hackers(at)postgreSQL(dot)org
> Subject: Re: [HACKERS] What is wrong with hashed index usage?
>
>
> On Mon, 22 Apr 2002 14:15:37 -0700
> "Dann Corbit" <DCorbit(at)connx(dot)com> wrote:
> > From here:
> > http://osdb.sourceforge.net/
> > We find this quote:
> > "For you long-suffering OSDB PostgreSQL users, we offer
> >
> > --postgresql=no_hash_index
> >
> > to work around the hash index problems of OSDB with
> PostgreSQL V7.1 and
> > 7.2. As always, let us know of any problems. May the source be with
> > you!"
> >
> > Does anyone know what the above is all about?
>
> Yes -- search the list archives, or check the PostgreSQL
> docs. This problem
> has been brought up several times: hash indexes deadlock
> under concurrent
> load. A run of pgbench with a reasonably high concurrency
> level (10 or 15)
> produces the problem consistently.
>
> Previously, I had volunteered to fix this, but
>
> (a) I'm busy with the PREPARE/EXECUTE stuff at the moment.
>
> (b) I'm not sure it's worth the investment of time: AFAIK,
> hash indexes don't have many advantages over btrees for
> scalar data.
>
> On the other hand, if someone steps forward with some data on a
> specific advantage that hash indexes have over btrees, I don't
> expect that the concurrency problems should be too difficult to
> solve.

Here is where a hashed index shines:
To find a single item using a key, hashed indexes are enormously faster
than a btree.

That is typically speaking. I have not done performance benchmarks with
PostgreSQL.

In general, hashed indexes are much to be preferred when you are doing
frequent keyed lookups for single items. Hashed indexes are (of course)
completely useless for an ordered scan or for wide ranges of continuous
data.