Choose SQL Indexes from Query Plans

An index is a stored access path with maintenance cost. It is useful when that path reduces enough work for the queries you run. Adding indexes to every column can slow writes and consume storage while leaving the important query unchanged.

Take a concrete request: the newest twenty events for one user. The useful details are the equality filter on user_id, the descending created_at order, the limit, and the table’s distribution. Those details point toward a composite access path more clearly than a generic rule to index frequently used columns.

Start with one query and one baseline

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, kind
FROM events
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

CREATE INDEX ON events (user_id, created_at DESC);

Run the measurement on representative data, then compare the plan after the index exists. Look at actual row counts, sort work, buffer access, and elapsed time. ANALYZE executes the statement, so this example is deliberately a SELECT. Use extra care with writes and expensive production queries.

A tiny development table often favors a sequential scan and tells you little about a large production table. Conversely, an index can look excellent with one unusually selective user and poor with a dominant tenant. Include realistic parameter values and keep statistics current.

Use specialized indexes for specialized questions

A partial index can be useful when a query repeatedly asks for a small active subset, such as pending jobs. An expression index can support a repeated normalized expression. GIN and GiST support particular operators and value types; they are not automatically faster versions of a B-tree. BRIN is useful when physical ordering correlates with the indexed value on a large table.

Covering an index with payload columns can enable an index-only scan, but visibility information may still require heap access. More columns also enlarge the index and add write work. Keep the proposed index tied to a measured query and a clear benefit.

Evaluate the write side too

Measure insertion and update throughput, disk growth, and maintenance behavior. Review whether an existing index already provides the necessary leading columns. Avoid deleting an apparently redundant index without checking uniqueness constraints and other workloads.

Record the query, representative parameters, data scale, before-and-after plan, and observed tradeoff. That record makes later tuning reviewable when the workload changes; a screenshot of one fast execution does not.

Reference tables to keep nearby

• PostgreSQL Index Design Patterns — Index choices for equality, ordered retrieval, selective predicates, and specialized PostgreSQL workloads.

Primary sources

These are the specifications and project documentation behind the examples. Check the relevant implementation and version before applying a pattern.

• PostgreSQL index types
• PostgreSQL multicolumn indexes
• PostgreSQL EXPLAIN