Planner cost model

planner-cost-model — how the planner chooses among plans

The planner generates many candidate Paths for a query. Each Path has a cost — a numeric estimate of how expensive it is to execute. The planner picks the cheapest.

Cost isn't in seconds or bytes — it's in "arbitrary units where seq_page_cost = 1.0". The RATIO matters, not the absolute value.

The file map

File	Role
optimizer/path/costsize.c	The main file. All the cost_* functions live here. 4000+ lines.
optimizer/path/allpaths.c	Path-generation orchestrator — calls the cost functions during path enumeration.
optimizer/path/joinpath.c	Join-specific path generation (calls cost_nestloop / _mergejoin / _hashjoin).
optimizer/path/pathkeys.c	Sort-order tracking — cheaper paths often preserve useful pathkeys.
include/optimizer/cost.h	The cost GUC declarations + planner-cost-related types.

The cost function family

Every path-shape has its cost function. A partial list:

Function	Node
cost_seqscan	SeqScan — read all pages sequentially
cost_index	IndexScan / IndexOnlyScan
cost_bitmap_heap_scan	BitmapHeapScan — after bitmap-index accumulator
cost_bitmap_and_or_node	BitmapAnd / BitmapOr
cost_tidscan	TidScan — direct block+offset access
cost_subqueryscan	SubqueryScan
cost_functionscan	FunctionScan (SRF-in-FROM)
cost_valuescan	ValuesScan (VALUES-in-FROM)
cost_gather / cost_gather_merge	Parallel gather
cost_recursive_union	Recursive CTE
initial_cost_nestloop / final_cost_nestloop	2-stage nestloop cost
initial_cost_mergejoin / final_cost_mergejoin	Mergejoin
initial_cost_hashjoin / final_cost_hashjoin	Hash join
cost_sort	Sort node
cost_hashagg / cost_agg	Aggregate
cost_windowagg	Window function
cost_incremental_sort	Incremental sort
cost_memoize_rescan	Memoize (PG 14+)
cost_material	Materialize
cost_ctecan	CTE Scan

Each computes startup_cost (time to first row) + total_cost (time to last row).

The 2-stage join cost pattern

Join paths use TWO cost functions:

• initial_cost_* — cheap; called during path enumeration to prune non-viable joins early.
• final_cost_* — expensive (selectivity + qual-cost); called only for paths that survive initial filtering.

This is O(N²) potential paths but O(N) expensive evaluations. The JoinCostWorkspace carries state between phases.

The cost GUCs

Configuration knobs (in postgresql.conf):

GUC	Default	Meaning
seq_page_cost	1.0	Cost of a sequential page read. The reference unit.
random_page_cost	4.0	Cost of a random page read. Lower for SSDs (2.0 or less common).
cpu_tuple_cost	0.01	CPU cost per tuple processed.
cpu_index_tuple_cost	0.005	CPU cost per index tuple.
cpu_operator_cost	0.0025	CPU cost per operator/function call.
parallel_setup_cost	1000	Fixed cost of starting workers — high, deters parallel for tiny queries.
parallel_tuple_cost	0.1	Cost of communicating a tuple from worker to leader.
min_parallel_table_scan_size	8MB	Threshold for considering parallel seqscan.
min_parallel_index_scan_size	512KB	Threshold for parallel index scan.
jit_above_cost	100000	JIT compilation threshold for total cost.
jit_optimize_above_cost	500000	JIT full optimization threshold.
jit_inline_above_cost	500000	JIT inlining threshold.
effective_cache_size	4GB	Estimate of OS+shmem cache — affects index-scan cost.
effective_io_concurrency	1	Estimated concurrent I/O — affects bitmap heap scan cost.

The typical cost pattern

A cost function generally accumulates:

startup_cost = seq_or_random_page_cost * pages_read_before_first_tuple
             + cpu_tuple_cost * tuples_before_first
             + qual_cost.startup

