PostgreSQL Index Types

This article describes the types of indexes in PostgreSQL and how to use them properly.

PostgreSQL supports several different types of indexes: B-tree, Hash, GiST, SP-GiST, GIN, and BRIN. Each index type uses different storage structures and algorithms to handle different types of queries. Different data may use different index types.

PostgreSQL uses the B-tree index type by default because it works best for the most common queries. When you use the CREATE INDEX statement to create an index, if you don’t specify the index type, B-tree is used by default.

B-tree indexes

A B-tree is a self-balancing tree that maintains sorted data.

The PostgreSQL query planner will consider a B-tree index whenever an indexed column involves a comparison using one of the following operators:

  • <
  • <=
  • =
  • >=
  • BETWEEN
  • IN
  • IS NULL
  • IS NOT NULL

Additionally, if the pattern is a constant and there is an anchor at the beginning of the pattern, the query planner can use a B-tree index for queries involving pattern matching operators LIKE and ~, for example:

column_name LIKE 'foo%'
column_name LKE 'bar%'
column_name  ~ '^foo'

Instead, B-tree indexes are not used if the comparison is col LIKE '%bar'.

For the pattern matching operators ILIKE and ~*, the query planner will consider a B-tree index if the pattern begins with a non-alphabetic character (a character that is not affected by case).

If you’re already using indexes to optimize your PostgreSQL database, a B-tree index might be what you’re looking for.

Hash indexes

Hash indexes can only handle simple equality comparisons (=). This means that whenever an indexed column is compared using the = operator, the query planner will consider a hash index.

To create a hash index, use the statement CREATE INDEX with HASH index-type in the USING clause as follows:

CREATE INDEX index_name
ON table_name USING HASH (indexed_column);

GIN indexes

GIN indexes are “inverted indexes”. It’s great for indexing complex values ​​(like array, hstore, json and range).

An inverted index that contains a separate entry for each constituent value can efficiently handle queries that test for the existence of a given constituent value.

GIN indexes are most useful when you store multiple values ​​in a single column.

BRIN index

BRIN stands for Block Range Indexes and stores summary information about values ​​stored on contiguous physical block ranges of a table. BRIN is a new index type in PostgreSQL 9.5. Compared to B-tree indexes, BRINs are smaller and less expensive to maintain.

BRIN allows the use of indexes on very large tables that were previously impractical with B-trees without horizontal partitioning.

BRIN is often used for columns that have a linear sort order, such as the creation date column of a sales orders table.

GiST index

GiST indexes are not a single index type, but an architecture on which many different indexing strategies can be implemented.

GiST stands for Generalized Search Tree. GiST indexes allow building general tree structures. GiST indexes can be used to index geometric data types and full-text searches.

SP-GiST index

SP-GiST stands for Spatial Partitioned GiST. SP-GiST supports partitioned search trees, which facilitate the development of a variety of different unbalanced data structures.

SP-GiST indexes are best for data that has natural clustering elements but is also not a balanced tree, such as GIS, multimedia, telephony routing, and IP routing.

Conclusion

This article describes various PostgreSQL index types, including B-tree, Hash, BRIN, GiST, and SP-GiST.