Seq Scan vs Index Scan vs Index-Only

When the planner needs rows from a table it picks one of three access methods, and interviewers expect you to name all three:

• Seq Scan, read every row in the table from start to finish.
• Index Scan, walk an index to find matching rows, then fetch each from the table.
• Index-Only Scan, answer entirely from the index without touching the table at all.

Knowing why the planner chooses each is the core of this lesson and a guaranteed senior question.

A Seq Scan reads the table's pages one after another and applies any filter to each row. No index is consulted.

This sounds bad but it is often the right choice. Sequential reads are fast for the disk (no random jumps), so when a query returns a large fraction of the table, scanning everything beats jumping through an index millions of times.

The example: scan orders, keep rows where amount > 100. If most orders exceed 100, a seq scan is correct.

An Index Scan uses a B-tree to jump straight to matching keys, then reads the corresponding rows from the table heap.

It shines when the filter is selective, returning a small slice of the table. Looking up 5 rows via an index beats reading 10 million.

The plan names the index it used. Each match costs one index lookup plus one heap fetch (a random read), so index scans lose their edge once they return too many rows.

The single concept that drives all of this is selectivity: what fraction of rows a predicate keeps.

• High selectivity (few rows match, like a unique id) favors an Index Scan.
• Low selectivity (many rows match, like status IS NOT NULL) favors a Seq Scan.

A common rule of thumb: once a query returns more than roughly 5 to 10 percent of a table, the planner often prefers a sequential scan because the random heap fetches of an index become more expensive than reading everything in order.

An Index-Only Scan is the fastest of the three. If every column the query needs is already in the index, the engine never touches the table heap at all.

The example query selects only customer_id and filters on it, and the index is on customer_id. All needed data lives in the index, so Postgres reports Index Only Scan.

This avoids the random heap reads that slow an ordinary index scan, a huge win on wide tables.

Interviewers love this nuance. An index-only scan still has to confirm each row is visible to your transaction (MVCC), and the index alone does not store visibility.

Postgres uses the visibility map: if a page is marked all-visible, it skips the heap; if not, it must fetch the heap row anyway. The plan shows Heap Fetches: N.

That is why a freshly updated table can show many heap fetches and slow index-only scans until VACUUM refreshes the visibility map.

There is a fourth method that often appears: the Bitmap Heap Scan. The planner picks it when a predicate matches more rows than a plain index scan wants but fewer than a full table.

It first builds a bitmap of matching row locations from the index (Bitmap Index Scan), then fetches heap pages in physical order instead of random order. Ordered fetches are much cheaper than the scattered reads of a regular index scan.

A classic interview prompt: I added an index but the plan still does a Seq Scan, why? Common reasons:

• The predicate is not selective, scanning is genuinely cheaper.
• A function wraps the column: WHERE lower(email) = ... cannot use a plain index on email.
• Type mismatch forces an implicit cast that defeats the index.
• Stale statistics, run ANALYZE.
• The table is tiny, scanning a few pages beats index overhead.

Suppose orders has an index on created_at but this query still seq-scans:

The culprit is DATE(created_at). Wrapping the column in a function means the index on the raw created_at cannot be used. The fix is to rewrite as a range predicate that leaves the column bare, or build an expression index on DATE(created_at).

Hold this comparison in your head for the interview:

• Seq Scan, best when returning a large fraction of rows; sequential I/O.
• Index Scan, best for selective lookups; index walk plus random heap fetches.
• Bitmap Heap Scan, mid-range match count; index to bitmap, then ordered heap reads.
• Index-Only Scan, fastest when the index covers every needed column and pages are all-visible.

The planner chooses by estimated cost, driven mainly by selectivity and statistics.

To prove a point in development you can temporarily nudge the planner: SET enable_seqscan = off; forces it to prefer indexes so you can compare plans.

This is a diagnostic trick, never a production fix. Mention in interviews that the real solutions are better statistics, a suitable index, or rewriting the predicate, not disabling planner features globally.

A query selects only email and filters on email, and there is a B-tree index on email. The plan shows Index Only Scan. Why is this faster than a regular Index Scan?

Key takeaways on access methods:

• Seq Scan wins for low-selectivity queries; Index Scan wins for selective ones.
• Index-Only Scan avoids the heap when the index covers all needed columns, watch Heap Fetches and the visibility map.
• Bitmap Heap Scan bridges the middle by fetching heap pages in physical order.
• The planner decides by selectivity and statistics; functions on columns, type mismatches, and stale stats are why an index gets ignored.