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.
Cost, rows, and actuals
Index Scan using idx_orders_status_created on orders
(cost=0.43..1240.12 rows=8500 width=48)
(actual time=0.032..45.221 rows=8421 loops=1)
- cost — Planner estimate (arbitrary units, not milliseconds).
- rows — Estimated row count; large estimate vs actual mismatch suggests stale statistics.
- actual time — Real execution time per node with
ANALYZE.
ANALYZE orders; -- refresh statistics after bulk load
VaultCommerce runs ANALYZE after large data migrations so the planner chooses index scans over sequential scans.
Fixing a slow VaultCommerce query
Before:
-- 12-second admin report
SELECT p.name, SUM(oi.quantity) AS units
FROM order_items oi
JOIN products p ON p.id = oi.product_id
JOIN orders o ON o.id = oi.order_id
WHERE o.created_at >= now() - INTERVAL '7 days'
GROUP BY p.id, p.name
ORDER BY units DESC;
Plan showed nested loop with seq scan on order_items. Fix:
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_orders_created_status ON orders(created_at)
WHERE status = 'PAID';
After: hash join with index scans, runtime under 200ms.
Query rewrite techniques
Optimization without new hardware
- Filter early — Push
WHEREconditions to reduce join input size. - **Avoid SELECT *** — Fetch only columns needed; enables index-only scans.
- Replace correlated subqueries — Rewrite as JOINs or CTEs when planner struggles.
- Limit result sets — Pagination caps work per request.
Monitoring in production
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
Enable pg_stat_statements extension on VaultCommerce RDS. Top mean-time queries feed the weekly performance review.
Quick recall
Everything you need if you only revisit this box.
EXPLAIN ANALYZEexecutes the query and shows real timings per plan node.- Seq Scan on large filtered tables usually needs an index or query rewrite.
- Mismatched estimated vs actual rows signal stale statistics — run
ANALYZE. - Filter early, avoid SELECT *, and rewrite correlated subqueries as JOINs.
pg_stat_statementssurfaces slow production queries for continuous tuning.
Test yourself
Answer these before moving on — recall is what makes it stick.