skip to content

Cost Knobs & Parallelism

PostgreSQL's plan choice moves with random_page_cost, the enable_* switches, generic plans for prepared statements, parallel workers and JIT. Interviewers ask how you steer it without hints.

part ofPostgreSQLread it →
on this pageshow

explore

questions

6

In PostgreSQL, what does random_page_cost model, what is its default, and why do teams on SSD storage often lower it?

level: middleimportance: must knowfreq 60%

answer

  1. relative units, not milliseconds
  2. seq_page_cost is the anchor
  3. default already assumes caching
  4. a tablespace option too

basics

~20 s

random_page_cost is the planner's cost of fetching a page out of order, default 4.0 against seq_page_cost 1.0. On SSDs random reads cost little more than sequential ones, so lowering it lets index scans win where they really are cheaper.

solid answer

~50 s

PostgreSQL's planner prices plans in abstract units anchored on `seq_page_cost` = 1.0. `random_page_cost`, default 4.0, is the price of a non-sequential page fetch, which is what an index scan pays when it visits heap pages out of order. The manual explains that 4.0 is already low for spinning disks because it assumes most random reads are served from cache. On SSD or NVMe storage the real gap to sequential reads is small, so a value close to `seq_page_cost`, often around 1.1, makes index and nested-loop plans cost what they really cost and moves the crossover where a sequential scan wins. It can be set per tablespace with `ALTER TABLESPACE ... SET (random_page_cost = ...)`. It only changes which plan is chosen, never the speed of the disk, so I validate a change with `EXPLAIN (ANALYZE)` on representative queries.

code

sql · 13 lines
sql
-- test in one session first
SET random_page_cost = 1.1;
EXPLAIN (ANALYZE)
SELECT * FROM loyalty_ledger
WHERE member_id = 4711
  AND earned_at >= now() - interval '90 days';

-- then for the cluster (reload, no restart)
ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();

-- or only where the storage differs
ALTER TABLESPACE archive_hdd SET (random_page_cost = 4.0);

go deeper

for a junior

Recall that random_page_cost defaults to 4.0 against seq_page_cost 1.0, and that it is a planner estimate, not a measurement of the disk.

for a middle

Explain how the ratio moves the crossover between sequential and index scans, and why SSD storage justifies a value close to seq_page_cost.

for a senior

Show how you test a change on representative queries, use tablespace overrides where storage differs, and keep it apart from row-estimate problems.

for a principal

Treat cost constants as the cluster's model of its hardware: change them rarely, with a regression check, and record why for the next storage migration.

