SQL Optimizer

SQL Optimizer

A disciplined, repeatable methodology for making slow SQL fast — read the EXPLAIN ANALYZE plan, find the single worst node, hypothesize the single fix that moves it, ship it, and re-measure. The skill is grounded in how senior DBAs actually work: small, ordered, evidence-driven changes — not a shotgun blast of CREATE INDEX statements.

Most "slow query" tickets come down to one of a small number of patterns: a missing or wrong-order composite index, a stale-statistics cardinality miss, a sort spilling to disk, or an N+1 in the app layer. This skill encodes the workflow, the plan-reading glossary, the indexing rules (leftmost-prefix, covering, partial, expression), the anti-patterns, and a red-flag scan into a single repeatable pass.

When to Activate

Activate when the user:

• Shares a slow SQL query and asks why it's slow or how to speed it up.
• Pastes an EXPLAIN ANALYZE (or EXPLAIN FORMAT=JSON) output and asks you to read it.
• Shares a schema / index DDL diff for pre-deployment review ("should we add this index?", "is this column order right?").
• Pastes an ORM-generated query (Django, Rails, SQLAlchemy, Hibernate, Prisma, EF Core) and asks why it's slow — or shares logs that look like N+1.
• Shares a pg_stat_statements row, MySQL slow query log entry, or an APM trace with a hot SQL call.
• Asks any of: "why is this query slow?", "what index do I need?", "is this an N+1?", "how do I read this plan?", "is my composite index in the right order?"

Honest scope & limits — read first.

• I cannot run the query against a live database. I reason over what you provide: the query, the EXPLAIN output, the schema/DDL, row counts, sample data, and engine/version.
• Plans are data- and config-dependent. A plan on a 1k-row dev table says nothing about a 100M-row prod table — always re-run EXPLAIN ANALYZE on prod-sized data.
• Index changes are not free: every index slows writes and consumes buffer cache. Verify the win before shipping.
• ⚠️ EXPLAIN ANALYZE on an INSERT / UPDATE / DELETE actually performs the write. Wrap in BEGIN; ... ROLLBACK; or run on a clone.
• This skill is not a substitute for proper APM + slow-query monitoring in production.

Step 1: Intake & Scope

Before reading a single plan line, establish exactly what you're tuning.

1. Engine and version. PostgreSQL 15? MySQL 8.0? SQLite? SQL Server? This skill goes deepest on PostgreSQL and MySQL 8+, with coverage of SQLite/SQL Server where the workflow is the same. Version matters: PG 12+ inlines CTEs by default, MySQL 8 added hash join and histograms, etc.
2. The query itself. The exact SQL — not a paraphrase. ORM-generated? Capture the actual SQL it emits (django.db.connection.queries, Rails ActiveRecord::Base.logger, Hibernate show_sql=true, etc.).
3. The schema. CREATE TABLE for every table in the query, plus every existing index (\d+ tablename in PG, SHOW CREATE TABLE in MySQL). Without this you cannot tell what's missing.
4. Cardinalities. Row count per table, roughly. "Big" means different things at 10M vs 10B. For the worst query, also: rows after each predicate (SELECT count(*) FROM t WHERE …).
5. The EXPLAIN output, if any. Prefer EXPLAIN (ANALYZE, BUFFERS) in PG and EXPLAIN ANALYZE or EXPLAIN FORMAT=JSON in MySQL 8+. Plain EXPLAIN is a guess; ANALYZE runs the query for real. (See the DML warning above.)
6. What "slow" means. Current p50/p95 latency. Target latency. Is this a hot OLTP query (every web request) or an analytical query (nightly job)? The fix calculus is different.
7. Workload context. Reads vs writes ratio, prepared statements, app connection pool size, replication setup. Tell-tale signs of parameter sniffing or plan instability.

If any of these are missing, ask — don't invent them.

Step 2: The 6-Step Workflow

1. Reproduce on real data        — tiny dev sets always pick the wrong plan.
2. Capture the plan              — EXPLAIN (ANALYZE, BUFFERS) in PG;
                                   EXPLAIN ANALYZE / FORMAT=JSON in MySQL 8+.
3. Diagnose the worst node       — highest (actual_time × loops) or biggest
                                   "Rows Removed by Filter" wins.
4. Hypothesize one fix           — missing index? wrong order? stale stats?
                                   N+1? subquery that should be a join?
5. Fix ONE thing, re-EXPLAIN     — never two changes at once.
6. Re-measure hot AND cold cache — hot run hides disk cost; do both.

