Steering ORCA: GUCs, Traceflags and Minidumps

There are dozens of optimizer GUCs and almost no documentation on when to reach for them. This is the working list.

Everything in this chapter so far has been machinery you watch. This section is the machinery you touch — and it opens with a genuinely awkward question. ORCA is a standalone C++ library. It links against libgpos, not against PostgreSQL’s GUC subsystem; it has no idea what a config_generic is, no SET command, no pg_settings. So when you type SET optimizer_enable_hashjoin = off, how does that reach a transformation rule buried three layers deep in libgpopt?

The answer is a single translation table, src/backend/gpopt/config/CConfigParamMapping.cpp, run once per query. It reads the C globals that back the GUCs and packs them into ORCA’s own currency: a CBitSet of traceflags — small integers in the 101000–104999 range that the engine tests with GPOS_FTRACE(). Some GUCs become one traceflag; others become a whole family of xform-disable bits. SetTraceflags installs the bitset for the duration of the optimization and ResetTraceflags puts the old values back. That is the entire bridge, and once you can see it, every ORCA knob in the manual stops being folklore.

17.6.1 One table, two mechanisms

CConfigParamMapping.cpp has two halves. The first is a static array, m_elements[] (:30), of {traceflag, &guc_variable, negate, description} quadruples — 60-odd rows, purely declarative. The negate column exists because ORCA’s flags are phrased as disablers (EopttraceDisableSort) while the GUCs are phrased as enablers (optimizer_enable_sort), so most rows flip the boolean on the way across. The second half is the imperative tail of PackConfigParamInBitset() (:351), a run of if (!some_guc) statements that set xform-disable bits instead: bit EopttraceDisableXformBase + xform_id, where the base is 102000 (traceflags.h:92) and the arithmetic is the GPOPT_DISABLE_XFORM_TF() macro (CXform.h:28).

src/backend/gpopt/config/CConfigParamMapping.cpp:99

// declarative half: one GUC pointer -> one traceflag  (m_elements[], :30)
	{EopttraceDisableMotions, &optimizer_enable_motions,
	 true,	// m_negate_param
	 GPOS_WSZ_LIT("Disable motion nodes in optimizer.")},
	...
	{EopttraceDisableInnerHashJoin, &optimizer_enable_hashjoin,
	 true,	// m_negate_param
	 GPOS_WSZ_LIT("Explore hash join alternatives")},

