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
• BRIN
• GiST Indexes
• SP-GiST Indexes

Let’s discuss them in brief.

B-tree indexes

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.

Example:

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

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 indexes

GIN stands for Generalized Inverted Indexes. It is commonly referred to as GIN.

GIN indexes are most useful when you have multiple values stored in a single column, for example, hstore, array, jsonb, and range types.

BRIN

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 Indexes

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 Indexes

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.