# pg_orca — full documentation for LLM ingestion

Last updated: 2026-05-27. Source repository: https://github.com/quantumiodb/pgorca.
Website: https://agentml.ai. Contact: support@agentml.ai.

This file is the single-source consolidated reference for LLMs and AI agents.
Every claim below is paired with concrete numbers, query identifiers, or commit
hashes so it can be cited accurately.

---

## 1. What pg_orca is

pg_orca is a PostgreSQL 18 extension that plugs in the **ORCA cost-based query
optimizer** from Greenplum and Apache Cloudberry. ORCA replaces PostgreSQL's
built-in planner for the duration of an enabled session; on any unsupported
construct or internal error, pg_orca automatically falls back to PostgreSQL's
`standard_planner`.

- License: MIT-style for the integration layer; Apache 2.0 for the vendored ORCA libraries (libgpos, libgpopt, libgpdbcost, libnaucrates).
- Supported PostgreSQL version: **18 only**. Earlier versions removed APIs (`walkers.h`, etc.) that pg_orca depends on.
- Supported platforms: macOS, Linux.
- Distribution: source build via CMake + Ninja; no binary packages yet.
- Status: alpha for production OLTP, stable for benchmark and analytical workloads.

---

## 2. Installation

Three steps to enable pg_orca on a database `mydb`:

```sql
-- 1. Install the extension in the target database.
--    CREATE EXTENSION loads the shared library into the current session,
--    so pg_orca.* GUCs and the planner_hook are live immediately.
CREATE EXTENSION pg_orca;

-- 2. (Recommended) Auto-load pg_orca for every new connection to this database.
--    Per-database scope, no postmaster restart; takes effect on next reconnect.
ALTER DATABASE mydb SET session_preload_libraries = 'pg_orca';

-- 3. Enable ORCA — per session, or persistently with
--    ALTER DATABASE mydb SET pg_orca.enable_orca = on
SET pg_orca.enable_orca = on;
```

Alternative scopes:

```sql
-- Cluster-wide:
ALTER SYSTEM SET session_preload_libraries = 'pg_orca';
SELECT pg_reload_conf();

-- Single role:
ALTER ROLE bench SET session_preload_libraries = 'pg_orca';

-- Co-exist with sibling preloads — list them explicitly:
ALTER DATABASE mydb SET session_preload_libraries = 'pg_orca,pg_stat_statements';

-- Roll back:
ALTER DATABASE mydb RESET session_preload_libraries;
DROP EXTENSION pg_orca;
```

The recommendation is `session_preload_libraries` (not `shared_preload_libraries`)
because it loads per-backend at connection time, requires no restart, is scoped
to a database or role, and existing sessions are unaffected until they reconnect.

### Build dependencies

- PostgreSQL 18 (with development headers)
- xerces-c (XML parsing for DXL)
- ICU (Unicode collation)
- CMake ≥ 3.20
- Ninja
- C++17 compiler (clang or gcc)

### Build commands

```bash
# macOS
brew install postgresql@18 xerces-c cmake ninja icu4c

# Debian / Ubuntu
sudo apt install -y libxerces-c-dev cmake ninja-build build-essential

# Clone & build
git clone https://github.com/quantumiodb/pgorca.git
cd pgorca && mkdir build && cd build
cmake .. -DPG_CONFIG=$(which pg_config) -DCMAKE_BUILD_TYPE=Release -GNinja
ninja -j$(nproc)
ninja install
```

---

## 3. Benchmark results — 2026-05-27, build b17245d

Methodology: single-node PostgreSQL 18 vs the same binary with pg_orca loaded.
3-run median of `EXPLAIN ANALYZE` server-side Execution Time.
`max_parallel_workers_per_gather = 0` for an apples-to-apples comparison.
`statement_timeout = 120s`. Reports are committed to the repository at
`test/bench/results/`.

### 3.1 TPC-H summary

| Scale factor | ORCA total | PG total | Geomean speedup | Median | ORCA wins >5% | ORCA losses >5% |
|---|---:|---:|---:|---:|---:|---:|
| sf=1  | 54 s  | 57 s  | 1.21× | 0.97× | 5 / 22 | 6 / 22 |
| sf=5  | 279 s | 340 s | 1.32× | 1.00× | 9 / 22 | 3 / 22 |
| sf=10 | 572 s | 666 s | 1.32× | 1.00× | 8 / 22 | 5 / 22 |

