pgorca

A PostgreSQL 18 / 19 extension that plugs the ORCA query optimizer (originally from Greenplum / Apache Cloudberry) into a standard single-node PostgreSQL instance.

Overview

ORCA is a Cascades-style cost-based optimizer: instead of a fixed bottom-up dynamic-programming pass, it stores all equivalent plan alternatives in a memo and explores them by firing transformation rules (xforms), so logical rewrites and physical implementation choices are made together and priced by the same cost model. It operates on an intermediate representation called DXL (Data eXchange Language). It was designed for massively-parallel processing (MPP) databases but contains a powerful optimization engine that is useful in single-node mode as well.

That architecture lets ORCA address several known limits of the built-in PostgreSQL planner:

• Correlated subqueries. PG pulls up only a limited set of sublink shapes; the rest stay as SubPlans re-executed per outer row. ORCA models them as Apply and decorrelates them into regular joins through xforms, so the subquery side participates in join ordering and costing like any other relation.
• Join ordering beyond the collapse limits. PG stops exhaustive search at join_collapse_limit/from_collapse_limit (8 by default) and falls back to the syntactic order or GEQO. ORCA enumerates join orders as part of the search itself (optimizer_join_order: exhaustive2 by default, or exhaustive / greedy / query), with pg_orca.join_order_dynamic_threshold downshifting to a cheaper strategy only when the query is genuinely large.
• Rewrites the planner does not attempt. Eager aggregation (pushing a GROUP BY below a join), CTE inlining as a cost-based decision rather than a syntactic rule, aggregate splitting, and inferred/transitive predicates pushed across join boundaries.
• Unified search space. Because rewriting and implementation live in the same memo, a rewrite that only pays off with a particular physical shape (index scan, merge join, partition pruning) can be chosen on cost instead of being applied — or skipped — ahead of the plan search.

The trade-off is planning time: exploring the full space costs more than PG's planner, which is why ORCA is opt-in per session and any query it cannot handle falls back to the standard planner automatically.

This project extracts ORCA's four core libraries and the PostgreSQL integration layer from Apache Cloudberry, adapts them for PG18 and PG19, and packages the result as a CREATE EXTENSION-installable plugin.

The ORCA optimizer is the work of many people from the Greenplum, Pivotal/VMware, and Apache Cloudberry projects. See CONTRIBUTORS.md for acknowledgments.

Components

Directory	Description
libgpos/	ORCA memory pool, error handling, concurrency primitives
libnaucrates/	DXL parser/serializer, metadata abstractions
libgpopt/	Core optimizer: search, transformation rules, cost model
libgpdbcost/	GPDB-specific cost model implementation
gpopt/	PostgreSQL ↔ DXL translation layer (relcache, planner bridge)
compat/	Stub headers replacing MPP-only Cloudberry types
pg_orca.cpp	Extension entry point, planner_hook, GUC definitions

Requirements

• PostgreSQL 18 (primary target), or PostgreSQL 19 beta
• xerces-c (XML parsing for DXL)
• CMake ≥ 3.20
• C++17 compiler (clang or gcc)

The same source tree builds against both majors — version differences are handled with #if PG_VERSION_NUM >= 190000 guards, there is no separate branch. CI builds PG18 on Ubuntu (gcc/clang, Debug/Release), Ubuntu arm64, CentOS Stream 9, Rocky 9 and macOS, and builds + runs the ORCA regression tests against PG19 on Rocky 9. See PostgreSQL 19 for what is PG19-specific.

On macOS with Homebrew:

brew install xerces-c cmake

Build & Install

1. Locate pg_config

CMake needs pg_config from the PostgreSQL installation you are building against to find headers, libraries, and install paths. Either add the PG bin/ directory to PATH:

export PATH="/path/to/pg/bin:$PATH"
# verify
pg_config --version   # should print "PostgreSQL 18.x" or "PostgreSQL 19..."

Or pass it explicitly on the CMake command line with -DPG_CONFIG=... (see below).

2. Configure & Build

Two generators are supported. Pick whichever suits your environment.

CMake (Unix Makefiles — no extra tools needed)

mkdir build && cd build

# pg_config is on PATH
cmake .. -DCMAKE_BUILD_TYPE=Debug

# pg_config NOT on PATH — pass it explicitly
cmake .. -DPG_CONFIG=/path/to/pg/bin/pg_config -DCMAKE_BUILD_TYPE=Debug

cmake --build . --target install -j$(nproc)

To rebuild after source changes:

cmake --build build -j$(nproc)

Ninja (faster incremental builds)

