The same result can come from very different work.
The catalog has eight products in storage order. A request asks for SKU-106,
the sixth row. A full scan checks rows until it finds the match; a SKU index follows a
modeled three-step tree path and fetches one row.
The result is not the plan. A plan is the route the engine takes to produce that result. If
the request asks for a missing SKU-999, the scan checks all eight rows while
the index can prove absence without visiting a data row.
One catalog · one key · equivalent result · inspect the work that produced it.
- Scan hit
- Six row visits to return
SKU-106. - Index hit
- Three modeled index steps plus one row fetch.
- Missing key
- Scan all eight rows; index visits no data row after proving absence.
- Write cost
- An indexed insert writes the row and updates the index.
A query plan names the work a result required.
The useful comparison is not “index good, scan bad.” A scan may be right for a tiny table or a large result. An index may be right for a selective lookup, but not when it causes many random row fetches. Start with the predicate, result size, data distribution, and write rate.
Predictable and simple; work grows with the rows that must be checked.
Fewer data-row visits for a selective key, with index traversal and storage overhead.
The same structure that accelerates reads must stay synchronized with the table.
What EXPLAIN is evidence forA plan is a workload-specific observation
An explain tool can show whether the engine chose an index, how many rows it estimates, and which filters or joins remain. Estimates are not measurements: compare them with execution metrics, representative data, and the actual query shape before changing schema or hints.
Run a plan, then make its trade-off visible.
The lab runs the displayed TypeScript model. Start with a hit through a scan, switch to the SKU index without changing the request, then inspect a missing key and an insert. The results stay equivalent while row visits and index writes change.
Which work does this access path perform?
Keep the eight-row catalog fixed. Change the operation, key, or index.
No SKU index; the engine must inspect rows in storage order.
Start with a hit and no index, then add the SKU index without changing the lookup.
This lab models one equality lookup, a three-step index, and one row write. It does not model a database optimizer, statistics, cache state, joins, or the physical cost of a particular engine.
A plan is not a promise about every engineKeep the model bounded
This example treats an index as a three-step lookup so the access-path difference can be counted. Real engines may use B-trees, hash indexes, covering indexes, pages, caches, or a different plan entirely. Use the database's explain and execution tools for the real workload.
Hold the access-path contract steady. Change the language.
The catalog and the access-path model make row visits, tree steps, and writes countable.
export function runPlan(
indexMode: IndexMode,
operation: Operation = 'lookup',
target: LookupTarget = 'hit'
): PlanReport {
if (operation === 'insert') {
const indexed = indexMode === 'sku';
const indexSteps = indexed ? indexDepth : 0;
const indexWrites = indexed ? 1 : 0;
return {
operation,
indexMode,
target,
result: ['SKU-999'],
plan: indexed ? 'append row + maintain SKU index' : 'append row only',
rowsExamined: 0,
indexSteps,
dataWrites: 1,
indexWrites,
workUnits: 1 + indexSteps + indexWrites,
explanation: indexed
? 'The lookup becomes cheaper later, but this insert also traverses and updates the SKU index.'
: 'Without a SKU index, the insert only appends the new row to the data store.'
};
}
const sku = target === 'hit' ? hitSku : missingSku;
if (indexMode === 'sku') {
const lookup = lookupByIndex(sku);
return {
operation,
indexMode,
target,
result: lookup.result,
plan: 'SKU index lookup',
rowsExamined: lookup.rowsExamined,
indexSteps: indexDepth,
dataWrites: 0,
indexWrites: 0,
workUnits: indexDepth + lookup.rowsExamined,
explanation: lookup.result.length
? 'The index narrows the access path to one matching row after three modeled tree steps.'
: 'The index proves the key is absent after three modeled tree steps, without scanning rows.'
};
}
const lookup = lookupByScan(sku);
return {
operation,
indexMode,
target,
result: lookup.result,
plan: 'full table scan',
rowsExamined: lookup.rowsExamined,
indexSteps: 0,
dataWrites: 0,
indexWrites: 0,
workUnits: lookup.rowsExamined,
explanation: lookup.result.length
? `The scan checks rows in storage order and finds ${sku} after ${lookup.rowsExamined} row visits.`
: `The scan checks all ${lookup.rowsExamined} rows before it can prove that ${sku} is absent.`
};
}func RunPlan(indexMode IndexMode, operation Operation, target LookupTarget) PlanReport {
if operation == Insert {
indexed := indexMode == SKUIndex
indexSteps := 0
indexWrites := 0
plan := "append row only"
explanation := "Without a SKU index, the insert only appends the new row to the data store."
if indexed {
indexSteps = indexDepth
indexWrites = 1
plan = "append row + maintain SKU index"
explanation = "The lookup becomes cheaper later, but this insert also traverses and updates the SKU index."
}
return PlanReport{
Operation: Insert, IndexMode: indexMode, Target: target, Result: []string{"SKU-999"},
Plan: plan, IndexSteps: indexSteps, DataWrites: 1, IndexWrites: indexWrites,
WorkUnits: 1 + indexSteps + indexWrites, Explanation: explanation,
}
}
sku := "SKU-106"
if target == Miss {
sku = "SKU-999"
}
if indexMode == SKUIndex {
result, rowsExamined := lookupByIndex(sku)
explanation := "The index proves the key is absent after three modeled tree steps, without scanning rows."
if len(result) > 0 {
explanation = "The index narrows the access path to one matching row after three modeled tree steps."
}
return PlanReport{
Operation: Lookup, IndexMode: indexMode, Target: target, Result: result,
Plan: "SKU index lookup", RowsExamined: rowsExamined, IndexSteps: indexDepth,
WorkUnits: indexDepth + rowsExamined, Explanation: explanation,
}
}
result, rowsExamined := lookupByScan(sku)
explanation := fmt.Sprintf("The scan checks all %d rows before it can prove that %s is absent.", rowsExamined, sku)
if len(result) > 0 {
explanation = fmt.Sprintf("The scan checks rows in storage order and finds %s after %d row visits.", sku, rowsExamined)
}
return PlanReport{
Operation: Lookup, IndexMode: indexMode, Target: target, Result: result,
Plan: "full table scan", RowsExamined: rowsExamined,
WorkUnits: rowsExamined, Explanation: explanation,
}
}Both examples keep the catalog, key choices, result equivalence, and modeled work counts the same. The language changes the representation; it does not change the access-path claim.
Copy the complete examplesStandard library only
These files model query-plan reasoning without pretending to be a database adapter. Replace the access path with your engine's SQL and keep the equivalence check and work evidence around it.
TypeScriptnode --experimental-strip-types directory.ts
Gogo run directory.go
Query work depends on data, predicates, and the engine.
This lesson establishes only that two modeled access paths return the same SKU result while performing different counted work. It does not establish production latency, memory use, cache behavior, join order, selectivity estimates, locking, or the best index for another query.
Check the actual plan and execution metrics for representative data. Ask whether the index covers the predicate, how many rows it returns, how writes maintain it, and what happens when the workload or distribution changes.
Build UIs?Your components already keep one index, and sometimes you build another.
Where it already is in your components
A keyed list is a lookup the framework keeps for you. React's key and
Svelte's keyed each let it match old and new rows by SKU instead of by position
when the list changes. You choose the key, the way you choose the indexed column; the framework
maintains the lookup.
When you have to own it
An order table shows each order's customer. Calling customers.find in every
row is a full scan per row. A Map built with useMemo or $derived is an index: each row becomes one lookup, and the rebuild when customers
change is its write cost. Name the read that matters and the write rate before you add it, as
you would for the database.
A keyed product list: the framework matches old and new rows by SKU, not by position.
type Product = { sku: string; name: string };
// The key is the lookup React keeps for you. When the list changes, it matches old and
// new rows by SKU instead of by position, so each row keeps its own DOM node and state.
export function ProductList({ products }: { products: Product[] }) {
return (
<ul>
{products.map((product) => (
<li key={product.sku}>
{product.sku} · {product.name}
</li>
))}
</ul>
);
}
Choose evidence that includes both sides of the index.
A product page reads SKUs thousands of times, while the catalog changes rarely. What should you check before deciding whether to add the index?
A product page looks up SKUs thousands of times, but inserts are rare.
What is the next defensible move?
Make the next query decision cheaper.
Record the query shape before the index: the predicate, expected result size, data distribution, observed plan, rows examined, write rate, and the limit that still needs production verification.
- Why
- Product pages read SKUs thousands of times, and a scan grows with the table.
- What
- A SKU index for the lookup, checked by equivalent results, the chosen plan, and rows examined.
- Constraint
- Every insert and update also maintains the index; storage grows with it; the engine may still pick another plan.
- Fallback
- If the index is not used or writes suffer, keep the scan for this workload or change the index to match the query shape.
- Reconsider when
- The predicate, result size, data distribution, or write rate changes, or production plans differ from the model.
A plan note to adapt to your own query. Nothing here is saved to an account.
Explore more concepts & practices →