ORCA's Metadata Layer and Cost Model Explained

ORCA does not use the planner’s estimates. It derives its own, through its own metadata layer — which is why the two optimizers disagree.

ORCA is not a part of PostgreSQL. It is a separate C++ library that happens to be linked into the backend, and — as §17.5 will show with minidumps — it can be driven entirely outside a running server. That single architectural fact dictates everything in this section. An optimizer that cannot call RelationGetDescr() must have the database described to it; an optimizer that does not share PostgreSQL’s Path structures must score plans with its own arithmetic. So ORCA grows two organs the PostgreSQL planner never needed: a metadata layer that ships catalog facts across the boundary as self-contained objects, and a cost model that lives in its own library, libgpdbcost, with its own units. Both are visible from SQL, and both are where most surprises about ORCA actually come from.

17.4.1 The provider seam

The seam is an abstract interface, IMDProvider, with exactly one interesting method: given a metadata id, hand back a metadata object. Everything ORCA knows about your database arrives through it. In a normal query the implementation is CMDProviderRelcache (src/backend/gpopt/relcache/CMDProviderRelcache.cpp), and it is almost comically thin — it is a forwarding stub. The offline implementation, CMDProviderMemory, reads the same objects out of a minidump file instead; that is the whole trick behind replaying an optimization without a server, and §17.5 takes it apart. COptTasks::OptimizeTask instantiates the relcache provider at src/backend/gpopt/utils/COptTasks.cpp:943 and hands it to the accessor at :948.

src/backend/gpopt/relcache/CMDProviderRelcache.cpp:39

// return the requested metadata object
IMDCacheObject *
CMDProviderRelcache::GetMDObj(CMemoryPool *mp, CMDAccessor *md_accessor,
                              IMDId *mdid, IMDCacheObject::Emdtype mdtype) const
{
    IMDCacheObject *md_obj =
        CTranslatorRelcacheToDXL::RetrieveObject(mp, md_accessor, mdid, mdtype);
    GPOS_ASSERT(nullptr != md_obj);
    return md_obj;
}

The real work is one file down, in src/backend/gpopt/translate/CTranslatorRelcacheToDXL.cpp (3208 lines). RetrieveObject (:93) switches on the kind of the metadata id and dispatches: EmdidRel → RetrieveRel (:479), EmdidColStats → RetrieveColStats (:1878), EmdidRelStats → RetrieveRelStats (:1822), EmdidInd → RetrieveIndex, EmdidExtStats → RetrieveExtStats (:331), and a plain EmdidGeneral id is resolved further by requested type into RetrieveType / RetrieveScOp / RetrieveAgg / RetrieveFunc (:172). The id itself is a string, serialized as MdidType.Oid.Major.Minor (libnaucrates/src/md/CMDIdGPDB.cpp:192), with the type codes enumerated in libnaucrates/include/naucrates/md/IMDId.h:69 — 0 general, 1 column stats, 2 relation stats, 6 relation, 7 index, 10 extended-stats info. So 6.34537.1.0 means the relation with OID 34537, and 1.34537.1.0.2 means the statistics of its third column. That string is also the MD cache key (CMDKey).

It is worth seeing what one of these objects actually looks like, because it is not an abstraction — it is a concrete, serializable record. The easiest window is a minidump (§17.5): with optimizer_minidump = always the coordinator writes every optimization, metadata included, to <datadir>/minidumps/. Here is the IMDRelation for a two-column-indexed-free demo fact table, reflowed for the page but otherwise verbatim.

An IMDRelation as it is actually serialized. Nothing in it is a pointer into PostgreSQL.

CREATE TABLE m174_fact (id int, dim_id int, region text, amt numeric)
  DISTRIBUTED BY (id);
INSERT INTO m174_fact SELECT g, g % 500, 'r' || (g % 8), g * 1.5
  FROM generate_series(1,200000) g;
ANALYZE m174_fact;

SET optimizer_minidump = always;
EXPLAIN SELECT count(*) FROM m174_fact f JOIN m174_dim d ON f.dim_id = d.dim_id
  WHERE f.region = 'r3';
-- then read <datadir>/minidumps/Minidump_20260814_003615_6175_2.mdp