mkdir build && cd build
cmake .. -DPG_CONFIG=/path/to/pg/bin/pg_config -DCMAKE_BUILD_TYPE=Debug -GNinja

ninja -j$(nproc)
ninja install

To rebuild after source changes:

ninja -j$(nproc) -C build

Release build

Replace Debug with Release in either generator. Example with Ninja:

mkdir build-release && cd build-release
cmake .. -DPG_CONFIG=/path/to/pg/bin/pg_config -DCMAKE_BUILD_TYPE=Release -GNinja
ninja -j$(nproc)
ninja install

Or with plain CMake:

mkdir build-release && cd build-release
cmake .. -DPG_CONFIG=/path/to/pg/bin/pg_config -DCMAKE_BUILD_TYPE=Release
cmake --build . --target install -j$(nproc)

Release mode enables -O3 -DNDEBUG and disables ORCA internal assertions (GPOS_DEBUG).

Usage

-- 1. Install the extension in the target database.  This 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) Have every new connection to this database
--    auto-load pg_orca.  Per-database scope; no restart, no cluster
--    GUC change, takes effect for subsequent connections:
ALTER DATABASE mydb SET session_preload_libraries = 'pg_orca';

-- 3. Enable ORCA per session (or persistently via
--    ALTER DATABASE mydb SET pg_orca.enable_orca = on):
SET pg_orca.enable_orca = on;

-- 4. Run a query — ORCA optimizes it.
EXPLAIN SELECT * FROM t WHERE id > 100;

Existing sessions are unaffected by step 2 until they reconnect. If ORCA cannot handle a query (unsupported feature or internal error) it falls back to the standard PostgreSQL planner automatically.

Alternative scopes for the preload setting:

-- Cluster-wide (every database, every role):
ALTER SYSTEM SET session_preload_libraries = 'pg_orca';
SELECT pg_reload_conf();

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

ALTER DATABASE ... SET and ALTER SYSTEM SET overwrite the value; if a sibling library was already present (pg_stat_statements, etc.), include it 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;

GUC Parameters

Parameter	Default	Description
pg_orca.enable_orca	off	Enable ORCA optimizer
pg_orca.trace_fallback	off	Log a message on fallback to standard planner
optimizer_segments	1	Number of segments for cost estimation
optimizer_sort_factor	1.0	Cost scaling factor for sort operations
optimizer_metadata_caching	on	Cache relation metadata between calls
optimizer_mdcache_size	16384	Metadata cache size (KB)
optimizer_search_strategy_path	""	Path to custom search strategy XML (empty = built-in)

Testing

See testing.md for the full guide. Quick start:

export PG_CONFIG=/Users/jianghua/pg-install/bin/pg_config
export PG_REGRESS_SQL=/Users/jianghua/code/postgresql/src/test/regress

# pg_orca's own regression tests
test/test.sh --orca-tests

# PostgreSQL standard suite with ORCA loaded (--ignore-plans suppresses plan-shape diffs)
test/test.sh --pg-tests --ignore-plans

Architecture Notes

MPP Stubs

Cloudberry's translation layer references many MPP-only types (Motion, PlanSlice, DynamicSeqScan, etc.). These are stubbed in compat/cdb/cdb_plan_nodes.h so the code compiles. ORCA will never generate these nodes in single-node mode.

GPDB GUCs

Many ORCA configuration knobs were GPDB-specific GUCs. They are re-defined as real GUCs in pg_orca.cpp under the optimizer.* prefix, so existing ORCA code referencing them continues to work.

PostgreSQL 19

PG19 is still a moving target, so treat it as preview support: it builds and passes test/test.sh --orca-tests, but the PG18 path is the one that gets the benchmark and regression mileage. Everything version-dependent is behind #if PG_VERSION_NUM >= 190000:

• planner_hook signature — PG19 passes an extra ExplainState * argument; the hook and both standard_planner() fallback paths are conditionally compiled (pg_orca.cpp).
• RESPECT/IGNORE NULLS window functions (SQL:2003, new in PG19) — supported natively rather than falling back: the null treatment is carried on CScalarWindowFunc and round-tripped through DXL.
• SQL/PGQ GRAPH_TABLE — the PG rewriter expands it into a LATERAL-joined subquery before planner_hook runs, so these queries go through ORCA on the normal LATERAL path. A defensive check falls back cleanly if an RTE_GRAPH_TABLE ever reaches the translator.
• Int8GetDatum() — removed from postgres.h in PG19; gpdbwrappers.cpp supplies a byte-identical #ifndef-guarded replacement so call sites are unchanged on both versions.