B-trees are designed to work very well with caching, even when only partially cached. They can operate against all datatypes, and can also be used to retrieve NULL values. B-tree indexes can be used for equality and range queries efficiently. Therefore the number of levels that must be traversed to find rows is always in the same ballpark. B-trees attempt to remain balanced, with the amount of data in each branch of the tree being roughly the same. Virtually all databases have some B-tree indexes. B-Tree is the default that you get when you do CREATE INDEX.Postgres supports many different index types: B-tree indexes are also useful for avoiding sorting. It’s only useful if the number of rows to be retrieved from a table is relatively small (that is, the condition for retrieving rows - the WHERE clause - is selective). 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.Īn index is a way to efficiently retrieve a relatively small number of rows from a table. There are many types of indexes in Postgres, as well as different ways to use them.
