Index Scan vs Table Scan

What a table scan does

A table scan — Seq Scan in Postgres's EXPLAIN output — reads the table's storage blocks from beginning to end, in whatever physical order the rows happen to sit in, and evaluates the WHERE clause against every single row it reads. There's no shortcut and no sorted structure involved: it's brute force, but predictable, sequential I/O, which storage hardware handles efficiently in bulk.

Seq Scan on orders  (cost=0.00..48291.00 rows=1 width=42)
  Filter: (id = 918204)
  Rows Removed by Filter: 2401879

Notice the plan had to read and discard 2.4 million rows to find the one it wanted — that's the defining cost of a table scan on a large table with a highly selective filter.

What an index scan does

An index scan walks the sorted B-tree structure built by CREATE INDEX (see how a B-tree index is built), locates the entries matching the filter in a handful of comparisons, and then follows a pointer from each matching index entry back to the corresponding table row — an operation sometimes called a "heap fetch" or "lookup."

Index Scan using orders_pkey on orders
  (cost=0.43..8.45 rows=1 width=42)
  Index Cond: (id = 918204)

Same table, same query, indexed on the primary key: the plan goes straight to the one row via the index instead of reading millions to find it.

Index seek vs index scan

Some databases (notably SQL Server) further distinguish an index seek from an index scan. A seek navigates directly to a specific value or narrow range using the tree structure — the fastest possible index operation. An index scan reads a wider, still-bounded range of the index sequentially, which is faster than a table scan but slower than a seek, and usually shows up when the predicate is only partially usable by the index (for example, matching the first column of a composite index but not narrowing the second).

Why the optimizer sometimes skips the index

This is the part that surprises people: an index existing is not enough for it to be used. The optimizer estimates how many rows the filter will match — its selectivity — and compares the estimated cost of an index scan (tree walk plus one row lookup per match) against a table scan (one sequential read of everything).

Once a query is expected to match a large share of the table, the per-row lookup cost of the index scan adds up to more than just reading the table straight through. A common rule of thumb: past roughly 10–20% of a table's rows matching, a table scan tends to win, though the exact crossover depends on the storage engine, row width, and how clustered the matching rows are physically.

Bitmap scans and index-only scans

Two variants worth knowing. A bitmap heap scan (common in Postgres) builds an in-memory map of matching row locations from the index first, sorts that map by physical location, then reads the table in that order — a middle ground that avoids both a full scan and the random-access cost of a plain index scan when moderately many rows match. An index-only scan happens when the index alone contains every column the query needs (a covering index), skipping the table lookup entirely — the fastest of all scan types.

Spotting scan type in EXPLAIN

The operation name in EXPLAIN output tells you directly which scan type was chosen — no interpretation needed:

For the full walkthrough of reading a plan end to end, see EXPLAIN Query Plan – Beginner to Advanced.

Before/after: selectivity flips the plan

Same indexed column, same table — the only thing that changes is how selective the filter is. A rare status value gets an index scan; a common one correctly falls back to a table scan.

SELECT id FROM orders
WHERE status = 'completed';
-- matches 71% of rows

Seq Scan on orders  (rows=1,705,301)
  Filter: status = 'completed'

SELECT id FROM orders
WHERE status = 'refund_pending';
-- matches 0.03% of rows

Index Scan using idx_orders_status
  (rows=721)
  Index Cond: status = 'refund_pending'

~190x faster identical index on the same column — the optimizer switched plans purely because selectivity changed