Postgres indexes and their types

Indexes are a common way to enhance database performance. An index allows the database server to find and retrieve specific rows much faster than it could do without an index. Without an index, the system would scan the entire table row by row to find all the matching records. But indexes also add overhead to the database system as a whole, so they should be used sensibly.

Following command can be used to create the index on one or more columns of a table.

CREATE INDEX index-name ON table-name (column-names);

While the creation of index only by default Postgres allows only reads to occur on the table. writes(INSERT, UPDATE, DELETE) are blocked. If Index creation takes a long time then it is often unacceptable. Indexes can be created concurrently without blocking writes.

Following command can be used to create index concurrently on one or more columns of a table.

CREATE INDEX CONCURRENTLY index-name ON table-name (column-names);

Command to remove the index

DROP INDEX index-name;

Index Types

Postgres provides several index types namely B-tree, Hash, GIST, SP-GIST, GIN, and BRIN. Each index type uses the different algorithm. By default CREATE INDEX command create B-Tree indexes.

Criteria for using different index types

B-Tree index

B-Tree indexes can be used to handle equality and range queries on data that can be sorted into some order. B-tree index will be considered to use by query planner, whenever the indexed column has any of the following comparison operators.

<, <=, =, >=, >, BETWEEN, IN, LIKE, ~ etc.

Hash index

B-Tree indexes can be used to handle simple equality queries on data. B-tree index will be considered to use by query planner, whenever the indexed column has comparison using = operator.

GIST index

GIST stands for Generalized Search Tree. It is a balanced, tree-structured access method, that acts as a base template in which to implement arbitrary indexing schemes. B-trees, R-trees and many other indexing schemes can be implemented in GIST. Accordingly, the particular operators with which a GIST index can be used vary depending on the indexing strategy (the operator class). the following table has the listing of the operator class, indexed data type, and operators. For more info please visit here.

Following command can be used to create GIST INDEX

CREATE INDEX index-name ON table-name USING GIST (column-name operator-class);

GIN index

GIN stands for Generalized Inverted Index. GIN is designed for handling cases where the items to be indexed are composite values, and the queries to be handled by the index need to search for element values that appear within the composite items. A GIN index stores a set of (key, posting list) pairs, where a posting list is a set of row IDs in which the key occurs. This can be used to the index array, jsonb types of columns.

built-in operator classes

Unique indexes

Uniqueness on the column/columns to be indexed can be enforced by unique indexes. Currently, Only B-tree indexes can be declared unique. When an index is declared unique, multiple table rows with equal indexed values are not allowed. Null values are not considered equal. A multicolumn unique index will only reject cases where all indexed columns are equal in multiple rows. PostgreSQL automatically creates a unique index when a unique constraint or primary key is defined for a table.

Following command can be used to create the unique index on one or more columns of a table.

CREATE UNIQUE INDEX index-name ON table-name (column-names);

Partial indexes

A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The predicate can be defined in WHERE clause. The index contains entries only for those table rows that satisfy the predicate. B-Tree partial indexes can also be declared unique.

Usage of the index for a query can be examined with EXPLAIN command(prepend EXPLAIN in SELECT QUERY). Use ANALYZE command to collect statistics about the distribution of the values in the table. Detailed guidelines can be found here.