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

Criteria for using different index types

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

built-in operator classes

Unique indexes

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

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.

Product Engineer at Gojek.

Product Engineer at Gojek.