Why this matters
- Slow queries are the top database on-call category at VaultCommerce — plans reveal root cause faster than guessing.
- Adding indexes without EXPLAIN validation sometimes makes queries slower by picking wrong plans.
- Interview performance rounds ask you to interpret
Seq Scan,Index Scan, andNested Loopnodes. - N+1 ORM patterns show up as hundreds of identical plans in
pg_stat_statements.
Running EXPLAIN
Prefix any query with EXPLAIN for the plan, or EXPLAIN ANALYZE to execute and show actual timings:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total_cents, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2026-09-01'
AND o.status = 'PAID';
ANALYZE runs the query — use on staging or off-peak production with care. BUFFERS shows cache hits vs disk reads.
Reading plan nodes
Plans read bottom-up (or inside-out). Key node types:
Common plan nodes
- Seq Scan — Reads every row in the table. Red flag on large tables with selective filters.
- Index Scan — Uses a B-tree to find matching rows, then fetches heap tuples.
- Index Only Scan — Satisfies query from index alone (covering index + visibility map).
- Nested Loop — For each outer row, scan inner table. Fine for small sets; disastrous at scale.
- Hash Join — Builds hash table on one side, probes with the other. Common for medium-large joins.
- Merge Join — Both inputs sorted on join key; efficient for large ordered datasets.
VaultCommerce's monthly revenue report once showed Seq Scan on orders (cost=0..450000 rows=2000000) — a missing index on (status, created_at) was the fix.
The rest of this guide is part of Pro
Free covers every fresher guide in full. Pro unlocks Senior, Staff and Architect guides across every track.
Still to come · 4 sections
- Cost, rows, and actuals
- Fixing a slow VaultCommerce query
- Query rewrite techniques
- Monitoring in production
Test yourself
Answer these before moving on — recall is what makes it stick.