Loading Academy Hub...
Master query execution plan forensics: Index Scan vs Bitmap Heap Scan, Nested Loop vs Hash Join vs Merge Join, and identifying memory spills.
[!IMPORTANT] Never optimize SQL by intuition. Run
EXPLAIN (ANALYZE, BUFFERS)to inspect estimated cost vs actual runtime, exact row counts, buffer cache hits, and join physical implementations.
| Join Operator | When Engine Chooses It | Cost Characteristics |
|---|---|---|
| Nested Loop Join | One side is very small (< 100 rows) with indexed inner lookup | |
| Hash Join | Large unsorted datasets with equality joins (=) | Builds in-memory hash table of smaller table, then probes with larger table |
| Merge Join | Both datasets are pre-sorted on join keys | single pass, zero hash memory overhead |
| Plan Operator | When It Appears | Warning Red Flag |
|---|---|---|
| Seq Scan / Full Table Scan | Reading every page of a table | Appears on huge tables with selective WHERE filters -> missing index or non-SARGable expression! |
| Nested Loop Join | Joining small outer set (e.g. 50 rows) to indexed inner table | Appears with 10M outer rows due to stale ANALYZE cardinality mis-estimates -> catastrophic O(N*M) stall! |
| Hash Join (Disk Batches) | Unsorted equality joins | Batches > 1 in Postgres/Snowflake remote spill -> insufficient work_mem / warehouse RAM! |
| Index Only Scan | Index covers every selected column | Optimal OLTP pattern; avoids heap table lookups completely! |
Review the production code, company interview Q&As, incident traces, and comprehension quiz inside this lesson.
EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE)
SELECT
c.customer_id,
c.name,
COUNT(o.order_id) AS total_orders,
SUM(o.amount) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2026-01-01'
GROUP BY c.customer_id, c.name
ORDER BY total_spent DESC
LIMIT 10;
-- What to inspect in the output:
-- 1. Actual vs Estimated rows: Big discrepancies mean stale table statistics (ANALYZE required).
-- 2. Sort Method: 'quicksort Memory' (Good) vs 'external merge Disk' (Slow memory spill).
-- 3. Buffers: 'shared read' (Disk I/O) vs 'shared hit' (RAM buffer pool hit).HashJoin: Hash (cost=... rows=500000) (actual ... batches=16 disk_spill=48MB)