// imperative half: one GUC -> a whole set of xform-disable bits  (:437)
	if (!optimizer_enable_hashjoin)
	{
		CBitSet *hash_join_bitste = CXform::PbsHashJoinXforms(mp);
		traceflag_bitset->Union(hash_join_bitste);

optimizer_enable_hashjoin happens to use both mechanisms, and the reason is instructive. Outer, semi and anti joins each have their own dedicated hash-join xform, so PbsHashJoinXforms() (CXform.cpp:199) simply lists seven ids — including ExfLeftJoin2RightJoin, because right joins are only ever implemented as hash joins — and disables them wholesale. But inner join implementation is a single xform that emits both the hash and the nested-loop alternative, so it cannot be switched off as a unit. Instead it reads the traceflag itself:

src/backend/gporca/libgpopt/src/xforms/CXformImplementInnerJoin.cpp:83

	if (!GPOS_FTRACE(EopttraceDisableInnerHashJoin))
	{
		CXformUtils::ImplementHashJoin<CPhysicalInnerHashJoin>(pxfctxt, pxfres,
															   pexpr);
	}

	if ((GPOS_FTRACE(EopttraceForceComprehensiveJoinImplementation) ||
		 pxfres->Size() == 0) &&
		!GPOS_FTRACE(EopttraceDisableInnerNLJ))
	{
		CXformUtils::ImplementNLJoin<CPhysicalInnerNLJoin>(pxfctxt, pxfres,
														   pexpr);
	}

Notice the pxfres->Size() == 0 guard on the second block: by default ORCA does not even look at a nested loop when a hash join was produced. It is the absence of the hash alternative that lets the nested loop appear at all. Which brings out the sharpest contrast with Chapter 15. There, enable_hashjoin = off adds disable_cost to a path that still gets built and still competes (§15.3); the plan space is unchanged and a hash join can still win if nothing else exists. Here the alternative is never constructed. An optimizer_enable_* GUC removes a rule, and the plan space physically shrinks — which you can watch, because optimizer_enumerate_plans prints its size.

The two families are disjoint. enable_hashjoin is inert under ORCA; optimizer_enable_hashjoin removes rules, and the plan space shrinks from 4 alternatives to 3.

SET client_min_messages = log;
SET optimizer_enumerate_plans = on;

SET enable_hashjoin = off;             -- the Ch15 planner's knob
EXPLAIN (COSTS OFF) SELECT b.c, s.d
  FROM m176_big b JOIN m176_small s ON b.a = s.a;

SET optimizer_enable_hashjoin = off;   -- ORCA's knob
EXPLAIN (COSTS OFF) SELECT b.c, s.d
  FROM m176_big b JOIN m176_small s ON b.a = s.a;

-- with enable_hashjoin = off  (ORCA does not care)
LOG:  2026-08-14 00:40:11:749503 CST,THD000,TRACE,"[OPT]: Number of plan alternatives: 4
",
 Gather Motion 3:1  (slice1; segments: 3)
   ->  Hash Join
         Hash Cond: (b.a = s.a)
         ->  Seq Scan on m176_big b
         ->  Hash
               ->  Seq Scan on m176_small s
 Optimizer: GPORCA

-- with optimizer_enable_hashjoin = off
LOG:  … TRACE,"[OPT]: Number of plan alternatives: 3
",
 Gather Motion 3:1  (slice1; segments: 3)
   ->  Nested Loop
         Join Filter: true
         ->  Seq Scan on m176_small s
         ->  Index Scan using m176_big_a_idx on m176_big b
               Index Cond: (a = s.a)
 Optimizer: GPORCA

Taken to its conclusion: with Motion removed, a distributed query has no plan at all. Compare §16.2 — Motion is an enforcer of a required distribution property, not an optional decoration.

SET optimizer_trace_fallback = on;
SET optimizer_enable_motions = off;
EXPLAIN (COSTS OFF) SELECT b.c, s.d
  FROM m176_big b JOIN m176_small s ON b.a = s.a;

INFO:  GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL:  Falling back to Postgres-based planner because no plan has been computed
         for required properties in GPORCA
 Gather Motion 3:1  (slice1; segments: 3)
   ->  Hash Join
         Hash Cond: (b.a = s.a)
         ->  Seq Scan on m176_big b
         ->  Hash
               ->  Seq Scan on m176_small s
 Optimizer: Postgres query optimizer

17.6.2 The optimizer_enable_* family, and where it hides

There are three tiers in this family, and telling them apart saves a lot of confusion. Tier one becomes a traceflag and is read at some decision point inside the engine. Tier two becomes xform-disable bits and is read once, early, in CGroupExpression::Transform (CGroupExpression.cpp:781), which returns before the rule ever runs. Tier three never reaches ORCA’s bitset at all: it is read directly in the Query→DXL translator, where a false raises an unsupported feature exception and hands the query to the PostgreSQL planner via §17.1’s fallback path.

GUC (default on this cluster)	Reaches ORCA as	Effect
optimizer_enable_hashjoin — on, hidden	traceflag EopttraceDisableInnerHashJoin (103045) and the PbsHashJoinXforms() bits	removes the hash-join alternative for inner joins and for every outer/semi/anti variant
optimizer_enable_nljoin, optimizer_enable_indexjoin — on, hidden	PbsNLJoinXforms() / PbsIndexJoinXforms() bits, plus traceflag EopttraceDisableInnerNLJ	removes nested-loop and index-nested-loop implementations
optimizer_enable_tablescan — on, hidden	bits on ExfGet2TableScan, ExfGet2ParallelTableScan	forces an index path, or no plan at all
optimizer_enable_indexscan, optimizer_enable_indexonlyscan, optimizer_enable_bitmapscan — on, hidden	bits on ExfIndexGet2IndexScan, ExfIndexOnlyGet2IndexOnlyScan, PbsBitmapIndexXforms()	the scan-method family; the dynamic* variants do the same for partitioned tables
optimizer_enable_hashagg, optimizer_enable_groupagg — on, hidden	bits on ExfGbAgg2HashAgg (+Dedup) / ExfGbAgg2StreamAgg (+Dedup)	aggregate strategy — compare the two-stage story in §16.4
optimizer_enable_mergejoin — on, hidden	a single bit on ExfImplementFullOuterMergeJoin	a much smaller knob than the name suggests: ORCA only merge-joins FULL OUTER JOIN
optimizer_enable_associativity — off, visible	bit on ExfJoinAssociativity	off by default for a reason; see below
optimizer_enable_right_outer_join — on, visible	bits on ExfLeftJoin2RightJoin, ExfRightOuterJoin2HashJoin	removes right-outer plan shapes
optimizer_enable_motions and the _motion_broadcast / _motion_gather / _motion_redistribute trio — on, hidden	traceflags EopttraceDisableMotions, …MotionBroadcast, …MotionGather, …MotionHashDistribute / …MotionRandom / …MotionRountedDistribute	removes Motion enforcers (§16.2); on a distributed query this usually empties the plan space entirely
optimizer_enable_sort, optimizer_enable_materialize — on, hidden	traceflags EopttraceDisableSort / EopttraceDisableSpool	removes the Sort and Spool enforcers
optimizer_enable_replicated_table, optimizer_enable_foreign_table, optimizer_enable_query_parameter — on, visible	nothing — read in the translator (CTranslatorQueryToDXL.cpp:3488, CTranslatorRelcacheToDXL.cpp:2628, CTranslatorScalarToDXL.cpp:325)	vetoes the query outright: ORCA raises unsupported feature and §17.1’s fallback runs

Most of the interesting knobs carry GUC_NO_SHOW_ALL, so pg_settings and SHOW ALL skip them (guc_funcs.c:555, :1015) — but SHOW, SET and pg_settings_get_flags() all work normally.

SELECT count(*) AS in_pg_settings
  FROM pg_settings WHERE name LIKE 'optimizer_print%';

SELECT g, current_setting(g) AS value, pg_settings_get_flags(g) AS flags
  FROM (VALUES ('optimizer'), ('optimizer_minidump'),
               ('optimizer_enable_hashjoin'),
               ('optimizer_print_memo_after_optimization'),
               ('optimizer_segments')) v(g);

 in_pg_settings
----------------
              0

                    g                    |  value  |            flags
-----------------------------------------+---------+-----------------------------
 optimizer                               | on      | {}
 optimizer_minidump                      | onerror | {}
 optimizer_enable_hashjoin               | on      | {NO_SHOW_ALL,NOT_IN_SAMPLE}
 optimizer_print_memo_after_optimization | off     | {NO_SHOW_ALL,NOT_IN_SAMPLE}
 optimizer_segments                      | 0       | {NO_SHOW_ALL,NOT_IN_SAMPLE}

select name, setting from pg_settings where name like 'optimizer%' returns 44 rows on this cluster, and that is not the whole family — the flag is GUC_NO_SHOW_ALL (guc_gp.c:2350 for optimizer_enable_hashjoin, and the same on every optimizer_print_* entry), which show_all_settings() honours by skipping the row. The rationale is that these are developer options; the consequence is that grepping pg_settings will convince you a knob does not exist. It does. Also worth stating plainly, because it comes up often: there is no crossover between the two GUC families. The planner’s enable_hashjoin, enable_nestloop, enable_seqscan and friends appear nowhere in CConfigParamMapping.cpp, and nowhere in src/backend/gpopt/ or libgpopt at all. Under optimizer=on they are dead weight, as the previous demo showed.

Two of the three tiers, side by side. Enabling join associativity more than doubles the plan space (86 → 183) and finds exactly the same plan; a tier-three GUC removes ORCA from the picture and names itself in the DETAIL.

SET client_min_messages = log;
SET optimizer_enumerate_plans = on;
SET optimizer_trace_fallback = on;

-- three-way join, associativity off (the default)
EXPLAIN (COSTS OFF) SELECT count(*) FROM m176_big b
  JOIN m176_small s ON b.a = s.a JOIN m176_other o ON b.b = o.k;
SET optimizer_enable_associativity = on;
EXPLAIN (COSTS OFF) SELECT count(*) FROM m176_big b
  JOIN m176_small s ON b.a = s.a JOIN m176_other o ON b.b = o.k;

-- tier three: a translator-level veto
RESET optimizer_enable_associativity;
SET plan_cache_mode = force_generic_plan;
SET optimizer_enable_query_parameter = off;
PREPARE p(int) AS SELECT count(*) FROM m176_big WHERE b = $1;
EXPLAIN (COSTS OFF) EXECUTE p(3);

-- associativity off
LOG:  … TRACE,"[OPT]: Number of plan alternatives: 86
",
-- associativity on: same winning plan, twice the search
LOG:  … TRACE,"[OPT]: Number of plan alternatives: 183
",
 Finalize Aggregate
   ->  Gather Motion 3:1  (slice1; segments: 3)
         ->  Partial Aggregate
               ->  Hash Join
                     Hash Cond: (o.k = b.b)
                     ->  Seq Scan on m176_other o
                     ->  Hash
                           ->  Redistribute Motion 3:3  (slice2; segments: 3)
                                 Hash Key: b.b
                                 ->  Hash Join
                                       Hash Cond: (b.a = s.a)
                                       …
 Optimizer: GPORCA

-- tier three
INFO:  GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL:  Falling back to Postgres-based planner because GPORCA does not support
         the following feature: Use optimizer_enable_query_parameter to enable
         Orca with query parameters
 Optimizer: Postgres query optimizer

17.6.3 Whole-optimizer switches

Three knobs act on the optimizer as a whole rather than on its plan space. optimizer is the gate itself, tested at planner.c:399; it is PGC_USERSET, so SET optimizer = off per query, ALTER ROLE … SET optimizer = off per role and ALTER DATABASE … SET optimizer = off per database are all legitimate — role- and database-level defaults are the normal way to keep one problem workload on the Chapter 15 planner without touching anyone else. optimizer_control is the meta-switch: it is PGC_SUSET (guc_gp.c:2316) and its only job is to make optimizer immutable, via the check hook at guc_gp.c:5519, which rejects any assignment whose GucSource is at or above PGC_S_INTERACTIVE. That covers SET and also the PGC_S_TEST validation pass that ALTER ROLE/ALTER DATABASE … SET run (guc.c:6865) — so with optimizer_control = off a user cannot escape the administrator’s choice in either direction, while a value already installed from postgresql.conf still applies. optimizer_segments (guc_gp.c:4455) is different again: it does not change what ORCA may build, only what it believes. COptTasks::OptimizeTask reads the real count from gpdb::GetGPSegmentCount() and then, if optimizer_segments is non-zero, hands that number to the cost model instead (COptTasks.cpp:951–956). §17.4 owns what the cost model does with it; here it is enough to see that the execution shape is untouched and only the numbers move.

optimizer_control locks the gate; optimizer_segments lies to the cost model. Note that the plan and its 3:1 Gather are identical — only the cost changes.

SET optimizer_control = off;
SET optimizer = off;                 -- refused
SET optimizer_control = on;

SET optimizer_segments = 100;        -- pretend we have 100 segments
EXPLAIN SELECT count(*) FROM m176_big WHERE b < 50;
RESET optimizer_segments;            -- back to the real 3
EXPLAIN SELECT count(*) FROM m176_big WHERE b < 50;

ERROR:  cannot change the value of "optimizer"

-- optimizer_segments = 100
 Finalize Aggregate  (cost=0.00..431.13 rows=1 width=8)
   ->  Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..431.13 rows=1 width=8)
         ->  Partial Aggregate  (cost=0.00..431.13 rows=1 width=8)
               ->  Seq Scan on m176_big  (cost=0.00..431.13 rows=33285 width=1)
                     Filter: (b < 50)
 Optimizer: GPORCA

