performance
Query Optimization Workflow
A repeatable process: log slow queries → run EXPLAIN ANALYZE → identify the operator that's expensive → fix (index, rewrite, denormalize) → verify.
Optimising database performance is most effective as a repeatable, evidence-driven process rather than a bag of tricks applied hopefully. Random tuning — adding indexes on a hunch, rewriting queries that were never the problem — burns time and can make things worse. A disciplined workflow finds the query that actually matters, diagnoses why it is slow, applies the right fix, and proves the fix worked.
Doctor's rounds — vitals first, focus on the worst patient, diagnose, treat, re-measure. Not "try every pill in the cabinet."
Key Concepts
1
The loop has five steps. First, find the real culprit: enable the slow query log (or use an APM/performance dashboard) to identify which queries are actually consuming time in aggregate — often a moderately slow query run thousands of times costs more than one genuinely slow report. Second, diagnose with EXPLAIN ANALYZE to see the execution plan and where the time and rows really go, comparing estimated against actual to spot bad cardinality. Third, identify the single most expensive operator — the full scan, the nested loop over millions of rows, the spilling sort. Fourth, apply the fix that targets that operator: add or reorder an index to turn a scan into a seek, rewrite a predicate that is defeating an index, reduce the rows entering a join, update stale statistics, or denormalize a hot aggregate as a last resort. Fifth, verify by re-running EXPLAIN ANALYZE and measuring against the original — and check you have not regressed write performance or other queries.
EXPLAIN ANALYZE
2
The mindset interviewers reward is "measure, change one thing, measure again." Optimise the queries that the data says matter, base each change on the plan rather than intuition, and confirm the result with numbers. This also guards against the trap of micro-optimising a query that runs once a day while ignoring the cheap-looking one on the critical path that runs constantly.