When to Add a Database Index (and When It Makes Things Worse)
A decision guide for indexes: the three signals that justify one, the four cases where adding one slows writes for nothing, composite column order, and how to verify with EXPLAIN that the index is actually used.
Quick Answer
- Add an index when: a query filters or joins on the column, the table is large (think 10k+ rows), and the column has high selectivity (many distinct values).
- Do not index when: the table is tiny, the column is low-cardinality (booleans, status with 3 values), or the table is write-heavy and the query is rare.
- Verify with EXPLAIN: an index that exists but is not used is pure write overhead. Confirm
Index Scan/Index Seekappears in the plan.
The Decision Checklist
Work down the list; a "no" anywhere before the last line means skip the index:
- Is there a real, repeated query behind this? Index for queries you run, not columns that "feel important". One-off analytics do not deserve permanent write cost.
- Is the table big enough to matter? Under a few thousand rows a sequential scan is fine — the planner will ignore your index anyway.
- Does the query touch a small fraction of rows? An index helps most when it can discard 95%+ of the table. If you fetch 40% of rows, a scan is cheaper than random access.
- Is the column selective? Indexing
statuswith three values rarely helps unless the distribution is heavily skewed (99% one value) and queries target the rare one — partial indexes handle that case. - Can you afford the write cost? Every INSERT/UPDATE/DELETE maintains every index. A table taking thousands of writes per second with five indexes pays for all five on every write.
The Signals, Ranked by Strength
| Signal | Strength | Why |
|---|---|---|
| Equality filter on a high-cardinality column in a hot query | Strong | Classic B-tree win: direct lookup, minimal I/O |
| Join column on the large side of a join | Strong | Turns nested loops into index lookups |
| Range filter on a sortable column (dates, ids) | Strong | B-tree range scan + enables ordered reads |
| ORDER BY + LIMIT on a large table | Medium | Index can supply order, skipping the sort step |
| LOW selectivity column (boolean, 3-value status) | Weak | Planner may still scan; partial index is usually better |
Column only ever used in functions: WHERE YEAR(d) = 2026 | None as-is | The function hides the column — index d and rewrite as a range, or use an expression index |
Composite Indexes: Column Order Is the Whole Game
An index on (customer_id, created_at) serves WHERE customer_id = 7 AND created_at > '2026-01-01' perfectly, and also serves WHERE customer_id = 7 alone (leftmost-prefix rule). It does not help WHERE created_at > '2026-01-01' alone — the leading column is missing. Order rule of thumb: equality columns first, then range/sort columns, then (on some engines) include columns to make it covering. If two queries disagree about order, you need two indexes or a covering index — not one index and hope.
The Cost Side Nobody Mentions
Each index is a sorted copy of part of the table, maintained on every write. On a table with 10 million rows and 20k inserts/minute, an unneeded index is not neutral — it is a write amplifier and storage consumer. Deeper dive with execution-plan examples: SQL indexing for slow queries. Also remember indexes decay: after heavy churn, PostgreSQL benefits from REINDEX, and stale statistics (ANALYZE) make even good indexes go unused.
Verify Before and After
EXPLAIN ANALYZEthe query and note the plan + timing.- Create the index (use
CREATE INDEX CONCURRENTLYon PostgreSQL production tables — it does not lock writes). - Run
ANALYZE, thenEXPLAIN ANALYZEagain. The plan should show an index scan and (usually) lower cost. - If the plan did not change: the index is dead weight. Drop it and reconsider the query instead.
Try It Yourself
Paste a slow query into our query optimizer — it flags the predicates an index would serve and suggests the column order, then explains why.