Tuning random_page_cost for SSD Storage #

random_page_cost is the planner’s belief about how much more expensive it is to fetch a page at a random offset than to fetch the next page in a sequential read. The default, 4.0, encodes the physics of a rotating disk: a seek, a rotational delay, then a read. On flash storage there is no seek and no rotation, so a default of 4.0 systematically overprices every index scan and pushes the planner toward sequential scans it does not need.

This page shows how the value enters the cost model, how to choose a defensible number, and how to avoid the failure modes at both extremes. The cost model itself is covered in cost estimation models.

Where the Value Enters the Cost Model #

Every table access path is costed as I/O plus CPU. The I/O half is built from two knobs:

seq_page_cost    = 1.0     -- one page fetched sequentially (the reference unit)
random_page_cost = 4.0     -- one page fetched at a random offset

A sequential scan of a 100,000-page table costs about 100000 × seq_page_cost, plus CPU per row. An index scan returning 5,000 rows scattered across the heap costs roughly index pages + 5000 × random_page_cost, discounted by how much of the table the planner believes is cached — that discount is effective_cache_size.

With random_page_cost = 4.0, those 5,000 heap fetches are priced at 20,000 cost units. Drop the setting to 1.1 and the same fetches cost 5,500, which is frequently the difference between the planner choosing the index and ignoring it. Nothing about the query or the data changed; the price list did.

Where the index stops winning at each setting A flat line represents the constant cost of a sequential scan. Two rising lines represent index scan cost with random_page_cost at four and at one point one. The steeper line crosses the sequential line at a much lower selectivity, showing that a high setting abandons the index sooner. rows returned (selectivity) → estimated cost Seq Scan (constant) index @ rpc = 4.0 index @ rpc = 1.1 2% 9% this range uses the index only at the lower setting

Annotated Evidence #

The symptom is a plan that ignores a perfectly good index:

SHOW random_page_cost;   -- 4
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id BETWEEN 40000 AND 40800;

Seq Scan on orders  (cost=0.00..214880.00 rows=48210 width=96)
                    (actual time=0.02..1841.60 rows=48122 loops=1)
  Filter: (customer_id >= 40000 AND customer_id <= 40800)
  Rows Removed by Filter: 7951878
  Buffers: shared read=91240
Execution Time: 1902.4 ms

Force the index path and compare the reality against the estimate:

BEGIN;
SET LOCAL random_page_cost = 1.1;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id BETWEEN 40000 AND 40800;

Index Scan using orders_customer_id_idx on orders  (cost=0.44..112402.10 rows=48210 width=96)
                                                    (actual time=0.05..142.80 rows=48122 loops=1)
  Index Cond: (customer_id >= 40000 AND customer_id <= 40800)
  Buffers: shared hit=1204 read=8891
ROLLBACK;
Execution Time: 148.9 ms

Twelve times faster, and the buffer numbers explain why: 10,095 pages touched instead of 91,240. The planner was not wrong about the row count — its estimate of 48,210 against an actual 48,122 is excellent. It was wrong about the price of reaching them.

Step-by-Step Tuning #

  1. Collect evidence before changing anything. Find statements where an index exists and is not used, then measure both paths with SET LOCAL inside a transaction. One anecdote is not a workload.

  2. Fix effective_cache_size first. It is more often wrong than random_page_cost and it changes the same decisions:

    SHOW effective_cache_size;                    -- default 4GB, usually far too low
ALTER SYSTEM SET effective_cache_size = '24GB';   -- ~50-75% of system RAM
SELECT pg_reload_conf();

  3. Test candidate values against a set of real queries, not one:

    BEGIN;
SET LOCAL random_page_cost = 1.5;
EXPLAIN (ANALYZE) SELECT … ;    -- repeat for each representative statement
ROLLBACK;

  4. Pick a value in the 1.1–2.0 band for SSD or NVMe, higher if a meaningful share of reads still miss the cache and reach network-attached storage.

  5. Apply it where it belongs. Globally when all data lives on the same class of storage; per tablespace when it does not:

    ALTER TABLESPACE fast_ssd SET (random_page_cost = 1.1);
ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();

  6. Re-check a sample of plans afterwards. The point of lowering the value is more index scans; the risk is index scans on queries that touch most of the table anyway. Watch for rising buffer counts on wide-range queries.

