Query plans

The query planner chooses how to execute SQL: join order, access methods (index scan vs sequential scan), sort/hash strategies, and parallelism. `EXPLAIN` (and `EXPLAIN ANALYZE`) reveals estimated vs actual cost and row counts.

					EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 50;
				

Red flags in plans:

| Signal | Often means | |--------|-------------| | Sequential scan on large table | Missing or unused index | | Nested loop with huge outer | Bad join order or stats | | Sort / Hash on millions of rows | Missing index for `ORDER BY` / join key | | Estimated ≠ actual rows (10×+) | Stale statistics — run `ANALYZE` | | Bitmap heap scan + high recheck | Low selectivity index |

Read top-to-bottom for cost hotspots; check whether an index-only scan is possible; verify filters are applied early (`Index Cond` vs `Filter`).

On interviews: given a slow endpoint, describe how you would capture a plan, which node you'd fix first, and what index or rewrite you'd try.

Common pitfalls: optimizing without `ANALYZE` after bulk load; trusting estimated rows only; adding indexes before understanding join order; ignoring buffer/cache effects in dev versus prod.

The trade-off is balancing planner-friendly SQL shape against readability — sometimes a rewrite (subquery to join, CTE materialization) beats another index.

Checklist:

• Run `EXPLAIN ANALYZE` on production-like data volume.
• Compare estimated vs actual row counts.
• Identify seq scans, sorts, and nested loops on big sets.
• Propose index or rewrite with predicted plan change.
• Re-check after statistics refresh or schema change.