Query Review Standards

New SQL in application PRs needs the same rigor as API contract review. Require evidence: EXPLAIN, row estimates, and index justification before merge to production paths.

Recipe

Quick-reference recipe card - copy-paste ready.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id = $1
  AND o.created_at >= now() - interval '90 days'
ORDER BY o.created_at DESC
LIMIT 50;

## PR SQL Review Checklist
- [ ] EXPLAIN attached from staging with production-like stats
- [ ] Estimated rows within 10x of actual (ANALYZE current)
- [ ] Index justification or proof seq scan is acceptable
- [ ] No SELECT * in hot paths
- [ ] Parameter types match column types (no implicit cast)

When to reach for this: Any PR touching repository SQL, ORM raw queries, report definitions, or migration-generated functions.

Working Example

Developer adds dashboard query filtering orders by status and created_at.

-- Before merge: reviewer runs on staging
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM orders
WHERE status = 'pending'
  AND created_at > '2026-01-01';
 
-- Plan shows Seq Scan on 12M rows, 4.2s
-- Reviewer requests index evidence
CREATE INDEX CONCURRENTLY orders_status_created_idx
  ON orders (status, created_at DESC);
 
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM orders
WHERE status = 'pending'
  AND created_at > '2026-01-01';
-- Index Scan, 45ms

What this demonstrates:

• Attach before and after plans when adding indexes.
• ANALYZE on staging after large data loads before trusting estimates.
• Multicolumn index column order matches filter + sort pattern.
• Migration for index ships in separate deploy step when risk tier requires.

Deep Dive

Required PR Artifacts

Row Estimate Sanity

SELECT relname, last_analyze, last_autoanalyze, n_live_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

If estimates off by 100x, run ANALYZE orders (or fix extended statistics) before approving index spend.

When Seq Scan Is OK

• Small dimension tables (< 10k rows) probed once per request.
• One-off admin report with statement timeout and off-peak schedule.
• Query returns large fraction of table where index + heap fetch costs more.

Document "accepted seq scan" with row count proof in PR comment.

ORM and N+1

-- Detect pattern: 1 + N queries in APM trace
-- Fix: JOIN or WHERE id = ANY($1::bigint[])
SELECT * FROM line_items WHERE order_id = ANY($1::bigint[]);

Gotchas

• EXPLAIN without ANALYZE - Shows estimates only; hides runtime. Fix: Require ANALYZE on staging for hot paths.
• Reviewing against empty tables - Instant index scans on 0 rows. Fix: Seed staging to 10-30% of prod row counts.
• Implicit cast kills index - WHERE created_at > $1 with text param. Fix: Match types; cast param not column.
• Missing LIMIT on sort - Sorts millions of rows for UI page 1. Fix: Keyset pagination or tighter filters.
• New index per PR - Index sprawl slows writes. Fix: Consolidate multicolumn indexes; review Handling Index Requests.

Alternatives

FAQs

Related

• Handling Index Requests - index approval
• EXPLAIN Basics - plan reading
• Code Review for SQL - git workflow
• pg_stat_statements - production audit

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.