# Patterns — Optimizing SQL Performance

# Contents

Reading EXPLAIN ANALYZE
Finding the workload's worst queries
Index recipes
N+1 rewrites
Keyset pagination
Materialized views
Dialect deviations (MySQL, SQLite)
Gotchas

# Reading EXPLAIN ANALYZE

sql

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_cents FROM orders
WHERE user_id = 42 AND created_at >= '2026-01-01';

Read inner-most node first; the fix usually targets the node with the largest actual time. Checklist per node:

Signal	Meaning	Action
`Seq Scan` + `Rows Removed by Filter` huge	selective filter, no usable index	add index (see recipes)
`rows=3` estimated vs `rows=30000` actual	stale statistics	`ANALYZE orders;` then re-plan
`Sort Method: external merge Disk`	sort spills to disk	index matching ORDER BY, or raise `work_mem` for the session
`Nested Loop` with huge outer side	join misestimate	fix stats; check join column indexes
`Heap Fetches` high on Index Only Scan	visibility map stale	`VACUUM orders;`

# Finding the workload's worst queries

sql

-- requires: CREATE EXTENSION pg_stat_statements;
SELECT calls, mean_exec_time::int AS mean_ms,
       (calls * mean_exec_time)::int AS total_ms, query
FROM pg_stat_statements
ORDER BY calls * mean_exec_time DESC
LIMIT 10;

Optimize by total_ms (aggregate cost), not by single-query time.

# Index recipes

sql

-- Equality-then-range composite (rule 3):
CREATE INDEX orders_user_created_idx ON orders (user_id, created_at);

-- Covering index for an index-only scan:
CREATE INDEX orders_user_created_cov_idx
    ON orders (user_id, created_at) INCLUDE (total_cents);

-- Expression index (query must use the same expression):
CREATE INDEX users_email_lower_idx ON users (lower(email));

-- Partial index for a hot subset:
CREATE INDEX orders_pending_idx ON orders (created_at)
    WHERE status = 'pending';

-- Trigram index for LIKE '%term%':
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX users_name_trgm_idx ON users USING gin (display_name gin_trgm_ops);

-- Build without blocking writes (production):
CREATE INDEX CONCURRENTLY ...;

-- Find unused indexes:
SELECT indexrelid::regclass, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelid::regclass::text NOT LIKE '%_pkey';

# N+1 rewrites

python

# ❌ one query per user
for uid in user_ids:
    cur.execute("SELECT * FROM orders WHERE user_id = %s", (uid,))

# ✅ one query, group in app code
cur.execute(
    "SELECT user_id, id, total_cents FROM orders WHERE user_id = ANY(%s)",
    (user_ids,))

Aggregate-per-parent variant in one round trip:

sql

SELECT u.id, COALESCE(SUM(o.total_cents), 0) AS spend
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.id = ANY($1)
GROUP BY u.id;

# Keyset pagination

sql

-- page 1
SELECT id, created_at FROM orders
ORDER BY created_at DESC, id DESC LIMIT 20;

-- next page: pass the last row's (created_at, id)
SELECT id, created_at FROM orders
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC LIMIT 20;

CREATE INDEX orders_created_id_idx ON orders (created_at DESC, id DESC);

Tie-break with id is mandatory — created_at alone skips/duplicates rows on equal timestamps. Tradeoff: no random page jumps.

# Materialized views

sql

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT created_at::date AS day, SUM(total_cents) AS revenue_cents
FROM orders GROUP BY created_at::date;

CREATE UNIQUE INDEX daily_revenue_day_idx ON daily_revenue (day);
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;  -- needs the unique index

# Dialect deviations (MySQL, SQLite)

MySQL: EXPLAIN ANALYZE (8.0.18+); no partial/expression indexes before 8.0.13 (functional indexes); use performance_schema instead of pg_stat_statements; no INCLUDE — add columns to the index key.
SQLite: EXPLAIN QUERY PLAN; run ANALYZE; to populate stats; partial indexes supported; no concurrent index builds.

# Gotchas

CREATE INDEX (without CONCURRENTLY) takes a write lock on the table.
EXPLAIN without ANALYZE shows estimates only — plans, not reality.
Casts defeat indexes: WHERE id::text = $1 seq-scans; cast the parameter instead.
Low-selectivity indexes (boolean flags) are usually ignored by the planner — a partial index on the rare value works.
random_page_cost default (4.0) is tuned for spinning disks; on SSDs the planner may wrongly prefer seq scans — typical SSD setting is 1.1.
After bulk loads, run ANALYZE (and VACUUM) before judging any plan.
ORMs hide N+1: enable SQL logging before believing "the query is slow" — often it's 500 queries.