Table of Contents
- Indexing One Column at a Time
- How the Planner Uses an Index
- Column Order Is the Whole Game
- Covering Indexes Avoid the Table
- What Indexes Cost
- When the Planner Ignores Your Index
- Reading a Query Plan
- Finding the Indexes You Need
- Common Pitfalls
- Conclusion
- Frequently Asked Questions
Key takeaway: A composite index serves queries that filter on a leading prefix of its columns. Getting the order wrong produces an index the planner cannot use, which is why single-column indexes on every column rarely help multi-condition queries.
Indexing One Column at a Time
A query is slow. Someone adds an index on the column in the WHERE clause. It is still slow. They add indexes on the other filtered columns. Marginal improvement. Eventually the table has eleven indexes, writes have slowed noticeably, and the query still performs badly.
The reason is that most real queries filter on several columns simultaneously, and separate single-column indexes serve that poorly.
Consider:
SELECT * FROM orders
WHERE customer_id = 4821
AND status = 'pending'
AND created_at > '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;With three separate indexes, the database has options that are all mediocre. It can use one index and filter the rest of the conditions by examining rows. It can combine indexes through a bitmap operation, which requires building intermediate structures and then visiting the table anyway. Or it can decide the whole exercise is not worth it and scan.
With one well-ordered composite index, it locates exactly the matching rows in already-sorted order and stops after twenty.
That is the difference between a query taking hundreds of milliseconds and taking a fraction of one, and it comes down to index design rather than to hardware or configuration.
How the Planner Uses an Index
A B-tree index is a sorted structure. Understanding that one fact explains most index behaviour.
Because entries are sorted, the database can perform three operations efficiently: find a specific value, find a range of values, and read entries in sorted order. What it cannot do is find entries by a value in the middle of a composite key without knowing the earlier parts, for the same reason you cannot find “everyone named Sarah” in a phone book sorted by surname.
For a composite index on (a, b, c), the sort is by a, then by b within equal a, then by c within equal a and b. This means the index supports:
- filtering on
a - filtering on
aandb - filtering on
a,b, andc - filtering on
aandbwith a range onc - ordering by
a,b,c
And does not usefully support filtering on b alone, or on c alone, or on b and c without a.
This is the leftmost prefix rule, and it is the single most useful thing to know about indexing. Once internalised, most index design questions answer themselves.
Column Order Is the Whole Game
Given the prefix rule, ordering follows a reliable pattern.
Equality conditions first. Columns compared with = should precede columns compared with ranges. An equality narrows to a contiguous section of the index; a range consumes the remaining sort order.
Range conditions last. Once a range is used, columns after it in the index cannot be used for further filtering, because within the range the subsequent columns are not globally sorted.
Ordering columns after equalities. If a query filters on equality and sorts on another column, placing the sort column immediately after the equality columns lets the index provide the order, eliminating a sort step entirely.
For the earlier query, the index that works:
CREATE INDEX idx_orders_lookup
ON orders (customer_id, status, created_at DESC);customer_id and status are equalities, narrowing to a small contiguous section. created_at is both the range filter and the sort key, and within that section the index is already in descending date order — so the range is a scan boundary and no sorting is required. The LIMIT 20 then stops after twenty entries.
Now consider what a different order costs. With (created_at, customer_id, status), the leading column is a range, so the database must scan every order since January across all customers, checking each against the other conditions. Same columns, same index size, dramatically different performance.
Selectivity is the secondary consideration. Among equality columns, placing the more selective one first reduces the section scanned, though the effect is smaller than getting equality-before-range right.
Covering Indexes Avoid the Table
An index normally locates a row, then the database reads that row from the table for the columns not in the index. That second step is a separate random read per row.
If every column the query needs is present in the index, the table read is unnecessary. This is an index-only scan, and for queries returning many rows the difference is substantial.
-- Needs the table for total_amount
CREATE INDEX idx_a ON orders (customer_id, status);
-- Answers entirely from the index
CREATE INDEX idx_b ON orders (customer_id, status) INCLUDE (total_amount);The INCLUDE clause stores additional columns in the index leaf without making them part of the sort key, which keeps the index smaller than adding them as key columns while still enabling index-only scans.
Two caveats. Covering indexes are larger, so they cost more in write overhead and memory. And the benefit is proportional to rows returned — for a query fetching one row it is negligible, and for a query fetching ten thousand it can be several times faster.
The queries worth covering are aggregations and list endpoints that return many rows with few columns. Those are also frequently the queries that dominate database load.
What Indexes Cost
Indexes are not free, and the costs are paid on writes rather than reads.
Write amplification. Every insert updates every index on the table. A table with ten indexes performs ten additional structural writes per row inserted. Updates touch every index containing a modified column.
Storage. Indexes commonly total more than the table itself on heavily-indexed tables.
Memory competition. Index pages compete with table data for cache. An unused index still occupies cache when touched during writes.
Maintenance overhead. Vacuum and statistics operations must process indexes, which lengthens maintenance windows.
Planning time. More indexes means more options for the planner to evaluate on every query.
The important corollary is that unused indexes are pure cost. They slow writes, consume storage and memory, and provide nothing. Most databases expose index usage statistics, and querying them on a mature system typically reveals several indexes with zero scans since the last statistics reset.
Dropping unused indexes is one of the few database changes that improves write performance with no read-side trade-off. It is worth doing periodically and it is rarely done.
When the Planner Ignores Your Index
An index exists and the query does not use it. The causes are specific and mostly identifiable from the plan.
A function applied to the column. WHERE lower(email) = ? cannot use an index on email. It needs an index on lower(email) — an expression index.
Type mismatch. Comparing an integer column to a string value can prevent index use, depending on the database’s coercion rules.
Leading wildcard in a pattern. LIKE '%text' cannot use a standard B-tree index, because the sort order is defined by leading characters. Trailing wildcards work fine.
Low selectivity. If a condition matches a large fraction of the table, a sequential scan is genuinely faster than an index scan plus that many random table reads. The planner is correct here, and the fix is a more selective query or a covering index rather than forcing the index.
Stale statistics. The planner estimates row counts from statistics. If they are outdated — commonly after a bulk load — estimates are wrong and plan choices follow. Running an analyse operation resolves it and this is a frequent cause of sudden plan changes.
Missing prefix. The query filters on the second column of a composite index without the first.
OR conditions across columns. These frequently prevent single-index use. Rewriting as a union of two indexed queries sometimes performs dramatically better.
That fifth item is worth emphasising because it presents as a mystery: a query that was fast becomes slow with no code change, because the data grew and statistics did not keep pace.
Reading a Query Plan
The plan is the authoritative account of what the database will do. Reading it is the skill that makes index work empirical rather than speculative.
What to look for, roughly in order of importance:
Sequential scans on large tables. Expected on small tables where scanning is cheaper. On a large table with a selective filter, it indicates a missing or unusable index.
Estimated versus actual row counts. A large discrepancy means the statistics are misleading the planner, and every downstream decision in the plan is built on a wrong estimate. This is the highest-value thing to check.
Explicit sort operations. A sort that could have been provided by an index is wasted work, and if it spills to disk it is expensive.
Nested loops with high iteration counts. Efficient for small inner sets, poor when the outer set is large.
Filter rows removed. A large number of rows retrieved and then discarded indicates the index brought back more than necessary — usually a column that should be in the index is not.
Heap fetches on index scans. Present when an index-only scan was nearly possible, indicating a covering index would help.
The practice that matters: always read the plan with actual execution statistics rather than estimates alone. Estimates tell you what the planner believed; actual counts tell you whether it was right, and the gap between them is where most surprises live.
Finding the Indexes You Need
A method that produces results without guessing.
Start from the slow query log. Log queries above a threshold. Order by total time consumed rather than by individual duration — a query taking 50ms called ten thousand times matters more than one taking 3 seconds called twice.
Read the plan for the top consumers. Identify sequential scans, sorts, and estimate discrepancies.
Design the index from the query shape. Equalities first, then the sort or range column, then included columns if the query returns many rows.
Test on representative data volumes. An index that helps on a thousand rows may be ignored on ten million, and vice versa. Testing on a small dataset produces misleading conclusions.
Verify usage after deploying. Check that the index is actually being scanned. Indexes created and never used are common.
Review unused indexes periodically. Drop what has no scans.
One practical caution: creating an index on a large production table locks writes in some databases unless the concurrent variant is used. That variant takes longer and does not block, and forgetting it has caused outages.
Common Pitfalls
One index per column. Serves multi-condition queries poorly.
Range column before equality columns. Prevents the equalities from narrowing the scan.
Functions on indexed columns in the query. Requires an expression index instead.
Leading wildcards. Cannot use a B-tree index.
Never dropping unused indexes. Pure write cost with no benefit.
Testing on small datasets. Plan choices differ at scale.
Creating indexes non-concurrently in production. Blocks writes on large tables.
Conclusion
Most slow queries are index design problems, and most index design problems are column ordering problems. A composite index serves queries filtering on a leading prefix of its columns, which means equality conditions belong first, the range or sort column belongs after them, and everything else is secondary.
Get that right and one index frequently replaces several while performing dramatically better — locating exactly the matching rows in already-sorted order, so no sort step is needed and a limit clause can stop early.
Then verify empirically. Read plans with actual row counts, watch for estimate discrepancies that indicate stale statistics, and check that the indexes you created are actually being scanned. And drop the ones that are not, because an unused index is a write penalty with no compensating benefit.
Frequently Asked Questions
How many indexes should a table have? As few as the query workload requires. Each one costs write performance. Five to ten on a heavily-queried table is unremarkable; twenty suggests unused indexes or a design problem.
In what order should composite index columns go? Equality conditions first, most selective among them earlier, then the column used for range filtering or ordering. Columns after a range condition cannot be used for further filtering.
Why is a sequential scan chosen over my index? Usually because the filter is not selective enough to make random table reads worthwhile, or because statistics are stale. Check the estimated versus actual row counts in the plan first.
Does index order matter for a single-column index? Direction matters only for ordering. A descending index helps queries sorting descending, and most databases can scan a B-tree backwards, so this rarely justifies a separate index.
Should foreign key columns be indexed? Generally yes. They are frequently used in joins, and some databases take table-level locks on the referencing table during parent deletions without one.
What about partial indexes? Very useful when queries consistently filter on a condition — indexing only active rows produces a much smaller index. The condition must appear in the query for the index to be used.
How is index bloat handled? Periodic rebuilding, concurrently where supported. Bloat accumulates from updates and deletes, and a bloated index is larger and slower than necessary while still functioning correctly.
