How the planner chooses · PostgreSQL

The planner enumerates ways to execute a query, assigns each a cost, and runs the cheapest. The cost is in arbitrary units anchored to one sequential page read, and it is arithmetic rather than judgement — which means you can check it.

Reproducing a cost

Read-only / Safethe planner's number— Captured on 18.6. A 2,000,000-row table occupying 16,667 pages.

$ psql -U postgres -c "EXPLAIN SELECT * FROM ordersx"

 Seq Scan on ordersx  (cost=0.00..36667.00 rows=2000000 width=32)

Read-only / Safethe same number, computed from the catalogue— Captured on 18.6. relpages and reltuples come from pg_class; the costs are the defaults.

$ psql -U postgres -c "SELECT relpages, reltuples::bigint, relpages*current_setting('seq_page_cost')::numeric + reltuples*current_setting('cpu_tuple_cost')::numeric AS predicted FROM pg_class WHERE relname='ordersx'"

 relpages | reltuples | predicted
----------+-----------+-----------
  16667 |   2000000 |     36667

16667 × 1 + 2000000 × 0.01 = 36667. Exactly the number in the plan.

The two numbers in cost=0.00..36667.00 are startup cost and total cost. Startup is what must happen before the first row can be returned — zero for a sequential scan, substantial for a sort, which is why LIMIT changes plan choices so dramatically.

The cost constants

Constant	Default	Represents
`seq_page_cost`	1.0	Reading one page sequentially. The unit.
`random_page_cost`	4.0	Reading one page at a random offset
`cpu_tuple_cost`	0.01	Processing one row
`cpu_index_tuple_cost`	0.005	Processing one index entry
`cpu_operator_cost`	0.0025	Evaluating one operator or function
`effective_cache_size`	4 GB	How much cache the planner assumes exists

The two that are worth changing, and why

random_page_cost. The default 4.0 encodes the ratio between a sequential read and a seek on a spinning disk. On SSD or NVMe there is no seek, and the true ratio is closer to 1.1–2.0.

Leaving it at 4.0 on flash makes the planner systematically overvalue sequential scans and undervalue index scans. The symptom is a query that should use an index choosing a sequential scan, on a table where the index would obviously win.

-- typical for local NVMe
ALTER SYSTEM SET random_page_cost = 1.1;
-- typical for cloud block storage, which does have latency
ALTER SYSTEM SET random_page_cost = 2.0;

effective_cache_size. This allocates nothing. It is a hint about how much of the table an index scan is likely to find already cached, between shared buffers and the operating system’s page cache. The default of 4 GB is conservative on any modern server.

Set it to roughly 50–75% of system memory. It affects index scan costing only, and getting it badly low makes the planner avoid indexes on large tables.

Change one, run your representative queries, compare. Both are user context so they can be tested per session before being committed:

SET random_page_cost = 1.1;
EXPLAIN (ANALYZE, BUFFERS) SELECT …;

Turning off enable_seqscan to make the planner use an index

SET enable_seqscan = off;   -- "fixed" it

These enable_* settings do not disable anything. They mark that node type as disabled, and the planner prefers plans using fewer disabled nodes — but if a sequential scan is the only option, the plan uses one anyway and says so:

Read-only / Safewhat a disabled node looks like on 18.6— EXECUTION VALIDATED on 18.6 against a table with no indexes. Note the cost.

$ SET enable_seqscan = off;
EXPLAIN SELECT * FROM noidx;

 Seq Scan on noidx  (cost=0.00..227.20 rows=16320 width=4)
 Disabled: true

227.20 is the ordinary sequential scan cost. The node is not priced out; it is flagged. Older PostgreSQL releases implemented this by adding a large constant (disable_cost) to the node’s cost, and that mechanism is gone — there is no disable_cost setting on 18.6:

Read-only / Safethe old mechanism is not there— EXECUTION VALIDATED on 18.6.

$ SELECT count(*) FROM pg_settings WHERE name = 'disable_cost';

 count
-------
   0

The practical difference is that costs shown for a plan containing a disabled node are still meaningful numbers you can compare, rather than astronomical ones you have to mentally subtract.

They are diagnostic tools, and they are excellent ones: forcing the alternative and comparing shows you what the planner was choosing between.

SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT …;   -- what the index scan really costs
RESET enable_seqscan;

As a production setting they are a trap. The planner was not being stupid; it had wrong information. Leaving enable_seqscan = off in place means every query in that session is planned under a lie, and the next query — the one you did not test — gets a worse plan than it would have had.

Fix the input: statistics, random_page_cost, effective_cache_size, or a missing index.

Scan types

Type	Reads	Best when
Seq Scan	Every page in order	A large fraction of the table is needed
Index Scan	Index, then heap rows one at a time in index order	Few rows, or the order is wanted
Index Only Scan	Index alone, if the visibility map allows	Every needed column is in the index
Bitmap Heap Scan	Index builds a page bitmap, then heap in page order	A moderate fraction; several indexes combined

