<!--
Author: Simon-Pierre Boucher
Contact: contact@spboucher.ai
-->

# 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.