Full Table Scan vs Index Only Scan

When the planner picks a full table scan over an index-only scan, it usually knows something you don't. But for the selective, hot-path queries that actually matter to your latency budget, the index-only scan wins decisively.

What they actually are

Neither of these is a product you buy — they're access methods the query planner chooses between, so the real question is which one you architect your schema to invite. A full table scan (sequential scan in Postgres) reads every page of the heap in order, checking each row against your predicate. An index-only scan walks a B-tree index and returns results directly from index entries, never visiting the table heap at all — provided the index covers every column the query touches and the rows are known-visible. The full scan is brute force with good cache locality. The index-only scan is precision: it trades a one-time index build and ongoing write amplification for the ability to answer covered, selective queries by touching a sliver of your data. They solve overlapping problems at opposite ends of the selectivity curve, which is exactly why people argue about them.

Where full table scan earns its keep

Stop adding indexes to fix scans the planner chose on purpose. When a query touches more than roughly 5-20% of a table, a sequential scan beats an index because sequential disk reads are cheap and predictable while index access scatters random I/O across the heap. Aggregations over the whole table — COUNT(*), SUM over everything, a nightly export — are full-scan territory, full stop. On a 500-row config table, the index lookup overhead isn't worth the bytes. And when your statistics are stale, the planner sometimes picks a full scan and is right, because the index it skipped wasn't as selective as you assumed. The full scan's quiet virtue is that it degrades gracefully: it's never catastrophically wrong, just sometimes slower than necessary. Its sin is that it scales linearly with table size, so the plan that was fine at 10k rows is a p99 disaster at 50 million.

Where index-only scan wins outright

This is the one you design for. A covered, selective query — find this user by email, does this order exist, join on this foreign key returning two columns — is an index-only scan's home turf, and it stays fast as the table grows because it reads log(n) index pages instead of all of them. The trick most people miss is the covering index: add the returned columns via INCLUDE so the index carries everything the query needs and the heap is never visited. Get that right and you've cut your I/O by an order of magnitude on the exact queries that decide your latency. The catch, and Postgres people learn this the hard way: index-only scans silently fall back to heap fetches when the visibility map is stale, because the index doesn't store row visibility. Skip VACUUM and your 'index-only' scan is quietly reading the heap anyway. Maintain the table, and it's the fastest plan you have.

The verdict

Pick the index-only scan — not because the full scan is wrong, but because the queries that hurt you are the selective, covered, high-frequency ones, and those belong to the index. Architect your hot path around covering indexes, keep autovacuum healthy so the visibility map stays current, and let the planner fall back to a sequential scan for the low-selectivity and whole-table work where it genuinely belongs. The mistake isn't choosing one access method forever; it's fighting the planner. Index for the predicate, cover the projection, and stop adding indexes to tables you read whole. If you're tuning for the 99th percentile of a transactional workload, the index-only scan is the pick. If you're tuning a warehouse aggregate, you were never in this argument to begin with.

Quick Comparison

Factor	Full Table Scan	Index Only Scan
Selective lookups (find one row)	Reads every page regardless — wasteful and scales linearly with table size	Reads log(n) index pages, returns directly without touching the heap
Whole-table aggregates / exports	Sequential I/O with good cache locality — the correct plan	Random index access offers no benefit when reading most rows
Scaling with table growth	Linear: fine at 10k rows, a p99 disaster at 50M	Logarithmic: stays fast as the table grows
Maintenance sensitivity	Robust — never catastrophically wrong, just sometimes slow	Degrades to heap fetches when the visibility map is stale (VACUUM matters)
Write / storage overhead	Zero — no index to build or maintain	Index build plus ongoing write amplification, worse with covering columns

The Verdict

Use Full Table Scan if: You're reading most of the rows anyway — analytics aggregates, full exports, or a small table where the index lookup overhead isn't worth it. Sequential heap reads beat random index hops past roughly 5-20% selectivity.

Use Index Only Scan if: You have a selective predicate and the query only needs columns the index already covers. This is your OLTP hot path, your foreign-key joins, your existence checks. Cover the query and skip the heap entirely.

Consider: Selectivity and visibility. Index-only scans degrade into index+heap fetches when the visibility map is stale — VACUUM matters. Full scans win quietly when statistics are wrong and the planner thinks an index is selective when it isn't.

The Bottom Line

Index Only Scan wins

For the queries that govern your p99 — selective lookups on a covered set of columns — an index-only scan reads a fraction of the pages, never touches the heap, and stays fast as the table grows. Full table scans are the right plan for low-selectivity or whole-table work, but they are an answer to a different question. If you're designing for it, you're designing for the index.

Related Comparisons