-- optimizer_segments = 0 (use the real count)
 Finalize Aggregate  (cost=0.00..435.24 rows=1 width=8)
   ->  Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..435.24 rows=1 width=8)
         ->  Partial Aggregate  (cost=0.00..435.24 rows=1 width=8)
               ->  Seq Scan on m176_big  (cost=0.00..435.24 rows=33285 width=1)
                     Filter: (b < 50)
 Optimizer: GPORCA

17.6.4 Plan hints

A GUC is a blunt instrument: it applies to every table and every join in the query. Hints are the surgical version, and ORCA’s implementation of them is a nice piece of pragmatism. The README in libgpopt/src/hints/ puts it bluntly — writing a parser is hard, so ORCA does not write one. Instead, when the pg_hint_plan extension is loaded it installs its parser into the plan_hint_hook function pointer (declared at COptTasks.cpp:91); COptTasks::GetPlanHints (:648) calls that hook, gets back a HintState full of pg_hint_plan structures, and translates them into ORCA’s own CPlanHint (:667), which rides along on the COptimizerConfig and is serialized into DXL with everything else. The hint text is parsed by PostgreSQL code and enforced by C++ code that never sees a comment.

Hint	ORCA structure	Where it bites
SeqScan(t), NoSeqScan, IndexScan, NoIndexScan, IndexOnlyScan, NoIndexOnlyScan, BitmapScan, NoBitmapScan	CScanHint	exploration — CHintUtils::SatisfiesPlanHints() (CHintUtils.cpp:41) rejects any CLogicalGet / CLogicalIndexGet / CLogicalDynamicGet alternative that violates the hint
HashJoin(a b), NoHashJoin, NestLoop, NoNestLoop, MergeJoin, NoMergeJoin	CJoinTypeHint	implementation — physical join alternatives that do not match are not kept (COptTasks.cpp:811)
Leading((a (b c))), and the direction-less Leading(a b c)	CJoinHint	preprocessing — the n-ary join is rewritten into the requested inner-join tree before search begins (COptTasks.cpp:782)
Rows(a b #1), and the relative forms	CRowHint	statistics derivation — the join’s row estimate is overridden in CLogicalJoin derive (COptTasks.cpp:760); §14 owns estimation itself
TidScan, NoTidScan, Parallel	—	parsed by pg_hint_plan but not translated: ORCA ignores them, and the README lists them as unsupported
Motion hints	—	the README section is literally TODO, and GetPlanHints has no motion loop — there is no way to hint a Motion today

Hints work in a plain session on this cluster — LOAD is enough, no shared_preload_libraries needed. With pg_hint_plan.debug_print on, ORCA echoes its translated CPlanHint (the EopttracePrintPgHintPlanLog path, COptTasks.cpp:658), which tells you which hints it actually understood.

LOAD 'pg_hint_plan';
SET client_min_messages = log;
SET pg_hint_plan.debug_print = on;

EXPLAIN (COSTS OFF) /*+ NoHashJoin(b s) SeqScan(b) */
  SELECT b.c, s.d FROM m176_big b JOIN m176_small s ON b.a = s.a;

EXPLAIN /*+ Rows(b s #1) */
  SELECT b.c, s.d FROM m176_big b JOIN m176_small s ON b.a = s.a;

LOG:  2026-08-14 00:40:26:832625 CST,THD000,TRACE,"PlanHint: [
used hint:
ScanHint: b[indexes: types:SeqScan]
JoinTypeHint: aliases:b,s type:NoHashJoin
not used hint:
]",
 Gather Motion 3:1  (slice1; segments: 3)
   ->  Nested Loop
         Join Filter: (b.a = s.a)
         ->  Seq Scan on m176_big b
         ->  Seq Scan on m176_small s
 Optimizer: GPORCA

LOG:  … TRACE,"PlanHint: [
used hint:
RowHint: b,s #1.000000
not used hint:
]",
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..877.25 rows=1 width=11)
   ->  Hash Join  (cost=0.00..877.25 rows=1 width=11)
         Hash Cond: (b.a = s.a)
         ->  Seq Scan on m176_big b  (cost=0.00..432.80 rows=66667 width=11)
         ->  Hash  (cost=431.00..431.00 rows=167 width=8)
               ->  Seq Scan on m176_small s  (cost=0.00..431.00 rows=167 width=8)
 Optimizer: GPORCA

Two limits worth internalising. First, the README’s own wording: a hint may only be used to coerce plans that exist in ORCA’s plan space. A hint cannot invent an alternative the xform catalogue never generates — it only changes which of the existing alternatives survives. Second, the not used hint: section of that debug output is where typos and misspelled aliases go to die; if a hint appears there (or not at all), ORCA never applied it. Note also that Rows is the one hint that is not a filter — it lies to the statistics, so it can steer a plan that no Scan/Join hint could reach, which is exactly why it is the most dangerous of the four.

17.6.5 The introspection cheat-sheet

This is the table to come back to. Every entry below is a session-settable GUC on this cluster, every one of them writes to the server log at LOG level — so you will see nothing in psql until you SET client_min_messages = log — and almost every one is hidden from pg_settings. The right-hand column says which section explains the output rather than repeating it here.

