sql-query-optimization

SQL Query Optimization Skill

Overview

Diagnoses, profiles, and optimizes slow SQL queries across traditional relational engines (PostgreSQL, MySQL, SQL Server, SQLite, Oracle) under the rule: start from the execution plan, never from intuition. Analyzes EXPLAIN ANALYZE plans, designs selective composite indexes, rewrites non-sargable predicates and deep OFFSET pagination, and enforces concise artifact caps and fail-honest plan reporting.

When to Use

Trigger Scenarios

• Query latency spikes, timeouts, sequential scan bottlenecks, or high CPU/buffer reads on OLTP RDBMS engines.
• Analyzing EXPLAIN (ANALYZE, BUFFERS) (Postgres) or EXPLAIN ANALYZE (MySQL/SQL Server) execution trees.
• Designing composite/covering indexes, unwrapping indexed predicates, and converting OFFSET to keyset pagination.

When NOT to Use

• Google Cloud BigQuery MPP queries: Route to bigquery-query-optimization.
• Schema migrations and DDL lock safety: Route to database-migration-expert.
• Application-layer caching: Route to performance-scalability.

Process

Phase 1: Fail-Honest Execution Plan Diagnostics

1. Obtain the Live Plan: Run EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <query>; (or read-only EXPLAIN for mutating statements wrapped in BEGIN; ... ROLLBACK;).
2. Fail-Honest Fallback (STATIC_ANALYSIS_ONLY / BLOCKED): If DB EXPLAIN fails due to missing credentials or an unreachable database, explicitly mark the output STATIC_ANALYSIS_ONLY / BLOCKED: Live plan not executed — <exact error> and NEVER invent row counts, buffer reads, or EXPLAIN ANALYZE latencies.
3. Locate Bottlenecks: Inspect unexpected Seq Scan nodes, massive estimated-vs-actual row skew (run ANALYZE <table>; if stale), and disk-spilling external sorts/hashes.

Phase 2: Indexing & Query Rewriting Patterns

1. Composite Index Ordering: Index WHERE, JOIN ... ON, and ORDER BY columns ordered equality predicates first, then range/sort columns; avoid over-indexing write-heavy tables.
2. Sargable Predicates: Unwrap indexed columns (WHERE created_at >= '2026-09-01' AND created_at < '2026-09-02' instead of WHERE DATE(created_at) = '2026-09-01').
3. Keyset Pagination & Decorrelation: Replace OFFSET 50000 with WHERE id > :last_seen_id ORDER BY id ASC LIMIT 20; convert correlated subqueries into explicit JOINs; replace SELECT * to enable Index-Only Scans.