Re: type of index?

Neil Conway <nconway(at)klamath(dot)dyndns(dot)org> writes:
> Should the index on this_col be a btree or a hash index? By default, it
> seems like Postgres is creating a btree index. But according to the
> PgSQL docs, a hash index could also be used. Which would result in
> better performance? Also, I've read in the list archives that
> btree indexes are much better, in general, than the others. Given this,
> which index is the best? Is there some of rule of thumb I can use
> to decide for this and other cases?

Right at the moment I see no good reason to use the hash index code at
all. It does nothing for you that btree doesn't do; it is known to have
problems with concurrent updates (you risk deadlocks in hash, but not in
btree); and it's not nearly as well tested/debugged as the btree code.

The rtree and gist index types have the same concurrency and robustness
question marks as hash does, but at least they offer you something in
return: support for query types that btree can't handle.

Eventually I'd like to see all these index types brought up to similar
quality standards as btree, but the facts on the ground right now are
that they are poor second cousins. Unless you've got *good* reasons
for choosing another index type, btree is the way to go.

regards, tom lane