GUC	What lands in the log	Explained in
optimizer_print_query	the DXL of the input query expression tree	§17.5
optimizer_print_plan	the DXL of the winning plan expression tree	§17.5
optimizer_print_memo_after_exploration	the memo once the stage’s exploration phase completes — logical alternatives only	§17.2
optimizer_print_memo_after_implementation	the memo once physical alternatives have been added	§17.2
optimizer_print_memo_after_optimization	MEMO after optimization (stage N) plus the whole memo with costed physical expressions. The single most useful print GUC (CEngine.cpp:1616, ProcessTraceFlags)	§17.2
optimizer_print_optimization_context	per-group optimization contexts and, on each physical group expression, its cost contexts (CGroupExpression.cpp:1061)	§17.2, §17.4
optimizer_print_optimization_stats	[OPT]: Memo (stage N): [G groups, D duplicate groups, E group expressions, X activated xforms], per-stage elapsed time, best cost, the activated-xform histogram and memory consumption	§17.2, §17.3
optimizer_print_xform	annotates every group expression in a memo dump with the xform that produced it (Origin: …)	§17.3
optimizer_print_xform_results	the input and output expression trees of each firing. Requires optimizer_print_xform as well — PrintXform tests both flags (CGroupExpression.cpp:1028)	§17.3
optimizer_print_expression_properties	derived and required relational/physical properties on each expression print (CExpression.cpp:1112)	§17.4
optimizer_print_group_properties	the same for groups (CGroup.cpp:1776)	§17.2
optimizer_print_job_scheduler	the scheduler’s queues on every job completion. Assert-build only — the call site is inside #ifdef GPOS_DEBUG (CScheduler.cpp:137)	§17.2
optimizer_print_preprocess_result	the expression tree after each preprocessing step. Assert-build only — the TRCAE_PREPROCESS_* macros are empty otherwise (CExpressionPreprocessor.cpp:3297)	§17.1
optimizer_print_missing_stats	the familiar WARNING: missing statistics for column … (COptTasks::PrintMissingStatsWarning, :1083)	§14.1, §17.4
optimizer_enumerate_plans	[OPT]: Number of plan alternatives: N — the size of the enumerated plan space	§17.2
optimizer_plan_id (int, default 0)	forces extraction of the N-th alternative instead of the cheapest; logs [OPT]: Successfully generated plan: N	§17.2
optimizer_sample_plans, optimizer_samples_number (1000), optimizer_cost_threshold (0)	random sampling of the plan space within a cost bound, instead of full enumeration	§17.2, §17.4
optimizer_trace_fallback	an INFO plus a DETAIL naming the unsupported feature, every time ORCA gives up (CGPOptimizer.cpp:100)	§17.1
optimizer_log (on by default), optimizer_log_failure (unexpected / expected / all)	GPORCA produced plan at DEBUG1, or GPORCA failed to produce plan (unexpected) at LOG (orca.c:61)	§17.1
gp_log_optimization_time	LOG: Optimizer Time: N ms around the whole ORCA call (planner.c:434)	§17.1
optimizer_minidump (onerror default, or always)	writes a self-contained replayable .mdp to minidumps/Minidump_YYYYMMDD_HHMMSS_<sessionid>_<cmdcount>.mdp, relative to the coordinator’s data directory (CMinidumperUtils.cpp:170, CUtils.cpp:3530)	§17.5

