SQL Query Tuning skill for Claude Code

A query is tuned when you can name the plan node that was slow, explain why the planner chose it, show a before/after plan on production-sized data, and the fix does not just move the cost to writes or another query. Never tune from the SQL text alone; tune from the plan.

Ground rules

• Measure on data shaped like production. A plan on 1,000 rows tells you nothing about a plan on 50M rows; the planner picks different strategies by size.
• EXPLAIN ANALYZE executes the statement. Wrap writes: BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK; and never run it on production writes without the user’s explicit go-ahead.
• Change one thing at a time and re-capture the plan after each change.
• Fix estimates before forcing plans. Most bad plans are bad row estimates.

Workflow

- [ ] 1. Find which queries matter (total time, not just slowest single run)
- [ ] 2. Capture the real plan with parameters production uses
- [ ] 3. Locate the expensive node and the estimate error
- [ ] 4. Classify the cause (table below)
- [ ] 5. Apply the smallest fix: statistics -> rewrite -> index -> config
- [ ] 6. Re-capture plan, compare, check write/other-query impact
- [ ] 7. Write the tuning note

1. Find the queries that matter

-- Requires pg_stat_statements in shared_preload_libraries + CREATE EXTENSION
SELECT queryid,
       calls,
       round(total_exec_time::numeric, 0)         AS total_ms,
       round(mean_exec_time::numeric, 1)          AS mean_ms,
       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct,
       rows / greatest(calls, 1)                  AS rows_per_call,
       shared_blks_read + shared_blks_hit         AS blks,
       left(query, 120)                           AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

A 5 ms query called 2M times a day usually matters more than a 4 s report run once. Rank by total_exec_time; look at mean only to decide the tuning approach.

To catch slow plans in the act, enable auto_explain (session or config):

LOAD 'auto_explain';
SET auto_explain.log_min_duration = '500ms';
SET auto_explain.log_analyze = on;
SET auto_explain.log_buffers = on;

2. Capture the real plan

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, VERBOSE) <query>;
-- PG18 includes BUFFERS by default with ANALYZE
-- JSON for tools: EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ...

• Use the literal parameter values from the slow case. Skewed values (“tenant 1 has 40% of rows”) produce different plans.
• Prepared statements may switch to a generic plan after five executions. If the app uses them (most drivers and ORMs do), compare with SET plan_cache_mode = force_generic_plan; then PREPARE/EXPLAIN EXECUTE.
• Run it twice; the first run may be cold cache. Report both if they differ greatly.
• Paste large plans into a visualizer (explain.dalibo.com, explain.depesz.com) only if the plan contains no sensitive literals or the user agrees.

3. Read the plan

Read inside-out, and for each node compare:

• rows= estimated vs actual rows= multiplied by loops=. An error of 10x or more is the usual root cause.
• actual time= is per loop: total node time is roughly time * loops.
• Buffers: shared hit= (cache) vs read= (disk). Many reads mean I/O-bound; consider whether the working set fits shared_buffers.
• Rows Removed by Filter large relative to rows returned: the index (if any) is not selective for this predicate.
• Sort Method: external merge Disk: ... or Hash ... Batches: >1: work_mem too small for that operation, or too many rows reach that node.

For node-by-node interpretation and what each warning sign means, read references/explain-nodes.md.

4. Classify the cause

Symptom in plan	Likely cause	First fix
Estimate off 10x+ on a single-table filter	Stale or coarse stats	ANALYZE tbl; raise column statistics target
Estimate off on combined predicates (city + zip)	Correlated columns assumed independent	CREATE STATISTICS ... (dependencies, mcv)
Seq Scan returning a small fraction of a big table	Missing or unusable index	Index, or make predicate sargable
Index exists but unused	Function/cast on column, leading column not filtered, low selectivity	Rewrite predicate or expression index
Nested Loop with huge loops on inner side	Underestimated outer rows	Fix estimate; then hash join wins naturally
Sort spilling to disk	Sorting many rows / low work_mem	Index matching ORDER BY + LIMIT, or raise work_mem for that role/session
OFFSET 100000 slow	Offset reads and discards rows	Keyset pagination
Time grows with count(DISTINCT) or DISTINCT after joins	Join fan-out then dedupe	EXISTS / pre-aggregate before joining
Fast in psql, slow in app	Generic plan, different params, or N+1 in app	Test generic plan; check call counts
Many short queries per request	N+1 from ORM	See n-plus-one-queries

5. Fix, smallest first

Statistics

ANALYZE orders;                                         -- refresh now
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;  -- finer histogram (default 100)
ANALYZE orders;

CREATE STATISTICS orders_city_zip_stats (dependencies, ndistinct, mcv)
  ON city, zip FROM orders;
ANALYZE orders;

