🗺️Execution plans

Plans are the ground truth of performance

Textbooks teach SQL; production teaches EXPLAIN. Plans reveal operators, cardinality estimates, buffer hits, and parallel workers, everything you need to argue about p95 vs p99 with evidence instead of vibes.

SQL

EXPLAIN ANALYZE (actually runs the query)

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM orders WHERE order_date >= DATE '2026-04-01';

✓Illustrative result set

metric	value
Planning Time	0.120 ms
Execution Time	42.531 ms
Shared Hit Blocks	18440

🚩Red flags to recognize in the wild

When estimated rows diverge wildly from actual rows, suspect stale stats or correlated predicates.

Signal	What it usually means
Seq Scan on a huge hot table	Predicate not index-friendly, wrong index, or optimizer convinced cardinality is tiny.
Nested loop with giant inner repeats	Underestimated outer cardinality, inner index probe executed millions of times.
Sort spill to disk	`work_mem` too small for DISTINCT / ORDER BY / hash aggregate, watch I/O spikes.

Plans without row truth

Staring only at total cost while ignoring estimated vs actual row counts at hot nodes.
Validating shapes on empty dev tables, cardinality lies quietly until production skew hits.
Ignoring parallel gather skew when only a few workers carry the heavy child.

Estimates, actuals, buffers

Pick one EXPLAIN format (text or JSON) and read join order plus largest row explosions first.
Use engine equivalents of analyze + buffers/timing so spills and cache hits are visible.
Re-check golden-query plans after bulk loads and major migrations.

Compare estimates vs actual

In PostgreSQL, ANALYZE refreshes statistics; in SQL Server, update stats with sensible sample rates. Big imports should end with a stats refresh or plans go stale immediately.

Capture plans from prod safely

Use auto explain / query store / slow log sampling, never run heavy EXPLAIN on untested prod traffic without a guardrail window.

🔍Path Selection: Seek vs Scan

The engine picks the path based on the selectivity of your WHERE clause.

Operator	Mechanical Movement	Cost Shape
Index Seek (Seek)	Traverse B-Tree from root to leaf to find specific values.	O(log N). Optimal for small subsets.
Index Scan	Walk the entire leaf level of the index.	O(N). Cheaper than Seq Scan if columns are covered.
Sequential Scan (Seq Scan)	Read every 8KB page in the physical table file.	O(N). Slow for large tables; high I/O.

⏱️Cost: Startup vs Total

Numbers like cost=0.00..431.20 tell two different stories.

First Row vs Final Row

Startup Cost (the first number) is the work done before the first row can be returned (e.g., building a Hash Table). Total Cost (the second number) is the work to finish the entire node. For LIMIT 1 queries, the optimizer picks the lowest Startup cost; for batch jobs, it picks the lowest Total cost.

Heap fetch

If your index isn't covering, the planner must perform an index-to-heap fetch (bookmark lookup) to read data from the heap page. Fetching tens of thousands of rows can mean tens of thousands of random I/O seeks, often making a seq scan cheaper.

Latency percentiles

A plan that looks fine at median rows can explode on skewed keys, always test with worst-case parameters, not only the happy-path literal you typed in psql.

TL;DR

An execution plan is the optimizer's recipe: which indexes, join algorithms (nested loop, hash, merge), sorts, and parallelism it chose. EXPLAIN shows estimates; EXPLAIN ANALYZE (PostgreSQL) or SET STATISTICS IO ON + actual plans (SQL Server) adds measured rows and timings. Compare estimated vs actual row counts, large gaps usually mean stale statistics or bad parameter sniffing.