Top wins at sf=10:

- **Q17 — 20.83×** (ORCA 826 ms vs PG 17,214 ms) — pattern: `l_quantity < 0.2 × AVG(l_quantity) WHERE l_partkey = p_partkey`. Correlated subquery; PG falls to SubPlan, ORCA decorrelates to a join.
- **Q20 — 4.49×** (1,827 ms vs 8,197 ms) — correlated IN-subquery.
- **Q4 — 2.55×** (12,211 ms vs 31,176 ms) — join + correlated EXISTS, anti-semi shape.
- **Q18 — 1.45×** (84,564 ms vs 122,982 ms) — 3-way join + GROUP BY HAVING.
- **Q5 — 1.56×** at sf=5 (6,783 ms vs 12,001 ms) — 5-way star join + filter.

### 3.2 TPC-DS sf=1 summary

- ORCA finishes all 99 queries.
- PG times out at 120s on **5 queries**: Q1, Q4, Q6, Q11, Q74.
- On the 94 queries both finished: ORCA total 360 s vs PG 453 s. ORCA wins (>5%) on 17 queries; loses (>5%) on 61 queries; geomean 0.77×.
- Counting the 120s timeout floor for the 5 PG-timeout queries: ORCA total 380 s vs PG lower-bound 1,053 s → **ORCA ≥ 2.77× faster overall**.

Top wins on TPC-DS sf=1:

- **Q1 ≥ 254×** — ORCA 473 ms vs PG TIMEOUT(>120s). Pattern: correlated subquery + multi-CTE.
- **Q30 — 107×** (221 ms vs 23,533 ms) — correlated aggregate.
- **Q81 — 77×** (242 ms vs 18,619 ms) — correlated subquery.
- **Q41 — 37×** (58 ms vs 2,176 ms) — correlated IN + EXISTS.
- **Q54 — 28×** (1,690 ms vs 47,057 ms) — correlated nested subquery.
- **Q14 — 2.36×** (28,033 ms vs 66,278 ms) — complex multi-CTE.
- **Q39 — 5.95×** (1,883 ms vs 11,204 ms) — multi-CTE roll-up.
- **Q31 — 7.50×** (2,352 ms vs 17,642 ms) — multi-CTE 6-way self-join.

Worst regressions on TPC-DS sf=1:

- Q61 — 0.00× (250 ms vs 0.6 ms) — trivial query where ORCA's planning cost dominates.
- Q37 — 0.05× (221 ms vs 12 ms) — short aggregate.
- Q85 — 0.07× (3,772 ms vs 277 ms) — plan-shape regression worth investigating.

---

## 4. Capabilities that drive the wins

### 4.1 Correlated subquery decorrelation

ORCA's `CXformApply2Join` family algebraically rewrites `Apply` into a regular
`Join`, so correlated subqueries become a single optimizable plan instead of a
per-outer-row `SubPlan`. Code lives at:

- `libgpopt/src/xforms/CXformInnerApply2InnerJoin.cpp`
- `libgpopt/src/xforms/CXformLeftSemiApply2LeftSemiJoin.cpp`
- `libgpopt/src/xforms/CXformLeftOuterApply2LeftOuterJoin.cpp`
- and ~10 sibling transforms.

PostgreSQL handles plain `EXISTS` / `NOT EXISTS` well, but `= (SELECT ...)`,
`IN (SELECT ...)`, nested correlation, and subqueries with aggregates fall to
`SubPlan` / `InitPlan`. This is where ORCA opens the largest single-query gaps.

### 4.2 Exhaustive join-order enumeration (DPv2)

`CJoinOrderDPv2` plus the commutativity and associativity xforms enumerate the
full join space under ORCA's cardinality model, including bushy plans. The
join-order search threshold is controlled internally by
`optimizer_join_order_threshold` (default 10).