Check freshness: SELECT relname, last_analyze, last_autoanalyze, n_mod_since_analyze FROM pg_stat_user_tables ORDER BY n_mod_since_analyze DESC;

Rewrites that change the plan

Instead of	Write
WHERE date(created_at) = '2026-01-05'	WHERE created_at >= '2026-01-05' AND created_at < '2026-01-06'
WHERE lower(email) = $1 without matching index	expression index ON users (lower(email)), or citext
WHERE id::text = $1 / mismatched param types	Cast the parameter, not the column
WHERE a = $1 OR b = $2 (no plan uses both indexes well)	UNION ALL of two indexed queries (dedupe if needed)
NOT IN (SELECT col ...)	NOT EXISTS (SELECT 1 ... WHERE ...) (also NULL-safe)
IN (SELECT ...) joined then DISTINCT	EXISTS semi-join
ORDER BY created_at LIMIT 20 OFFSET 50000	WHERE (created_at, id) < ($last_ts, $last_id) ORDER BY created_at DESC, id DESC LIMIT 20
Correlated scalar subquery per row in SELECT	LEFT JOIN LATERAL (...) ON true or pre-aggregated join
SELECT * when an index-only scan is possible	Select only needed columns; consider INCLUDE columns
count(*) for a UI badge on a huge table	Estimate from pg_class.reltuples or cap: SELECT count(*) FROM (SELECT 1 ... LIMIT 1001) s

CTEs: since PG12 non-recursive CTEs referenced once are inlined. Use WITH x AS MATERIALIZED (...) to force an optimization fence, NOT MATERIALIZED to force inlining.

Indexes: if a new index is the fix, design it with postgres-indexing (column order, partial, covering) and build it CONCURRENTLY.

Configuration (last, scoped narrowly):

SET work_mem = '64MB';              -- per sort/hash node, per query; not global
ALTER ROLE reporting SET work_mem = '256MB';
SET jit = off;                      -- if JIT compile time dominates short OLTP queries

Never raise global work_mem blindly: it is per operation, and a query can use several multiples of it across many connections.

Avoid planner switches (enable_nestloop = off) except as a diagnostic to prove a different plan is faster. Do not ship them.

6. Verify

1. Re-run EXPLAIN (ANALYZE, BUFFERS) with the same parameters; the target node changed and total time dropped. Note buffers too: fewer buffers is the more stable signal than wall time.
2. Test the other parameter extremes (largest tenant, empty result, newest data).
3. If you added an index or statistics, check write-heavy paths are not slower and that no other top query in pg_stat_statements changed plan for the worse.
4. After deploy, reset and watch: SELECT pg_stat_statements_reset(); only with user consent (it clears shared history), or compare mean_exec_time for the queryid.

MySQL notes (8.0+)

• EXPLAIN ANALYZE <select> (8.0.18+) gives actual timings in tree form; EXPLAIN FORMAT=TREE shows the plan without executing.
• Slow query log: slow_query_log=ON, long_query_time=0.5; aggregate with pt-query-digest. performance_schema.events_statements_summary_by_digest is the pg_stat_statements equivalent.
• type: ALL = full scan; Using filesort / Using temporary in Extra flag sorts and temp tables. Refresh stats with ANALYZE TABLE t; and add histograms with ANALYZE TABLE t UPDATE HISTOGRAM ON col WITH 64 BUCKETS;.
• InnoDB secondary indexes contain the PK, so the PK is implicitly “included”.

Gotchas

• LIMIT with ORDER BY on a non-indexed column still sorts every matching row.
• A plan that is fast because of a warm cache will be slow after a failover or restart. Look at read= buffers, not only time.
• Implicit casts kill index use: comparing varchar to a numeric parameter, or timestamptz column to a date expression evaluated per row.
• ILIKE '%term%' cannot use a B-tree; needs pg_trgm GIN or full-text search.
• Huge IN (...) lists (thousands of literals) bloat planning time; use = ANY($1::bigint[]) or join against unnest($1).
• Autovacuum falling behind causes bloated tables and stale visibility maps, which make index-only scans fetch the heap (Heap Fetches high). Check vacuum before blaming the query.

Output: tuning note

**Query:** GET /api/orders list (queryid 81726354), 31% of total DB time.
**Before:** 1,840 ms mean; Seq Scan on orders (12.4M rows), Rows Removed by Filter 12.39M,
estimate 1,200 rows vs actual 48.
**Cause:** `WHERE date(placed_at) = $1` is not sargable; no usable index.
**Fix:** Rewrote to a half-open range; added `orders (tenant_id, placed_at DESC)`
built CONCURRENTLY.
**After:** 3.2 ms mean; Index Scan, 52 buffers vs 410k.
**Side effects checked:** insert latency unchanged (p95 1.1 ms); no other top-20 plan changed.