Pricing plans in PostgreSQL's currency: pg_orca's PG-aligned cost model

A Cascades optimizer is a search engine built around one question: which of these equivalent plans is cheapest? ORCA can enumerate bushy join orders, decorrelated subqueries and pushed-down aggregates, but every one of those choices is settled by a number the cost model returns. If that number is wrong, the search will find the wrong plan very efficiently.

ORCA came out of Greenplum with a cost model calibrated for MPP clusters. Its constants are bandwidths and unit costs measured on Greenplum hardware (DSeqIOBandwidth = 1024, DTableScanCostUnit = 5.5e-07, plus send and receive costs for every kind of Motion). On a single PostgreSQL node it priced plans in a different unit from PostgreSQL. It also ignored the knobs DBAs already tune (random_page_cost, work_mem, effective_cache_size), and comparing EXPLAINs could not tell you why ORCA had picked a plan.

So pg_orca ships a second cost model, CCostModelPG, which ports PostgreSQL's costsize.c into ORCA one operator at a time. It has been the default (pg_orca.cost_model = pg) since May 2026.

Where it plugs in

ORCA already hides costing behind an ICostModel interface. During search, the engine calls Cost(exprhdl, costing_info) for every candidate physical expression in the Memo, passing the children's total costs, row counts, widths and rebind counts. COptTasks::GetCostModel picks the implementation from the GUC.

CCostModelPG::Cost keeps PostgreSQL's additive structure: the sum of the children's costs plus a local cost for the operator. It dispatches to two dozen helpers such as CostScan, CostIndexScan, CostHashJoin, CostNLJoin, CostHashAgg, CostSort and CostLimit. It does not keep its own copy of the cost constants. It declares PostgreSQL's planner globals extern "C" and reads them live, so SET random_page_cost = 1.1 shifts ORCA's choice between index scan and seq scan just as it shifts PostgreSQL's.

Porting costsize.c, one operator at a time

Most helpers translate PostgreSQL's code directly. Here is the scan from PostgreSQL's own EXPLAIN documentation:

ORCA models WHERE unique1 < 7000 as a Filter operator above the scan, while PostgreSQL attaches the qual to the scan node. CostScan charges cost_seqscan's page I/O and per-tuple CPU (445.00). CostFilter charges one cpu_operator_cost per qual operator per input row (25.00). The sum is the 470.00 that PostgreSQL prints.

The larger operators bring more of PostgreSQL's detail with them. Hash joins separate hash clauses from residual quals and take bucket sizes from histograms. HashAgg models spilling with hash_agg_entry_size against work_mem × hash_mem_multiplier. Sort derives its merge order from work_mem, and btree scans get the real tree height through amgettreeheight.

Where the two models disagree

The formulas were the easy part. The hard part is that ORCA and PostgreSQL describe a plan differently. Three gaps come up again and again in the 53 commits on CCostModelPG.cpp.

No startup cost. PostgreSQL tracks startup_cost and total_cost separately, and LIMIT needs both: startup + (total − startup) × fraction. ORCA reports only a child's total. CostLimit estimates the run portion from the type of the child: a Sort's cost is almost all startup, and an IndexScan's is almost none.

Rebinds instead of loop counts. ORCA prices the inner side of a nested loop by multiplying it by the number of rebinds. PostgreSQL's cost_index uses loop_count instead, and spreads heap I/O across repeated probes with the Mackert–Lohman formula, because later probes find pages already cached. CostIndexScan passes ORCA's rebind count in as loop_count.

Early termination. A semi or anti join stops at the first match. Following final_cost_nestloop, CostNLJoin charges each matched outer row only 2 / (match_count + 1) of an inner scan. Without this, an EXISTS in the style of TPC-H Q4, with an indexed inner, came out about 3× too expensive. That pushed ORCA toward hashing the whole inner side and deduplicating it.

Keeping it honest

A cost model that is "PG-aligned" only in its comments drifts quickly. test/cost_align.sh runs 311 EXPLAINs twice, once through PostgreSQL's planner and once through ORCA with cost_model = pg. When both pick the same top operator, their costs must agree within 2%. Today 183 of those 188 plans do. Commit messages for cost changes routinely quote the before and after counts.

Aligning the costs also changed which plans ORCA was willing to pick. PostgreSQL prices a CTE Scan very cheaply. Once ORCA used the same price, it began choosing a Merge Join over two CTE Scans, a plan the Greenplum calibration had always priced out. PostgreSQL's CTE Scan can't mark and restore its position, which a merge join's inner side needs, so that plan crashed. The fix belonged in ORCA's property derivation rather than in the cost model, and it shows the general lesson: a new cost model opens new regions of the search space, and the tests have to follow.

Why it matters

When both planners price plans the same way, a pg_orca EXPLAIN reads like a PostgreSQL EXPLAIN. A cost of 470 means the same thing in both, and your tuning carries over. When ORCA still picks a different plan, the cause is a different search space or a different row estimate, not a different exchange rate, and that makes the plan much easier to debug.

To switch models or review the related settings, see Configuring pg_orca. For how the cost model fits into Memo search, see ORCA 101.