All topics
15 / 66
performance

Query Optimization Workflow

A repeatable process: log slow queries → run EXPLAIN ANALYZE → identify the operator that's expensive → fix (index, rewrite, denormalize) → verify.

Tuning by hunch wastes time and sometimes makes things slower. This is the loop that does not.

Doctor's rounds — vitals first, focus on the worst patient, diagnose, treat, re-measure. Not "try every pill in the cabinet."

Key Concepts

1
1. Find the query that actually costs you. Turn on the slow query log, or read an APM dashboard, and sort by total time — calls multiplied by average time — not by the slowest single call:
2
    query                    calls    avg     total
    monthly report              12   2.4 s     29 s
    fetch cart items       180,000   31 ms   5580 s
3
The report looks terrible and is irrelevant. The cart query is 190 times more expensive in aggregate. The slowest query is rarely the most expensive one.
4
2. Get the plan, with real numbers. Run EXPLAIN ANALYZE. Estimates alone tell you what the optimiser believed; actuals tell you what happened.
EXPLAIN ANALYZE
5
3. Name the one operator that dominates. Almost always a single step owns most of the time: a sequential scan on a large table, a nested loop running far more times than expected, a sort that spilled to disk. Write it down before changing anything.
6
4. Apply the fix aimed at that operator.
    sequential scan, selective filter  ->  add or reorder an index
    index exists but unused            ->  the predicate is defeating it; rewrite it
    estimate wildly below actual       ->  refresh the table statistics
    sort spilled to disk               ->  more working memory, or an index giving the order
    nested loop over millions          ->  filter earlier, or help it choose a hash join
7
Denormalization comes last, after the cheaper fixes are exhausted.
8
5. Verify, and check what you broke. Re-run EXPLAIN ANALYZE and compare against the original measurement. Then check the other side of the ledger: a new index speeds this query and slows every write to that table. Confirm that trade is one you want.
EXPLAIN ANALYZE
9
Change one thing at a time. Add an index and rewrite the query together and you will not know which helped — or that one helped while the other hurt. This matters months later when someone asks whether the index can be dropped.
10
The trap this workflow exists to avoid. A query taking 2 seconds feels urgent and a query taking 30 ms feels fine. If the first runs nightly and the second runs a thousand times a minute, your attention is on the wrong one. Let the totals choose.
11
What the interviewer is probing.1. "What is the first step?" Probing: the discipline. Stalls: "Add an index." Moves up: find the actual slow query with logs or statistics — optimising a query nobody runs is wasted work.
12
2. "What do you check before rewriting?" Probing: the order. Stalls: "Nothing." Moves up: the plan and whether estimates match reality; stale statistics make every other conclusion unreliable.
13
3. "How do you know a change helped?" Probing: the measurement. Stalls: "It feels faster." Moves up: compare plans and timings on representative data volumes — a change that helps on a thousand rows can hurt on a million.
14
4. "When is the fix not in the database?" Probing: the breadth. Stalls: "Never." Moves up: when the query is fine and the problem is N+1, missing caching, or fetching far more data than the application uses.
Use cases:Tuningpg_stat_statementsWorkflow