<dxl:Relation Mdid="6.34537.1.0" Name="m174_fact" IsTemporary="false"
              Rows="200000.000000" StorageType="Heap"
              DistributionPolicy="Hash" DistributionColumns="0" Keys="10,4">
  <dxl:Columns>
    <dxl:Column Name="id"     Attno="1" Mdid="0.23.1.0"   Nullable="true" ColWidth="4"/>
    <dxl:Column Name="dim_id" Attno="2" Mdid="0.23.1.0"   Nullable="true" ColWidth="4"/>
    <dxl:Column Name="region" Attno="3" Mdid="0.25.1.0"   Nullable="true" ColWidth="3"/>
    <dxl:Column Name="amt"    Attno="4" Mdid="0.1700.1.0" Nullable="true" ColWidth="7"/>
    <dxl:Column Name="ctid"   Attno="-1" Mdid="0.27.1.0" Nullable="false" ColWidth="6"/>
    …
  </dxl:Columns>
  <dxl:IndexInfoList/>
  <dxl:CheckConstraints/>
  <dxl:DistrOpfamilies><dxl:DistrOpfamily Mdid="0.1977.1.0"/></dxl:DistrOpfamilies>
</dxl:Relation>

Look at the last line. The distribution policy travels through the metadata layer too. RetrieveRel reads GpPolicy off the relcache entry (:535), maps POLICYTYPE_PARTITIONED+nattrs>0 to EreldistrHash, nattrs==0 to EreldistrRandom, POLICYTYPE_REPLICATED to EreldistrReplicated and POLICYTYPE_ENTRY to EreldistrMasterOnly (GetRelDistribution, :737), records the distribution column positions, and — crucially — resolves each distribution opclass to its opfamily (RetrieveRelDistributionOpFamilies, :854). That is how gp_distribution_policy.distclass = 10020 (int4_ops) becomes Mdid="0.1977.1.0" (integer_ops) above. ORCA’s engine has no concept of a segment or a hash function; what it has is an opfamily identity it can compare, which is exactly enough to decide that two tables are co-located and no Motion is needed. This is the ORCA-side counterpart of CdbPathLocus from §16.1 — same information, arriving by a completely different route.

The same join, twice. The only thing that changed is which column the fact table is distributed on — and that fact reached ORCA as an opfamily mdid.

SELECT localoid::regclass AS tbl, policytype, distkey, distclass
  FROM gp_distribution_policy WHERE localoid::regclass::text LIKE 'm174%';

-- m174_byd is the same 200k rows, DISTRIBUTED BY (dim_id)
EXPLAIN (COSTS OFF) SELECT count(*) FROM m174_byd  b JOIN m174_dim d ON b.dim_id = d.dim_id;
EXPLAIN (COSTS OFF) SELECT count(*) FROM m174_fact f JOIN m174_dim d ON f.dim_id = d.dim_id;

    tbl     | policytype | distkey | distclass
------------+------------+---------+-----------
 m174_fact  | p          | 1       | 10020
 m174_dim   | p          | 1       | 10020
 m174_byd   | p          | 2       | 10020

 Finalize Aggregate                              -- m174_byd: co-located
   ->  Gather Motion 3:1  (slice1; segments: 3)
         ->  Partial Aggregate
               ->  Hash Join
                     Hash Cond: (b.dim_id = d.dim_id)
                     ->  Seq Scan on m174_byd b
                     ->  Hash
                           ->  Seq Scan on m174_dim d      -- no Motion at all

 Finalize Aggregate                              -- m174_fact: not co-located
   ->  Gather Motion 3:1  (slice1; segments: 3)
         ->  Partial Aggregate
               ->  Hash Join
                     Hash Cond: (f.dim_id = d.dim_id)
                     ->  Seq Scan on m174_fact f
                     ->  Hash
                           ->  Broadcast Motion 3:3  (slice2; segments: 3)
                                 ->  Seq Scan on m174_dim d

17.4.2 CMDAccessor and the MD cache

CMDAccessor (libgpopt/src/mdcache/CMDAccessor.cpp, 1331 lines) is the gate every metadata request passes through, and GetImdObj (:477) is the whole story: a three-level lookup. First the accessor’s own hashtable, private to this one optimization. On a miss, the process-wide CMDCache. On a miss there, the provider — which is the only path that ever reaches a Relation. Whatever comes back is inserted into the MD cache, pinned, and then also parked in the local hashtable with a deletion lock, so that for the rest of the optimization every RetrieveRel(mdid) is a hashtable probe returning a stable const pointer. That stability matters: the engine of §17.2 asks for the same relation thousands of times while enumerating alternatives.

src/backend/gporca/libgpopt/src/mdcache/CMDAccessor.cpp:499

