sql-optimization

# 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 1. Which **queries** or **endpoints** are slow? User-facing vs batch? 2. **Regression**—did deploy, data volume, or stats change? 3. **Isolation level** and **consistency** requirements—can we read replicas? 4. **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.