Indexes & Query Plans

This cover frames the entire topic with one visceral comparison: a book index versus reading the whole book to find a sentence. That image is exactly what an index does for a database, and it sets up the two intertwined ideas this post covers — the index (a data structure) and the query plan (the engine's strategy for using it).

The goal of post one is conceptual clarity, not tuning. By the end you should be able to say what an index is, what a query plan is, and why one query can be a thousand times slower than another that returns the same answer.

An index is best understood as a second, smaller copy of part of your data, kept sorted. When you index the email column, the database builds a structure that holds every email value in sorted order, each paired with a pointer to where the full row lives. Searching a sorted structure is fast because you can discard half the remaining candidates at every step, instead of inspecting rows one by one.

The book analogy is precise: the index at the back of a textbook lists terms alphabetically with page numbers. You don't read the whole book to find 'mitochondria' — you jump to M. A database index gives the engine the same shortcut, turning a scan of millions of rows into a few targeted lookups.

A query plan is the crucial second half of the story. SQL is declarative: you describe the result you want, not the steps to produce it. The planner's job is to translate that into an executable procedure — which table to read first, whether to use an index or scan, how to join tables, whether to sort. There are often many valid plans for one query, differing in cost by orders of magnitude.

This separation is powerful but also why performance can be surprising. You didn't write a slow query; the planner chose a slow plan, usually because it lacked a good index to pick or had bad information about your data. Learning to read and influence the plan is the core skill of database tuning.

B-trees are the workhorse index structure in nearly every relational database. A B-tree is a balanced tree where each node holds many keys and points to child nodes that cover ranges of values. Because each node is wide, the tree stays shallow — even a billion rows fit in a tree only a few levels deep.

The practical consequence is logarithmic lookup cost. Doubling your data adds at most one extra level to traverse, not double the work. That's why a well-indexed query stays fast as the table grows from thousands to billions of rows, while a full scan gets linearly slower. The B-tree is what makes that scaling property possible.

This diagram shows a tiny B-tree to make the descent concrete. The root node splits the value space at 50; from there each branch narrows further until you reach a leaf range. To find the value 35, you go left at the root (35 < 50), then right at the 20 node (35 > 20), landing in the 20-49 leaf.

Real B-trees are far wider — hundreds of keys per node — so they're even shallower than this drawing suggests. The point to internalize is that you never compare against every value. Each level eliminates a huge fraction of the data, which is the geometric reason lookups stay cheap at scale.

This comparison is the single most important distinction in the whole topic. An index scan (or seek) uses the sorted structure to jump directly to matching rows, reading only a few pages. A full table scan ignores any index and reads every row in the table, which is acceptable on small tables but ruinous on large ones.

Note the right column's last item: 'index ignored.' Having an index doesn't guarantee it's used. If your query is written in a way the planner can't match to the index, or if the planner estimates a scan is cheaper, you'll still get a full scan. Recognizing which one you got is what EXPLAIN is for.

This snippet shows the two everyday operations. CREATE INDEX builds the structure once; from then on the database maintains it automatically as rows change. The SELECT that follows is the kind of point lookup an index is built for — an equality match on a high-cardinality column.

The comment matters: without the index, that lookup is a full scan because the engine has no sorted path to 'ava@x.io'. With it, the engine seeks straight to the matching row. This is the most common, highest-value index you'll ever create: one on the columns you look rows up by.

EXPLAIN is how you stop guessing. Prefixing any query with EXPLAIN asks the database to print the plan it would use — without running the query. You read it to confirm whether your index is actually being used.

The two phrases to watch for are 'Index Scan' (or 'Index Only Scan'), which means the engine is using a sorted shortcut, and 'Seq Scan' (sequential scan), which means it's reading the whole table. Seeing a Seq Scan on a large table where you expected an index is the number-one signal that something — the query, the index, or the statistics — needs attention.

This slide corrects the beginner instinct to index everything. An index is not free speed; it's a trade. Reads get faster, but every write must now update both the table and each index on it, and the index occupies disk and memory. On a write-heavy table, too many indexes can make inserts noticeably slower.

The discipline this implies: index the columns you actually filter on (WHERE), join on (ON), and sort by (ORDER BY). Don't index columns you only ever read back as output. The right number of indexes is the smallest set that covers your real query patterns.

This pipeline places the planner in context so the rest of the series makes sense. Your SQL text is first parsed and validated, then handed to the planner, which is the brain of the operation — it considers candidate plans and picks one. The executor then carries out that plan and streams rows back.

The key insight is that the planner sits between your intent and the data. You influence it indirectly: by providing good indexes for it to choose, by keeping statistics fresh, and by writing queries it can optimize. You don't tell it how to run the query — you give it better options and better information.

This recap distills post one into five memorizable lines. The index is a sorted shortcut; the plan is the engine's chosen recipe; the B-tree is why lookups scale; EXPLAIN is your window into reality; and every index taxes writes. Together they form the mental model the rest of the series builds on.

If you remember nothing else, remember the trade: indexes buy read speed with write cost and storage. Everything in tuning is balancing that trade for your specific query patterns.

Post one gave you the vocabulary and the mental model. Post two shifts from 'what' to 'why it matters' — the concrete, real-world consequences of getting indexes and plans right or wrong, from silent performance decay to full outages.