Lesson 17 · Query performance
Tuning slow queries
A repeatable method — measure, don't guess.
Your win: follow a reliable loop to diagnose and fix a slow query, pulling together the planner, EXPLAIN, scans/joins, and indexes from this course — and know the exact workflow in this repo.
The loop
1. REPRODUCE with realistic parameters
2. EXPLAIN (ANALYZE, BUFFERS) the query
3. FIND the most expensive node (actual time × loops, biggest I/O)
4. DIAGNOSE why it's slow (table below)
5. CHANGE one thing
6. RE-MEASURE confirm it actually helped → repeat
| Symptom in the plan | Likely fix |
|---|---|
| estimated ≠ actual rows | ANALYZE (stale stats, Lesson 14) |
| Seq Scan + selective filter | add/fix an index (Part 3) |
| function wraps the column | expression index (Lesson 12) |
| composite index unused | match the leading column / reorder (Lesson 12) |
huge JSONB pulled by SELECT * | select fewer columns (TOAST, Lesson 7) |
| Nested Loop over many rows | help the planner estimate; index the inner side |
The golden rule
Measure, don't guess. Change one thing, then re-run
EXPLAIN ANALYZE to confirm it helped. Intuition about query cost is wrong
surprisingly often — the plan is the ground truth.
Anchor — the workflow in this repo
Since there's no
EXPLAIN in the Go code, the real loop is:
(1) an OpenTelemetry span (every repo method wraps
interceptors.StartSpan) or the activity logs in the zeus DB tell
you which query/endpoint is slow and with what params; (2) the
/query-db skill (read-only, RLS set for you) runs
EXPLAIN ANALYZE on the real SQL against Cloud SQL; (3) you
add an index (often a composite led by resource_path, or a
deleted_at IS NULL partial index), trim SELECT *, or rewrite —
then re-measure.
Read this next
PostgreSQL — Performance Tips + Use The Index, Luke!
The docs' performance chapter (populating, planner control), and Winand's practical guide to fixing the index-related slow queries you'll meet most.
→ postgresql.org/docs — Performance Tips
→ use-the-index-luke.com
Check yourself (from memory)
Q1. The first step in tuning a slow query is…
See the real plan first. Fixing before measuring is
guessing — and usually wrong.
Q2. The golden rule of tuning is…
Change one thing, re-measure with
EXPLAIN ANALYZE. The plan is the ground truth.
Q3. In our repo, you find the slow query via…
Spans/activity logs point at the query;
/query-db
runs the EXPLAIN. No EXPLAIN lives in the app code.
Describe the repeatable method for tuning a slow query.
recall, then click to reveal
(1) Reproduce with realistic params. (2)
EXPLAIN (ANALYZE,
BUFFERS). (3) Find the most expensive node (actual time × loops, biggest I/O).
(4) Diagnose: estimate-vs-actual gap → ANALYZE; Seq Scan on a selective
filter → add/fix an index; function-wrapped column → expression index; wrong composite
leading column; big JSONB via SELECT * → select fewer columns.
(5) Change ONE thing. (6) Re-measure. Measure, don't guess. In our repo: OTel span →
/query-db EXPLAIN ANALYZE → index/rewrite → re-measure.
🎓 That's Part 4 — query performance
You can explain how the planner chooses, read a plan, name the scans and joins, and run a
disciplined tuning loop. That's the core of "diagnose a slow query" interviews.
Part 5 returns to correctness under load: transactions, isolation
levels, and locks — building on MVCC.
Ready for Part 5 (transactions & concurrency), or a mixed quiz across
Lessons 14–17 first? Ask me.