Access path selection

Where is the crossover between an index scan and a full scan, and what moves it?

This is the original cost-based decision. Access path selection means: how will a table be read? By a full scan, by an index, by an index that holds all needed columns (an index-only scan), or by a bitmap scan (collect the matching page numbers from the index first, then read those pages in order)?

Whether an index beats a scan has a clean answer, derived in Why indexes exist: an unclustered index wins only while the fraction of rows it returns stays below the sequential-to-random cost ratio divided by rows per page.

RelationshipWhen an index beats a scan →

s^{*} \approx \frac{P \times c _{seq}}{n \times c _{rand}} = \frac{1}{rows per page} \times \frac{c _{seq}}{c _{rand}}

c_seq: $s$ — cost of a sequential page read (seq_page_cost)
c_rand: $r$ — cost of a random page read (random_page_cost)
P (pages): $N$ — pages in the table

Stated in 4 places →Trade-off: Read-optimized vs. write-optimized →

With 100 rows per page and a random read four times dearer than a sequential one, that is about 0.25%; caching, shared pages, clustering and NVMe raise it. The 5% folklore is a spinning-disk artifact that outlived its hardware. This answers the colleague's claim in Ananya's story.

The planner's problem is different: it must use the selectivity before the query has run, so it uses an estimate (the next page explains where it comes from). The crossover is a knife edge. An estimate ten times too low puts a query that returns 2% of the table on the index side of a 0.25% threshold, and the plan does random reads for rows a scan would have streamed. The two costs cross at a small selectivity and diverge fast on either side, so the expensive mistakes are made near the crossover, by the estimate, not by the formula.