Reading Many Pages in Order Can Beat Reading Fewer Pages at Random
A database that follows an index to scattered rows pays a random read for each heap page, while a database that scans the table reads pages in order and can fetch ahead in big chunks. Past some fraction of the table, usually a few percent, reading everything in order is genuinely cheaper than chasing a smaller number of scattered addresses, which is why an engine will choose a sequential scan even when a perfectly good index exists. Understanding this crossover stops you from fighting the planner over a decision that is correct.
Questions this Concept answers
Explain why a `Seq Scan` becomes genuinely cheaper than an index path once a query matches a large fraction of a table.
J
jeremy
Video
The Selectivity Crossover Where Index Scans Cost More Than Sequential Scans in PostgreSQL
A cost-based query planner chooses between a sequential scan, whose cost is essentially fixed and independent of how many rows satisfy the predicate, and an index scan, whose cost grows with the numb…