Databases

Index scan vs full table scan

DatabasesPerformanceDecision guide

Short answer

Use an index when a predicate is selective and the index matches the filter and order. Allow a full table scan when the query needs a large fraction of rows or the table is small; forcing an index can add random I/O and be slower than reading sequentially.

Written and reviewed by Sahil Srivastav

What each one actually is

An index scan walks an ordered or hashed auxiliary structure to find candidate rows, then may visit the table for columns not covered by the index. It trades write and storage cost for selective access.

A full table scan reads table pages and tests each row. Sequential reads are efficient for broad aggregates, and the planner can choose parallel workers or avoid random lookups.

The optimizer estimates selectivity from statistics. The right answer depends on data distribution, visibility, cache state, row width, and requested columns, so inspect `EXPLAIN (ANALYZE)` rather than guessing.

Side by side

	Index scan	Full table scan
Best selectivity	Small fraction of rows	Large fraction of rows
I/O pattern	Index traversal and possible random heap reads	Sequential pages
Extra storage	Index pages	None beyond table
Write cost	Maintained on insert/update/delete	No index maintenance
Covering query	Can avoid heap visits	Reads requested table columns directly
Small table	Often overhead is not worthwhile	Usually cheap
Ordering	Can provide ordered output	Needs sort unless physical order helps
Risk	Stale stats or low selectivity can mislead	Unexpectedly expensive as table grows

Choose Index scan when

The predicate returns a small fraction of rows
The index covers the requested columns or order
The query filters on a high-cardinality column
The table is large enough that scanning all pages is measurable

Choose Full table scan when

The query aggregates or returns most of the table
The table is small or fits cheaply in cache
The predicate has low selectivity or poor correlation
Sequential access is cheaper than many heap lookups

The trade-off in detail

An index is not a command to skip work; it is a different access path. If a query returns 40 percent of a table, traversing the index and fetching scattered rows can cost more than one sequential pass.

A plan that was good yesterday can become wrong after a bulk load, data skew, or cache change. Keep statistics current and compare estimated versus actual rows.

Adding indexes improves one access path while increasing write amplification, vacuum or compaction work, and memory pressure. Index only measured predicates and sort keys.

Things that are commonly said and are wrong

“A full scan means the query is broken.” Broad reads often should scan; the concern is whether the returned fraction matches the plan.
“Indexes always make reads faster.” Low selectivity and heap lookups can make them slower.
“The index exists, so the database will use it.” The optimizer chooses based on cost estimates and may correctly reject it.

Decide it in a real repository

Choosing correctly on a whiteboard and enforcing the choice in code are different skills. Gronex ships broken backend repositories whose tests assert the invariant, not the happy path.

FAQ

Why did the planner ignore my index?

The predicate may be unselective, the table may be small, statistics may be stale, or a function or cast may prevent a matching access path. Compare plans with actual timing and row counts.

When is a full table scan acceptable?

For small tables, reporting queries that need most rows, and sequentially friendly aggregates. Measure elapsed time and I/O rather than treating the plan label as failure.

Should I force an index?

Only as a temporary diagnostic. Fix statistics, query shape, data distribution, or the index definition; forced plans become liabilities as the workload changes.