If you give me one slow query and an hour, the fix is an index more often than anything else. Indexes are the highest-leverage performance tool in a relational database, and they are also where I see the most confident, wrong intuition. People add an index to every column “just in case,” or they add a multi-column index in the wrong order and wonder why the planner ignores it. This is my mental model for how indexes work and how I decide which ones to build.
None of this matters if the schema underneath is a mess, so if you have not already, the foundation comes from good design and normalization. Assume here that the model is sound and we are making it fast.
What an index actually is
An index is a separate, sorted data structure that lets the database find rows without scanning the whole table. The default in most relational databases is a B-tree, which keeps keys in sorted order and supports both equality lookups and range scans in logarithmic time. That sorted order is the whole point, and it explains almost everything an index can and cannot do.
Because the keys are sorted, a B-tree index is great at three things. Finding a specific value, finding a range of values, and returning rows already in sorted order so the database can skip a separate sort step. It is useless for the opposite. A query that asks for everything except one value, or that wraps the column in a function the index does not know about, cannot use the sorted structure and falls back to a full scan.
The cost nobody mentions
Every index you add makes reads faster and writes slower. That is not a slogan, it is mechanical. When you insert, update, or delete a row, the database has to update every index that covers the affected columns. A table with eight indexes pays for eight little maintenance operations on every write. Indexes also take disk space and memory, and a bloated index that does not fit in cache stops being the speed-up you wanted.
So I do not index defensively. I index in response to evidence. The right number of indexes is the smallest set that makes your actual queries fast, and finding that set means reading query plans rather than guessing.
Reading the query plan
The single most useful skill in database performance is reading EXPLAIN output. It tells you what the planner intends to do, and EXPLAIN ANALYZE tells you what actually happened with real timings. I run it constantly.
-- See the plan and the real execution numbers
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, placed_at
FROM orders
WHERE customer_id = 42
AND status = 'paid'
ORDER BY placed_at DESC
LIMIT 20;The things I look for, in order. Is there a sequential scan on a big table where I expected an index scan? That is the loudest alarm. How far off is the planner’s estimated row count from the actual row count? A big gap means stale statistics and I run ANALYZE on the table. Is there an expensive sort that an index could satisfy directly? Is the same table being scanned more than once?
Composite indexes and column order
Multi-column indexes are where the most points are won and lost. The rule that took me too long to internalize is that column order is everything, and it follows from the sorted structure. An index on (customer_id, status, placed_at) is sorted by customer_id first, then status, then placed_at. That ordering means it can serve a query filtering on customer_id alone, or customer_id and status, or all three. It cannot efficiently serve a query that filters only on status, because status is not the leading column.
The guideline I use is equality columns first, then the range or sort column last. For the query above, an index on (customer_id, status, placed_at) is close to ideal. The two equality predicates narrow the search, and because placed_at is the final column and already sorted, the database can satisfy the ORDER BY and the LIMIT without a separate sort. One index, no sort step, twenty rows.
-- Equality columns first, then the column we sort on
CREATE INDEX idx_orders_customer_status_time
ON orders (customer_id, status, placed_at DESC);Covering indexes and index-only scans
There is a further trick. If an index contains every column a query needs, the database can answer the query from the index alone and never touch the table. That is an index-only scan, and it can be dramatically faster because it avoids the random reads back into the heap. I get there by including the extra columns the query returns.
-- INCLUDE adds payload columns without changing the sort key
CREATE INDEX idx_orders_cover
ON orders (customer_id, status)
INCLUDE (placed_at, id);I do not do this everywhere, because wider indexes cost more to maintain and store. But for a hot, well-understood query that runs constantly, turning it into an index-only scan is one of the best returns on effort available.
Indexes the planner will quietly refuse
A surprising number of indexes go unused because of how the query is written, not how the index is built. The classic mistakes I look for first.
- Functions on the indexed column. WHERE lower(email) = ‘x’ cannot use a plain index on email. Either store the normalized value, use a case-insensitive type, or build an expression index on lower(email).
- Leading wildcards. LIKE ‘foo%’ can use a B-tree, but LIKE ‘%foo’ cannot, because the sorted order is useless when the start of the string is unknown.
- Type mismatches. Comparing a text column to a number forces a cast that can disable the index. Match your types.
- Low selectivity. An index on a column with two possible values rarely helps, because reading the index plus the heap is often slower than just scanning. The planner knows this and skips it, correctly.
Partial indexes for skewed data
One of my favorite tools for real workloads is the partial index, which only covers rows matching a condition. If 95 percent of your orders are completed and you almost always query the small slice that is still pending, indexing only the pending rows gives you a tiny, fast index that stays hot in memory.
-- Index only the rows we actually search for
CREATE INDEX idx_orders_pending
ON orders (placed_at)
WHERE status = 'pending';This keeps the index small, which keeps it fast and cheap to maintain. For tables with heavy skew it is often the difference between an index that fits in cache and one that does not.
How I actually work
My loop is boring and it works. Find the slow query from real metrics, not from a hunch. Run EXPLAIN ANALYZE and read it carefully. Identify whether the problem is a missing index, a bad column order, stale statistics, or a query written in a way that defeats indexing. Make one change. Measure again. Repeat until the plan is clean.
I resist the urge to add five indexes at once, because then I cannot tell which one helped and I have signed up for write overhead I may not need. One change, one measurement. And I revisit the index set periodically, because workloads drift and yesterday’s essential index can become today’s dead weight that only slows down writes. The same care that goes into the schema and the data model goes into keeping indexes honest. Measure, change one thing, measure again. That discipline beats cleverness every time.