Index Types
Index Types
От:
Adam Sherman <adam@tritus.ca>
Дата:
According to the doco, there are 4 index types (access methods): - BTREE - RTREE - HASH - GIST Which type is suited to what kind of applications? Thanks, A. -- Adam Sherman Tritus CG Inc. http://www.tritus.ca/ +1 (613) 797-6819
Re: Index Types
От:
Adam Sherman <adam@tritus.ca>
Дата:
On 05/14/03 11:58:53 -0600 scott.marlowe wrote: > So, for most production systems you'd pick btree for non spatial data and > rtree for spatial data. Thanks for another very informative anwser! A. -- Adam Sherman Tritus CG Inc. http://www.tritus.ca/ +1 (613) 797-6819
Re: Index Types
От:
"scott.marlowe" <scott.marlowe@ihs.com>
Дата:
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.
