Database Optimization Skill

Degree of freedom: MIXED. Which query/index/N+1 to fix [HIGH freedom]; existing-index probes and EXPLAIN ANALYZE [LOW freedom — run exactly].

How to reason

1. Observe — EXPLAIN ANALYZE / pg_stat_statements / existing pg_indexes
2. Interpret — seq scan vs N+1 vs over-fetch vs missing pagination
3. Classify — add-index / eager-load / narrow-select / paginate / leave-alone
4. Severity — write-path timeout outranks a 200ms list page

Worked example

Observe: /feed p95 2.4s; Prisma logs 81 queries; pg_indexes has no idx_posts_user_created. Interpret: findMany posts then per-row user.findUnique — N+1; ORDER BY created_at is a seq scan. Classify: eager-load include: { author } + composite index (user_id, created_at DESC). Verify: EXPLAIN ANALYZE → Index Scan; query count 2; p95 < 200ms. Did not add a duplicate index.