There are many types of indexes in Postgres, as well as different ways to use them. In this article we give an overview of the types of indexes available, and explain different ways of using and maintaining the most common index type: B-Trees.
Index Types
Postgres supports many different index types:- B-Tree is the default that you get when you do
CREATE INDEX. The B stands for Balanced, and the idea is that the amount of data on both sides of the tree is roughly the same. Therefore the number of levels that must be traversed to find rows is always in the same ballpark. B-Tree indexes can be used for equality and range queries efficiently. They can operate against numeric, text or NULL values. - Hash Indexes are only useful for equality comparisons, but you pretty much never want to use them since they are not transaction safe, so the advantage over using a B-Tree is rather small.
- Generalized Inverted Indexes (GIN) are useful when an index must map many values to one row, whereas B-Tree indexes are optimized for when a row has a single key value. GINs are good for indexing array values as well as for implementing full-text search.
- Generalized Search Tree (GiST) indexes allow you to build general balanced tree structures, and can be used for operations beyond equality and range comparisons. They are used to index the geometric data types, as well as full-text search.
