Query Planning and Indexes
AdvancedHow PostgreSQL's query planner chooses between sequential scan, index scan, and bitmap scan; how table statistics drive cost estimates; and which index type to choose for which access pattern.
Step 1 of 6
Sequential Scan: When Full Table Read Wins
PostgreSQL's query planner is cost-based, not rule-based. The planner assigns a numeric cost to each possible execution plan and picks the cheapest. Cost is measured in arbitrary units where 1.0 = one sequential 8KB page read; a random page read costs 4.0 by default (random_page_cost = 4.0).
A sequential scan reads every page in the table in order. For a table with 10,000 pages, the cost is 10,000. An index scan on a moderately selective predicate might cost 4,000 (1,000 index pages + 3,000 random heap fetches at 4.0 each). The planner picks the index scan.
But consider a query that returns 50% of the table. The index scan now costs 1,000 + (5,000 × 4.0) = 21,000, versus the sequential scan at 10,000. The planner correctly picks the sequential scan. The crossover point is roughly 5-10% selectivity on spinning disks; on SSDs (random_page_cost ≈ 1.1), the index wins down to 30-40% selectivity.
This is why adding an index does not always speed up a query. If the planner estimates more than ~5% of rows will be returned (on spinning disk), it will prefer a sequential scan even with a perfect index available.
Planner cost comparison: seq scan vs index scan at different selectivities
Key Takeaways
- →The planner is cost-based: it picks sequential scan when it expects to return >5-10% of rows
- →random_page_cost=4.0 is the default for spinning disk; SSDs should use 1.1 to unlock index scans at higher selectivities
- →Adding an index does not guarantee it will be used: selectivity and cost estimates determine this
Operational Insights
Set random_page_cost=1.1 on SSD-backed PostgreSQL instances
Consequence: Default 4.0 causes planner to prefer sequential scans at selectivities where an index would be faster on SSD
Mitigation: ALTER SYSTEM SET random_page_cost = 1.1; SELECT pg_reload_conf();