Re: USING HASH considered harmful?

On Thu, Aug 16, 2001 at 09:59:02PM -0400, Bruce Momjian wrote:
>
> Checking application/pgp-signature: FAILURE
> -- Start of PGP signed section.
> > We've just discovered a rather nasty feature of hashes, namely that
> > simultaneous reads & writes to a single row will deadlock if there
> > is a hash index on the table.
> >
> > I guess this is because PG really has to lock the hash table entry in
> > both cases. It does, however, make HASH indices completely useless for
> > any table that you might want to update.
> >
> > Is this a known feature?
>
> Yes, I have heard about this problem. Would you test btree vs hash and
> report back which is faster. I have requested this from >20 people and
> no one reported back.

I changed to hash because it was marginally faster for the queries
we were doing - maybe 5% or so. It was very marginal, but we
need every bit of performance we can get.

Stephen