Skip to main content
PreparationZone Logo
PrepZone

Query Optimization and EXPLAIN

Read execution plans, spot sequential scans, and fix slow VaultCommerce reports.

Pass checkpoint below

Read these first

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, and Nested Loop nodes.
  • N+1 ORM patterns show up as hundreds of identical plans in pg_stat_statements.
Clustered (PK)
Table rows (sorted by PK)
Row 1id=1
Row 2id=2
Row 3id=3
Non-clustered (email)
email index→ row pointer
Table heapunordered rows
A clustered index sorts table rows by key. Non-clustered indexes point to row locations.

Running EXPLAIN

Prefix any query with EXPLAIN for the plan, or EXPLAIN ANALYZE to execute and show actual timings:

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