SQL Optimization

Out of scope: OLAP engines and lakehouse tuning. Use data-lake-platform for ClickHouse, DuckDB, Doris, StarRocks, Iceberg, Delta Lake, or Hudi.

Quick Reference

Scripts

Script	What it does	Usage
scripts/pg_slow_query_triage.sql	Six-section triage report from pg_stat_statements: total time, mean time, I/O, variance, cache-hit ratio, and spills/planning/WAL	Copy-paste into psql or any SQL client; requires pg_stat_statements extension
scripts/explain_collector.py	Collects estimated JSON plans via psql; --analyze opts into query execution	DATABASE_URL=postgresql://... python explain_collector.py --queries slow.txt
scripts/test_explain_collector.py	Offline input, execution-mode and JSON-envelope regressions	python scripts/test_explain_collector.py

# Triage: paste directly into psql
psql "$DATABASE_URL" -f skills/universal/data-sql-optimization/scripts/pg_slow_query_triage.sql

# Collect estimated plans for reviewed queries (default):
python scripts/explain_collector.py --queries queries.txt --no-analyze --output plans.jsonl

# Collect actual plans (executes queries — review side effects first):
DATABASE_URL=postgresql://user:pass@test-db:5432/db \
  python scripts/explain_collector.py --queries queries.txt --analyze --output plans.jsonl

Need	Start Here	Use When
Slow query triage	template-slow-query.md	You need a safe intake before changing anything
Plan review	references/explain-analysis.md	You already have EXPLAIN, EXPLAIN ANALYZE, Query Store, or Performance Schema evidence
Index design or index removal	references/index-patterns.md	You are deciding whether to add, reshape, make invisible, or drop an index
Query rewrite	references/query-tuning-patterns.md	A query shape or estimation problem is the likely bottleneck
Connection saturation	references/connection-pooling-patterns.md	App pools, PgBouncer, RDS Proxy, Supavisor, or Cloud SQL pooling are involved
Monitoring and alerting	references/monitoring-alerting-patterns.md	You need dashboards, baselines, or alerts for database performance
Locking / deadlocks	template-lock-analysis.md	The issue is blocking, deadlocks, or long transactions rather than raw query cost
Partitioning	references/partition-strategies.md	Retention, pruning, or table growth is driving the change
Backup and recovery design	references/recovery-strategy-design.md	You need a recovery capability mapped to failure scenarios, not just a backup job
Security or RLS review	template-security-audit.md	You are reviewing least privilege, SQL injection controls, or tenant isolation

Coverage Model

Engine	Status	Notes
PostgreSQL	Primary	Deepest coverage. Version-gated features used here (skip scan, AIO, uuidv7(), statistics kept across pg_upgrade) arrived in PostgreSQL 18
MySQL	Primary	Recommend an LTS line, not an Innovation release, for production. Check whether the HyperGraph optimizer is on by default in the user's version before relying on its plans
SQL Server	Primary	Use Query Store for plan regressions; IQP features have different release and compatibility prerequisites. Check the Microsoft feature matrix for the exact feature before recommending it
Oracle	Secondary	Use templates and official docs for optimizer-specific edge cases
SQLite	Secondary	Focus on indexes, planner behavior, WAL, and PRAGMA optimize

Version lookups: take the user's exact version from SELECT version() (or @@version) first; current minor releases are in data/versions.json (postgresql, mysql), refreshed by script. For which majors or LTS lines are supported and when each reaches end of life, read the vendor's versioning or support-policy page; plan an upgrade before the line in use reaches EOL. Before recommending a feature from a newer major, confirm in the release notes that the major is GA; do not recommend beta-only features for production.

First Response Checklist

Before recommending changes, collect:

1. Database engine and exact version
2. Query text or workload shape
3. Relevant schema, indexes, and estimated row counts
4. Actual evidence: plan output, wait stats, query stats, or error text
5. Recent changes: schema, config, deploy, traffic spike, or data skew
6. Concurrency context: app pool, server pooler, replica topology
7. Success metric: p95 latency, CPU, reads, lock time, error rate, or connection count

If any of these are missing, request them or use the intake templates before suggesting a production change.

EXPLAIN-Driven Diagnosis Checklist

