Re: Index Types

On Wed, 14 May 2003, Adam Sherman wrote:

> According to the doco, there are 4 index types (access methods):
>
> - BTREE
> - RTREE
> - HASH
> - GIST
>
> Which type is suited to what kind of applications?

When in doubt, use btree. It supports the operators: <, <=, =, >=, >

When dealing with spacial data, use rtree, operators: <<, &<, &>, >>, @,
~=, &&

Hash trees still have performance issues, so they are generally no faster
than btree.

The docs don't say a lot about GiST. I'm guessing they are in the same
realm as rtree for applicability, but that's just a guess. It looks like
it's not quite done yet. When it is it might be a replacement for both
btree and rtree for many applications.

So, for most production systems you'd pick btree for non spatial data and
rtree for spatial data.

One caveat, is that most versions of postgresql currently in production
have an issue with index bloat of btrees. This issue arises when you do
something like index a date field where the new data is always a later
date than all the other dates, and you remove old dates. What happens is
that you get a lot of empty leaves between the root and the far right hand
side of the tree, and no left side of the tree.

For example, I had a table that was update each night with 1000 new
records, then deleted the oldest 1000. Eventually, my table of well under
1 meg on the hard drive had an 80 meg index file associated with it.

reindexing can fix this, but for these situations, you may find that for
now using a hash index is a decent fix. While they're not as fast as
btrees for most stuff, they don't suffer from bloat, so over a long period
of time, they may be easier on your server since you don't have to worry
about index growth problems.