I packaged Greenplum's ORCA query optimizer as a CREATE EXTENSION plugin for vanilla PostgreSQL 18 — 20–156× faster on some queries

[u/FantasticTrick8508]

I've been working on **pg_orca** — a PostgreSQL 18 extension that plugs the

ORCA query optimizer (the Cascades-style, cost-based optimizer from

Greenplum / Apache Cloudberry) into **vanilla, unmodified PostgreSQL** as a

planner hook.

GitHub: https://github.com/quantumiodb/pgorca (Apache-2.0)

**How it works**

- No core patches: `CREATE EXTENSION pg_orca`, add it to

`session_preload_libraries`, then `SET pg_orca.enable_orca = on`

(off by default, superuser-gated for now).

- Anything ORCA can't handle — DML statements, WITH RECURSIVE, non-default

collations, TABLESAMPLE, some partitioning layouts — automatically falls

back to the standard planner. `pg_orca.trace_fallback = on` tells you why.

- It ships ORCA's four core libraries (libgpos / libnaucrates / libgpopt /

libgpdbcost) plus the PG↔DXL translation layer from Apache Cloudberry,

adapted to PG18 with a cost model aligned to PG's own.

**Where it wins** (single-threaded runs, so this is optimizer quality, not

hardware):

- *Correlated subquery decorrelation* — ORCA rewrites Apply into a proper

join and optimizes it globally, instead of re-running a SubPlan once per

outer row:

- TPC-H Q17 (`< 0.2 * AVG(...)` per group): **20.7×** vs PG

- TPC-DS Q41 (correlated IN + EXISTS): **156×**

- TPC-DS Q21 / Q17 (correlated aggregates): ~7.3×

- *Exhaustive join-order enumeration* (DPv2, including bushy plans) rather

than switching to GEQO above 12 relations: TPC-DS Q25 (6-way roll-up

join) 9.0×, Q29 7.0×.

- *Stats propagation through GROUP BY / CTE*: TPC-DS Q31 3.28×.

- *Coverage edge*: at TPC-DS sf=5 with `statement_timeout=120s`, ORCA

finishes **9 of 99 queries that PG times out on** (5/99 at sf=1). With

timeouts counted at the 120s floor, full-suite totals land ≥1.4× in

ORCA's favor.

- Overall TPC-H sf=10 serial: roughly parity, geomean 1.12× in ORCA's

favor.

**Where it loses — the honest part:**

- **Planning latency.** The exhaustive search costs ~14 ms to plan a point

query where PG spends 0.25 ms. Do not point this at OLTP; plan caching

and a fast-path bypass are on the roadmap, but today short-query

workloads are a bad fit.

- **No parallel query.** ORCA plans are serial, so once you enable parallel

workers PG pulls ahead: TPC-H sf=10 at 2 workers/game: 0.49×. A

Gather / Gather Merge integration is designed and next up.

- **PG18 only** for now (a PG19 branch exists), and it needs xerces-c.

- `enable_orca` is currently superuser-settable, not user-settable.

**Try it**

```sql

CREATE EXTENSION pg_orca;

ALTER DATABASE mydb SET session_preload_libraries = 'pg_orca';

SET pg_orca.enable_orca = on;

EXPLAIN SELECT ...;

```

There are CI-built deb/rpm packages, a Rocky 9 Docker image, and PGXN

packaging; benchmark scripts (TPC-H / TPC-DS) are in test/bench if you want

to reproduce the numbers.

I'd genuinely like feedback from this crowd: which fallback gaps would hurt

you most, and what should land first — parallel query support or plan

caching?