Configuring pg_orca: a guide to the GUCs you'll actually use

pg_orca is a PostgreSQL 18 extension that swaps in the ORCA cost-based query optimizer for analytical queries. When enabled, ORCA generates plans that PostgreSQL's built-in planner won't — bushy joins, decorrelated subqueries, ahead-of-time partition pruning — and prices them with a PostgreSQL-aligned cost model. If you're new to how ORCA makes its decisions, the companion post ORCA 101: How a Cascades-style optimizer actually works walks through the four-step pipeline most of these parameters control.

The extension exposes about thirty configuration parameters. Most users only need three: the master switch, the join-order algorithm, and the fallback tracer. This guide walks the rest in the order you're likely to meet them.

All parameter names follow PostgreSQL convention and can be set with SET, ALTER DATABASE, ALTER ROLE, or in postgresql.conf. Scope labels (USERSET, SUSET) follow PostgreSQL's standard meaning.

Turning ORCA on

pg_orca.enable_orca · default off · SUSET

The master switch. Installing the extension is harmless on its own — no plan changes until you turn this on. Typical pattern:

-- per session
SET pg_orca.enable_orca = on;

-- per database, persistent
ALTER DATABASE warehouse SET pg_orca.enable_orca = on;

pg_orca.trace_fallback · default off · SUSET

ORCA will hand a query back to PostgreSQL's standard planner if it can't handle some feature in it. The hand-off is silent by default. Set this to on while evaluating pg_orca to see which fraction of your workload is actually being optimized by ORCA:

LOG:  pg_orca: falling back to standard planner
DETAIL:  <reason>

Each fallback writes one log line. Disable it again in production.

Choosing a cost model

pg_orca.cost_model · default pg · USERSET

Selects the cost model ORCA uses to compare candidate plans.

Value	Description
pg (default)	A PostgreSQL-aligned model. Prices plans using the same constants you already tune for the PostgreSQL planner: cpu_operator_cost, cpu_tuple_cost, seq_page_cost, random_page_cost, work_mem.
gpdb	The legacy Greenplum calibration. Kept for parity testing.

The PG-aligned model is the default because it makes ORCA's costs directly comparable to PostgreSQL's. When you want to influence costing under this model — for example, to make sorts more expensive — adjust the standard PostgreSQL parameters; you don't need ORCA-specific knobs.

A consequence of using the default model: a few legacy ORCA cost knobs have no effect. See Legacy parameters below.

Controlling join-order search

This is where pg_orca diverges most visibly from PostgreSQL's planner. ORCA searches a much larger space of join orderings, including bushy trees that PostgreSQL never considers.

optimizer_join_order · default exhaustive2 · USERSET

Selects the algorithm ORCA uses to enumerate join orderings.

Value	Behavior
query	Use the join order exactly as written in the SQL.
greedy	A fast heuristic. Plans aren't always optimal.
exhaustive	Original exhaustive search.
exhaustive2 (default)	Improved exhaustive search; the algorithm that produces bushy plans.

For analytical workloads, leave this at exhaustive2. The other values are useful when planning time matters more than plan quality.

pg_orca.join_order_dynamic_threshold · default 12 · USERSET

A safety valve for very large joins. When a query references at least this many base relations — counting tables nested inside subqueries and CTEs — and optimizer_join_order is exhaustive or exhaustive2, ORCA automatically downshifts to greedy for that single query and restores the setting afterward.

The threshold counts across the whole query, so it applies to TPC-DS-style shapes (CTEs + subqueries) before the surface query looks large. Set it to 0 to disable the auto-downshift. Raise it if your hardware can afford longer planning time; lower it if you'd rather planning stay quick on moderately joined queries.

CTE handling

PostgreSQL's planner has inlined WITH clauses by default since version 12. ORCA materializes them by default, which can hurt performance when the CTE is referenced only once. Two parameters control this:

optimizer_cte_inlining · default off · USERSET

optimizer_cte_inlining_bound · default 0 · USERSET

To get PostgreSQL-style inlining behavior, set both: enable the feature, and raise the bound to the maximum number of references at which a CTE may still be inlined. A common starting point:

SET optimizer_cte_inlining = on;
SET optimizer_cte_inlining_bound = 1;  -- inline only single-use CTEs

Above the bound, ORCA materializes the CTE.

Metadata cache

ORCA caches relation statistics, type info, and operator definitions between calls so it doesn't re-walk PostgreSQL's catalog for every query.

optimizer_metadata_caching · default on · USERSET

Leave on. The off path exists to diagnose cache-invalidation bugs.

optimizer_mdcache_size · default 16384 KB · USERSET

Soft cap on cached metadata. 16 MB is enough for most schemas. Increase it if you have very many partitioned tables and observe repeated metadata work in optimizer_print_optimization_stats output.

Debug prints

These parameters write diagnostic output to PostgreSQL's log. They default to off and are all USERSET. Reach for them when you want to understand why ORCA picked a particular plan.

Parameter	What it dumps
optimizer_print_query	The input query expression tree
optimizer_print_plan	The final physical plan
optimizer_print_xform	Input and output trees of each transformation rule that fires
optimizer_print_xform_results	Full result set of each transformation — very verbose
optimizer_print_job_scheduler	Search engine state-machine transitions
optimizer_print_optimization_stats	Memo group counts, cache hit rates, time per phase
optimizer_print_memo_after_exploration	The Memo after step 1
optimizer_print_memo_after_implementation	The Memo after step 3
optimizer_print_memo_after_optimization	The Memo after step 4
optimizer_print_optimization_context	The property-requirement chain that drove step 4

A useful starting combination when investigating an unexpected plan:

SET client_min_messages = log;
SET pg_orca.enable_orca = on;
SET optimizer_print_optimization_stats = on;
SET optimizer_print_plan = on;
EXPLAIN ANALYZE SELECT ... ;

optimizer_print_xform = on is very verbose. Only enable it on small test queries.

Legacy parameters (no effect under the default cost model)

These parameters target either the legacy Greenplum cost model or the legacy statistics-damping path. Under the default configuration (pg_orca.cost_model = pg) they have no effect on plan choice. They remain registered for compatibility:

Parameter	Default
optimizer_sort_factor	1.0
optimizer_spilling_mem_threshold	0.0
optimizer_index_join_allowed_risk_threshold	3.0
optimizer_damping_factor_filter	0.75
optimizer_damping_factor_join	0.0
optimizer_damping_factor_groupby	0.75

Under the default cost model:

• To bias sort cost or control sort spill, adjust cpu_operator_cost and work_mem — the same controls PostgreSQL uses.
• To improve cardinality estimates, follow standard PostgreSQL practice: run ANALYZE, declare foreign keys, use extended statistics for correlated columns.

A typical configuration

For most analytical workloads, this is enough:

-- Load the extension globally
ALTER SYSTEM SET shared_preload_libraries = 'pg_orca';

-- Enable ORCA on the database that needs it
ALTER DATABASE warehouse SET pg_orca.enable_orca = on;

-- During evaluation only: see why ORCA falls back on any given query
ALTER DATABASE warehouse SET pg_orca.trace_fallback = on;

Everything else has a sensible default. Reach for the join-order or debug parameters when a specific query needs investigation, and consult this guide as a reference.

If you find a query where a non-default parameter produces a materially better plan, please share the repro at github.com/quantumiodb/pgorca/issues — that's the kind of feedback that improves the defaults.