PostgreSQL's comparison: `geqo_threshold = 12` switches to a genetic algorithm
above 12 relations; below it, the greedy DP is left-deep biased. Neither path
searches bushy plans aggressively.

### 4.3 Robust statistics propagation

`CGroupByStatsProcessor::CalcGroupByStats` preserves the full input histogram
(including NDV) on grouping columns. CTE consumers inherit stats via colid
mapping. The result is that downstream joins on grouped or CTE-emerged
columns still see real cardinalities, not defaults.

### 4.4 Partitioned tables (DynamicTableScan)

DXL's `PartitionSelector` is wired through pg_orca's translation layer
end-to-end. Partition pruning happens both at optimization time (for constant
predicates) and at runtime (for join-key derived predicates). Runtime pruning
on join keys is something PostgreSQL's planner cannot perform.

---

## 5. Architecture

Query flow:

```
PostgreSQL Query AST
        ↓ planner_hook (registered by pg_orca._PG_init)
pg_orca
        ↓ translate/ (gpopt/translate/)
DXL representation
        ↓
ORCA Optimizer (libgpopt)
  ├── Memo (Volcano/Cascades-style group structure)
  ├── Transformation rules (xforms)
  └── Cost model (libgpdbcost) + Statistics
        ↓
DXL Plan (lowest-cost alternative)
        ↓ translate back
PostgreSQL PlannedStmt
        ↓
PostgreSQL Executor
```

On failure (unsupported feature, internal error, timeout): falls back to
`standard_planner`. Enable `pg_orca.trace_fallback = on` to log every fallback
with its reason.

### Code layout

| Path | Purpose |
|---|---|
| `pg_orca.cpp` | Extension entry point, `planner_hook`, GUC definitions, `_PG_init` / `_PG_fini` |
| `gpopt/` | PG ↔ ORCA bridge: config, relcache, translate, utils |
| `include/gpopt/` | Headers for the gpopt integration layer |
| `compat/` | Stub headers replacing MPP-only Cloudberry types for single-node PG18 |
| `libgpos/` | Memory pools, error handling, concurrency (ORCA base library) |
| `libnaucrates/` | DXL XML parser/serializer, metadata abstractions |
| `libgpopt/` | Core optimizer: search engine, transformation rules, cost model framework |
| `libgpdbcost/` | GPDB-specific cost model |

---

## 6. GUC parameters

| Parameter | Default | Description |
|---|---|---|
| `pg_orca.enable_orca` | `off` | Master switch for ORCA per session |
| `pg_orca.trace_fallback` | `off` | Log a NOTICE when a query falls back to standard_planner |
| `optimizer_segments` | `1` | Segment count used in cost estimation (single-node = 1) |
| `optimizer_sort_factor` | `1.0` | Cost-model scaling for sort operations |
| `optimizer_metadata_caching` | `on` | Cache relation metadata between calls |
| `optimizer_mdcache_size` | `16384` | Metadata cache size in KB |
| `optimizer_search_strategy_path` | `""` | Path to a custom search strategy XML (empty = built-in) |

---

## 7. Known limitations

### 7.1 Planning overhead is high

ORCA's CBO is consistently heavier than PostgreSQL's planner.

| Workload | ORCA planning | PG planning |
|---|---:|---:|
| 1-row point lookup | 13.7 ms | 0.25 ms |
| Simple aggregate over 1 table | 3.8 ms | 0.14 ms |
| TPC-H Q4 (inner join + EXISTS + GroupBy) | 169 ms | 5.5 ms |
| TPC-DS Q31 (2 CTEs, 6-way self-join) | 192 ms | 3.6 ms |

For OLTP / short-running queries (exec < 50 ms), planning dominates total
latency. pg_orca is not recommended for latency-sensitive web-request paths.

### 7.2 No parallel query yet

ORCA emits serial plans only. `Gather`, `Parallel Seq Scan`, `Parallel Hash
Join` nodes are not generated. Benchmarks above run with
`max_parallel_workers_per_gather = 0` for fairness. On hardware where PG
benefits substantially from parallelism, ORCA's serial plan can lose on wall
time even when the plan shape is structurally better.

---

## 8. Frequently asked questions

