Indexesindex decisionselectivitywrite amplificationunused indexpg_stat_user_indexes

Should I Add an Index?

Six questions decide it — frequency, how the column is used, selectivity, table size, write rate, and what already exists — and any one of them can end the conversation with "no".

▶ Interactive Interview question See how this works internally →

Progress

The questions, in order

Is the query frequent? A once-a-day report does not justify a permanent write cost. Does it filter, join or sort on this column? An index does nothing for a column you only display. Is the predicate selective? Under a few percent, yes; over twenty, the scan wins. Is the table large? A few hundred rows is one page. What is the write rate? Every index is a write per row per statement. Does an index already cover it? A composite on (a, b) already serves (a).

Walk them in that order and stop at the first "no". Most proposed indexes die at question one or six.

Evidence, not intuition

The workflow: capture the slow query with its actual parameters (pg_stat_statements ranks queries by total time). Run EXPLAIN (ANALYZE, BUFFERS). Find the node that dominates — usually a Seq Scan with large Rows Removed by Filter, a Sort over many rows, or a Nested Loop with a high loops count. Design the index for that node. Create it (CONCURRENTLY in production, so it does not lock the table). Run the plan again. If the node did not change, the index is wrong; drop it.

Then look at the other side: SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 lists indexes nothing has used since the statistics were reset. Each one is pure write cost. Drop them, one at a time, with the same care you added them.

Finding the work and the waste

1-- what is actually slow, weighted by total time
2SELECT calls, round(total_exec_time) AS ms, left(query, 80)
3FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
4 
5-- what nobody uses
6SELECT relname, indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS size
7FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;
8 
9-- add without locking writers
10CREATE INDEX CONCURRENTLY orders_user_recent ON orders (user_id, created_at DESC);

"Indexes are not free"

The full bill: storage (often exceeding the table), one extra write per index per row on INSERT and DELETE, and on UPDATE of any indexed column; slower VACUUM; more planner work; a larger buffer-cache footprint, which evicts table pages you needed. A table with fifteen indexes is a table where every write does sixteen things, and the fourteenth index is almost certainly unused.

The counter-intuitive consequence: sometimes the fastest thing you can do for a system is *drop* indexes. Write latency falls, the cache holds more useful pages, and the queries that relied on the dropped index were not running anyway.

Key points

Frequency, usage, selectivity, size, write rate, existing coverage — in that order, stop at the first no.
Evidence: pg_stat_statements → EXPLAIN ANALYZE → index the dominant node → EXPLAIN ANALYZE again.
CREATE INDEX CONCURRENTLY in production.
Drop unused indexes; they are write cost with no read benefit.