random_page_cost Miscalibrated: Planner Prefers Seq Scans on SSD

Scenario

A production PostgreSQL server was migrated from spinning HDD to NVMe SSD. The random_page_cost parameter was not updated and remains at the default 4.0 — calibrated for HDD where random I/O is roughly 4× more expensive than sequential I/O. On NVMe, random and sequential I/O costs are nearly equal. With random_page_cost = 4.0, the planner greatly overestimates the cost of index scans and prefers seq scans on tables where index scans would be significantly faster. Lowering to 1.1 (NVMe) or 1.5 (SATA SSD) corrects the cost model.

How to Identify

Conditions:

• SHOW random_page_cost returns 4 (HDD default) on an SSD or NVMe server
• EXPLAIN shows Seq Scan on large tables with selective predicates and valid indexes
• Lowering random_page_cost in a session (SET random_page_cost = 1.1) causes plan switch to index scan
• Hardware change (HDD → SSD/NVMe) occurred without corresponding configuration update
• effective_cache_size is already correctly set — planner still avoids index scans

Analysis Steps

-- Check current cost parameters:
SHOW random_page_cost;   -- HDD default: 4.0; should be 1.1 for NVMe, 1.5 for SSD
SHOW seq_page_cost;      -- baseline: 1.0 (do not change this)

-- View all planner cost parameters:
SELECT name, setting
FROM pg_settings
WHERE name IN (
    'random_page_cost', 'seq_page_cost',
    'cpu_tuple_cost', 'cpu_index_tuple_cost',
    'effective_cache_size'
)
ORDER BY name;

-- Demonstrate the impact — test before and after in a session:
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
-- With random_page_cost=4.0: likely Seq Scan

SET random_page_cost = 1.1;
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
-- With random_page_cost=1.1: likely Index Scan

RESET random_page_cost;

-- Check if random_page_cost has been set per tablespace (overrides global):
SELECT spcname, spcoptions
FROM pg_tablespace
WHERE spcoptions IS NOT NULL;

Pitfalls

• seq_page_cost = 1.0 is the baseline reference for all other cost parameters — do not change it. Only random_page_cost needs adjustment for storage type.
• random_page_cost is relative to seq_page_cost, not an absolute time. random_page_cost = 1.1 means random reads cost 10% more than sequential reads (accurate for NVMe).
• Target values by storage: NVMe ≈ 1.1, SATA SSD ≈ 1.5, HDD ≈ 4.0 (default). Cloud storage (EBS, network) may warrant 1.5–2.0 depending on IOPS limits.
• random_page_cost can be set per tablespace with ALTER TABLESPACE ... SET (random_page_cost = ...). This is essential for mixed storage (some tables on SSD, some on HDD).
• EXPLAIN shows cost estimates, not actual runtimes — a higher cost estimate does not mean the plan is wrong, just that the planner’s model is miscalibrated.
• This setting interacts with effective_cache_size: both must be correctly set together, especially after a hardware migration.

Resolution Approach

Set random_page_cost = 1.1 for NVMe (or 1.5 for SATA SSD) globally. If using mixed storage, set per tablespace instead. Reload configuration (no restart required). Verify with EXPLAIN before and after.