An index lookup is usually described as fast and a table scan as slow, which is close enough to be useful and wrong often enough to be worth replacing with an actual accounting of what each one does.

What an index access path consists of

Descending the index to find where the matching entries begin, walking the leaf level while the entries still match, and then fetching the corresponding row from the table for each entry found.

Breaking the path into its three parts is the right opening because the parts have wildly different costs. Everything in this lesson follows from the observation that they are not comparable.

Why the descent is almost free

The tree is shallow and its upper levels are read constantly, so they are resident in memory, which makes finding the starting point a handful of in-memory comparisons.

Dismissing the descent explicitly is useful because it is what people picture when they think about index cost. It is the cheapest part of the operation by a wide margin.

Why the leaf walk is cheap too

Entries matching a condition are adjacent at the leaf level, so reading them is a sequential sweep over a small number of pages, which is the access pattern storage handles best.

Covering the second part matters because it is also inexpensive. Two thirds of the index access path costs almost nothing, which concentrates the whole question onto the third part.

Why the table fetch is the expensive part

Each matching entry points at a row that may be anywhere in the table, so the fetches arrive in index order rather than storage order and touch pages in no useful pattern.

Naming the expensive part plainly is the central point of the section. One scattered page access per matching row is the cost that determines whether the access path is a good idea.

Why one fetch per row is worse than it sounds

A page holds many rows, so fetching one row costs a whole page read, and the next entry may point into a page that has already been evicted, which means paying for it again.

Explaining the granularity mismatch is necessary because it is the source of the surprise. The unit of work is a page and the unit of benefit is a row.

What clustering means

How closely the physical order of the rows in the table matches the order of the entries in the index, which determines whether consecutive entries point into the same page or scattered ones.

Introducing clustering is essential because it is the one property that changes the fetch cost by orders of magnitude. The same index can be excellent or useless depending on it.

Why a well-clustered index is so much better

Consecutive entries point into the same few pages, so a hundred matching rows may cost two page reads rather than a hundred, and the fetches become almost sequential.

Quantifying the difference is worthwhile. Clustering does not improve the fetch cost slightly, it collapses it, which is why the planner tracks it as a statistic.

Why only one index can be well clustered

The rows have one physical order, so at most one index's ordering can match it, and every other index over the same table is scattered with respect to that order.

Stating the constraint is important because it bounds what is achievable. A table cannot be well clustered for all of its indexes, which is a permanent limitation rather than a tuning failure.

Why a table scan can beat an index

Reading every page of a table in order is a sequential sweep, and sequential reads are far cheaper per page than scattered ones, so enough scattered fetches cost more than reading everything.

Explaining the competition is necessary because it is counterintuitive. Reading a hundred times as much data can be faster when the pattern is a hundred times better.

Where the crossover sits

Surprisingly low, often a small single-digit percentage of the table for a poorly clustered index, because the ratio between a sequential page read and a scattered one is large.

Giving the rough number is valuable. Most people place the crossover far higher than it is, and that misplacement is behind a great deal of unnecessary index creation.

Why the crossover depends on the storage

Solid state storage narrows the gap between sequential and scattered reads considerably, which moves the crossover upward and makes index access paths viable over larger fractions.

Covering the hardware dependence matters because defaults often lag it. An engine configured for rotating storage underestimates index access paths on modern hardware.

Why the planner needs the clustering statistic

Without it the planner cannot distinguish an index whose fetches are nearly sequential from one whose fetches are random, and those differ by orders of magnitude in cost.

Connecting back to estimation is worthwhile. The statistic exists precisely because the cost of the fetch cannot be derived from the index or the condition alone.

Why this statistic explains confusing plans

An index ignored in favour of a scan is usually the planner correctly concluding that the fetches would cost more than reading the table, which looks wrong and is not.

Making the diagnostic connection is practical. This is the most common cause of the complaint that the planner refuses to use an index that obviously applies.

Why the fetch count is the number to read

It tells you directly how much scattered work the access path performed, which is the quantity the choice actually turns on, where row counts and index sizes are only proxies.

Giving the practical reading is the right close to the mechanics. One number distinguishes a good access path from a bad one.

Why the fetch is sometimes avoidable entirely

If every column the query needs is already present in the index entry, there is nothing to fetch, because the index itself contains all the data the query asked for.

Introducing the idea here is deliberate, because the whole lesson is a consequence of this sentence. Removing the only expensive part of the access path removes the cost.

Why that possibility changes index design

It turns an index from a way of finding rows into a way of answering queries, which is a different purpose and leads to different choices about what an index should contain.

Framing the shift in purpose is the right transition. The sections that follow are about designing for the second purpose rather than the first.

Why this is not merely an optimisation

The saving is not a constant factor, because it removes the term that scales with the number of matching rows and leaves only the terms that do not.

Stressing the asymptotic difference is worthwhile. Eliminating the per-row cost is categorically different from reducing it.

Why it also removes the clustering dependence

With no fetch to perform, the physical order of the table is irrelevant, so the access path performs identically whether the index is well clustered or not.

Pointing out the second benefit is valuable because it is less obvious. The technique frees an index from the one-clustered-index constraint entirely.

What to take from the access path accounting

Three things. An index access path is a cheap resident descent, a cheap sequential leaf walk, and one scattered table page fetch per matching row, and that last part is effectively the whole cost. Clustering decides how expensive it is, only one index per table can be well clustered, and the crossover where a sequential table scan wins is far lower than most people expect. And if the index contains every column the query needs there is no fetch at all, which removes the term that scales with the matching rows and makes clustering irrelevant.