if (nullptr == pimdobj)
{
    // object not in local hashtable, try lookup in the MD cache
    IMDProvider *pmdp = Pmdp(mdid->Sysid());
    CMDKey mdkey(mdid);
    a_pmdcacc = GPOS_NEW(m_mp) CacheAccessorMD(m_pcache);
    a_pmdcacc->Lookup(&mdkey);
    IMDCacheObject *pmdobjNew = a_pmdcacc->Val();
    if (nullptr == pmdobjNew)
    {
        // object not found in MD cache: retrieve it from MD provider
        pmdobjNew = pmdp->GetMDObj(mp, this, mdidCopy, mdtype);
        …
        IMDCacheObject *pmdobjInserted = a_pmdcacc->Insert(a_pmdkeyCache.Value(), pmdobjNew);

The MD cache is per backend and persists across queries — that is the point of it. optimizer_metadata_caching (on here) decides whether it survives at all; if you turn it off, OptimizeTask calls CMDCache::Shutdown() on the way out (COptTasks.cpp:1065). optimizer_mdcache_size (16384 kB here) is its quota, re-applied on every optimization. You can watch the cache work directly, because CMDAccessor’s destructor prints its accumulated fetch and lookup timers when optimizer_print_optimization_stats is on (CMDAccessor.cpp:381) — this build is --enable-cassert, which is why ORCA’s developer print GUCs emit at all.

Same query, twice, same session. The second optimization fetches nothing from the catalog.

SET client_min_messages = log;
SET optimizer_print_optimization_stats = on;
EXPLAIN (COSTS OFF) SELECT count(*) FROM m174_fact f JOIN m174_dim d
  ON f.dim_id = d.dim_id WHERE f.region = 'r3';
-- ... and again, verbatim

LOG:  2026-08-14 00:31:41:333326 CST,THD000,TRACE,"timer:
[OPT]: Query To DXL Translation Time: 7ms",
…
2026-08-14 00:31:41:364755 CST,THD000,TRACE,"
Memory consumption after exploration Engine: [3.003151] MB, MD Cache: [0.335472] MB, Total: [13.877274] MB",
…
2026-08-14 00:31:41:370394 CST,THD000,TRACE,"[OPT]: Total metadata fetch time: 16.494000ms
[OPT]: Total metadata lookup time (including fetch time): 20.890000ms
",

-- second run
2026-08-14 00:31:41:411058 CST,THD000,TRACE,"[OPT]: Total metadata fetch time: 0.000000ms
[OPT]: Total metadata lookup time (including fetch time): 2.794000ms
",

So how does a stale entry ever get thrown out? Not by object. ORCA has no fine-grained metadata invalidation at all. Instead src/backend/gpopt/gpdbwrappers.cpp registers syscache callbacks for the nine catalogs whose contents can end up in a metadata object — pg_aggregate, pg_amop, pg_cast, pg_constraint, pg_operator, pg_opfamily, pg_statistic, pg_type, pg_proc — plus one blanket relcache callback (:2333), and each of them does nothing but mdcache_invalidation_counter++ (:2319). gpdb::MDCacheNeedsReset() (:2399) compares that counter against the value it last saw, and OptimizeTask reacts by nuking the entire cache. It is deliberately coarse: CMDCache::Reset() is literally Shutdown(); Init(); (libgpopt/src/mdcache/CMDCache.cpp:121).

src/backend/gpopt/utils/COptTasks.cpp:902

bool reset_mdcache = gpdb::MDCacheNeedsReset();

// initialize metadata cache, or purge if needed, or change size if requested
if (!CMDCache::FInitialized())
{
    CMDCache::Init();
    CMDCache::SetCacheQuota(optimizer_mdcache_size * 1024L);
}
else if (reset_mdcache)
{
    CMDCache::Reset();
    CMDCache::SetCacheQuota(optimizer_mdcache_size * 1024L);
}

One unrelated DDL statement, and the whole MD cache is gone: the next optimization pays for the catalog again.

SET client_min_messages = log;
SET optimizer_print_optimization_stats = on;
EXPLAIN (COSTS OFF) SELECT count(*) FROM m174_fact f JOIN m174_dim d ON f.dim_id = d.dim_id WHERE f.region = 'r3';
EXPLAIN (COSTS OFF) SELECT count(*) FROM m174_fact f JOIN m174_dim d ON f.dim_id = d.dim_id WHERE f.region = 'r3';
CREATE TEMP TABLE m174_ddl(x int);     -- touches nothing in the query above
EXPLAIN (COSTS OFF) SELECT count(*) FROM m174_fact f JOIN m174_dim d ON f.dim_id = d.dim_id WHERE f.region = 'r3';
EXPLAIN (COSTS OFF) SELECT count(*) FROM m174_fact f JOIN m174_dim d ON f.dim_id = d.dim_id WHERE f.region = 'r3';

[OPT]: Total metadata fetch time: 15.175000ms      -- cold
[OPT]: Total metadata fetch time: 0.000000ms       -- warm
LOG:  statement: CREATE TEMP TABLE m174_ddl(x int);
[OPT]: Total metadata fetch time: 2.668000ms      -- cache was Reset()
[OPT]: Total metadata fetch time: 0.000000ms       -- warm again

This is the same invalidation event that drives PostgreSQL’s own relcache and catcache flushes (§8.5) — ORCA simply subscribes to it and reacts with the bluntest possible policy. The consequence is practical: on a workload that does DDL between queries, ORCA’s metadata cache buys you nothing, and the 15 ms of catalog work is paid per query. Note also that CMDCache is per backend, so a fresh connection always starts cold.

17.4.3 Statistics: the same catalog, different arithmetic

ORCA reads exactly the statistics Chapter 14 described, and nothing more. RetrieveColStats (CTranslatorRelcacheToDXL.cpp:1878) opens the pg_statistic tuple and pulls out stanullfrac, stawidth, stadistinct (converting the negative fraction form to an absolute NDV at :1956), the MCV slot (:1973) and the histogram slot (:2024), converting both into a single uniform array of DXL buckets — an MCV becomes a singleton bucket with DistinctValues="1". It also reads Cloudberry’s own STATISTIC_KIND_NDV_BY_SEGMENTS slot (:2095), the cross-segment NDV of §14.1, and the extended statistics of §14.2: RetrieveExtStats (:331) converts pg_statistic_ext_data’s functional dependencies into CMDDependency objects and its n-distinct items into CMDNDistinct. Same inputs as the planner, then, down to the extension. What differs is everything after.

An IMDColStats. Eight singleton buckets — the MCVs of a uniformly-distributed text column — plus the Cloudberry per-segment NDV.

-- from the same minidump as above
grep -o '<dxl:ColumnStatistics [^>]*>' Minidump_20260814_003615_6175_2.mdp

<dxl:ColumnStatistics Mdid="1.34537.1.0.2" Name="region" Width="3.000000"
   NullFreq="0.000000" NdvRemain="0.000000" FreqRemain="0.000000"
   ColStatsMissing="false" NdvBySeg="24.000000">
  <dxl:StatsBucket Frequency="0.12756666541099548" DistinctValues="1.00000000000000000">
    <dxl:LowerBound Closed="true" TypeMdid="0.25.1.0" Value="GAAAAHI3" LintValue="1029747886"/>
    <dxl:UpperBound Closed="true" TypeMdid="0.25.1.0" Value="GAAAAHI3" LintValue="1029747886"/>
  </dxl:StatsBucket>
  <dxl:StatsBucket Frequency="0.12590000033378601" DistinctValues="1.00000000000000000">
  …  (8 buckets, one per distinct value of region)
</dxl:ColumnStatistics>

Cardinality derivation happens entirely inside ORCA, in libnaucrates/src/statistics/ — CStatistics, CHistogram, CFilterStatsProcessor, CJoinStatsProcessor, one processor class per logical operator. And it has its own damping. For a conjunction of filters, CScaleFactorUtils::CalcScaleFactorCumulativeConj (:497) sorts the per-column scale factors in descending order and then discounts the k-th one by optimizer_damping_factor_filter raised to the k-th power — the first predicate is undamped, the second is multiplied by 0.75², the third by 0.75³, and so on. Because a scale factor is a divisor, damping it down pushes the estimate up: it is a deliberate hedge against correlated columns. §14.3 covered the planner’s version of the same instinct; this is a separate implementation with a separate GUC.

src/backend/gporca/libnaucrates/src/statistics/CScaleFactorUtils.cpp:511

for (ULONG ul = 0; ul < num_cols; ul++)
{
    // apply damping factor
    CDouble local_scale_factor = *(*scale_factors)[ul];
    scale_factor =
        scale_factor * std::max(CStatistics::MinRows.Get(),
                                (local_scale_factor *
                                 CScaleFactorUtils::DampedFilterScaleFactor(
                                     stats_config, ul + 1))
                                    .Get());
}

Three genuinely independent predicates, one catalog, three answers. The true row count is 6200.

-- region = 'r3' is 1 in 8; dim_id < 250 is 1 in 2; id < 100000 is 1 in 2
EXPLAIN SELECT * FROM m174_fact WHERE region='r3' AND dim_id<250 AND id<100000;
SET optimizer = off;                              -- PostgreSQL planner
EXPLAIN SELECT * FROM m174_fact WHERE region='r3' AND dim_id<250 AND id<100000;
RESET optimizer;
SET optimizer_damping_factor_filter = 1.0;        -- ORCA, damping disabled
EXPLAIN SELECT * FROM m174_fact WHERE region='r3' AND dim_id<250 AND id<100000;
SELECT count(*) FROM m174_fact WHERE region='r3' AND dim_id<250 AND id<100000;

ORCA, default damping 0.75
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..441.23 rows=22338 width=18)
   ->  Seq Scan on m174_fact  (cost=0.00..439.74 rows=7446 width=18)
 Optimizer: GPORCA

PostgreSQL planner
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..1460.97 rows=14123 width=18)
   ->  Seq Scan on m174_fact  (cost=0.00..1272.67 rows=4708 width=18)
 Optimizer: Postgres query optimizer

