Add the index the query needs

B-tree indexes cover most equality and range lookups. PostgreSQL also offers specialized index types for other operators and data types.

A useful index starts with a real query:

SELECT id, created_at, title
FROM notes
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

A matching index is:

CREATE INDEX notes_user_created_idx
ON notes (user_id, created_at DESC);

A composite B-tree index is ordered by its leading columns. PostgreSQL can jump to one user’s range and read recent rows in index order. The same index is much less useful for a query that only filters on created_at.

B-tree is the default and covers equality, ranges, and ordering. PostgreSQL also provides specialized types: GIN is common for JSONB containment, arrays, and full-text search; GiST supports operator families such as ranges and geometry; BRIN can be compact and effective on very large tables where values correlate with physical row order.

Partial indexes store only rows matching a predicate:

CREATE INDEX notes_unpublished_user_idx
ON notes (user_id)
WHERE published_at IS NULL;

They are valuable when the application repeatedly queries a small, stable subset. The query predicate must imply the index predicate for PostgreSQL to use it.

Indexes cost disk space, cache, and write work. Before adding one, capture the slow query and inspect EXPLAIN (ANALYZE, BUFFERS) on representative data. After adding it, confirm the selected plan and real row counts. A sequential scan is often correct for a small table or a query returning much of the table.

In production, CREATE INDEX can block writes while building. CREATE INDEX CONCURRENTLY reduces that blocking but takes longer, performs more work, cannot run inside a transaction block, and can leave an invalid index after failure. Plan the operational path, not only the final schema.

Recheck the plan after adding an index and remove indexes the workload no longer uses.

Try it: compare the plans for the query above with no index, an index on user_id, and the compound index. Explain why the winner depends on both filtering and ordering.