Index Best Practices

Indexes are one of the most effective tools for query performance, but they are not free, and treating every column as a candidate for indexing tends to backfire — slowing down writes, bloating storage, and confusing the optimizer with too many options to choose from. This page is a practical checklist for deciding what to index, and, just as importantly, when to stop.

A practical checklist

Index columns that are actually used in WHERE, JOIN, and ORDER BY clauses in real, frequently-run queries — not columns that merely seem important.
Favor composite indexes over several separate single-column indexes when queries commonly filter or sort on the same combination of columns together.
Do not over-index — every additional index adds overhead to every INSERT, UPDATE, and DELETE on that table, and consumes storage whether or not it is ever used.
Periodically review index usage statistics and drop indexes that are rarely or never used by the query planner.
Watch for redundant indexes, such as a single-column index made obsolete by a composite index that already covers it as a leftmost prefix.
Reconsider indexing on columns with very low selectivity, such as a boolean flag with only two possible values across millions of rows — an index there often is not worth its overhead.
Monitor real production query performance and plans over time; the right set of indexes for today's data and query patterns may not be the right set a year from now.

Finding unused indexes

Most production databases expose statistics about how often each index is actually used. In PostgreSQL, pg_stat_user_indexes shows a scan count per index, making it straightforward to spot ones that are dead weight:

PostgreSQL: finding indexes that are rarely or never used

SQL

SELECT
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  idx_scan AS times_used
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

An index with an idx_scan of zero, on a table that has been running under normal production load for a reasonable period, is a strong candidate for removal — it is providing no read benefit while still costing write overhead and storage on every change to that table.

Composite index review

When reviewing indexes, check whether a composite index already covers what a separate single-column index was created for. Because a composite index on (customer_id, status) can already serve queries filtering on customer_id alone (via the leftmost prefix rule), a standalone index on just customer_id is usually redundant once that composite index exists, and can typically be dropped.

'Index everything' is an anti-pattern

It is tempting to add an index for every column that ever appears in a WHERE clause, on the theory that more indexes can only help. In practice, this backfires: every write to the table now has to update every one of those indexes, INSERT and UPDATE performance degrades noticeably, storage grows, and the query optimizer has a harder time choosing the best plan among a large number of overlapping options. Index deliberately, based on measured query patterns — not exhaustively, based on what could theoretically be queried someday.

Index based on real, observed query patterns, not hypothetical ones.
Prefer well-chosen composite indexes over a pile of overlapping single-column ones.
Regularly check index usage statistics and drop indexes that are not earning their overhead.
Avoid indexing very low-selectivity columns on their own.
Treat indexing as an ongoing process tied to production monitoring, not a one-time setup step.