total_cost = startup_cost
           + (seq_or_random_page_cost * pages_read_after)
           + (cpu_tuple_cost * tuples_produced)
           + (cpu_operator_cost * total_operator_calls)
           + qual_cost.per_tuple * tuples_produced

Selectivity comes from clauselist_selectivity (which consults per-column stats + extended stats).

Parallel cost adjustment

Parallel paths get costs adjusted:

• path->parallel_workers > 0: divide the per-tuple work by parallel_workers.
• Then add parallel_setup_cost (once) + parallel_tuple_cost * (tuples returned to leader).
• Only paths with parallel_safe = true are eligible.

The tradeoff: parallel adds fixed overhead but divides per-row work. Only wins for larger inputs.

Common patch shapes

Add a cost function for a new node

• Function in costsize.c following the naming convention.
• Match the pattern (startup / per-page / per-tuple / per-operator).
• Register in the path-generation code that produces the new node.
• Test against existing similar nodes to validate.

Change a cost function's algorithm

• Very impactful — plan choices shift.
• Compare EXPLAIN output before + after on a diverse workload (regression suite + your own).
• pgsql-hackers discussion recommended.

Tune a cost GUC for a specific workload

• Not a code change; user-level.
• SSD workloads: random_page_cost = 1.1 (close to seq).
• Very large working set: effective_cache_size = 75-90% of RAM.
• Parallel-heavy: parallel_tuple_cost = 0.05 (allows parallel for smaller queries).

Debug "planner picking the wrong plan"

• EXPLAIN ANALYZE — compare estimates to actual.
• If cost is WAY off but rows are close: cost function issue.
• If rows are WAY off: selectivity issue — see extended-statistics skill.
• Check if a specific GUC would fix it (random_page_cost is most common culprit).

Pitfalls

• Cost isn't in seconds — it's a unit. Comparing costs across queries is meaningful (same units); interpreting a cost as time is not (varies with hardware).
• random_page_cost too high locks in SeqScan — a common Rate Limit on modern SSD systems. Reducing to 1.1-1.5 fixes many "why SeqScan on a small filtered range" problems.
• effective_cache_size too low deters IndexScan — the planner estimates repeat reads; a low value assumes cache misses.
• parallel_setup_cost too low → over-parallelization — small queries pay the worker startup + coordination for negative net gain.
• JIT thresholds too aggressive → planning-time spike — extremely large queries hit JIT compilation which is CPU-heavy in the planning path.
• Cost of qual_cost includes function calls — for user-defined functions marked as VOLATILE / EXPENSIVE, the planner assumes worst case. Marking IMMUTABLE where truly IMMUTABLE gives better plans.
• cost_sort uses work_mem — under memory pressure, sort spills to disk; the cost function accounts for this.
• enable_seqscan = off doesn't remove the option, just penalizes — the planner still considers SeqScan but with disable_cost added. Sometimes needed anyway.
• Cross-partition-pruning costs are per-partition — hitting many partitions accumulates cost; enable_partition_pruning = on (default) removes irrelevant ones early.

Related corpus

• Idioms (many hits): cost-scan-paths, cost-join-paths, cost-parallel-adjustments, cost-units-gucs.
• Subsystem: optimizer (the parent — cost is one aspect of path generation).
• Related skills: extended-statistics (selectivity side), custom-scan-api (registering costs for custom paths).

Corpus-chain shortcut

python3 scripts/corpus-chain.py --idiom cost-scan-paths
python3 scripts/corpus-chain.py --file src/backend/optimizer/path/costsize.c

Boundary

Use this skill for cost estimation + cost function additions + cost GUC tuning.

Don't use for:

• Selectivity — different concept. See extended-statistics and analyze-mcv-histogram-correlation idiom.
• EXPLAIN output rendering — that's explain.c, uses the cost as an input.
• Actual runtime performance — cost is an estimate; measure real perf with EXPLAIN (ANALYZE, BUFFERS, TIMING).
• Query rewriter cost — the rewriter doesn't consult a cost model; it's rule-based.