About assert builds. This cluster is configured with —enable-cassert (show debug_assertions → on), and configure.ac:895 turns that into #define GPOS_DEBUG 1 — ORCA’s own debug switch. It is worth being precise about what that buys, because it is less than folklore suggests: the memo dumps, the xform prints, the optimization statistics, plan enumeration and optimizer_plan_id are all compiled in unconditionally (CEngine::ProcessTraceFlags has no #ifdef around it), so they work in a release build too. Only two of the print GUCs are genuinely assert-only: optimizer_print_job_scheduler and optimizer_print_preprocess_result (together with optimizer_debug_cte) — and the mapping table says so itself, in the description strings at CConfigParamMapping.cpp:86 and :90. What an assert build really costs you is the assertions, and what it costs you in speed; be careful about drawing timing conclusions from it.

17.6.6 A playbook

Put together, the chapter suggests a fixed order of questions. Work down the list and stop as soon as one of them answers you; each step is cheap, and each one eliminates a whole class of explanation.

1 — Is ORCA even planning this? — Read the EXPLAIN footer: Optimizer: GPORCA or Optimizer: Postgres query optimizer. If it is the latter, SET optimizer_trace_fallback = on and look for the INFO/DETAIL that names the unsupported feature. No INFO at all means the query was diverted before ORCA was called (INSERT … ON CONFLICT, CURSOR_OPT_UPDATABLE, a non-coordinator plan) — §17.1 has the gate conditions.

