A lookup that should have taken forty milliseconds was taking a second and a half, and the plan told me why in one line: Parallel Seq Scan on a 200-million-row events table, with a filter that matched two percent of it and a perfectly good btree index sitting unused on the predicate column. The planner was not confused. It was doing exact arithmetic with prices from 2005 — random_page_cost 4.0 on hardware where a random page read costs essentially what a sequential one costs. One SET command later the same query flipped to an index scan and ran in 45 ms.
random_page_cost is the planner's price for one non-sequential page fetch, and it is the most under-reviewed GUC I find on SSD-backed clusters. seq_page_cost stays at 1.0 by convention; everything below is about the ratio between them.
What does random_page_cost actually steer?
It steers the tipping point between index scans and sequential scans — the selectivity at which the planner stops believing random fetches are worth paying for. An index scan's I/O price is roughly random_page_cost times the pages it expects to touch; a sequential scan is seq_page_cost times every page in the table, plus CPU per tuple either way. With the ratio at 4, a predicate matching a few percent of rows often prices out cheaper as a full table read, because the model insists each random page costs four sequential ones. With the ratio at 1.1, the crossover moves far out, and selective predicates get the index plans they deserve. The setting reaches further than point lookups: bitmap heap scans price between the two extremes, and the inner side of a nested loop is charged random fetches for each probe, so join strategy shifts too. Changing this one number re-prices a meaningful share of your plan space, which is exactly why you verify with plans afterward instead of trusting folklore — mine included.
What value is sane on NVMe and cloud SSD?
On local NVMe I start at 1.1 and rarely move it; the honest range is 1.0 to 1.5. The 4.0 default is spinning-disk math — a raw random-versus-sequential penalty of roughly forty to one, discounted by the assumption that ninety percent of random reads come from cache — and on NVMe there is no disk head doing the seeking. A random 8 KB read from NVMe lands in tens of microseconds, and sequential readahead keeps its small edge mostly through larger I/Os, which a ratio near 1 already reflects. Do not set random_page_cost below seq_page_cost: the planner would then price random reads cheaper than sequential ones, and even NVMe does not justify that inversion. Cloud block storage is the exception that keeps me honest. Networked volumes like gp3 or Persistent Disk carry latency local flash does not, and their throughput is capped, so 1.1 can overcorrect there; I have left 2.0 on a burstable-volume cluster after the plans told me to. Measure your hardware with a quick random-read benchmark if you like, but the verdict that matters comes from EXPLAIN on your own queries, not from anyone's blog numbers.
How does effective_cache_size interact with it?
effective_cache_size tells the planner how much caching stands between a random fetch and a disk — shared_buffers plus the OS page cache, as one number — and it allocates nothing. It only changes the assumed probability that a page the index scan needs is already in memory, which discounts the random_page_cost charge on a fraction of the fetches. The default 4 GB is timid on any modern host; I set it to 60 to 75 percent of RAM and forget it. The interaction is the part people miss. On a working set that fits in cache, a high effective_cache_size already makes index scans look cheap, and random_page_cost matters less. On data much larger than RAM — our events table was — cache assumptions help little, and the page-cost ratio dominates the decision. And do not confuse the two knobs that actually allocate memory with the one that does not: raising shared_buffers changes real caching behavior, while effective_cache_size only changes what the planner believes about it. Setting one when you meant the other is a classic, and the plans will not tell you which mistake you made.
How do wrong costs show up in EXPLAIN?
The signature is a sequential scan — usually parallel — over a highly selective predicate that has a usable index, in a workload that is mostly random reads. Confirm it with the diagnostic before changing production: in a session, SET enable_seqscan = off, rerun the query with EXPLAIN (ANALYZE, BUFFERS), and compare actual time and the read-versus-hit split in the Buffers line. If the index plan is genuinely faster in reality but costed higher, your page costs are lying; if it is genuinely slower, the planner was right and your intuition is what needs adjusting. Then test the candidate value the same way, per session, before touching the cluster.
SET random_page_cost = 1.1;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, occurred_at, payload
FROM events
WHERE tenant_id = 42
AND occurred_at >= now() - interval '7 days';
RESET random_page_cost;Two things to read in the output: the Buffers line's read against hit counts tell you whether the test even exercised storage — a fully cached seq scan hides precisely the cost you are investigating — and whether the estimated costs now rank the plans in the same order as the actual runtimes. The EXPLAIN ANALYZE guide covers reading those buffers lines in detail.
Can you scope the setting instead of changing the whole cluster?
Yes, and the tablespace level exists for exactly this: ALTER TABLESPACE ... SET (random_page_cost = ...) applies the price to relations living on that storage, so a hot NVMe tablespace can carry 1.1 while an archive tablespace on cheap volumes keeps something closer to the default.
ALTER TABLESPACE hot_nvme SET (random_page_cost = 1.1);
ALTER DATABASE analytics SET random_page_cost = 1.1;Per-database and per-role settings work too, and per-session SET is how you test. My rule: when storage is uniform, set it cluster-wide once with ALTER SYSTEM, reload, and move on — this is a setting you tune once and verify with plans, not a knob to fiddle per query. While you are in there on NVMe, effective_io_concurrency in the low hundreds lets bitmap scans actually use the device's parallel read capacity; the default assumed a single outstanding I/O all the way through PostgreSQL 17 — 18 raises it to 16 — and even that is gentle for this hardware.
Watching the plan shift with MonPG
The proof of this change lives in counters, not feelings. pg_stat_user_tables shows the idx_scan share on the hot table climbing after the flip — ours went from forty percent to ninety-two within a day — and pg_stat_statements shows per-statement mean time falling for the queries that changed plans. MonPG graphs both series as part of its PostgreSQL monitoring, alongside block read-versus-hit ratios, so you can watch the seq-scan signature fade and catch any query that regressed because the new prices pushed it somewhere worse. Tune once, verify with plans, then let the counters argue with anyone who wants to change it back.