PostgreSQL Index Types

In this tutorial, you will learn about various PostgreSQL index types and how to use them appropriately.

PostgreSQL has several index types: B-tree, Hash, GiST, SP-GiST, GIN, and BRIN.

Each index type uses a different storage structure and algorithm to cope with different kinds of queries.

B-Tree İndex

It is used for equality and range queries on data that can be sorted in a certain order.

These indexes are also used in queries such as beetween, is null, is not null and LIKE.

B-tree indexes are indexes used in sorting.

If the USING parameter is not used when creating the index in PostgreSQL, a b-tree index is created by default.

HASH İndex

Hash indexes are used for simple equality comparisons.

The query made using the “=” operator will use the hash index.

Attention: Since hash indexes are not written to the wall log in versions 9.6 and 9.5, reindexing is required when the service is closed.

Therefore, it is not recommended to use it in these versions.

This problem has been removed after version 9.6.

Example usage is as follows.

GIN INDEX

GIN (Generalized Inverted) indexes are used for hstore, array, jsonb and range data types.

GIST Index

In GIST indexes, there is not only one type of indexing, but more than one type of indexing.

The specific operators that the GIST index can use vary depending on the indexing strategy.

The operators used in gist indexes are given below.

<<, &<, &>, >>, <<|, &<|, |&>, |>>, @>, <@, ~=, &&

The operators and corresponding data types used by GIST indexes are as follows.

SP-GIST Index

It supports various search types.

SP-GIST allows the implementation of disk-based data structures such as indexes, such as quadtrees, k-d trees, and radix trees (tries).

SP-GIST indexes support queries using two-dimensional points.

Indexes using SP-GIST are as follows:

<<, >>, ~=, <@, <^, >^

SP-GIST operator classes are as follows.

BRIN INDEX

BRIN (Block Range Index) indexes keep summaries of the values ​​held in consecutive physical block ranges of a table.

The specific operators that the BRIN index can use, rather than other index types, vary depending on the indexing strategy.

BRIN indexes are used in large tables where B-Tree indexes are insufficient.