Indexeshash indexpartial indexexpression indexcovering indexunique index

Index Types: B-tree, Hash, Partial, Expression, Covering, Full-Text

B-tree answers almost everything; the other types exist for specific shapes — equality-only, a rare subset, a transformed value, text search, similarity — and each is wrong outside its shape.

▶ Interactive Interview question See how this works internally →

Progress

B-tree: the default

Equality, ranges, prefix LIKE, ORDER BY, MIN/MAX, multi-column, unique enforcement. Works on any type with a total order. If you are not sure which index type you need, it is a B-tree. Every PRIMARY KEY and UNIQUE constraint creates one.

Hash

Equality only. Slightly smaller and marginally faster than a B-tree for = on long values like URLs, and useless for anything else — no ranges, no ordering, no multi-column. Since a B-tree handles equality nearly as well and everything else besides, hash indexes are a niche choice. This is the hash-table of Hash Table applied to an index: O(1) average lookup, no order.

Unique

A B-tree that rejects duplicate keys. It *is* the mechanism behind UNIQUE and PRIMARY KEY; there is no constraint without the index. Creating one over existing duplicates fails — find them with GROUP BY … HAVING count(*) > 1 first. NULLs never conflict, because no NULL equals another; NULLS NOT DISTINCT changes that in recent PostgreSQL.

Partial

CREATE INDEX … WHERE status = 'failed' indexes only the rows matching the predicate. The index is a fraction of the size, the tree is shallower, and — decisively — it makes a low-cardinality column indexable when you only ever query the rare value. Soft-delete flags (WHERE deleted = false), work queues (WHERE processed_at IS NULL), staff accounts (WHERE is_staff). The query must repeat the predicate for the planner to use it.

A queue table’s only useful index

1CREATE INDEX jobs_pending ON jobs (created_at) WHERE processed_at IS NULL;
2-- tiny, and exactly the rows the worker wants:
3SELECT id FROM jobs WHERE processed_at IS NULL ORDER BY created_at LIMIT 10 FOR UPDATE SKIP LOCKED;

Expression

CREATE INDEX ON users (lower(email)) indexes the *result* of the expression. It is the fix for every "the index is not used because the column is wrapped" problem: case-insensitive lookups, date(created_at), (data->>'tenant')::int on a JSONB column. The query must use the identical expression. The expression is evaluated on every write, so keep it cheap.

Covering

Not a separate type but a usage: an index that contains every column a query needs, enabling an Index Only Scan. In PostgreSQL, INCLUDE (cols) adds payload columns to a B-tree without adding them to the key. See Composite Indexes and the Leftmost-Prefix Rule.

GIN: full-text, arrays, JSONB

A Generalized Inverted Index maps each *element* inside a value — each word in a document, each item in an array, each key in a JSONB object — to the rows containing it. That is what makes WHERE tags @> ARRAY['sql'], WHERE data @> '{"plan":"team"}' and full-text search (to_tsvector(body) @@ to_tsquery('index & scan')) fast. GIN indexes are large and slow to update; they are the reason PostgreSQL can do a credible job of search without a search engine, up to a point — see JSONB, Full-Text Search and Extensions.

GiST / SP-GiST / BRIN

GiST is a framework for indexes over things without a total order: geometry, ranges (the EXCLUDE overlap constraint), nearest-neighbour. BRIN stores min/max per block range and is tiny; it works only when the physical order correlates with the column — append-only timestamp columns — where it gives most of a B-tree’s benefit at a hundredth of the size. HNSW and IVFFlat (via pgvector) are the approximate-nearest-neighbour indexes for embeddings; see Vector Search: Embeddings, Similarity and ANN.

Key points

B-tree by default. Hash for equality-only on long values, rarely worth it.
Unique indexes are the constraint. Partial indexes make rare values indexable and shrink the tree.
Expression indexes fix "column wrapped in a function". Covering indexes remove the heap read.
GIN for elements-inside-values: full text, arrays, JSONB. BRIN for huge append-only tables. GiST for overlap and geometry.

Seven index types, executed

Seven index types, run for real

Each one is created in a fresh copy of the e-commerce database and the query is executed against it. Toggle the index off to see the same query without it.

CREATE INDEX ix ON orders (user_id);

EXPLAIN ANALYZE
SELECT id, total FROM orders WHERE user_id = 17

(cost=17.66 rows=4) (actual rows=15 loops=1)slowest

Index Cond: user_id = 17

Nodes

Pages read

Index Scan

using ix on orders

Estimated rows: 4
Actual rows: 15
Estimated cost: 17.66
Time: 1.600 ms
Pages: 17

Index Cond

user_id = 17

B-tree index ix matched 1 of its 1 column(s): user_id. Estimated cost 18 against 54 for a sequential scan.

Answers: Equality, ranges, prefix LIKE, ORDER BY, MIN/MAX. The default for a reason: one structure answers almost everything.

Cannot: Suffix or infix matching (`LIKE '%foo'`), and anything where the column is wrapped in a function.

index created

When to use — and when not

Use it when

Match the index type to the predicate shape: ordered comparison → B-tree; containment → GIN; overlap → GiST; similarity → HNSW.

Avoid it when

A GIN index on a column that is updated constantly.
A hash index where you will ever need a range or a sort.

Failure modes

A partial index the query does not match because the predicate is phrased differently.
An expression index on lower(email) while the query says LOWER(TRIM(email)).
BRIN on a table whose physical order is random.