ORCA, optimizer_damping_factor_filter = 1.0
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..439.98 rows=6315 width=18)
   ->  Seq Scan on m174_fact  (cost=0.00..439.56 rows=2105 width=18)

 count
-------
  6200

Two details in that output. First, note the per-segment convention: 7446 × 3 = 22338 — below a Gather, ORCA’s rows= is what one segment produces, exactly as in §15.3. Second, ORCA’s undamped estimate (6315) is nearly perfect here and its damped one is 3.6× high, because these three columns really are independent — damping is insurance you pay for whether or not you need it. It is not a bug, and the fix is not to turn it off globally. §14.2 showed the mirror-image case, where ORCA’s hedging made it the more accurate of the two. Related trap: optimizer_damping_factor_join = 0 does not mean “no join damping” — a zero there selects an entirely different algorithm (a square-root scheme over predicates on the same table pair), while any value above zero re-enables the legacy exponential one whose default of 0.01 the source itself describes as severely overestimating non-correlated columns (CScaleFactorUtils.cpp:247).

17.4.4 Missing statistics

When a column has no usable pg_statistic row, RetrieveColStats returns a dummy CDXLColStats (:1938) whose histogram reports IsColStatsMissing(). CMDAccessor::RecordColumnStats notices that (CMDAccessor.cpp:1034) and, if the optimizer_print_missing_stats GUC is on — it maps to the traceflag EopttracePrintColsWithMissingStats in CConfigParamMapping.cpp:160 — registers the column with CStatisticsConfig::AddMissingStatsColumn. At the end of optimization COptTasks::OptimizeTask drains that set (:1025) and PrintMissingStatsWarning (:1083) emits one LOG line per column and one NOTICE per table with an actionable HINT. That NOTICE is one of the most-seen messages in Cloudberry, and now you know it is produced after the plan was chosen, by a set collected during statistics derivation.