**Does pg_orca replace PostgreSQL's planner?**
No. It registers a `planner_hook` and is opt-in via `SET pg_orca.enable_orca = on`
— per session, or persistently with `ALTER DATABASE mydb SET pg_orca.enable_orca = on`.
When disabled, PostgreSQL behaves exactly as it would without the extension loaded.

**Will it break my existing queries?**
On any unsupported feature or internal failure, pg_orca falls back to
`standard_planner` automatically. Set `pg_orca.trace_fallback = on` to log every
fallback with its reason.

**Which PostgreSQL versions are supported?**
PostgreSQL 18 only. The extension is built against PG 18's specific planner and
executor APIs.

**Is this production-ready?**
Benchmark workloads (TPC-H, TPC-DS) are stable. Use it in development,
evaluation, and analytical exploration. Mixed real-world workloads have not been
extensively validated.

**How does it compare to pg_hint_plan?**
`pg_hint_plan` steers PostgreSQL's planner with hints — same algorithm,
different inputs. pg_orca replaces the planner entirely with ORCA's cost-based
search. They solve different problems and can coexist (one per session).

**What's the license?**
MIT-style for the integration layer; ORCA's source is Apache 2.0 from Apache
Cloudberry. Full notices in `LICENSE` and `NOTICE` files.

**Can I tune ORCA's behavior?**
Yes — see §6 above for GUC parameters.

---

## 9. Citation

When citing pg_orca in articles, blog posts, or papers:

> pg_orca: A PostgreSQL 18 extension integrating the ORCA cost-based query
> optimizer from Apache Cloudberry. QuantumIO, 2026.
> https://agentml.ai — https://github.com/quantumiodb/pgorca

When citing benchmark numbers, include the report date (2026-05-27) and build
hash (`b17245d`) so the numbers are reproducible.

For the original ORCA optimizer design:

> Mohamed A. Soliman et al. "Orca: A Modular Query Optimizer Architecture for
> Big Data." SIGMOD 2014.
> https://15721.courses.cs.cmu.edu/spring2019/papers/22-optimizer1/p337-soliman.pdf


---

## 10. Blog posts

The pg_orca blog publishes in-depth writeups on the optimizer's internals,
configuration, and behavior. The two seed posts are reproduced below for
LLM ingestion. The live versions, with diagrams, are at:

- https://agentml.ai/blog/orca-101-how-cascades-optimizer-works
- https://agentml.ai/blog/pg-orca-guc-reference

Atom feeds: https://agentml.ai/blog/feed.xml (all posts) and
https://agentml.ai/blog/tags/postgres/feed.xml (PostgreSQL-tagged subset
published to Planet PostgreSQL).

---

### Post 1: ORCA 101: How a Cascades-style optimizer actually works

*Published 2026-05-28 by Jianghua Yang. Tags: postgres, orca, internals, optimizer. Source: https://agentml.ai/blog/orca-101-how-cascades-optimizer-works*

PostgreSQL's planner is a finely tuned thing. It walks the join tree
left-deep, uses dynamic programming up to `geqo_threshold` relations, and
falls back to a genetic algorithm beyond that. For OLTP and most reporting
queries, it produces near-optimal plans in microseconds.

