Asheet Kumar

Postgres Indexes and Their Types

| Comments

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 index system would scan 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 index on one or more coulmns of a table.

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

While 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 longer time then it is often unacceptable. Indexes can be created concurently without blocking writes.

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

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

Command to remove index

1
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 different algorithm. By defualt 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 ordering. B-tree index will be considered to use by query planner, whenever indexed column has any of following comparison operators.

1
2
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 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). following table has listing of operator class, indexed data type and operators. For more info please visit here.

Name Indexed Data Type Indexable Operators Ordering Operators
box_ops box && &> &< &< >> << <<| <@ @> @ |&> |>> ~ ~= |
circle_ops circle && &> &< &< >> << <<| <@ @> @ |&> |>> ~ ~= | <->
inet_ops inet, cidr && >> >>= > >= <> << <<= < <= =
point_ops point >> >^ << <@ <@ <@ <^ ~= <->
poly_ops polygon && &> &< &< >> << <<| <@ @> @ |&> |>> ~ ~= | <->
range_ops any range type && &> &< >> << <@ - - = @> @> |
tsquery_ops tsquery <@ @>
tsvector_ops tsvector @@

source

Following command can be used to create GIST INDEX.

1
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 index array, jsonb types of columns.

built-in operator classses

Unique indexes

Uniqueness on the coulmn/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 unique index on one or more coulmns of a table.

1
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). 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 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.

Comments