PostgreSQL's `random_page_cost` has been set to 4.0 by default for ~25 years, but experiments on modern SSDs show the actual cost ratio of random vs. sequential I/O is closer to 25-35x, not 4x. This means the planner picks suboptimal plans (sequential scan instead of index scan) for selectivities between 0.2% and 2.2%. Setting `random_page_cost` to ~30 aligns cost estimates with actual durations. However, lowering the value can still be justified in OLTP workloads with high cache hit rates, where random I/O avoids expensive full table scans. A complicating factor is that prefetching (which benefits sequential and bitmap scans but not index scans) interacts with `random_page_cost` in non-obvious ways, and the current cost model ignores prefetching entirely. Proposed improvements include separating non-I/O costs from `random_page_cost`, better cache statistics, and incorporating prefetching into the cost model.
Sort: