Postgres indexes: the five you actually need

Most slow queries have the same cause: the database reads a whole table to find a few rows. An index fixes that. PostgreSQL offers more than a dozen index features, and you need about five of them. This post covers those five, when to use each and how to check that they work.

What an index is

A table is a heap of rows in no useful order. To find the rows where email = 'a@b.com', the database must look at every row. That is a sequential scan, and it takes time in proportion to the size of the table.

An index is a second structure, sorted by one or more columns, that points back to the rows. Looking up a value in a sorted structure takes a handful of steps, even with a hundred million rows.

The cost is real. Each index takes disk space, and every insert, update and delete must update it. Index what you query, not everything.

1. The plain B-tree

This is the default, and it is right nine times in ten. It handles equality, ranges and sorting.

CREATE INDEX idx_users_email ON users (email);

SELECT * FROM users WHERE email = 'ada@example.com';
SELECT * FROM orders WHERE created_at >= '2026-01-01' ORDER BY created_at;

Create one for every foreign key column and for every column that appears often in a WHERE or ORDER BY.

2. The multi-column index

When a query filters on two columns, one index on both is much better than two separate indexes.

CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at DESC);

SELECT * FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Order matters. The index is sorted by the first column, then by the second inside each value of the first. It serves queries on customer_id alone, and on both. It does not serve queries on created_at alone.

Put the column you test for equality first and the column you sort or range over last.

Priya Nair

3. The partial index

Often you query only a small part of a table: unpaid invoices, active users, jobs that have not run. Index only those rows.

CREATE INDEX idx_jobs_pending ON jobs (run_at)
WHERE status = 'pending';

On a table with fifty million finished jobs and two thousand pending ones, this index is tiny, stays in memory and is very fast. The query must include the same condition for the planner to use it.

4. The covering index

If an index contains every column a query needs, PostgreSQL can answer from the index alone and never visit the table. That is an index-only scan.

CREATE INDEX idx_orders_cover ON orders (customer_id)
INCLUDE (total, status);

SELECT total, status FROM orders WHERE customer_id = 42;

INCLUDE adds columns to the leaves of the index without making them part of the sort key. Use it for hot queries that read two or three small columns.

5. The expression index

An index on a column does not help when the query wraps that column in a function.

-- This cannot use an index on (email)
SELECT * FROM users WHERE lower(email) = 'ada@example.com';

-- Index the expression itself
CREATE INDEX idx_users_email_lower ON users (lower(email));

The same applies to dates truncated to a day, to JSON fields and to anything else you compute in the WHERE clause.

Check that it is used

Never assume. Ask the database.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

Look for Index Scan or Index Only Scan in place of Seq Scan, and compare the time before and after. The next post in this series explains how to read that output line by line.

Find the indexes you do not need

Unused indexes cost you on every write. PostgreSQL counts how often each one is scanned.

SELECT relname AS table, indexrelname AS index, idx_scan AS scans,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

An index with zero scans over a few weeks of normal traffic is a candidate for removal, unless it enforces a unique constraint.

When not to index

The short version

Start with B-tree indexes on the columns you filter and join on. Add a multi-column index when a query uses two columns together. Use partial indexes for small, hot subsets. Then run EXPLAIN ANALYZE and let the numbers decide.