The order is non-negotiable. Skipping step 1 wastes everyone's afternoon. Skipping step 6 ships a "fix" that only works while pages are still in RAM.

Step 3: Reading a Plan — the Glossary

Every PostgreSQL and MySQL plan is built from a small set of operators. Knowing what each one means — and when it's a red flag — is 80% of the skill.

Beyond the operators, two diagnostic rules matter more than any single node:

• Plan Rows vs Actual Rows mismatch ⇒ stale statistics. Look at every node and compare the planner's rows= estimate to the actual rows the node returned. A 10×, 100×, or 10000× miss is a flashing red light: the planner picked the wrong join order, the wrong join strategy, or the wrong index. Fix the stats first, then re-EXPLAIN — often the bad plan goes away on its own.
• Rows Removed by Filter is the over-fetching signal. If a node returned 50 rows but Rows Removed by Filter: 1,200,000, the planner scanned 1.2M rows and threw 99.996% of them away. That's a missing index condition: the filter belongs inside the index, not on top of it.

Step 4: Indexes — the 90% Lever

Most "slow query" tickets are fixed by adding (or fixing the order of) a single index. Index design is its own discipline; here are the rules that matter for tuning.

Index types

The leftmost-prefix rule for composite indexes

For an index INDEX (a, b, c):

The practical heuristic for column order:

Equality columns first → range columns next → sort columns last.

So a query like WHERE country = ? AND created_at > ? ORDER BY created_at DESC wants INDEX (country, created_at DESC) — not the other way around.

Covering / INCLUDE indexes

CREATE INDEX idx_users_country_created_at
  ON users (country, created_at DESC)
  INCLUDE (email);

The INCLUDE columns aren't part of the key but are stored in the index leaf. This lets the planner pick an Index-Only Scan — it never has to touch the heap. For hot read paths returning a few columns, this is a massive win.

Partial indexes

CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

If 95% of orders are complete and you only ever query pending, a partial index is smaller, hotter in cache, and faster than a full index on (status, created_at).

Expression indexes

CREATE INDEX idx_users_email_lower ON users (lower(email));
-- Query MUST match the expression:
SELECT * FROM users WHERE lower(email) = 'alice@example.com';

A plain INDEX (email) is defeated by the lower() wrap in the predicate — see pitfall #2.

The 12 indexing pitfalls

