sql-optimization

by codekungfu

Install this Skill

curl -fsSL -o sql-optimization.zip https://skill.seembox.com/api/v1/skills/sql-optimization/download && unzip sql-optimization.zip

Download sql-optimization.zip

SKILL.md About

---
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

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.

_meta.json Metadata

{
  "ownerId": "kn79jhw85bss2z4dbpshhxg64x82zn3p",
  "publishedAt": 1774366585889,
  "slug": "sql-optimization",
  "version": "1.0.0"
}