As discussed in “A Grand Piano for Your Violin”, indexes are quite useful for speeding up database queries.
When creating an index using
CREATE INDEX, Postgres will create a B-Tree
type index by default. The B-Tree type is great for general purpose indexes but
there are special cases when other types provide better results.
Postgres 9.5 ships with a variety of index types that use distinct algorithms to speed up different queries. This article provides an overview of the available types and their use-cases.
USING clause to tell Postgres what type of index to create:
CREATE INDEX band_member_names ON bands USING GIN (member_names);
The B-Tree index type uses a balanced tree structure to speed up equality and range queries on columns of all data types. Since B-Tree index entries are sorted, they are sometimes used to retrieve table rows in order, thereby avoiding to manually sort them after retrieval.
Hash indexes can only be used for equality comparisons. They are not
transaction safe, won’t be replicated over streaming or file-base replication,
and might need to be rebuilt manually with
REINDEX after a crash. Because of
this, using hash indexes is generally discouraged.
Generalized Inverted indexes are great for indexing columns and expressions
that contain more than one value. Good examples are array columns, text search
tsvector), and binary json documents (
GiST stands for Generalized Search Tree and isn’t a single indexing scheme but rather an infrastructure that makes it possible to implement indexing schemes for new data types by providing a balanced tree-structured access method.
GiST indexes are used for operations beyond equality and range comparisons and the standard Postgres distribution includes GiST operator classes for geometric data types, network addresses, range types, and text search documents.
Space partitioned GiST is also an infrastructure for implementing new indexing schemes, but instead of providing balanced tree-structures it provides partitioned search trees. These can be used to implement different types of non-balanced data structures that can yield fast lookups if they are partitioned using a rule that matches the queries they are used for.
Block range indexes are designed to handle very large tables in which the rows’ natural sort order correlates to certain column values. For example, a table storing log entries might have a timestamp column for when each log entry was written. By using a BRIN index on this column, scanning large parts of the table can be avoided when querying rows by their timestamp value with very little overhead.
To learn more about Postgres indexes and performace, be sure to check out How to Create Postgres Indexes Concurrently in ActiveRecord Migrations and PostgreSQL performance considerations.