The NOTICE users see — and, first, the case where it stays silent.

-- (a) nobody has ever ANALYZEd this table: pg_class.reltuples is still -1
CREATE TABLE m174_nostats (a int, b text) DISTRIBUTED BY (a);
INSERT INTO m174_nostats SELECT g, 'x'||g FROM generate_series(1,50000) g;
EXPLAIN SELECT * FROM m174_nostats WHERE a < 100 AND b = 'x5';

-- (b) now reltuples is real, but column c has no pg_statistic row of its own
ANALYZE m174_nostats (a);
ALTER TABLE m174_nostats ADD COLUMN c int;
UPDATE m174_nostats SET c = a % 100;
SET client_min_messages = log;
EXPLAIN SELECT * FROM m174_nostats WHERE c = 7;

-- (a) 50000 rows in the table, no complaint whatsoever
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=12)
   ->  Seq Scan on m174_nostats  (cost=0.00..431.00 rows=1 width=12)
         Filter: ((a < 100) AND (b = 'x5'::text))
 Optimizer: GPORCA

-- (b)
LOG:  Missing statistics for column: m174_nostats.c
NOTICE:  One or more columns in the following table(s) do not have statistics: m174_nostats
HINT:  For non-partitioned tables, run analyze <table_name>(<column_list>). For
partitioned tables, run analyze rootpartition <table_name>(<column_list>). See log
for columns missing statistics.
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..433.21 rows=20000 width=14)
   ->  Seq Scan on m174_nostats  (cost=0.00..432.16 rows=6667 width=14)
         Filter: (c = 7)