The bitmap variant exists for the middle ground. Rows in index order are scattered across the heap, so a plain index scan revisits pages; the bitmap sorts them so each page is read once.

Read-only / Safea bitmap heap scan reading 30,000 rows from 260 pages— Captured on 18.6. amount is not in the index, so the heap must be visited.

$ psql -U postgres -c "EXPLAIN (ANALYZE, BUFFERS, COSTS ON, TIMING OFF, SUMMARY OFF) SELECT sum(amount) FROM ordersx WHERE customer_id BETWEEN 1 AND 3000"

 ->  Bitmap Heap Scan on ordersx  (cost=422.75..18066.13 rows=28324 width=4) (actual rows=30000.00 loops=1)
     Recheck Cond: ((customer_id >= 1) AND (customer_id <= 3000))
     Heap Blocks: exact=260
     Buffers: shared hit=241 read=56
     ->  Bitmap Index Scan on ordersx_customer_idx  (cost=0.00..415.67 rows=28324 width=0) (actual rows=30000.00 loops=1)
           Buffers: shared hit=37

Where the crossover actually is

Read-only / Safethe same query shape, widening the range— Captured on 18.6 with the planner unconstrained each time.

$ psql -U postgres -c "EXPLAIN (ANALYZE, BUFFERS, COSTS ON, TIMING OFF, SUMMARY OFF) SELECT count(*) FROM ordersx WHERE customer_id BETWEEN 1 AND N"

 rows matched | plan chosen              | buffers
--------------+--------------------------+---------
      2 000 | Index Only Scan          |       6
    200 000 | Parallel Index Only Scan |     236
  2 000 000 | Parallel Seq Scan        |   16667

The switch happened between 10% and 100% selectivity here. The “indexes stop helping past 5%” rule that circulates is not a constant: it depends on random_page_cost, effective_cache_size, index correlation and row width. Measure it on your data rather than assuming it.

Join types

Type	Mechanism	Chosen when
Nested Loop	For each outer row, look up matching inner rows	The outer side is small
Hash Join	Build a hash of one side, probe with the other	Both sides substantial, join on equality
Merge Join	Sort both sides, walk them together	Both already sorted, or sorting is cheap

The nested loop is where bad estimates become disasters, because its cost is multiplied by the outer row count. A plan expecting 12 outer rows and receiving 1,000 does 83 times the work it was costed for. Lesson X-04 measures exactly that.

Parallel query, and why a plan you expected to parallelise did not

A Gather node in a plan means parallel workers. Deciding whether to use them is its own cost calculation with its own settings, and four things commonly prevent it.

The table is too small. min_parallel_table_scan_size defaults to 8 MB. Below that, parallelism is not considered at all.

The saving does not cover the setup. parallel_setup_cost defaults to 1000, which is large next to most plans, and parallel_tuple_cost of 0.1 charges for every row passed from a worker to the leader. A plan returning many rows pays heavily for that transfer.

No workers were available. max_parallel_workers_per_gather (default 2) bounds one query; max_parallel_workers bounds the cluster; max_worker_processes bounds everything. When the pool is exhausted the plan still says Workers Planned: 2 and the execution reports Workers Launched: 0. That discrepancy is the diagnostic — a query that is intermittently slow with a parallel plan is often losing its workers to other queries.

The query is not parallel-safe. A VOLATILE function, a non-parallel-safe user function, or writing data all disqualify it. pg_proc.proparallel records a function’s marking, and an unmarked PL/pgSQL function defaults to unsafe, which silently disables parallelism for every query that calls it.

SELECT proname, proparallel FROM pg_proc WHERE proname = 'my_function';
-- 'u' unsafe, 'r' restricted, 's' safe

That last one accounts for a good share of “why is this query not using all my cores”: one helper function, unmarked years ago, that no plan can parallelise around.

What to take from this

Cost is arithmetic. relpages × seq_page_cost + reltuples × cpu_tuple_cost reproduced a plan’s number exactly.
random_page_cost = 4 encodes a spinning disk. On flash it should be closer to 1.1.
effective_cache_size allocates nothing and changes index costing. The 4 GB default is low for modern servers.
enable_* settings are diagnostics, not fixes. On 18.6 they mark a node type as disabled — Disabled: true in the plan — rather than inflating its cost.
The index-to-sequential crossover is not a fixed percentage. Measure it.
Workers Planned against Workers Launched is where intermittent parallel slowness shows up.

Cross-course references

Linux for Production Sysadmins — Part XLI (Storage Performance) covers measuring the random-versus-sequential ratio that random_page_cost is supposed to express, so the setting comes from a device rather than from a blog post.
Observability for Production Sysadmins — Part LIX (Database observability) covers tracking plan-shape changes rather than discovering them during an incident.