Choosing an index type

ConceptAdvanced4 min read10 min hands-on#indexing

The idea in 30 seconds

B-tree is the default because it answers equality, ranges and sorting. The other index types each answer a different kind of question: GIN what a value contains, GiST whether things overlap, BRIN roughly where in a huge append-only table a range of values lives, hash only exact equality. Pick by the operators your queries use, then check the plan.

Helps to know first: B-tree indexes

Six index types, six kinds of question

An index helps a query only if it supports the operator in the WHERE clause. That, more than anything, decides the type:

Type	Answers	Typical use
B-tree (default)	`=`, `<`, `>`, `BETWEEN`, `ORDER BY`, `IS NULL`	Almost everything: keys, dates, names
Hash	`=` only	Equality lookups on nearly unique values
GIN	“does it contain …”: `@>`, `?`, `&&` on arrays	JSON documents, arrays, full-text search
GiST	“do they overlap / which are nearest”: `&&`, `<->`	Ranges, geometry, exclusion constraints
SP-GiST	Searches over unbalanced structures (quadtrees, k-d trees, tries)	Points and similar specialised data
BRIN	Ranges over values that follow the table’s physical order	Huge append-only tables keyed by time

You met B-tree in depth in B-tree indexes and GIN in Indexing jsonb. Two more patterns work with any type: an expression index stores the result of an expression (lower(email)), and a partial index stores only the rows that match a condition (WHERE status = 'pending').

GiST: rules that compare rows with each other

A unique index guarantees no two rows are equal. Some rules need something else: no two bookings of the same room may overlap. A GiST index can enforce that as an exclusion constraint:

CREATE EXTENSION IF NOT EXISTS btree_gist;   -- lets GiST handle the integer room_id

CREATE TABLE bookings (
  room_id int NOT NULL,
  during  tstzrange NOT NULL,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

INSERT INTO bookings VALUES (101, '[2025-10-20 09:00, 2025-10-20 10:00)');
INSERT INTO bookings VALUES (101, '[2025-10-20 10:00, 2025-10-20 11:00)');   -- back to back: fine
INSERT INTO bookings VALUES (102, '[2025-10-20 09:30, 2025-10-20 10:30)');   -- other room: fine
INSERT INTO bookings VALUES (101, '[2025-10-20 09:30, 2025-10-20 10:30)');   -- overlaps

ERROR:  conflicting key value violates exclusion constraint "bookings_room_id_during_excl"
DETAIL:  Key (room_id, during)=(101, ["2025-10-20 09:30:00+00","2025-10-20 10:30:00+00")) conflicts with existing key (room_id, during)=(101, ["2025-10-20 09:00:00+00","2025-10-20 10:00:00+00")).

(Our session’s time zone was UTC; the times print in yours.) [09:00, 10:00) includes 09:00 but not 10:00, so back-to-back bookings do not overlap. Unlike checking for a clash in application code before inserting, the constraint holds even when two requests try to book the same slot at the same moment.

BRIN: tiny, if the order is right

A BRIN index does not record every value. It splits the table into ranges of blocks and stores just the smallest and largest value in each. To find one day of events, it skips every block range whose minimum and maximum cannot contain that day.

PredictSame rows, different order

Five million events, one every six seconds. A BRIN index on created_at lets PostgreSQL count one day’s events by reading 259 pages. The same rows are copied into a second table in random order, with its own BRIN index. How many pages does the same count read there?

Check yourself

A query filters on tags @> '{urgent}' where tags is an array. Which index type can serve it?
Show answer
GIN: it indexes each element of the array, so it can answer containment queries.
I could explain this without looking
Why can’t a unique index prevent overlapping bookings?
Show answer
A unique index only rejects equal values. Two different time ranges can overlap without being equal; an exclusion constraint with GiST and the && (overlaps) operator rejects that.
I could explain this without looking
When is BRIN a good choice, and when is it useless?
Show answer
When the indexed values follow the table’s physical order, as timestamps do in an append-only table: then block ranges have narrow minimum and maximum values. When rows are in random order, every block range covers almost everything and nothing can be skipped.
I could explain this without looking

Sources

Where each claim on this page comes from.

PostgreSQL documentation: 11.2 Index Types

Primary· §11.2 Index Types

Each index type and the operators it supports; hash indexes handle only equality; GIN indexes are inverted indexes for values with many components; GiST is an infrastructure for many strategies such as geometric and range operators; BRIN stores summaries of block ranges and works best when values correlate with physical row order.

PostgreSQL documentation: 5.5 Constraints

Primary· §5.5.6 Exclusion Constraints

An exclusion constraint ensures no two rows compare true on all the specified operators, and creates an index to enforce it.

PostgreSQL documentation: F.8 btree_gist

Primary· F.8 btree_gist

btree_gist provides GiST operator classes for ordinary types such as integers, so they can be combined with ranges in a GiST index.

PostgreSQL documentation: 11.8 Partial Indexes

Primary· §11.8 Partial Indexes

A partial index covers only rows that satisfy its predicate; a common reason is to avoid indexing common values.

PostgreSQL documentation: 12.9 Preferred Index Types for Text Search

Primary· §12.9

GIN and GiST indexes can speed up full-text search.