Case (a) is a real, documented hole, and there is a CBDB_MERGE_FIXME sitting on it at COptTasks.cpp:1023: “empty table after analyze still have no stats cause CBDB can’t tell empty or no stats”. The mechanism is precise. RetrieveRelStats sets relation_empty = true when reltuples is either 0 or -1 (CTranslatorRelcacheToDXL.cpp:1855), and RecordColumnStats begins with !isEmptyTable — so for a genuinely empty table the silence is correct, but a never-analyzed table (PostgreSQL 16 leaves reltuples = -1 until the first ANALYZE or VACUUM) is misclassified as empty, and you get both a nonsense rows=1 and no warning that anything is wrong. The one estimate you most want to be told about is the one that is suppressed. Case (b), where reltuples is real, behaves as designed: rows=20000 out of 50000 is ORCA’s fixed default equality selectivity, and the NOTICE fires.

17.4.5 The cost model as a separate library

ORCA’s costing lives in its own library, libgpdbcost — CCostModelGPDB.cpp is 2760 lines and CCostModelParamsGPDB.cpp holds 56 named parameters, each with a calibrated value and a lower/upper bound (CCostModelParamsGPDB.h:111 sizes the array by EcpSentinel). CCostModelGPDB::Cost (:2585) is a switch over physical operator ids dispatching to CostScan, CostFilter, CostHashJoin, CostNLJoin, CostMotion, CostHashAgg, CostSort and so on. optimizer_cost_model picks which parameter family is in force — guc_gp.c:524 accepts exactly legacy, calibrated (the default, and what this cluster runs) and experimental, and the choice reaches ORCA as the traceflags EopttraceLegacyCostModel / EopttraceExperimentalCostModel (CConfigParamMapping.cpp:553). A handful of GUCs then override individual parameters in COptTasks::SetCostModelParams (:440), called from GetCostModel (:492), which constructs the model with the segment count.

The best function to read is CostMotion, because it is the piece with no PostgreSQL analogue at all — ORCA prices data movement as a first-class operator rather than as a wrapper decision. Note UlHosts(), which is simply the segment count the model was built with (CCostModelGPDB.h:247).

src/backend/gporca/libgpdbcost/src/CCostModelGPDB.cpp:1486

if (COperator::EopPhysicalMotionBroadcast == op_id)
{
    // broadcast cost is amplified by the number of segments
    dSendCostUnit = …PcpLookup(EcpBroadcastSendCostUnit)->Get();
    dRecvCostUnit = …PcpLookup(EcpBroadcastRecvCostUnit)->Get();
    recvCost = num_rows_outer * dWidthOuter * pcmgpdb->UlHosts() * dRecvCostUnit;
}
else if (COperator::EopPhysicalMotionHashDistribute == op_id || …)
{
    dSendCostUnit = …PcpLookup(EcpRedistributeSendCostUnit)->Get();
    dRecvCostUnit = …PcpLookup(EcpRedistributeRecvCostUnit)->Get();
    recvCost = pci->Rows() * pci->Width() * dRecvCostUnit;   // no UlHosts()
}
costLocal = CCost(pci->NumRebinds() *
                  (num_rows_outer * dWidthOuter * dSendCostUnit + recvCost));

Three terms, then: rows × width × a send unit, plus a receive cost, all multiplied by the rebind count (how many times this subtree is re-executed under a nested loop). The asymmetry between the branches is the distributed cost model. A redistribute pays for its own rows once; a broadcast pays UlHosts() times, because every segment receives every row — and on top of that the per-byte constants are themselves lopsided: EcpBroadcastSendCostUnit is 4.965e-05 against EcpRedistributeSendCostUnit’s 2.33e-06, a factor of 21 (CCostModelParamsGPDB.cpp:72, :78). Above optimizer_penalize_broadcast_threshold rows the model stops modelling and simply asserts a verdict: costLocal = CCost(100000000000000.0) (:1571). Compare §16.2’s cdbpath_cost_motion, which reaches broadly the same conclusions from the planner side with a fraction of the machinery — and which, unlike this, is not a pluggable library.

