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

The cost function family

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

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):

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.