2 — How long did it take? — SET gp_log_optimization_time = on gives Optimizer Time: N ms. ORCA is a search engine; on wide joins it can dominate planning. If the number is large, optimizer_join_order and optimizer_join_order_threshold are the first things to try (§17.2), and optimizer_xform_bind_threshold caps runaway bindings.

3 — Are the estimates sane? — A bad plan from a good optimizer is usually a statistics problem, not a search problem. Compare EXPLAIN ANALYZE’s actual rows against the estimate, check optimizer_print_missing_stats, and re-read §14. ORCA reads the same pg_statistic rows the Chapter 15 planner does but re-derives cardinalities its own way (§17.4) — so an estimate that is wrong under ORCA may look fine under optimizer=off, and that difference is a finding, not a bug.

4 — What did the search actually consider? — SET client_min_messages = log; SET optimizer_print_memo_after_optimization = on; and read the memo (§17.2). optimizer_enumerate_plans tells you how big the space was; optimizer_plan_id lets you extract and run the alternative you wanted, which turns “ORCA can’t do X” into “ORCA priced X at N”.

5 — Which rules fired? — optimizer_print_optimization_stats gives the activated-xform histogram; optimizer_print_xform plus optimizer_print_xform_results gives the before/after trees. §17.3 reads that output.

6 — Isolate it. — Narrow with the smallest knob that reproduces the change: disable_xform('CXform…') for a single rule (§17.3), then the optimizer_enable_* family above for a whole operator class. Remember the shrinking plan space — if a knob makes ORCA fall back entirely, you have removed an enforcer, not an option.

7 — Capture it. — SET optimizer_minidump = always; and run the query once. The .mdp under the coordinator’s minidumps/ directory is a complete, replayable reproduction — query DXL, all metadata, the config and the plan — and is what an ORCA bug report should contain. §17.5 explains what is inside it and how to replay it.

Steps 1 and 2 in one session. Note that the fallback path is timed too: 17 ms spent in ORCA before it gave up, then 0.4 ms in the PostgreSQL planner to produce the plan that actually ran.

SET client_min_messages = log;
SET gp_log_optimization_time = on;
SET optimizer_trace_fallback = on;

EXPLAIN (COSTS OFF) SELECT b.c, count(*) FROM m176_big b
  JOIN m176_small s ON b.a = s.a GROUP BY 1;

EXPLAIN (COSTS OFF)
  WITH RECURSIVE r(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM r WHERE n<5)
  SELECT * FROM r;

LOG:  Optimizer Time: 51.822 ms
LOG:  Planner Hook(s): 53.671 ms
 Gather Motion 3:1  (slice1; segments: 3)
   ->  HashAggregate
         Group Key: b.c
         ->  Redistribute Motion 3:3  (slice2; segments: 3)
               Hash Key: b.c
               ->  Hash Join
                     …
 Optimizer: GPORCA

INFO:  GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL:  … GPORCA does not support the following feature: WITH RECURSIVE
LOG:  Optimizer Time: 17.185 ms
LOG:  Planner Time: 0.367 ms
 Recursive Union
   ->  Result
   ->  WorkTable Scan on r
         Filter: (n < 5)
 Optimizer: Postgres query optimizer

And one honest closing note, because the most effective ORCA knob is often the one that turns it off. SET optimizer = off is not an admission of defeat; it is a legitimate per-query, per-role or per-database choice. ORCA earns its keep on wide star-schema joins, partitioned tables and queries where getting the join order and the Motion placement right is worth tens of milliseconds of search. It earns nothing on short OLTP-shaped statements, where its planning time is pure overhead, and it earns nothing on the features it does not support, where you pay for a failed search before falling back anyway. Chapters 15 and 16 described a perfectly good optimizer; keeping one workload on it while the rest of the system uses ORCA is ordinary operational judgement, not a workaround. Either way, what leaves this chapter is the same artefact: a PlannedStmt with a slice table and Motion nodes in it. Chapter 18 picks it up and executes it; Chapter 19 takes those Motions and turns them into traffic on the interconnect.