Postgres index notes

2 March 2026

Notes to myself after a week of chasing a slow query. Nothing here is new, but I keep forgetting it, so it goes on the page.

Column order in a composite index

An index on (a, b) can serve a query filtering on a alone, or on a and b together. It cannot efficiently serve a query filtering on b alone. The leftmost column is the one that has to be in the filter.

Equality first, then range. An index on (status, created_at) works for "recent rows with this status" because the equality column is leading. The reverse order does not.

Partial indexes earn their keep

If a query only ever looks at one slice of the table, index only that slice:

CREATE INDEX CONCURRENTLY idx_jobs_pending
    ON jobs (created_at)
    WHERE state = 'pending';

Smaller index, faster writes, and the planner will use it whenever the query carries a matching WHERE clause. The catch is that the predicate has to be provable at plan time — a bound parameter is not enough on its own.

Checking that it is actually used

EXPLAIN (ANALYZE, BUFFERS) is the one I reach for. If the plan says Seq Scan on a table I expected an index scan on, the question is usually selectivity, not the index: returning 30% of the table is cheaper as a sequential read, and the planner is right.

Also worth remembering: an index that is never used still costs you on every insert. pg_stat_user_indexes.idx_scan is the quickest way to find the ones that have quietly become dead weight.