PostgreSQL index types: B-tree, GIN, partial

Before you start

Abstract illustration of several different rounded shapes, representing different index types

CREATE INDEX with no type builds a B-tree, and that is right most of the time. The other types exist for data a B-tree handles badly: arrays, JSON, text search, ranges, and very large tables. Pick by what the query does to the column.

Pick a type

B-tree (default)

Equality and range comparisons (=, <, >, BETWEEN) and ORDER BY. The right choice for ids, dates and most columns.

Hash

Equality only. Rarely better than a B-tree, so use it only if you have measured a reason.

GIN

Values that hold many items: arrays, jsonb and full-text search. Answers "which rows contain this item".

GiST

Geometric data, ranges and nearest-neighbour searches.

BRIN

Very large tables where the column follows the physical order of the rows, such as an insert-ordered timestamp. Tiny, but only useful for that shape.

Three useful variations

Partial index. Indexes only the rows you query. Smaller and cheaper to maintain.

CREATE INDEX idx_orders_open
  ON orders (created_at)
  WHERE status = 'open';

The query must include the same condition (status = 'open') for the planner to use it.

Expression index. Indexes a computed value, so a query that applies the same function can use it.

CREATE INDEX idx_users_email_lower ON users (lower(email));

GIN on an array or jsonb column.

CREATE INDEX idx_posts_tags ON posts USING GIN (tags);
-- serves: SELECT * FROM posts WHERE tags @> ARRAY['postgres'];

What each index costs

Every index uses disk and must be updated on every write to its table. GIN indexes can be slower to update than B-trees. Create an index for a query that matters, check the plan uses it, and drop it if it does not. See Create an index in PostgreSQL for how to build one safely and find unused ones.

Next step

Not sure which index a slow query needs? Paste the query and its plan into dbtempo for a proposal, or read how the PostgreSQL index advisor chooses.

Sources