- name
- sql-optimization
- description
- Deep SQL performance workflow—symptom framing, execution plans, indexing strategy, query rewrite, locking/transaction behavior, statistics, partitioning, and verification. Use when queries time out, DB CPU spikes, or migrations change access patterns.
SQL Optimization (Deep Workflow)
Optimization without measurement is guesswork. Structure the work as observe → explain (plan) → change → verify, with explicit attention to correctness, locks, and write amplification from indexes.
When to Offer This Workflow
Trigger conditions:
- Slow queries, growing P95/P99, replication lag, lock waits
- ORM-generated SQL surprises; N+1 at DB layer
- Index explosion, bloat, or “we added indexes everywhere”
Initial offer:
Use six stages: (1) frame the problem, (2) reproduce & measure, (3) read execution plans, (4) schema & indexes, (5) query & transaction tuning, (6) verify & guardrail. Confirm engine (PostgreSQL, MySQL, SQL Server, etc.) and environment (prod-like data volume).
Stage 1: Frame the Problem
Goal: Define SLO, scope, and non-goals.
Questions
- Which queries or endpoints are slow? User-facing vs batch?
- Regression—did deploy, data volume, or stats change?
- Isolation level and consistency requirements—can we read replicas?
- Write risk: is this table write-heavy? Index cost?
Exit condition: One-line problem statement with metric (e.g., “p95 2.4s on /reports at 10k RPS”).
Stage 2: Reproduce & Measure
Goal: Stable repro with representative cardinality and parameters.
Actions
- Capture exact SQL, parameters, and frequency
- Use EXPLAIN (ANALYZE, BUFFERS) or equivalent—engine-specific
- Check buffer cache effects: cold vs warm cache; run twice when needed
- Compare prod stats vs staging—row counts, histograms
Pitfalls
- Optimizing on empty dev DB
- Different parameter sniffing values changing plan choice
Exit condition: Baseline numbers + plan hash or saved plan for A/B.
Stage 3: Read Execution Plans
Goal: Name the dominant cost: seq scan, bad join order, sort, hash spill, nested loop explosion.
Interpret (adapt to engine)
- Seq scan on large tables—filter selectivity? missing index? stats?
- Index scan vs bitmap vs index only—covering indexes trade-offs
- Joins: wrong order, missing stats, outdated NDV
- Sort/hash spills to disk—work_mem / memory grants
- Locks:
FOR UPDATE, long transactions, hot row updates
Exit condition: Hypothesis tied to plan node(s), not generic “add index.”
Stage 4: Schema & Indexes
Goal: Right indexes for read paths without destroying writes.
Strategy
- Composite index column order: equality → range; avoid redundant indexes
- Partial indexes for hot subsets
- Covering indexes vs table bloat—measure write cost
- Foreign keys and constraints affecting plans
- Statistics:
ANALYZE, extended stats, histograms—when stale stats lie
Advanced (when relevant)
- Partitioning for prune + maintenance
- Materialized views / pre-aggregation for heavy reports
Exit condition: DDL proposal with rationale and rollback (drop index concurrently if supported).
Stage 5: Query & Transaction Tuning
Goal: Sometimes the fix is SQL rewrite, not hardware.
Techniques
- Reduce rows touched early (CTEs vs inline—engine-dependent)
- Pagination without OFFSET on huge pages (keyset)
- Batch vs row-by-row; UNION ALL vs OR
- N+1: batch queries, joins, data loader patterns
- Transactions: shorten locks; avoid unnecessary
SELECT FOR UPDATE - ORM: eager vs lazy loading discipline
Exit condition: New plan shows lower cost / measured latency; lock time acceptable.
Stage 6: Verify & Guardrail
Goal: Improvement holds under load and doesn’t regress neighbors.
Verify
- Re-run EXPLAIN ANALYZE with production-like parameters
- Load test or shadow traffic if available
- Monitor: buffer hit ratio, index bloat, replication lag
Guardrails
- Query timeouts and statement_timeout where safe
- Alerts on sequential scans on large tables if observability supports
Final Review Checklist
- [ ] Baseline and target metrics documented
- [ ] Plan-based root cause, not guesswork
- [ ] Index/DDL changes justified vs write load
- [ ] Transaction/lock behavior considered
- [ ] Verification on realistic data and load
Tips for Effective Guidance
- Always mention parameter sniffing and stale statistics as frequent culprits.
- Warn when adding indexes on very write-heavy tables without measuring bloat.
- Prefer keyset pagination education for large lists.
Handling Deviations
- No EXPLAIN access: infer from symptoms + ORM logs + index list; recommend safe staging repro.
- Vendor DB: name that hints and features differ—avoid PostgreSQL-only advice on SQL Server without caveat.