A relational database can read the rows a query needs either by reading the whole table or by going through an index. Describe what physical work each of those two access paths performs, step by step.
answer
- engines read pages, not rows
- scan: every page, filter after
- index: descend, walk leaves, then chase pointers
- index scan = two lookups (index + table)
- sequential and flat vs random and per-row
basics
~20 sA full scan reads every page of the table in order and discards rows that do not match. An index scan descends the index to the matching keys, then follows each key's pointer to fetch that row's table page. Fewer rows touched, but scattered reads.
solid answer
~60 sAn **access path** is the physical method the engine uses to get rows out of a table. A **full (sequential) table scan** reads every data page of the table from start to finish, applies the filter to each row in the page, and discards non-matching rows. Its cost is proportional to table size, not to how many rows match, and the reads are sequential, so they are cheap per page. An **index scan** does two lookups. First it descends the index (usually a B+Tree) from root to leaf to locate the range of keys satisfying the predicate, then walks along the leaf level. Each matching index entry holds a pointer to where the row lives. Second, for each entry it fetches that row's table page to read the columns the query needs, the *row fetch*. So the cost is roughly `descend + (rows matched x one row fetch)`, and those fetches are scattered random reads. An **index-only scan** is the special case where every column the query needs already lives in the index, so the second step is skipped entirely.
code
text · 8 lines-- full scan
Seq Scan on orders (rows=10,000,000 read, 120,000 pages)
Filter: status = 'SHIPPED'
-- index scan + row fetch
Index Scan using orders_status_idx on orders (rows=42 returned)
Index Cond: status = 'SHIPPED'
-> ~4 index pages descended, ~42 table pages fetched randomlygo deeper
Name the two paths and describe them concretely: a scan reads every page and filters, an index descends to the key then goes and gets the row. Mentioning that databases read pages rather than individual rows already puts you ahead.
Add the cost shape: scan cost tracks table size and is sequential, index cost tracks match count and is random, plus the index-only case where the row fetch disappears. Mention residual filters applied after the fetch.
Frame it as two cost curves that cross, and translate to page counts you could put numbers to. Be ready to say what makes the row fetch cheap or expensive in practice, such as buffer-pool residency and several matching rows landing on the same page.
Treat access paths as the vocabulary for schema and workload design: which queries you intend to serve index-only, which tables you accept scanning, and how those choices interact with write cost and storage layout.
## What "access path" means SQL states *what* rows you want; it never states *how* to find them. The engine chooses a physical **access path**: the concrete sequence of page reads that produces the rows. For a single table the menu is short: read the whole table, go through an index and then fetch rows, or answer entirely from an index. Everything else in single-table tuning is a variation on those three. A key background fact: relational engines do not read individual rows from storage. They read fixed-size **pages** (commonly 8 KB or 16 KB), each holding many rows. All access-path reasoning is therefore counting pages, not rows. ## Full (sequential) table scan The engine reads the table's pages from the first to the last. For each page it walks the rows inside and evaluates the predicate; matching rows go to the next operator, the rest are discarded. Properties worth stating in an interview: - **Cost is proportional to table size**, not to the number of matching rows. A scan of a 10 GB table costs the same whether one row matches or all of them. - **The I/O is sequential.** Pages are requested in physical order, so the storage layer and the engine's read-ahead machinery can fetch large contiguous chunks. On spinning disks this avoided seek time; on SSDs and cloud block storage it still matters, because one large request is far cheaper than thousands of small ones. - **No index is required and none can be stale.** A scan always works. - The predicate is applied *after* the row is already in memory, so a very selective filter still costs a full read. ## Index scan: two structures, two lookups An index is a separate, ordered structure that maps key values to row locations. The scan proceeds in two phases. **Phase 1, index traversal.** Start at the root page, compare the search key, descend one level at a time to a leaf page. A B+Tree over millions of rows is typically three or four levels deep, so this costs a handful of page reads. For a range predicate the engine then walks the leaf pages in key order, since leaves are chained, collecting entries until the key leaves the requested range. **Phase 2, row fetch.** Each index entry contains the key plus a pointer to the row (a physical row identifier, or in a clustered/index-organized table the primary key used to walk the base structure). The index rarely holds the other columns the query wants, so for every matching entry the engine must go read the row's own page. This is the step candidates forget: an index scan that matches N rows may do N page reads on top of the index reads. Those fetches follow index-key order, which usually has nothing to do with the order rows were physically written. The result is **random I/O**: many small, scattered page requests. A buffer-pool hit makes an individual fetch nearly free, and several matching rows may happen to live on the same page, but the worst case is one page read per matching row. After the fetch, any part of the predicate the index could not evaluate is applied as a residual filter, and rows can still be discarded there, meaning the fetch work was wasted for them. ## Index-only scan: the third path If every column the query references, in the select list and in the predicate, is present in the index, phase 2 disappears. The engine reads index leaf pages, produces the answer directly, and never touches the table. Because index entries are narrower than rows, more of them fit per page, so the same answer comes from fewer pages, and leaf pages are read broadly in order rather than randomly. Engines that keep row versions outside the index may still need a per-row visibility check against auxiliary structures, which softens but does not remove the saving. ## Why this taxonomy matters The two paths degrade in opposite directions. The index path is superb when the predicate matches a handful of rows out of millions, since it reads a handful of pages instead of the whole table. It degrades linearly as the match count grows, and each additional matching row can cost a random page read. The full scan starts expensive and stays flat. Somewhere between those curves is a crossover, and past it the scan wins outright. So the honest one-line summary is: an index scan trades a large number of cheap sequential reads for a small number of expensive random reads. That trade is excellent for selective queries and terrible for unselective ones.
- If the index scan reads far fewer rows, why is it not always the faster path?Because it reads fewer rows but at a much higher price per row. Each matching entry can trigger a random single-page fetch of the table, while a scan pulls pages in long sequential runs. Once the number of matching rows approaches a meaningful fraction of the table, the index path issues nearly as many page reads as the scan but without any sequential benefit, plus it pays for the index reads on top.
- What happens to parts of the WHERE clause that the index cannot evaluate?They become a residual filter applied after the row has been fetched from the table. The engine has already paid the full cost of locating and reading that row, and then discards it if the residual condition fails. That is why an index that matches the leading predicate but not the selective one can be worse than useless, and why plan output distinguishes an index condition from a post-fetch filter.
A full scan is reading a book cover to cover looking for every mention of a word. An index scan is using the back-of-book index: fast when the word appears three times, slower than just reading the book when it appears on half the pages, because you keep flipping back and forth.
saying these in an interview costs you the question
- Claiming an index always makes a query faster, so any full scan is a bug
- Forgetting the row-fetch step and costing an index scan as if the index alone answers the query
- Believing the index stores the rows themselves, so no table access is ever needed
- Reasoning in rows rather than pages, so random and sequential I/O look equally priced
- Saying a full scan reads only the matching rows because the database knows where they are