Start with an estimated plan. Use actual-plan capture only after reviewing the statement, functions and triggers: ANALYZE executes it, and rollback does not undo sequence changes or external effects. The collector accepts one line per statement and rejects internal semicolons, including inside literals/comments.

-- PostgreSQL: estimated plan first; opt into ANALYZE for reviewed execution
EXPLAIN (VERBOSE, FORMAT TEXT) <query>;

-- MySQL: get JSON plan for detailed cost breakdown
EXPLAIN FORMAT=JSON <query>;

-- SQL Server: turn on I/O and CPU evidence
SET STATISTICS IO, TIME ON;
<query>;

Step	What to Check	Red Flag
1	Operator time and loops	Parent times include child work; do not sum them or treat cost units as milliseconds
2	Rows estimated vs rows actual	Ratio >10x in either direction
3	Loops * rows per loop = total rows processed	High total even if one loop looks cheap
4	Shared hit vs read buffers (PostgreSQL)	reads >> hits on a hot query
5	Sort or hash spill	Sort Method: external merge, Hash Batches > 1
6	Key/bookmark lookup on hot path	Many per parent row; add INCLUDE columns
7	Nested loop outer rows × inner work	Check estimates and repeated inner scans before choosing a join strategy
8	Waiting time >> execution time	Investigate locks or pool saturation, not the plan

Bottleneck decision table:

Plan shows	Likely cause	First lever
Seq scan, high rows-read/rows-returned	Missing/unusable index or valid scan choice	Check selectivity and sargability; compare an index trial
Index scan but high loops	N+1 or bad join order	Batch or fix estimation
Actual >> estimated rows	Stale/insufficient stats	ANALYZE; CREATE STATISTICS (PG); histogram (MySQL)
Plan varies by parameter	Parameter sensitivity	Query Store / OPPO (SQL Server); separate query shapes
Sort spill	Projection too wide; no order-aligned index	Narrow projection; add covering index
Cheap plan but slow wall time	Waits: locks, I/O, pool	Check pg_stat_activity, wait events, pool stats

Workflow

1. Confirm the engine, workload, symptom, and evidence available before suggesting a change.
2. Route search, lakehouse, backend-architecture, or observability-heavy work to the adjacent skill when SQL tuning is not the primary problem.
3. Gather plans, stats, and workload context before proposing indexes, rewrites, or configuration changes.
4. Change one lever at a time and verify correctness plus performance impact after each step.
5. Re-check version-sensitive behavior with the navigation references before final recommendations.

Production Change Gate

Choose proof and rollback by the change being made:

Change	Trial	Rollback trigger	Rollback
Query rewrite	Replay representative parameters and concurrency; compare result sets	Wrong rows or worse p95/reads/locks	Revert query or feature flag
New index	Build with the engine's online/concurrent path where available; confirm chosen plans	Write latency, lock time, or storage exceeds budget	Drop with the safe online path after dependents are checked
Statistics change	Capture plans before/after across skewed values	Regression for another parameter class	Restore target/statistics setting and analyze
Pool/config change	Canary one service or pool; watch waits and saturation	Queueing, timeouts, or connection churn rises	Restore prior value and recycle only affected pools
Partition/schema change	Rehearse on production-shaped data and verify dual reads/writes	Row-count mismatch, blocked writers, or replication lag	Stop cutover and return traffic to old path

Do not declare a tuning win from one warm-cache execution. Record correctness, p50/p95/p99, logical/physical reads, CPU, locks, and write impact over the same workload window; name any metric that could not be measured.

Routing Guide

If the problem is a slow query

• Start with template-slow-query.md
• Then use:
  • PostgreSQL: template-pg-explain.md
  • MySQL: template-mysql-explain.md
  • SQL Server: template-mssql-explain.md
  • Oracle: template-oracle-explain.md

If the likely problem is cardinality or estimator drift

• PostgreSQL: check references/operational-patterns.md for CREATE STATISTICS, pg_upgrade statistics retention, and PG18 planner changes
• MySQL: use histograms and optimizer statistics in references/operational-patterns.md
• SQL Server: inspect Query Store, parameter sensitivity, and OPPO in template-mssql-explain.md

If the issue is index design

• Start with references/index-patterns.md
• Use vendor templates:
  • PostgreSQL: template-pg-index.md
  • MySQL: template-mysql-index.md
  • SQL Server: template-mssql-index.md

