DB indexes

Indexes in DB are very important to speed up the retrieval of tuples and thus to make query execution more efficient. PostgreSQL supports a rich set of index types, each optimized for different kinds of queries and data structures. This document only covers the main types of indexes but more details can be found on the official documentation.

An index speeds up read access but slows down write and update statements.

B-tree Index

It is the default time of index materialized with balanced tree structure optimized for general-purpose queries. It is used for:

  • Equality comparisons (=, IN)
  • Range queries (<, <=, >, >=)
  • Sorting / ORDER BY

Here is a typical example to create such index:

1

CREATE INDEX ON users (last_name);

Hash Index

Hash indexes rely, as indicated by their name, on hash function to perform fast equality search. It is thus used:

  • Only equality comparisons (=)

Fast for equality lookups but not range queries, still rarely used because B-tree usually performs as well or better.

1

CREATE INDEX USING hash ON users (email);

GIN (Generalized Inverted Index)

This type of index best suits for documents, arrays, and cases with many indexed elements per row. It supports fast containment queries, but is slow to update. It is generally used for:

  • Full-text search
  • JSONB queries
  • Array membership (@>, <@, &&)
  • hstore

Here are some examples of definitions of GIN indexes:

1
2
3
4
5
6
7
8

-- JSONB
CREATE INDEX ON products USING gin (data);

-- Full text
CREATE INDEX ON docs USING gin (to_tsvector('english', content));

-- Array
CREATE INDEX ON tags USING gin (tag_list);

GiST (Generalized Search Tree)

This more specific type of index is mainly use for data that isn’t well ordered—like geometry, ranges, or similarity search. It is thus mainly used for:

  • Geospatial data (PostGIS)
  • Range types
  • Fuzzy search
  • Custom data types
1
2
3
4
5

-- Range types
CREATE INDEX ON reservations USING gist (daterange(check_in, check_out));

-- PostGIS geometry
CREATE INDEX ON places USING gist (location);