Indexes & Query Plans

This cover reframes what 'running a query' means. The engine doesn't leap straight to your data — it first parses your SQL, rewrites it into a canonical form, enumerates possible execution strategies, estimates the cost of each, and only then runs the cheapest. All of that planning happens before a single data page is read.

Post three is the architecture post. Understanding this pipeline is the difference between cargo-culting 'add an index' advice and actually diagnosing why a specific query is slow and what will fix it.

The pipeline diagram lays out the four stages every query passes through. Parsing checks that the SQL is syntactically valid and turns it into a tree the engine can manipulate. Rewriting expands views, applies macro-like substitutions, and simplifies the query. Planning is the heart: it costs the alternatives and selects one. Execution carries out the chosen plan and produces rows.

Most performance problems live in the planning stage — either the planner had no good index to choose, or it estimated costs wrong. Knowing the query passes through these distinct phases tells you where to look: bad SQL fails at parse, but a slow but correct query is almost always a planning issue.

This slide walks the actual mechanics of a B-tree seek. Starting at the root node, the engine compares your search value to the node's keys to decide which child to descend into. It repeats this at each level — a few comparisons per node — until it reaches a leaf node that either contains the value or proves it's absent.

The leaf doesn't hold the full row; it holds a pointer to the row's physical location (in Postgres, a page and slot). The engine then fetches that page to read the row. The whole operation is a handful of page reads regardless of table size, which is the concrete reason index lookups are fast.

The trace diagram animates a single seek as a sequence of decisions. At the root it asks whether 'ava' sorts before 'm' — yes, go left. At the next node, before 'e' — yes, go left again. At the leaf it finds the exact value and reads its pointer: page 88, slot 3. Then it fetches that row and returns it.

Seeing it as a short list of branch decisions makes the logarithmic cost tangible. Each line eliminated roughly half of what remained. Even a tree over a billion rows resolves in around thirty such steps — which is why the difference between a seek and a scan is the difference between thirty operations and a billion.

This slide explains the planner as a cost-based optimizer. For a given query it generates candidate plans — a sequential scan, an index scan, several join orderings — and assigns each an estimated cost derived from table statistics: how many rows the table holds and how values are distributed across columns. It then executes the plan with the lowest estimate.

The word 'estimate' is load-bearing. The planner is making an educated bet based on a statistical summary, not reading the data to be sure. When its summary is accurate the bets are excellent; when statistics are stale or the data is skewed, it can confidently choose a bad plan. This is why refreshing statistics sometimes fixes performance overnight.

The comparison shows two candidate plans for one query, with the costs that decide between them. Plan A, a sequential scan, would read all ten million rows and filter them in memory — estimated cost 180,000. Plan B, an index scan, seeks the index and fetches the twelve matching rows — estimated cost 34. The planner picks B.

This is cost-based optimization in miniature. The numbers aren't milliseconds; they're abstract cost units the planner uses to rank plans. What matters is the ratio: when an index dramatically reduces the estimated rows touched, its plan wins decisively. When statistics make the planner think the index would match most rows, the scan can win instead.

This slide adds a dimension beginners miss: joining tables isn't one operation, it's a choice among algorithms. A nested loop join walks one input and, for each row, looks up matches in the other — excellent when one side is small and the other has an index. A hash join builds an in-memory hash table of one input, ideal for large unsorted sets. A merge join steps through two already-sorted inputs in lockstep.

The planner picks the join method just as it picks scans, based on input sizes and available indexes or sort orders. A query that's slow despite good indexes on each table is often choosing the wrong join method because of a row-count mis-estimate on one side.

This is the command you'll run more than any other when tuning. EXPLAIN ANALYZE doesn't just show the plan — it executes the query and reports the actual time and row counts at each step alongside the estimates. That comparison is where the truth lives.

Note that because ANALYZE actually runs the query, you shouldn't use it on a statement with side effects (an UPDATE or DELETE) outside a transaction you intend to roll back. For SELECTs it's safe and indispensable. The query here joins users and orders filtered by email — exactly the shape where you want to confirm both indexes are used.

This is annotated EXPLAIN ANALYZE output so you can actually read one. The top line shows an Index Scan on users using the email index, estimating one row and actually finding one — a perfect estimate. Below it, a Nested Loop join drives an Index Scan on orders by user_id, finding seven matching orders. Planning took 0.2 ms and execution 0.3 ms.

The two patterns to read: the access method per node (Index Scan, good) and the estimated-versus-actual rows in parentheses. Here estimates match reality, so the plan is trustworthy. The nested loop is the right join choice because the outer side resolved to a single user — exactly the small-input case nested loops excel at.

This slide names the single most useful diagnostic in plan reading: compare the planner's estimated rows to the actual rows it found. When they're close, the planner had good information and its plan choice is sound. When they diverge wildly — estimated 1, actual 900,000 — the planner optimized for the wrong reality and likely chose a bad plan.

The fix for a big gap is usually to run ANALYZE (or let autovacuum's analyze run) to refresh the table's statistics so the planner's estimates match the data again. This is why a query that mysteriously slowed down after a big data load often recovers the instant you re-analyze the table.

This recap pins the mechanics into five lines: SQL is parsed then planned then run; a B-tree seek is a few page reads; the planner costs plans from statistics; the join method is a deliberate choice; and the estimated-versus-actual row gap reveals bad statistics. Each maps to a diagnostic move you now know how to make.

The payoff of post three is that tuning stops being superstition. You can open a plan, find the expensive node, see whether the estimate matched reality, and act — add an index, rewrite the query, or refresh statistics.

Post three explained how the planner thinks. Post four gets hands-on: we seed a real multi-million-row table, capture its slow plan, add the right index in the right column order, build a covering index, and watch the plan flip to a fast Index Only Scan.