The tuning workflow

Step 1 — find the query that actually matters

You cannot tune what you haven’t measured, and intuition about “the slow query” is almost always wrong. The first move is pg_stat_statements — an extension that aggregates every executed query (normalized, with literals stripped) and tracks its cumulative cost. The number that matters is total_exec_time, not mean_exec_time: a 5 ms query run 2 million times costs more total database time than a 4-second report run twice, and shaving the former saves more.

-- The queries eating the most total time across the whole server:
SELECT
  substring(query, 1, 60)        AS query,
  calls,
  round(total_exec_time)         AS total_ms,
  round(mean_exec_time, 2)       AS mean_ms,
  round(100 * total_exec_time / sum(total_exec_time) OVER (), 1) AS pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Read the pct column: tuning is Pareto. If one query is 78% of total time, that’s your whole afternoon; the rest is noise until it isn’t. Sort by total_exec_time, pick the top, ignore everything else.

Step 2–4 — reproduce, diagnose, fix the smallest thing

Once you have the query, the loop is mechanical:

• Reproduce with EXPLAIN (ANALYZE, BUFFERS) (lesson 01). This is the ground truth — never tune from the query text alone.
• Diagnose by matching the evidence to one root cause (the whole unit, compressed):
  • Big estimated-vs-actual row gap? → stale or independence-assuming statistics → ANALYZE or CREATE STATISTICS (lesson 03).
  • Seq Scan on a selective filter, high read= buffers? → missing index → add one (CONCURRENTLY), per the databases indexes chapter.
  • Live rows tiny but buffers/size huge? → bloat → vacuum/pg_repack (lesson 04).
  • Estimates fine, right scan, still slow? → genuinely too much data → rewrite, paginate, or pre-aggregate.
• Apply the smallest fix that addresses that cause. One change. A targeted ANALYZE beats a new index; a new index beats a rewrite; a rewrite beats a config change — in that order of locality, because the smallest change has the least blast radius.
• Verify by re-running EXPLAIN (ANALYZE, BUFFERS) and confirming the number moved. Then re-check pg_stat_statements after real traffic — sometimes the fix shifts the bottleneck to the next query, and the loop repeats.

Why “measure, don’t guess” is the whole discipline

Every anti-pattern in tuning is a guess that skipped a measurement step:

• Premature indexing — adding indexes “to be safe” before any query proves it needs one. Each index taxes every write (the databases indexes chapter quantifies it) and can mislead the planner; an unused index is pure cost. Add an index only after EXPLAIN shows the Seq Scan it would remove.
• Cargo-cult config — copying random_page_cost or work_mem values from a blog without measuring your workload.
• Rewriting blind — the junior in the hook rewrote SQL three times because the text felt slow, when the real cause was a stale estimate the rewrite couldn’t touch.

The senior reflex is the inverse: every change is preceded by a measurement that justifies it and followed by a measurement that confirms it. That’s what makes the loop converge instead of thrash. (The unit-09 capstone runs this exact loop end-to-end on a realistic analytics API — this lesson is the method it applies.)