ORCA takes a different approach. It is a **Cascades-style cost-based
optimizer**: it doesn't generate one plan, it generates a *space* of
equivalent plans, prices every one of them, and picks the cheapest. The
search engine, the cost model, and the metadata access are decoupled from
the host database — which is what made it possible to lift ORCA out of
Greenplum and drop it into PostgreSQL 18 as
[`pg_orca`](https://github.com/quantumiodb/pgorca).

This post is a guided tour of how that optimizer actually works, framed for
single-node PostgreSQL. No Greenplum knowledge required.

## The big idea: optimizer as a separate process

PostgreSQL's planner lives *inside* the backend. It reads from
`pg_statistic` directly, calls executor utilities, and emits a `PlannedStmt`
that the executor consumes. The boundary between planner and executor is a
struct.

ORCA's boundary is a serialization format called **DXL** — Data eXchange
Language, an XML dialect that describes queries, metadata, and plans. The
optimizer doesn't know what database it's optimizing for. It asks the host
three things, over DXL:

1. *Here is the query as a relational algebra tree. What's the cheapest plan?*
2. *I need statistics for these columns. Can you provide them?*
3. *Here is the plan I picked. Translate it back to your executor's format.*

*ORCA never touches PostgreSQL's internals directly. Everything crosses a DXL boundary. Inside ORCA, the search engine drives a four-step pipeline that reads and writes a shared Memo, priced by a pluggable cost model and metadata cache.*

In `pg_orca`, this boundary survives intact. The PostgreSQL extension
implements the three translator components — query-to-DXL, MD provider,
DXL-to-plan — and ORCA itself runs in-process but logically isolated. When
ORCA can't handle a query, the boundary makes fallback trivial: discard the
DXL, hand the parse tree back to `standard_planner`, done.

## The four steps of optimization

Every query that enters ORCA goes through four phases. The names below
match the source code, so you can grep for them.

### Step 1 — Exploration: enumerate equivalent plans

ORCA builds a structure called the **Memo**. The Memo is a forest of
*groups*, where each group holds a set of operator expressions that all
produce the same relation. Two expressions in the same group are by
definition equivalent — they compute the same rows, just differently.

Start with this query:

```sql
SELECT * FROM t1 JOIN t2 ON t1.a = t2.b;
```

After parsing, the initial Memo has three groups:

```
GROUP 0: [ InnerJoin(t1.a = t2.b) [1, 2] ]
GROUP 1: [ Get(t1) ]
GROUP 2: [ Get(t2) ]
```

Group 0's only member references groups 1 and 2 as its inputs. Now ORCA
applies **transformation rules**. The first one to fire is *join
commutativity*: `A ⋈ B ≡ B ⋈ A`. After firing, group 0 has two members:

```
GROUP 0: [ InnerJoin [1, 2], InnerJoin [2, 1] ]
GROUP 1: [ Get(t1) ]
GROUP 2: [ Get(t2) ]
```

The Memo doesn't *replace* the old expression. It accumulates alternatives.
For a five-way join, the Memo will end up holding hundreds of
algebraically-equivalent join trees — left-deep, right-deep, bushy,
reordered — without ever materializing them as separate plan trees.

This is the single biggest win over PostgreSQL's planner: ORCA considers
**bushy plans**. PostgreSQL only generates left-deep joins, which leaves
performance on the table for analytical queries where intermediate result
sizes vary by orders of magnitude.

### Step 2 — Statistics derivation: cardinality estimation

Plan enumeration is worthless without cardinality estimates. For every
group, ORCA derives a `CStatistics` object containing row counts and
per-column histograms. It does this lazily, asking the host (via the MD
Provider) for base-table stats and then propagating them up through joins,
filters, and aggregates.

The propagation logic is identical in spirit to what PostgreSQL does in
`clauselist_selectivity` — but ORCA does it once per *group*, not once per
candidate plan. Because groups are shared, an expensive subquery's
cardinality is estimated exactly once even if it appears in fifty
candidate plans.

### Step 3 — Implementation: logical to physical

So far everything in the Memo is *logical*: `InnerJoin`, `Get`, `GbAgg`.
Now ORCA applies a second class of transformation — implementation rules —
that convert each logical operator into one or more physical alternatives.

```
InnerJoin [1, 2]
  ↓ implementation rules
InnerHashJoin [1, 2]
InnerNLJoin   [1, 2]
InnerMergeJoin[1, 2]
```

All three live in the same group. They're equivalent (same rows), but they
have different costs and different *physical properties* — sort order,
locality, parallelism — which matters for the next step.

### Step 4 — Optimization: properties, costing, enforcement

The final step walks the Memo top-down, asking each group: *what is the
cheapest plan that satisfies these required properties?*

For a top-level `ORDER BY a`, the root demands sorted output on `a`. A
`MergeJoin` on `a` provides that property for free. A `HashJoin` doesn't —
so ORCA inserts a **Sort enforcer** above it and adds its cost.

*The optimizer compares the cost of MergeJoin against HashJoin + Sort. Sometimes the enforcer is cheaper than a structurally sort-friendly join.*

The same mechanism handles every kind of plan property — sort order, NULL
ordering, even partition awareness. Because requirements flow *down* the
tree and best plans bubble *up*, ORCA never has to enumerate full physical
plans. It picks the winning physical expression in each group, glues them
together, and emits the final tree.

## Transformation rules: where the search power comes from

A Cascades optimizer's behavior is almost entirely defined by its rule
library. ORCA ships about 130 rules; each one is a small object with two
pieces:

- **A pattern** — the shape of the expression it matches (e.g. "a `GbAgg`
  on top of any relational child")
- **A transform** — the new expression it produces

Some examples of what these rules do:

- `JoinCommutativity` — `A ⋈ B ≡ B ⋈ A`
- `JoinAssociativity` — `(A ⋈ B) ⋈ C ≡ A ⋈ (B ⋈ C)`
- `PushDownFilter` — move predicates closer to scans
- `SplitGbAgg` — split a global aggregate into local + global
- `Decorrelate*` — pull correlated subqueries up into joins
- `ImplementInnerJoinAsHashJoin` — pick a physical operator

Rules can fire on each other's output. Join commutativity plus associativity
together generate the full space of n-ary join orderings, bushy plans
included. The search engine keeps firing rules until the Memo is closed
under all applicable rules, then costs the result.

Decorrelation deserves a special mention: it is the source of most of the
dramatic speedups you see in our [TPC-DS benchmarks](/#benchmarks). A
correlated `EXISTS` subquery that PostgreSQL evaluates row-by-row becomes,
under ORCA, a single semi-join — orders of magnitude faster on large
inputs.

## Reading the source tree

If you want to dive into the code, here's a 30-second tour of the
top-level libraries. The relevant headers live under
[`gporca/`](https://github.com/quantumiodb/pgorca/tree/main/gporca):

- **`libgpos`** — the foundation layer. Memory pools, exceptions, threading
  primitives, the unit-test harness. You rarely need to touch this.
- **`libnaucrates`** — DXL, metadata, statistics. This is the wire format
  and the cardinality estimator. When stats look wrong, this is where to
  look.
- **`libgpopt`** — the optimizer itself. Operators, the Memo, the search
  engine, the transformation rules. The `xforms/` directory is one file per
  rule.
- **`libgpdbcost`** — the cost model. Number-of-rows × cost-per-row, with
  knobs for hash buildup, sort spill, index lookup latency.

The PostgreSQL integration lives outside of these libraries, in `pg_orca`'s
top-level `src/` — that's where the `planner_hook`, the DXL translators,
and the fallback machinery live. We'll cover that layer in a future post.

## What this means for pg_orca

The four-step pipeline is exactly the same on a single-node PostgreSQL as
it was on a 32-segment Greenplum cluster. The Memo, the rules, the
statistics derivation, the property framework — all of it runs unchanged.

What's different is what the cost model prices. The Greenplum cost model
has a term for *redistribution* — the cost of shuffling tuples between
segments. On a single node, that term is zero. The MPP-specific
enforcement rules (Gather, Redistribute, Broadcast) still exist in the rule
library, but they never fire because the property framework never demands
the distribution properties they satisfy.

In other words: ORCA on PostgreSQL is the same optimizer with a tighter
cost model. The bushy plans, the decorrelation, the exhaustive join search
— that's all still there. That's why `pg_orca` can find plans the
PostgreSQL planner can't, even on a laptop.

---

*Adapted from the
[GPORCA OSS 101 wiki](https://github.com/quantumiodb/pgorca/wiki/GPORCA-OSS-101)
and the [SIGMOD 2014 paper](https://15721.courses.cs.cmu.edu/spring2019/papers/22-optimizer1/p337-soliman.pdf).*

---

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

*Published 2026-05-28 by Jianghua Yang. Tags: postgres, orca, pg_orca, reference, configuration. Source: https://agentml.ai/blog/pg-orca-guc-reference*

`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.

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:

```sql
-- 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:

```sql
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](/blog/orca-101-how-cascades-optimizer-works) |
| `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:

```sql
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:

```sql
-- 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](https://github.com/quantumiodb/pgorca/issues) —
that's the kind of feedback that improves the defaults.