If the issue is blocking or lock waits

• Use template-lock-analysis.md
• Favor transaction-shape fixes before configuration changes

If the issue is connection pressure

• Use references/connection-pooling-patterns.md
• Distinguish connection helpers from actual poolers. Cloud SQL Auth Proxy and language connectors are not poolers by themselves.

If the request is PostgreSQL tenant isolation or privilege review

• Use template-pg-rls.md
• Pair with template-security-audit.md

Navigation and Templates

Load cross-engine worksheets only when documenting the corresponding decision:

Decision	Template
Multi-symptom incident diagnosis	diagnostics
Rewrite and result-equivalence review	query tuning
New schema or integrity review	schema design
Record baseline, experiment and verification	tuning worksheet
Record a plan review across engines	EXPLAIN analysis
Record index trial and write impact	index design
Rehearse schema rollout and rollback	migration
Rehearse recovery and verify recovered data	backup/restore
Plan replication topology, lag, and failover	MySQL replication/HA, PostgreSQL replication/HA
Tune an embedded SQLite workload	SQLite optimization

Reference guides — load on demand:

• references/explain-analysis.md — Load when reading EXPLAIN/EXPLAIN ANALYZE output: row estimates, join order, memory spills, per-engine capture commands.
• references/index-patterns.md — Load when deciding whether to add, reshape, or retire an index; covers composite, partial, covering, BRIN, invisible, and PG18 skip scan.
• references/query-tuning-patterns.md — Load when the likely fix is in the SQL itself: sargability, OR rewrites, keyset pagination, N+1 collapse, estimation fixes.
• references/sql-best-practices.md — Load for workload-grounded tuning defaults and safe-change workflow; useful before making production changes.
• references/sql-antipatterns.md — Load during schema or query code review to detect and remediate common anti-patterns (SELECT *, N+1, EAV, non-sargable predicates).
• references/query-optimization-research-runtime.md — Load when a recommendation depends on version-specific engine behavior (PG18 AIO, MySQL HyperGraph, SQL Server 2025 IQP, LITHE rewrite research).
• references/partition-strategies.md — Load when table growth, retention, or vacuum pressure motivates partitioning; includes migration patterns and pg_partman guidance.
• references/connection-pooling-patterns.md — Load when the symptom is connection saturation, pooler misconfiguration, or cloud-managed pool selection (PgBouncer, RDS Proxy, Supavisor, Cloud SQL).
• references/monitoring-alerting-patterns.md — Load when setting up query stats, wait-event monitoring, or alert thresholds for PostgreSQL, MySQL, or SQL Server.
• references/operational-patterns.md — Load for the production tuning workflow, safe migration checklist, engine-specific operational cautions, work_mem sizing, idle-in-transaction lock cascades, and online schema change tooling (gh-ost vs pt-osc).
• references/recovery-strategy-design.md — Load when designing or reviewing backup and recovery: failure-scenario taxonomy, detection per failure class, storage tiering, recovery testing as a deliverable.

Primary sources live in data/sources.json.

Known Traps

• Sequential scans, hash joins, materialization, subqueries and CTEs can be correct choices. Require plan evidence before rewriting.
• Each added index increases write and maintenance work. Check overlapping indexes and measure write impact alongside read gains.
• Development data hides production skew and tenant hot spots; compare representative parameters under concurrency.
• Planner hints and session knobs are diagnostic experiments after query, schema and statistics checks; do not transfer hints across engines.

Related Skills

• software-backend - Application query generation, ORM behavior, and API/database interaction
• software-security-appsec - SQL injection prevention, auth, secrets, and hardening
• ops-devops-platform - Infrastructure, failover automation, and operational runbooks
• qa-observability - Telemetry, alerting, and SLO design
• qa-debugging - Production incident debugging workflow
• data-lake-platform - OLAP engines, Parquet, and warehouse/lakehouse tuning

Learnings Loop

When prior decisions or pitfalls are relevant, consult learnings.consolidated.md if present; use learnings.md only for needed history or as the available fallback. Otherwise skip both.

After applying it, if you encountered a pattern worth remembering, a mistake worth preventing, or a domain fact that surprised you, append one dated bullet to learnings.md via agents-skills-feedback-loop/scripts/append_learning.py. Do not modify SKILL.md itself.