Create an index in PostgreSQL: when it helps

An index lets PostgreSQL find rows without reading the whole table. It speeds up reads and slows down writes, so the aim is a few indexes that earn their cost. You need permission to create indexes on the table, and a copy of the slow query.

-- Blocks writes to the table while it builds
CREATE INDEX idx_orders_customer_created
  ON orders (customer_id, created_at);

-- Builds without blocking writes (slower, and cannot run inside a transaction block)
CREATE INDEX CONCURRENTLY idx_orders_customer_created
  ON orders (customer_id, created_at);

On a table that is being written to, use CONCURRENTLY. If a concurrent build fails, PostgreSQL leaves an INVALID index behind. Drop it and try again.

Column order matters. A multicolumn B-tree index is most useful when the query filters on its leading columns. For WHERE customer_id = 42 ORDER BY created_at DESC, put customer_id first: the index can jump to one customer and read their rows already in date order.

Run plain EXPLAIN on the query. You want an Index Scan on your index where there was a Seq Scan. See Read a PostgreSQL EXPLAIN plan. Later, find indexes nothing reads:

SELECT relname AS table, indexrelname AS index, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC
LIMIT 20;

An idx_scan of 0 over a long period means the index has not been used since the statistics were last reset. Check that period before dropping anything. These counters are per server, so if reads run on a replica, check the replica too: the primary can show 0 for an index the replica uses. Never drop an index that enforces a unique or primary key constraint.