Query Optimization

Query Optimization

Purpose

Find the queries that actually cost the most, understand why they're slow from the plan, and fix the root cause (missing index, N+1, bad estimate) — measure-first, never guess.

Universal — the find-rank-diagnose-fix workflow (statement stats → query plan → index/rewrite) applies to any SQL DB; tool names differ.

Procedure

1. Rank queries by total cost — measure first

   • Use pg_stat_statements ordered by total_exec_time (not single-call time)
   • The biggest win is usually a moderately-slow query called millions of times, not the one slow query
   • Identify the top 5 offenders

2. Diagnose each offender with EXPLAIN (ANALYZE, BUFFERS)

   • Look for: Seq Scan on large tables, bad row estimates (estimated vs actual rows far apart), nested loops over big sets, high buffers read (IO)
   • ANALYZE runs the query; BUFFERS shows IO — both needed for truth
   • Compare planning time vs execution time — if planning dominates, suspect many partitions, complex inheritance, or stale prepared-statement plans
   • "Slow query" can really be connection-pool exhaustion (queries queue behind connections, not behind themselves) — confirm via pg_stat_activity waits before chasing the plan

3. Detect N+1 at the ORM layer first

   • Symptom: one query + N follow-up queries per row
   • Fix in the ORM (include / findMany batching / DataLoader) before reaching for raw SQL
   • N+1 is the #1 backend perf bug and it hides in the application, not the DB

4. Fix by root cause

   • Missing index → add the right type (see schema-design)
   • Bad estimate → ANALYZE the table / increase statistics target. Multi-column correlations (e.g. WHERE country='KR' AND city='Seoul') need CREATE STATISTICS (extended stats) — single-column stats over-estimate selectivity
   • Index used to work, now slow → suspect index bloat from heavy UPDATE/DELETE; REINDEX INDEX CONCURRENTLY and tune autovacuum
   • Hot-row contention (many writers contending on one row) → not an index problem; design SELECT ... FOR UPDATE SKIP LOCKED for queue patterns, or partition the hot key (cross-ref transaction-management)
   • N+1 → batch / eager-load
   • Genuinely expensive aggregate → materialized view or cache (see caching-strategy)

5. Validate (validation loop)

   • Re-run EXPLAIN ANALYZE; if still Seq Scan / still slow → the index isn't being used (check column order, type mismatch, function-wrapping) → adjust and re-run
   • Re-check pg_stat_statements after deploy — confirm the query dropped in ranking

Anti-patterns

Severity tiers

Completion Criteria

• Top 5 pg_stat_statements offenders diagnosed
• No Seq Scan on large tables in hot paths
• N+1 eliminated at ORM layer
• Each fix verified with before/after EXPLAIN ANALYZE
• All Critical findings fixed; all Major scheduled

Output

• Query audit report: docs/query-audit-YYYY-MM-DD.md — top offenders, plans, fixes, before/after timings
• Index migrations + ORM eager-load changes
• Commit format: perf(db): eliminate N+1 in <endpoint> / perf(db): add index for <query>

Implementation

TypeScript + Prisma + Postgres (default)

• Stats: SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
• N+1: Prisma include / select with relations; avoid per-row findUnique in a loop
• Plan: EXPLAIN (ANALYZE, BUFFERS) <query> in psql / Supabase SQL editor
• Prisma query logging (log: ['query']) in dev to spot N+1
• PgBouncer transaction-pooling caveat: it doesn't keep server-side prepared statements across transactions. Either run PgBouncer in session-pooling for prepared-statement workloads, or disable Prisma prepared statements (?statement_cache_size=0) — silent perf cliff otherwise
• Bloat / autovacuum: pg_stat_user_indexes for unused indexes; pgstattuple for bloat; REINDEX INDEX CONCURRENTLY to rebuild without lock

Other stacks

• Python: SQLAlchemy selectinload/joinedload for N+1; pg_stat_statements same
• Go: sqlc / GORM Preload; same Postgres tooling
• Universal: pg_stat_statements + EXPLAIN ANALYZE are Postgres; MySQL uses performance_schema + EXPLAIN ANALYZE (8.0+)

Related skills

• schema-design — the fix is often the right index type
• performance-profiling — query time is usually the top backend bottleneck
• caching-strategy — cache the query result when optimization hits its limit

Reference

• Key insight encoded: Use pg_stat_statements to rank by total time, then EXPLAIN (ANALYZE, BUFFERS) the offenders looking for Seq Scans / bad row estimates / N+1 before adding indexes. Three senior diagnostics that look like "slow query" but aren't: connection-pool exhaustion, index bloat after heavy writes (re-REINDEX CONCURRENTLY), and correlated-predicate misestimation (needs extended CREATE STATISTICS). PgBouncer transaction-pooling silently breaks server-side prepared statements — pin pooling mode to your driver's assumption.