PostgreSQL – Index Types
In this article, we will look into the index types in PostgreSQL and how to use them appropriately.
PostgreSQL has 6 primary index types:
- B-tree indexes
- Hash indexes
- GIN indexes
- GiST Indexes
- SP-GiST Indexes
Let’s discuss them in brief.
B-tree is a self-balancing tree that maintains sorted data and allows searches, insertions, deletions, and sequential access in logarithmic time.
PostgreSQL query planner will consider using a B-tree index whenever index columns are involved in a comparison that uses one of the following operators:
< <= = >= BETWEEN IN IS NULL IS NOT NULL
In addition, the query planner can use a B-tree index for queries that involve a pattern matching operator LIKE and ~ if the pattern is a constant and is anchor at the beginning of the pattern.
column_name LIKE 'foo%' column_name LIKE 'bar%' column_name ~ '^foo'
Furthermore, the query planner will consider using B-tree indexes for ILIKE and ~* if the pattern starts with a non-alphabetic character which are the characters that are not affected by upper/lower case conversion.
Hash indexes can handle only simple equality comparison (=). It means that whenever an indexed column is involved in a comparison using the equal(=) operator, the query planner will consider using a hash index.
To create a hash index, you use the CREATE INDEX statement with the HASH index type in the USING clause as follows:
Syntax: CREATE INDEX index_name ON table_name USING HASH (indexed_column);
GIN stands for Generalized Inverted Indexes. It is commonly referred to as GIN.
BRIN stands for Block Range Indexes. BRIN is much smaller and less costly to maintain in comparison with a B-tree index.
BRIN allows the use of an index on a very large table that would previously be impractical using B-tree without horizontal partitioning. BRIN is often used on a column that has a linear sort order, for example, the created date column of the sales order table.
GiST stands for Generalized Search Tree. GiST indexes allow a building of general tree structures. GiST indexes are useful in indexing geometric data types and full-text search.
SP-GiST stands for space-partitioned GiST. SP-GiST supports partitioned search trees that facilitate the development of a wide range of different non-balanced data structures. SP-GiST indexes are most useful for data that has a natural clustering element to it and is also not an equally balanced tree, for example, GIS, multimedia, phone routing, and IP routing.