random_page_cost in Postgres - why the default is 4.0 and should you lower it?
summary
Recreates the original 2000s experiment: large uniform tables, forcing index vs sequential scans, and measuring per-page timings with direct I/O and EXPLAIN ANALYZE. Finds raw random/sequential ratios far above the 4.0 default—estimated random_page_cost 27–140 across SSDs and spinning disks—so the default is not simply the raw I/O ratio. Raising cost to match raw numbers may help isolated queries but increases cache pressure and hurts overall concurrency, so tuning needs measured system-wide monitoring rather than a single-number benchmark.
AI-written summary · about
chapters (12)
- 0:00Music & introduction
- 0:28Agenda and key questions to answer
- 0:45How Postgres cost-based planning works
- 2:05Sequential vs random I/O explained
- 3:40Breaking down cost formulas and parameters
- 7:50Where the default value 4.0 comes from
- 10:40Re-running old experiments on modern hardware
- 13:20Measuring real I/O costs with explain analyze
- 15:35Surprising results from SSDs and spinning disks
- 18:25Why higher costs can hurt overall performance
- 22:37Should you lower random_page_cost?
- 24:10Practical tuning advice and trade-offs
related talks
26mWhere do the performance cliffs come from?
25mScaling PostgreSQL Beyond Query Optimization
32mEXPLAIN: make it make sense
45mProper PostgreSQL Parameters to Prevent Poor Performance
43mTuning PostgreSQL for High Write Workloads
30mQuerySet.explain(): make it make sense.
40mExplaining the Postgres Query Optimizer
04mPostgres Tuning is saved by Sovenath Shaw