## Cost units, not time PostgreSQL's planner gives every candidate plan an estimated **cost** and keeps the cheapest. The cost is a number on an arbitrary scale: by convention one sequential page read costs `1.0`, and every other constant is set relative to that. Only the **ratios** matter; multiplying all of them by ten changes no plan choice. | constant | default | prices | |---|---|---| | `seq_page_cost` | 1.0 | one page read as part of a sequential pass | | `random_page_cost` | 4.0 | one page read out of order | | `cpu_tuple_cost` | 0.01 | processing one row | | `cpu_index_tuple_cost` | 0.005 | processing one index entry | | `cpu_operator_cost` | 0.0025 | one operator or function call | All five are user-settable, so they can be changed per session for testing and applied with a configuration reload, without a restart. A rough illustration of how they combine: an index scan expected to fetch 1,000 heap pages in random order carries about 4,000 units of page cost at the default, but about 1,100 at `random_page_cost` = 1.1, while a sequential scan of a 10,000-page table carries 10,000 units plus a CPU charge for every row. The same query can therefore flip from a sequential scan to an index scan purely because the ratio changed, with no change to the data, the index or the statistics. ## What random_page_cost changes - **Index scans.** An index scan finds matching entries in the index, then fetches each heap page that holds a matching row. When the table is not stored in index order, those fetches jump around the file and are charged at `random_page_cost`. The planner blends random and sequential pricing using how well the column's values follow the physical row order. - **Bitmap heap scans** sit in between: they collect matching row locations first and then visit heap pages in physical order. - **Nested loops** whose inner side is an index lookup pay random reads once per outer row, so they are very sensitive to this constant. - **The crossover point.** For a given predicate there is a fraction of the table above which a sequential scan is cheaper than an index scan. Lowering `random_page_cost` raises that fraction, so index plans win for larger result sets. ## Why 4.0, and why SSDs go lower The manual is explicit: random access to durable storage is normally much more than four times as expensive as sequential access, but 4.0 is used because most random reads, such as index lookups, are expected to be in cache. On SSD or NVMe the physical penalty for random access is small, so the planner over-prices index plans and chooses sequential scans where an index would be faster. Common practice on SSD is a value close to `seq_page_cost`, such as 1.1. Two limits from the manual: - **Never below `seq_page_cost`.** The server allows it, but it is not physically sensible: random reads are never cheaper than sequential ones. - **Equal values fit an entirely cached database.** In that case, also lower both page costs relative to the CPU constants, because a page already in memory costs very little to touch. The manual also notes that network-attached storage has latency that reduces the relative overhead of random access, so the right value depends on what the storage really is, not on the word 'SSD' on a price list. ## Changing it safely 1. Find queries where the planner chose a sequential scan but a forced index plan was faster, for example with an `enable_seqscan = off` experiment. 2. In one session, `SET random_page_cost = 1.1` and run `EXPLAIN (ANALYZE)` on a representative set, including large reports where a sequential scan should still win. 3. Apply it cluster-wide with `ALTER SYSTEM` and a reload, or per tablespace when storage differs: `ALTER TABLESPACE ... SET (random_page_cost = ...)` applies to the tables and indexes stored there. 4. Watch the top statements for regressions after the change. ## What it does not fix - **Bad row estimates.** If the planner expects 50 rows and gets 500,000, no page-cost constant repairs the plan; the statistics behind the estimate do. - **Disk speed.** It changes which plan is chosen, never how fast a read is. - **Prefetching.** `effective_io_concurrency` is a different knob: it sets how many I/O requests the executor keeps in flight, and the cost model does not use it. - **Cache size assumptions.** `effective_cache_size` also feeds index costing; sizing it is a memory-tuning subject.

  • Why is setting random_page_cost below seq_page_cost a mistake in PostgreSQL?
    It tells the planner that reading pages out of order is cheaper than reading them in order, which is never physically true, so it would favour index plans even where a sequential pass over the same pages is cheaper. If the data is fully cached, the manual's advice is to set the two equal and lower both relative to the CPU cost constants.
  • Lowering random_page_cost did not fix a PostgreSQL plan that expected 50 rows and got 500,000. Why not?
    The constant prices each page; the plan went wrong because the row estimate was off ten-thousand-fold, and that error swamps any page price. The fix is on the estimate side: fresh ANALYZE, a higher statistics target for the column, or extended statistics for correlated columns.
  • How do you give HDD-backed archive tables a different random_page_cost from NVMe-backed hot tables in PostgreSQL?
    Place them in separate tablespaces and set the option on each with ALTER TABLESPACE ... SET (random_page_cost = ..., seq_page_cost = ...). The planner uses the tablespace's values for the tables and indexes stored there. There is no per-table or per-index random_page_cost option.

  • random_page_cost is measured in milliseconds per page read.
  • Lowering random_page_cost makes the disk reads themselves faster.
  • On SSD, set random_page_cost well below seq_page_cost, such as 0.5.
  • Changing random_page_cost needs a server restart.
  • Raising effective_io_concurrency makes the planner cost index scans lower.
open as a page

In PostgreSQL, what does SET enable_seqscan = off actually do, and why is it a diagnostic test rather than a fix?

level: juniorimportance: should knowfreq 52%

basics

~20 s

It makes the planner avoid sequential scans unless no other path exists, exposing the plan it rejected so you can time it. A faster forced plan proves the estimates or cost settings are wrong; fix those, not the switch.

open as a page

In PostgreSQL, which settings decide how many parallel workers a query plans and actually gets, and why can it get fewer?

level: middleimportance: should knowfreq 45%

basics

~10 s

max_parallel_workers_per_gather (default 2) caps workers per Gather node and table size sets the planned count; max_parallel_workers and max_worker_processes (8 each) cap the shared pool, so a busy server launches fewer or none.

open as a page

In a PostgreSQL-backed loyalty-points service, a prepared statement is fast for five executions and then turns slow; what changed, and how do you fix it?

level: seniorimportance: should knowfreq 50%

basics

~20 s

After five custom-planned executions, PostgreSQL builds a generic plan and keeps it if its estimated cost is below the custom average. On skewed data that estimate misleads; force_custom_plan, scoped to that role or function, or better statistics fix it.

open as a page

In PostgreSQL 18, why can JIT compilation make a short query slower, and which settings decide whether a query is JIT-compiled?

level: seniorimportance: should knowfreq 34%

basics

~20 s

JIT starts when a plan's estimated total cost exceeds jit_above_cost (100000), with inlining and optimization above 500000. A short query the planner overestimates pays compile time that can exceed its runtime; jit = off or higher thresholds fix it.

open as a page

In PostgreSQL, how do join_collapse_limit, from_collapse_limit and geqo_threshold bound join-order search, and when would you set join_collapse_limit to 1?

level: seniorimportance: nice to knowfreq 24%

basics

~20 s

from_collapse_limit and join_collapse_limit (both 8) stop subqueries and explicit JOINs merging into one larger search; join problems of geqo_threshold (12) items or more go to the genetic optimizer. join_collapse_limit = 1 joins explicit JOINs in written order.

open as a page