1. Indexing low-cardinality columns alone — INDEX (is_active) where only two values exist; the planner correctly ignores it.
2. Function-wrap defeats index — WHERE upper(email) = ? defeats INDEX (email). Fix with an expression index or stop wrapping.
3. OR chains without a bitmap-or plan — WHERE a = ? OR b = ? can force a full scan. Often a UNION ALL of two indexed queries is faster.
4. Implicit type cast — WHERE id = '42' where id is bigint. The cast can block index use; pass the right type.
5. Wrong column order in composite — see the leftmost rule above. The single most common index bug.
6. Over-indexing — every index slows every INSERT / UPDATE / DELETE and consumes buffer cache. Drop the indexes nobody uses.
7. Extreme skew without extended stats — country = 'US' matches 80% of rows; the planner needs to know.
8. Stale statistics — ANALYZE the table; in PG, raise default_statistics_target on skewed columns.
9. LIKE '%abc' on a B-tree — a trailing-wildcard pattern cannot use a normal B-tree; needs pg_trgm + GIN (PG) or a generated/reverse column.
10. Missing partial index on the hot subset — the 95/5 case above.
11. Forgotten unused indexes — check pg_stat_user_indexes.idx_scan = 0 (or MySQL's sys.schema_unused_indexes). They cost writes and cache forever.
12. Invalid after a failed CONCURRENTLY build — pg_index.indisvalid = false. The index is on disk but the planner ignores it. DROP INDEX CONCURRENTLY and rebuild.

Step 5: Cardinality & Statistics

Bad estimates ⇒ wrong plans. Period. If the planner thinks a node will return 100 rows and it returns 10,000,000, it will pick Nested Loop and your query melts.

PostgreSQL

ANALYZE users;                                       -- refresh per-column histograms
ALTER TABLE users ALTER COLUMN country
  SET STATISTICS 1000;                               -- raise sample size for a skewed column
SET default_statistics_target = 500;                 -- session/global default; 100 → 500–1000 on skewed schemas

-- Extended statistics for correlated columns:
CREATE STATISTICS users_country_signup_stats (dependencies, ndistinct)
  ON country, signup_source FROM users;
ANALYZE users;

Autovacuum runs ANALYZE periodically, but only when row churn exceeds a threshold — bulk loads and slow-trickle writes often leave stats stale.

MySQL 8+

ANALYZE TABLE users;                                 -- refresh InnoDB stats
ANALYZE TABLE users UPDATE HISTOGRAM ON country WITH 64 BUCKETS;  -- skewed-column histogram

MySQL 8 introduced histograms — use them for skewed columns the optimizer otherwise misjudges.

Step 6: N+1 — the App-Layer Killer

The pattern: load 100 parents, then for each parent issue a child query — 101 round-trips instead of 1 or 2.

# Rails — N+1
users = User.where(country: "IN")        # 1 query
users.each { |u| puts u.orders.count }   # +100 queries

Detection

• Query log shows the same prepared statement repeated N times in fast succession (often microseconds apart).
• ORM debug log is the giveaway (django.db.backends, Rails ActiveRecord logger, Hibernate show_sql, Prisma log: ['query']).
• PG: pg_stat_statements row with very high calls and low mean_exec_time for a query that fetches a parent's children.
• MySQL: slow query log + APM trace.
• APM: Datadog / New Relic / Scout will flag it as an "N+1 query" span.

Fixes (pick the right one for the workload)

• Eager loading in ORMs — the right fix when the children are needed for display:
  • Rails: User.where(country: "IN").includes(:orders)
  • Django: User.objects.filter(country="IN").prefetch_related("orders") (or select_related for forward FK / one-to-one).
  • Hibernate: LEFT JOIN FETCH in JPQL/HQL.
  • EF Core: db.Users.Include(u => u.Orders).
  • SQLAlchemy: selectinload(User.orders).
• Batch query — SELECT * FROM orders WHERE user_id IN (?, ?, ?, …) then group in app code.
• Single JOIN with grouped aggregate — when you only need a count or sum, not the rows:

  SELECT u.id, COUNT(o.id) AS order_count
  FROM users u LEFT JOIN orders o ON o.user_id = u.id
  WHERE u.country = 'IN' GROUP BY u.id;
  

• DataLoader-style batching in GraphQL — coalesces per-request field resolutions into a single batched query.

Step 7: Anti-patterns to Rewrite

Step 8: Caching & Memory Knobs

These are bigger levers than most micro-index tweaks and are routinely under-tuned.

PostgreSQL (defaults are conservative for any modern server):

MySQL (InnoDB):

These move scans from disk to RAM — the difference between 50 ms and 50 µs per page is the difference between "slow query" and "fast query."

Step 9: Modern (2025) Features Worth Knowing

PostgreSQL

• JIT compilation (jit = on, default since PG 12) — speeds up big-cost analytical plans by JIT-compiling expression evaluators. Adds startup latency on small queries — disable for OLTP if it shows up as overhead.
• Parallel query — sequential scans, hash joins, hash aggregates, and many index scans can run with multiple workers. Tune max_parallel_workers_per_gather.
• Partitioning — PARTITION BY RANGE / LIST / HASH. With partition pruning, queries hit only the relevant partitions; combined with partition-wise joins, very large tables become tractable.

MySQL 8

• Hash join for inner equi-joins (massive win vs. the old block-nested-loop on big × big joins). Outer joins use hash join from 8.0.20+ when supported.
• Invisible indexes — ALTER TABLE t ALTER INDEX idx INVISIBLE to test removing an index safely (planner ignores it, but it stays on disk and is still maintained on writes; flip back instantly if a query regresses).
• Histograms — see Step 5; required for skewed columns.

Both

• Prepared statements + bind variables — avoid re-planning the same query for every literal value, and protect against SQL injection. But: on parameter-sensitive queries this is the source of parameter sniffing / plan instability — same SQL, different plans across runs, because the cached plan was made for an unrepresentative bind value.

Step 10: Red-Flag Quick Scan

Run this checklist over any plan:

• Seq Scan on a big table (> 1M rows) with a selective filter.
• Nested Loop with > 100k outer rows and no inner index.
• Rows Removed by Filter ≫ rows returned (over-fetching).
• Sort spilling — external merge Disk: ...kB.
• Index Cond is empty but Filter is heavy (wrong/missing index).
• Plan rows ≪ actual rows (stale stats).
• N+1 pattern in app logs / pg_stat_statements.
• OFFSET over 5-figure values.
• LIKE '%...' on a non-trigram column.
• Hash Join with Batches: > 1 (memory spill).
• Same query has very different plans across runs (parameter sniffing / plan instability).
• An invalid index (pg_index.indisvalid = false).

If any box is ticked, name it in the diagnosis.

Step 11: Output Format — the Tuning Report

Return your review in this exact shape.

🟠 Verdict banner

One of:

• ✅ Well-optimized — no actionable wins; current plan is appropriate.
• 🟡 Caveats — minor tweaks; latency mostly limited by data volume.
• 🟠 High wins available — clear bottleneck(s) with a known fix.
• ⛔ Critical fix needed — query is broken in production (timeouts, locking, missing critical index on a hot path).

Followed by one sentence: "Verdict because …"

Plan summary table

Worst node first. One row per non-trivial node.

Diagnosis

Two or three sentences. Root cause(s) only — not the fix yet.

Example: "The Seq Scan on users filters 50M rows down to ~750k with a selective country = 'IN' predicate; the planner has no index on country so it must scan the whole table. The downstream Sort then spills to disk because work_mem is the 4 MB default."

Fix plan — ordered

Numbered list, smallest-blast-radius first. Always include the exact DDL for index changes, the before/after SQL for query rewrites, and the config command for stats/memory changes.

-- 1. Add the missing predicate index (no table rewrite, online build):
CREATE INDEX CONCURRENTLY idx_users_country_created_at
  ON users (country, created_at DESC);

-- 2. Bump work_mem for this session to keep the sort in RAM:
SET work_mem = '64MB';

-- 3. Refresh statistics on the join column:
ANALYZE users;

For N+1 fixes, show the single resulting SQL and the ORM change next to each other:

# Before — 1 + N
users = User.objects.filter(country="IN")
for u in users:
    u.orders.count()

# After — 1
users = (
    User.objects
        .filter(country="IN")
        .annotate(order_count=Count("orders"))
)

Re-measure protocol

The exact commands to confirm the fix:

-- PG: hot run after warm-up
EXPLAIN (ANALYZE, BUFFERS) <the query>;

-- Cold cache (Linux):  sudo sysctl -w vm.drop_caches=3
-- then re-run EXPLAIN (ANALYZE, BUFFERS).

State the target: "expect Seq Scan → Index Scan, sort in memory, total time < 50 ms."

Risk notes

• Index build time / locking. CREATE INDEX CONCURRENTLY is online but takes ~2× as long, can fail and leave an invalid index, and won't finish if other long transactions block it. Plan a window.
• Write amplification. Every new index slows every INSERT / UPDATE / DELETE that touches its keys. If the table is write-hot, measure both sides.
• Parameter sniffing. A plan that's great for country = 'US' (80% of rows) may be terrible for country = 'IN' (1.5%). Prepared statements + a non-representative first bind ⇒ stuck on the wrong plan. Watch for plan instability after the change.
• DML EXPLAIN warning. ⚠️ Don't EXPLAIN ANALYZE an UPDATE / DELETE against production without wrapping in BEGIN; ... ROLLBACK; — it actually performs the write.

Disclaimer footer

I cannot run this query against your live database. Plans are data-and-config-dependent — re-run EXPLAIN (ANALYZE, BUFFERS) on prod-sized data, on both hot and cold caches, before declaring victory. Index changes slow writes; verify the win.

Severity Model

Mirror the verdict banner:

• ⛔ Critical — hot-path query returning > 10× the rows it needs, full scan on a > 10M-row table on a request path, long-running write blocking reads, or a data-corruption / lost-update risk (e.g. OFFSET over an evolving result set).
• 🟠 High — missing index that would convert Seq → Index on a hot path; an N+1 in a production endpoint; a bad cardinality estimate driving wrong join order.
• 🟡 Medium — over-indexing on a write-heavy table, Sort spilling to disk on an analytical query, SELECT * in a hot path.
• 🟢 Low — style / maintainability (COUNT(*) vs COUNT(1) — both are equivalent in modern engines).
• ℹ️ Info — future-proofing notes (e.g. "partitioning is worth considering once this table crosses ~500M rows").

Cross-links

This skill pairs with:

• code-review-skill — for the broader correctness/security pass on the surrounding code; SQL is one layer.
• api-security-review — when the slow query is on an API hot path and you also want to scope authz / rate limit / cost-budget risks.

Educational engineering guidance, not a substitute for production APM, slow-query monitoring, or a qualified DBA on critical systems. Not affiliated with or endorsed by Anthropic.