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

# 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

Coverage Model

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

Bottleneck decision table:

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:

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:

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.