Knob (live value here)	What it actually biases
optimizer_cost_model = calibrated	Which of the three parameter families is loaded: legacy / calibrated / experimental. Delivered as a traceflag, not a parameter.
optimizer_segments = 0	The segment count the cost model assumes. 0 means use gpdb::GetGPSegmentCount(). Does not change the plan’s actual gang size.
optimizer_nestloop_factor = 1024	Multiplies the final nested-loop cost (CostNLJoin, CCostModelGPDB.cpp:1444). The library’s own default is 1.0 — this GUC is a 1000× thumb on the scale against NL joins.
optimizer_sort_factor = 1	Scales EcpSortTupWidthCostUnit and its bounds. Only applied when != 1.0.
optimizer_skew_factor = 0	Hash-join skew penalty. 0 caps the skew multiplier at EcpPenalizeHJSkewUpperLimit (10.0); a value f > 0 feeds pow(1.0307, f-1), topping out near 20 at f = 100 (:1129).
optimizer_penalize_broadcast_threshold = 100000	Rows above which a Broadcast Motion is priced at 1e14 instead of being costed. 0 disables the penalty entirely.
optimizer_damping_factor_filter = 0.75	Base of the exponential discount applied to the k-th conjunctive filter predicate.
optimizer_damping_factor_groupby = 0.75	Same, for grouping columns — with exponent k+1 (CScaleFactorUtils.cpp:361).
optimizer_damping_factor_join = 0	0 selects the square-root algorithm; any value > 0 selects the legacy exponential one.
optimizer_cost_threshold = 0	Not a cost-model parameter: it bounds plan sampling relative to the best plan’s cost, 0.0 meaning unbounded (guc_gp.c:4810). Belongs to §17.2’s enumerator.
optimizer_metadata_caching = on, optimizer_mdcache_size = 16384 kB	MD cache lifetime and quota, from §17.4.2.

The nested-loop factor, demonstrated. Identical plan, cost scaled by almost exactly 1024.

-- a non-equi join, so a Nested Loop is the only option and the shape cannot change
EXPLAIN SELECT count(*) FROM m174_dim a, m174_dim b WHERE a.dim_id < b.dim_id;
SET optimizer_nestloop_factor = 1;
EXPLAIN SELECT count(*) FROM m174_dim a, m174_dim b WHERE a.dim_id < b.dim_id;

optimizer_nestloop_factor = 1024 (default)
 Finalize Aggregate  (cost=0.00..1330225.06 rows=1 width=8)
   ->  Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..1330225.06 rows=1 width=8)
         ->  Partial Aggregate  (cost=0.00..1330225.06 rows=1 width=8)
               ->  Nested Loop  (cost=0.00..1330225.06 rows=27778 width=1)
                     Join Filter: (a.dim_id < b.dim_id)
                     ->  Seq Scan on m174_dim a  (cost=0.00..431.00 rows=167 width=4)
                     ->  Materialize  (cost=0.00..431.04 rows=500 width=4)
                           ->  Broadcast Motion 3:3  (slice2; segments: 3)  (cost=0.00..431.04 rows=500 width=4)

optimizer_nestloop_factor = 1
               ->  Nested Loop  (cost=0.00..1299.05 rows=27778 width=1)
                     Join Filter: (a.dim_id < b.dim_id)
                     ->  Seq Scan on m174_dim a  (cost=0.00..431.00 rows=167 width=4)
                     ->  Materialize  (cost=0.00..431.04 rows=500 width=4)

Lying to the cost model about the segment count changes the plan — but not the gang. Both Motions still say 3:3.

EXPLAIN SELECT count(*) FROM m174_fact f JOIN m174_dim d ON f.dim_id = d.dim_id;
SET optimizer_segments = 100;
EXPLAIN SELECT count(*) FROM m174_fact f JOIN m174_dim d ON f.dim_id = d.dim_id;

optimizer_segments = 0  (model uses the real count, 3)
               ->  Hash Join  (cost=0.00..876.52 rows=66667 width=1)
                     Hash Cond: (f.dim_id = d.dim_id)
                     ->  Seq Scan on m174_fact f  (cost=0.00..432.91 rows=66667 width=4)
                     ->  Hash  (cost=431.04..431.04 rows=500 width=4)
                           ->  Broadcast Motion 3:3  (slice2; segments: 3)  (cost=0.00..431.04 rows=500 width=4)
                                 ->  Seq Scan on m174_dim d  (cost=0.00..431.00 rows=167 width=4)

