Index Scan vs Sequential Scan: When Does the Optimizer Choose Each?
Learn when a database optimizer picks an index scan versus a sequential scan, and why full scans can beat indexes.
Expected Interview Answer
A sequential scan reads every row of a table in physical order to find matches, while an index scan uses an index structure to jump directly to only the rows that satisfy a condition, and the optimizer picks whichever is estimated to be cheaper given the query's selectivity and the table's size.
For a highly selective condition — one matching a small fraction of rows — an index scan wins because it avoids reading rows that will not match. But when a condition matches a large fraction of the table, a sequential scan can actually be cheaper, because random-access index lookups followed by a row fetch per match cost more in total I/O than simply reading the table straight through in physical order. This crossover point depends on selectivity, table size, and whether the disk favors sequential over random I/O, which is exactly why the same query can flip between plans as data grows or a WHERE value's frequency changes.
- Sequential scans avoid per-row random I/O overhead on unselective queries
- Index scans avoid touching irrelevant rows on selective queries
- The optimizer's crossover choice reflects real selectivity, not query text
- Understanding this trade-off explains "why isn't my index being used" cases
AI Mentor Explanation
Think of searching for one specific player's stats: if you need the record of a single obscure player, you flip straight to their page using the book's alphabetical index — that is an index scan. But if you need every player who ever scored a century, which is most of the book, flipping page by page in order turns out faster than jumping around the index for hundreds of individual lookups — that is a sequential scan. The database makes the same choice: use the index for a rare match, or read the whole table in order when most rows qualify anyway.
Step-by-Step Explanation
Step 1
Estimate selectivity
The optimizer estimates what fraction of rows satisfy the WHERE condition using statistics.
Step 2
Cost the index scan option
Random-access cost of index lookups plus a row fetch per match is estimated.
Step 3
Cost the sequential scan option
Straight-through sequential I/O cost of reading the whole table is estimated.
Step 4
Choose the cheaper option
The lower estimated-cost plan is selected — index scan for selective queries, sequential scan for unselective ones.
What Interviewer Expects
- Understanding that selectivity, not just index existence, drives the choice
- Awareness that a sequential scan can legitimately beat an index scan
- Ability to explain why "the index isn't being used" is often correct optimizer behavior
- Knowledge that random vs sequential I/O cost differences underlie the trade-off
Common Mistakes
- Assuming an index scan is always faster than a sequential scan
- Not mentioning selectivity as the deciding factor
- Blaming the optimizer as buggy when a sequential scan is actually correct
- Ignoring that stale statistics can cause a wrong scan choice either way
Best Answer (HR Friendly)
“A sequential scan reads the whole table in order, while an index scan jumps straight to matching rows using an index. The database picks whichever is actually cheaper: index scans win when few rows match, but if a condition matches a large chunk of the table, reading straight through can beat doing thousands of individual index lookups, so seeing a sequential scan isn't automatically a bug.”
Code Example
-- Highly selective: optimizer likely picks an Index Scan
EXPLAIN SELECT * FROM Orders WHERE order_id = 48213;
-- Low selectivity (most rows match): optimizer may pick a Seq Scan
-- even though an index on status exists, because reading the whole
-- table in order beats thousands of random index lookups.
EXPLAIN SELECT * FROM Orders WHERE status = 'completed';
-- (assuming 'completed' covers most rows in the table)Follow-up Questions
- What is a selectivity threshold and how does it influence plan choice?
- How does an index-only scan differ from a regular index scan?
- Why can outdated statistics cause the optimizer to pick the wrong scan type?
- How does disk type (SSD vs spinning disk) affect the sequential-vs-index trade-off?
MCQ Practice
1. When does the optimizer typically prefer a sequential scan over an available index?
When a large fraction of rows match, reading the whole table sequentially can be cheaper than many random index lookups.
2. An index scan is generally most beneficial when?
High selectivity means few rows match, so jumping directly to them via an index avoids reading irrelevant rows.
3. Seeing a Seq Scan in an execution plan despite an index existing on the filtered column usually means?
This is often correct optimizer behavior when the condition is unselective, not a bug or misconfiguration.
Flash Cards
What is a sequential scan? — Reading every row of a table in physical order to find matches.
What is an index scan? — Using an index structure to jump directly to rows matching a condition.
What decides which scan the optimizer picks? — Estimated selectivity — the fraction of rows expected to match the condition.
Can a sequential scan beat an index scan? — Yes, when a large fraction of rows match, avoiding random index lookups per row.