The band where the setting is defensible A horizontal scale from one to four. Values near one risk index scans that re-read heap pages. Values near four suppress index scans on flash storage. The band from one point one to two is marked as the usual SSD range. 1.0 1.5 2.0 3.0 4.0 (default) usual SSD / NVMe band too low index paths that re-read heap pages too high for flash index scans suppressed unnecessarily it is a ratio against seq_page_cost = 1.0, never a measurement in milliseconds

Before and After #

-- BEFORE: random_page_cost = 4.0
Seq Scan on orders   Buffers: shared read=91240        Execution Time: 1902.4 ms

-- AFTER: random_page_cost = 1.1
Index Scan using orders_customer_id_idx
                     Buffers: shared hit=1204 read=8891  Execution Time:  148.9 ms

The estimate accuracy was already good in both plans. What changed is which path the cost model preferred, and the buffer count is the honest measure of the improvement.

Common Pitfalls #

Treating the value as a millisecond figure. It is a ratio against seq_page_cost = 1.0. Diagnostic signal: someone proposing a value derived from a disk benchmark in microseconds. Fix: reason in ratios.

Changing it globally after testing one query. A value that rescues one range scan can push a large report onto an index path that re-reads the whole heap. Diagnostic signal: total buffer reads rising after the change. Fix: validate against a representative set.

Leaving effective_cache_size at the default. It undercounts the cache so heavily that index paths look expensive regardless of random_page_cost. Diagnostic signal: SHOW effective_cache_size returning 4 GB on a 64 GB machine. Fix: set it to a realistic fraction of RAM.

Setting it below seq_page_cost. Random access is never genuinely cheaper than sequential. Diagnostic signal: bizarre plans preferring index scans over trivially small tables. Fix: keep it at or above 1.0.

Tuning it to force one plan. The setting is a global statement about storage, not a per-query hint. Lowering it until one stubborn query picks an index changes the cost of every index path in the database. Diagnostic signal: a value chosen by bisection against a single statement, such as 1.03. Fix: pick the value from the storage characteristics, then fix the individual query through statistics or an index.

Ignoring the interaction with parallelism. A cheaper index path can stop a plan clearing the parallel-scan cost threshold, so a query that used four workers quietly becomes single-threaded. Diagnostic signal: a Gather node disappearing from a plan after the change. Fix: compare parallel plans specifically after adjusting page costs, as covered in why parallel workers are not launched.

Assuming cloud block storage behaves like local NVMe. Network-attached volumes have latency profiles closer to a fast spinning disk than to local flash, and their random-read penalty is real. Diagnostic signal: a value of 1.1 that produces index plans slower than the sequential ones they replaced. Fix: measure both paths on the actual volume type before committing, and set the value per tablespace when storage classes differ.

Fix these in order Three sequential steps: correct effective_cache_size first, then measure both access paths on real queries, then set random_page_cost per tablespace or globally. 1 · effective_cache_size 50–75% of system RAM 2 · measure both paths SET LOCAL, several queries 3 · random_page_cost per tablespace if storage differs reversing this order is how one query's fix becomes the whole fleet's regression

Frequently Asked Questions #

What value should random_page_cost be on SSD? Most SSD-backed systems settle between 1.0 and 2.0, and 1.1 is a common starting point. Because it is a ratio against seq_page_cost, the right value depends on how much more expensive random access really is on your storage and on how much of the working set is cached.

Should I set it to exactly 1.0? Only if random and sequential access are genuinely indistinguishable, which is closer to true for RAM than for any device. At 1.0 the planner loses its last reason to prefer sequential access and can pick index paths that revisit the same heap pages many times.

Is effective_cache_size more important? Often, yes, and it is more frequently mis-set. It tells the planner how much of the relation is likely cached, which discounts repeated random access; random_page_cost prices the uncached fetch. Correct effective_cache_size first, then revisit the page cost.