optimizer_segments = 100
               ->  Hash Join  (cost=0.00..862.46 rows=66667 width=1)
                     Hash Cond: (f.dim_id = d.dim_id)
                     ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..431.10 rows=66667 width=4)
                           Hash Key: f.dim_id
                           ->  Seq Scan on m174_fact f  (cost=0.00..431.06 rows=66667 width=4)
                     ->  Hash  (cost=431.00..431.00 rows=167 width=4)
                           ->  Seq Scan on m174_dim d  (cost=0.00..431.00 rows=167 width=4)

That last demo is the cleanest illustration of where the segment count enters. OptimizeTask reads the real count once (COptTasks.cpp:951) and passes it to COptimizer::PdxlnOptimize, while a separate num_segments_for_costing — optimizer_segments if non-zero, otherwise the real count (:952) — is what GetCostModel builds the model with. Told there are 100 segments, CostMotion’s UlHosts() amplification makes broadcasting 500 dimension rows look 33× worse than it is, and ORCA switches to redistributing the 200 000-row fact table instead. The plan it emits still runs on the three segments that exist. The knob is a cost-model microscope, not a topology setting.

17.4.6 The costs are not on the same scale

Which brings us to the single most common misreading of an ORCA plan. Because both optimizers print cost= in the same EXPLAIN column, people compare the numbers. They are not comparable — not by ratio, not by offset, not at all.

Same query, same statistics, same plan shape, same cluster. Read the costs, then read the times.

EXPLAIN ANALYZE SELECT count(*) FROM m174_fact f JOIN m174_dim d ON f.dim_id = d.dim_id;
SET optimizer = off;
EXPLAIN ANALYZE SELECT count(*) FROM m174_fact f JOIN m174_dim d ON f.dim_id = d.dim_id;

GPORCA
 Finalize Aggregate  (cost=0.00..876.52 rows=1 width=8) (actual time=47.199..47.207 rows=1 loops=1)
   ->  Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..876.52 …)
         ->  Partial Aggregate  (cost=0.00..876.52 …)
               ->  Hash Join  (cost=0.00..876.52 rows=66667 …) (actual time=6.059..39.638 rows=66711 loops=1)
                     ->  Seq Scan on m174_fact f  (cost=0.00..432.91 rows=66667 …)
                     ->  Hash  (cost=431.04..431.04 rows=500 …)
                           ->  Broadcast Motion 3:3  (cost=0.00..431.04 rows=500 …)
                                 ->  Seq Scan on m174_dim d  (cost=0.00..431.00 rows=167 …)
 Optimizer: GPORCA
 Execution Time: 68.492 ms

Postgres query optimizer
 Finalize Aggregate  (cost=1871.64..1871.65 rows=1 width=8) (actual time=35.595..35.598 rows=1 loops=1)
   ->  Gather Motion 3:1  (slice1; segments: 3)  (cost=1871.58..1871.63 rows=3 …)
         ->  Partial Aggregate  (cost=1871.58..1871.59 …)
               ->  Hash Join  (cost=15.58..1704.92 rows=66667 …) (actual time=3.933..31.640 rows=66711 loops=1)
                     ->  Seq Scan on m174_fact f  (cost=0.00..772.67 rows=66667 …)
                     ->  Hash  (cost=9.33..9.33 rows=500 …)
                           ->  Broadcast Motion 3:3  (cost=0.00..9.33 rows=500 …)
                                 ->  Seq Scan on m174_dim d  (cost=0.00..2.67 rows=167 …)
 Optimizer: Postgres query optimizer
 Execution Time: 36.390 ms

Identical plan shape; total cost 876.52 against 1871.65; and the same 167-row segment-local scan of m174_dim priced at 431.00 by ORCA and 2.67 by the planner — a factor of 161. The 431 is not an estimate of anything: it is DInitScanFacorVal = 431.0, a flat calibrated constant every table scan starts from (CCostModelParamsGPDB.cpp:34), which is why an ORCA scan of a tiny table looks expensive and an ORCA cost below 431 essentially does not exist. Notice too that every ORCA node reports startup cost 0.00: ORCA’s CCost is a single scalar, so the startup..total pair PostgreSQL uses to reason about LIMIT and about which side of a nested loop to put where (§15.3) simply has no second component to fill in. Across three repeats the measured times here were 50–63 ms with ORCA and 37–50 ms with the planner — overlapping, and bearing no relation whatsoever to the 2.1× cost gap. So: compare ORCA costs to other ORCA costs to understand why it chose one alternative over another, as §17.2’s memo dumps do; compare optimizers only by EXPLAIN ANALYZE. And when you file an ORCA bug, do not send the cost — send the minidump, which is where §17.5 goes next.