EXPLAIN Review Skill
Plan critique checklist for app-team PRs on PostgreSQL 18.4 - structured SKILL.md workflow agents and humans use to accept or reject query changes with EXPLAIN evidence.
Recipe
## EXPLAIN Review Skill - Quick checklist
- [ ] EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) attached
- [ ] Runs on staging with production-scale stats (ANALYZE recent)
- [ ] No unexpected Seq Scan on large tables
- [ ] Row estimates within 10x of actual (Planning vs Actual rows)
- [ ] No Sort on large rowsets without LIMIT index support
- [ ] Buffer read count justified for query frequencyEXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total
FROM orders o
WHERE o.tenant_id = $1 AND o.status = 'open'
ORDER BY o.created_at DESC
LIMIT 50;When to invoke this skill:
- ORM or hand-written SQL PR touches hot path
- pgbadger/pg_stat_statements flagged regression after deploy
- Advisor suggested index needs human validation
Working Example
Input PR: Django queryset adds OR across status values.
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE tenant_id = $1 AND (status = 'open' OR status = 'pending');FAIL signals in output:
Seq Scan on orders (cost=0..250000 rows=1200000)
Buffers: shared read=180000
Planning Rows: 50000 Actual Rows: 890000Skill output:
## Verdict: FAIL
### Issues
1. Seq Scan - filter on tenant_id + status should use index
2. Row estimate off 18x - run ANALYZE orders
3. OR prevents simple btree composite use - consider IN (...) rewrite
### Suggested fix
```sql
SELECT * FROM orders
WHERE tenant_id = $1 AND status IN ('open', 'pending');Index (if missing)
CREATE INDEX CONCURRENTLY idx_orders_tenant_status
ON orders (tenant_id, status);
## Deep Dive
### Pass/Fail Rubric
| Signal | Pass | Fail |
| --- | --- | --- |
| Access path | Index Scan / Index Only Scan on filter | Seq Scan > 10k rows |
| Join | Hash/Merge on large sets with equi-join keys | Nested Loop with inner seq scan |
| Sort | Top-N heap scan with index | External sort on millions |
| Buffers | Stable vs call frequency | Read entire table per request |
### Required PR Attachments
```sql
-- Table scale context
SELECT relname, n_live_tup, pg_size_pretty(pg_relation_size(oid))
FROM pg_class c JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE relname = 'orders';
Stats Freshness
SELECT relname, last_analyze, last_autoanalyze
FROM pg_stat_user_tables WHERE relname = 'orders';
ANALYZE orders; -- staging before EXPLAIN if staleGotchas
- EXPLAIN without ANALYZE - shows cheap plan that lies at runtime. Fix: require ANALYZE on staging.
- Empty staging table - instant index scan on 0 rows. Fix: seed staging or use prod stats snapshot.
- Generic "add index" - duplicate of existing index. Fix:
pg_indexesdiff before DDL. - Ignoring write cost - index saves read but kills INSERT SLA. Fix: note table write rate in output.
- Prod EXPLAIN on hot tables - takes real locks with some settings. Fix: default to staging policy.
Alternatives
| Alternative | Use When | Don't Use When |
|---|---|---|
auto_explain | Sample plans in logs | PR review before merge |
| pganalyze plan UI | Continuous monitoring | Air-gapped review |
| Pairing session | Junior author's first PR | Routine trivial CRUD |
FAQs
Accept bitmap heap scan instead of index scan?
Yes when moderate selectivity and multiple predicates combine. Fail when bitmap covers most of table repeatedly at high QPS.
How strict on row estimate drift?
10x between planned and actual triggers investigate stats or extended statistics. 2x may be acceptable on volatile tables.
Does skill approve migration index DDL?
It recommends; Migration Safety Skill scores lock risk before merge.
Related
- Agent Skills Basics - skill structure
- EXPLAIN Basics - plan reading
- Seq Scan vs Index Scan - access paths
- Code Review for SQL - PR requirements
- Pairing on Query PRs - live review
Stack versions: This page was written for PostgreSQL 18.4 and psql client 18.4.