The cheapest performance win in a backend system is almost always a good index. The expensive part is figuring out which index is good, because “add an index” without reading the plan is how teams end up with twelve single-column indexes that the planner ignores in favor of a sequential scan.
Why p99 is the right target
Mean latency tells you the system is healthy. p99 tells you whether your slowest users are having a good day. The tail is dominated by a small number of unusually expensive requests, and those requests are almost always doing something the database did not expect — a missing index, a bad row estimate, or a join order that looked fine in development and falls apart at production scale.
Optimizing the tail is a different exercise from optimizing the median. The median gets faster when the common path gets faster; the tail gets faster when the worst paths get faster, which often means writing indexes for queries that nobody admits to running.
Read the plan the planner actually wrote
EXPLAIN without ANALYZE is the plan the planner thinks it will run. EXPLAIN (ANALYZE, BUFFERS) is the plan it actually ran, with row counts and timing. The interesting numbers are not the totals; they are the estimates versus the actuals on each node.
When the planner estimates 50 rows and the actual is 50,000, it makes bad decisions. It picks nested loops instead of hash joins, chooses the wrong index, or gives up and scans the table. Fixing the estimate by updating statistics is sometimes enough. Fixing the query — usually by giving it a better index — is almost always better.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, created_at
FROM orders
WHERE customer_id = $1
AND status = 'open'
ORDER BY created_at DESC
LIMIT 20;
The plan output tells you three things you need: which index (if any) the planner picked, how many rows it expected at each step, and where the time went. A Bitmap Heap Scan with a huge actual row count on the heap is the fingerprint of an index that exists but does not cover enough columns.
Covering and composite indexes
A covering index includes every column the query needs, so the database can serve the result from the index alone without visiting the heap. The trick is figuring out which columns actually need to be in the index — not the ones the query selects (a covering index usually does not need every selected column), but the ones it filters and orders by.
A composite index is ordered. The order matters. An index on (customer_id, status, created_at) serves a query that filters on customer_id and sorts on created_at. It also serves a query that filters on customer_id alone. It does not serve a query that filters on status alone. Putting the high-cardinality equality columns first and the range or sort columns last is the rule of thumb that holds up most of the time.
When to stop
Indexes are not free. Each one slows down writes, takes disk, and adds another choice for the planner to make. The right number of indexes is the smallest set that serves the real query patterns, not the union of every column someone has ever filtered on.
If a query is slow despite a good index, the next question is whether it should be slow. A dashboard query that scans a year of data is going to be slow even with perfect indexes; the fix is aggregation tables, materialized views, or a different storage shape. Don’t paper over a workload mismatch with another index.
Make it a habit
The fastest way to keep a system fast is to read EXPLAIN before merging a query change. A query that passes a code review, looks reasonable, and lands in production can quietly double p99 the next time a particular column drifts in cardinality. A 30-second plan review catches most of those before users do.