Full scan
no index200 pages
Every page of 10,000 orders, read to find one.
Module 22 · Databases & indexes
Why one order can take a whole table to find
A database with no index reads every page to find one row. Here you find an order in Larkspur’s table, build the index that jumps straight to it, and learn what that index costs every time an order is written.
Read as
The whole idea in one example
Order 1042, two ways
Through the index
B-tree4 pages
One page per level of the tree, then the row.
Page counts come from Larkspur’s generated table and a real B-tree, counted as they run: a teaching database, not any real database’s numbers.
How it works
Four ideas carry the module
1 Find one order
With no index, every drawer opens
PostgreSQL’s docs: fetching rows separately is much more expensive than reading them sequentially, and a table on one page nearly always gets a sequential scan, indexes or not.
Source, checked 24 October 2026
2 The index
A B-tree walks one page per level
Use The Index, Luke: tree depth grows very slowly compared to the number of leaf nodes; real-world indexes with millions of records have a depth of four or five.
Source, checked 24 October 2026
3 Every write pays
Each new order is filed in every index
Use The Index, Luke: the number of indexes on a table is the most dominant factor for insert performance, and each additional index slows inserts further.
Source, checked 24 October 2026
4 The planner skips it
Too many rows, and a scan is cheaper
PostgreSQL’s planner estimates each plan’s cost, in arbitrary units, and seeks the lowest estimated total cost; EXPLAIN shows its estimated rows.
Source, checked 24 October 2026
The story
Larkspur’s orders page crawls
Larkspur Supply Co.’s orders page crawls; the team finds one order among ten thousand, builds indexes, pays for them on every write and plans the day’s set. You’re on the team that runs the order system.
- 10,000
- orders in Larkspur’s table, generated from a seed
- 200
- pages a full scan reads to find one of them
- 4
- pages the same lookup reads through a B-tree index
- 21,240
- pages a day for the best index plan in chapter 7
Your path
The chapters
Chapter 7 · done
Plan Larkspur’s indexes
Which indexes make the day’s reads cheapest within the write budget?
Who it’s for
- Engineers
- Product managers
- Analysts
- No SQL needed
Anyone whose product keeps its data in a database and has wondered why one page is quick and another crawls, or why adding an index made writes slower. No background needed; the SQL stays folded away.