When PostgreSQL does not choose an index, lowering random_page_cost is a common suggestion. Christophe Pettus's discussion of these settings raises a useful question: can that number really be chosen from storage speed alone?

Even on SSDs, repeatedly accessing data in memory differs from retrieving a large working set from storage. A setting that helps one query may therefore be a poor fit for another.

Caching, physical row order, and the settings actually applied provide a better basis for deciding. PostgreSQL 18 documentation helps connect these mechanisms to a practical comparison.

The default of 4.0 includes assumptions about caching

The planner assigns costs to page access and CPU work when comparing execution methods. seq_page_cost estimates sequential page access, while random_page_cost estimates nonsequential access. Their defaults are 1.0 and 4.0, on a relative scale defined by the planner cost constants.

That ratio is not a direct measurement of device speeds. Cached accesses are part of the assumptions behind it.

The documentation history illustrates this distinction. An official documentation revision on October 30, 2025 removed the example of a fortyfold random-access penalty with 90% cache hits, together with the SSD example of 1.1. It retained caching and added network-storage latency to the explanation. This changed the documentation, not the default.

Pettus recommends 1.1 for SSD-backed OLTP whose working set fits in memory. The working set is the data repeatedly accessed by the workload. Preserve that condition rather than turning his operational advice into a universal SSD recommendation.

Physical row order changes the cost of an index scan

An ordinary index scan retrieves matching rows from the table, or heap. Rows stored close together lead to a different page-access estimate from rows scattered throughout it.

pg_stats.correlation describes the relationship between column-value order and physical row order. It ranges from −1 to +1; values near either endpoint make index access look cheaper than values near zero. See the pg_stats definition.

For an ordinary, non-repeated index scan, the PostgreSQL 18 costing implementation interpolates between uncorrelated and perfectly correlated heap I/O estimates using correlation squared. In the perfectly correlated case, it charges one random fetch followed by sequential fetches.

To isolate that effect, suppose both cases fetch 100 pages. These are illustrative calculations for heap I/O, not measurements.

Here, seq_page_cost stays at 1.0. Actual page-count estimates also differ, and index access and CPU work add further costs. This is not a total scan-cost calculation, but it shows why the same setting change can have different effects.

Inspect tablespace overrides as well as SHOW output

Before comparing plans, establish the effective settings. SHOW displays runtime parameters; tablespace page-cost overrides need separate inspection. The SHOW specification and ALTER TABLESPACE override rules describe these separate mechanisms.

The following SQL only reads settings and catalog data. The spcoptions column contains options stored in pg_tablespace.

-- Inspect page costs visible in the session
SHOW random_page_cost;
SHOW seq_page_cost;

-- Inspect tablespace overrides
SELECT spcname, spcoptions
FROM pg_tablespace
ORDER BY spcname;

Overrides on the tablespaces holding the relevant table or index take precedence. If changing a session setting does not affect the plan as expected, first check which settings apply to those objects.

Test 1.1 by comparing execution time separately from estimated cost

An index scan appearing after a setting change does not establish a speedup. Follow the EXPLAIN guide to compare estimated and actual rows, execution time, and buffer activity. EXPLAIN (ANALYZE, BUFFERS) executes the query, so use representative read queries in a test environment.

A useful comparison proceeds as follows:

1. Save the plan and execution results under the current settings. Look for large differences between estimated and actual rows.
2. After checking tablespace overrides, compare the same query using SET LOCAL random_page_cost = 1.1; within a transaction. SET LOCAL lasts only until that transaction ends.
3. Include both selective lookups and broader reads. Record regressions as well as improvements instead of deciding from one query.

Buffer counters also have limits. A shared hit indicates a shared-buffer hit, but a read does not necessarily mean physical storage access: the operating-system page cache may supply the data. The official I/O statistics documentation recommends combining PostgreSQL statistics with operating-system observations.

Choosing random_page_cost means asking how this workload accesses pages, beyond whether its storage is an SSD. Check physical ordering and effective settings, then judge representative execution times. That makes 1.1 a candidate to test rather than a number to memorize.

Source

• Title: Christophe Pettus: All Your GUCs in a Row: random_page_cost and seq_page_cost
• URL: https://postgr.es/p/9yN