Balanced tree index is considered as the most efficient index type in Postgres database system. As name implies, the index structure is balanced between its left and right side.
Theoretically it requires same travelling distance to any leaf page entry. It can be used to locate a single value or a range of values efficiently if the indexed column is involved in comparison categories of:
- equalgreater than,
- smaller than or
- combinations of these operators.
B-tree index can also assist in string comparisons such as LIKE, pattern matching if the comparison tries to match the beginning part of the column. However in case of string comparison, if your database uses other locale other than C, one will need to create index with a special mode for locale sensitive character by character comparison.
In the next article, we'll consider Hash Indexes and GiST.
Top comments (0)