PostgreSQL Planner Cost Analysis: B-Tree Index Scans vs Sequential Scans
Database administrators often encounter queries where PostgreSQL deliberately ignores a newly created B-Tree index and selects a sequential scan instead. Developers sometimes assume the query planner failed or that statistics are stale. In reality, the query planner evaluates execution cost based on 8KB disk page reads and CPU cycle costs rather than raw row counts. Understanding the operational divergence between sequential scans and B-Tree index scans prevents misconfigured database instances and wasted maintenance overhead.
The query planner evaluates access paths by page I/O weight, not row cardinality. If reading every page in sequence produces fewer I/O cycles than hopping through an index tree, sequential scan wins every time.
Criterion 1 – Page Fetch Cost and Random I/O
PostgreSQL models sequential scans around sequential page reads, represented by the configuration parameter seq_page_cost (defaulting to 1.0). When reading a table sequentially, the storage engine reads contiguous blocks from the filesystem cache or disk with high readahead throughput. The kernel optimizes sequential prefetching, reducing head movement on spinning disks and maximizing controller bus efficiency on flash drives.
B-Tree index scans introduce random access patterns governed by random_page_cost (defaulting to 4.0). An index lookup requires traversing intermediate B-Tree nodes, reading leaf pages, and then jumping across non-contiguous heap pages to fetch row tuples. If a query matches five percent of a million-row table spread across thousands of pages, the query planner must price thousands of individual random page reads. When the calculated cost of random lookups exceeds the total cost of reading the entire table contiguously, the optimizer discards the index.
-- Inspect planner cost calculation between access methods
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, account_id, amount
FROM ledger_transactions
WHERE account_id = 4281;
-- Compare by disabling sequential scan temporarily in session
SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, account_id, amount
FROM ledger_transactions
WHERE account_id = 4281;
RESET enable_seqscan;Criterion 2 – Shared Buffers and Memory Cache Density
Sequential scans handle memory pressure differently than index scans. When running an unbounded table scan on a large relation, PostgreSQL avoids polluting the primary shared buffer pool. It allocates a small 256KB ring buffer (equivalent to 32 pages of 8KB each). As pages are read from storage, processed, and discarded, they cycle through this restricted ring buffer. This mechanism protects hot working sets in PostgreSQL shared buffers from getting evicted during large analytics queries.
B-Tree index lookups do not use the ring buffer strategy. Every index leaf page and visited heap page must be located within or brought directly into shared buffers. For high-concurrency transactional systems with narrow point lookups, this behavior ensures sub-millisecond retrieval. When batch queries scan wide ranges through an index, random page loading risks evicting heavily cached reference tables, causing cache churn across unrelated connections.
Criterion 3 – Write Amplification and Index Maintenance
Sequential scans require zero storage footprint and incur zero write amplification. The storage engine reads heap files directly. As data modifications occur, tables with no auxiliary indexes run write operations at the physical write speed of raw append-only or update-in-place operations.
B-Tree indexes add direct operational tax to write paths. Every INSERT and non-HOT (Heap-Only Tuple) UPDATE must modify the corresponding B-Tree leaf pages. When leaf pages run out of free space, page splits occur, creating fragmentations and doubling storage I/O. Furthermore, dead index entries cannot be cleaned inline by HOT optimizations; they require autovacuum worker processes to run full index scan passes. The operational decision between indexing a column or relying on sequential batch scans depends on the read-to-write ratio of the table.
- Sequential scans avoid write amplification caused by B-Tree page splits.
- B-Tree index maintenance increases autovacuum memory requirements.
- Point queries justify the write overhead; wide analytical filtering often does not.
Criterion 4 – Selectivity Thresholds and Cost Cutoffs
The crossover point where the optimizer prefers an index scan over a sequential scan is the selectivity cutoff. Selectivity represents the fraction of rows returned relative to the total rows in the relation. In production environments with NVMe storage, engineers frequently adjust random_page_cost down toward 1.1, narrowing the gap between random and sequential access costs.
Even with NVMe tuning, selectivity above five to fifteen percent almost always favors a sequential scan or a bitmap heap scan over a plain B-Tree index scan. The planner formula balances CPU tuple processing against total page fetches:
-- View current cost weight parameters
SHOW seq_page_cost; -- Default is 1.0
SHOW random_page_cost; -- Default is 4.0
SHOW cpu_tuple_cost; -- Default is 0.01
SHOW cpu_operator_cost; -- Default is 0.0025When query selectivity is high (returning fewer than one percent of total rows), B-Tree scans deliver clear performance advantages by eliminating 99 percent of heap page reads. When selectivity falls into double-digit percentages, sequential scan reads the physical storage faster by avoiding repeated pointer dereferencing.
Conclusion
Choosing between sequential table scans and B-Tree index scans is an architectural trade-off between random I/O overhead and sequential data throughput. Do not force index usage by setting enable_seqscan = off in production configuration. If PostgreSQL chooses a sequential scan on a table where you expect an index lookup, verify table statistics with ANALYZE, evaluate query selectivity, and inspect random_page_cost against your actual storage hardware. A sequential scan on a small or moderately selective table is often the most resource-efficient access path available.