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.
Use a USING clause to tell Postgres what type of index to create:
CREATE INDEX band_member_names
ON bands
USING GIN (member_names);
B-Tree
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
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.
GIN
Generalized Inverted indexes are great for indexing columns and expressions that
contain more than one value. Good examples are array columns, text search
documents (tsvector), and binary json documents (jsonb).
GiST
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.
SP-GiST
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.
BRIN
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.
What’s next?
To learn more about Postgres indexes and performance, be sure to check out How to Create Postgres Indexes Concurrently in ActiveRecord Migrations and PostgreSQL performance considerations.
