Performance analysis: query-pipeline hotspots — exponential compile on [0..1] navigation chains, quadratic mapping compile, union pure-to-SQL; CI perf-canary proposal

> Research/analysis only — no code changes proposed here. Produced with a standalone local harness that drives the engine's own entry points (`PureGrammarParser` → `Compiler.compile` → `PlanGenerator.generateExecutionPlanAsPure` → `bindPlan` → `serializeToJSON` → `PlanExecutor` vs H2) with per-phase timing, plus async-profiler wall-clock profiles demangled back to Pure function FQNs. Harness, demangler, and raw CSVs are small standalone files — happy to share or contribute them as part of the §7 CI proposal. Comments and corrections welcome, especially on the open questions in §5.

**Context.** Users report that "simple" queries take 10–20 seconds through the platform while the underlying database answers in under a second. This document identifies where that time goes across the pipeline — grammar parse → compile → plan generation (routing, clustering, pure-to-SQL, SQL-to-string) → execution — and ranks the areas by expected payoff. It is analysis only; remediation is deliberately out of scope.

**TL;DR.** For single-set mapping shapes, per-request cost is *not* in plan generation — routing, clustering, pure-to-SQL and SQL-to-string together stay in the tens of milliseconds even against a 1,000-class model. The dominant costs are:

1. **Exponential compile blow-up on deep chains of optional (`[0..1]`/to-many) property navigations** — a filter+project navigating a 22-deep chain takes **18 seconds to compile** on a 30-class model, on a warm JVM — and the separate lambda-build step pays the same 18s again; the identical chain with `[1]` multiplicities compiles in ~13ms. This alone reproduces the 10–20s complaint. Milestoning steepens it further. (H1)
2. **Quadratic compile cost in relational mapping validation and table resolution** — `classMappingById` linear scans per property mapping, `findRelation` linear scans per column reference. (H2, H3)
3. **Parse + compile dominate every warm request** (92% of warm pipeline time at scale 1,000): if the platform re-parses/re-compiles the model per request, the biggest architectural lever is not making any phase faster but not re-running them. (H4)
4. **Cold-start pays ~2.5–3.5s once per JVM** — classloading/JIT of the generated Pure codebase, plus first-use construction of DbExtension/DbConfig, extension lists, and platform-binding conventions. (H5)
5. **Pure-to-SQL is quadratic in union set-implementations** — a class union-mapped over 40 sets pays ~1s of plan generation per query (warm); ~52% of it in `buildUniqueName`/`reprocessJoinTreeNode` inside `pureToSQLQuery.pure`. Union-heavy federated mappings are the one measured case where plan generation itself is the problem. (H6)
6. **A second sweep over complex mapping shapes** (includes, milestoning, ModelJoin/Relation-`~func`, graphFetch, aggregation-aware, polymorphic, M2M-over-relational) found one new linear cost — Relation `~func` mappings compile ~10× slower per class (H7) — and otherwise confirmed plan generation behaves (§2.5); milestoning is a 2× multiplier on pure-to-SQL, and graphFetch plans are ~60× larger with real Java-binding cost. (H7, H8)
7. **A third round on semi-structured/Variant access cleared it**: flat in JSON-path depth, linear in access width, and the combined union×join×semi cell is purely additive — H6 is join-tree-driven, not amplified by semi-structured sharing. Variant functions are benign on the relation path and simply unroutable on the classic mapping path (a functionality gap, not a perf one). Side finding: ~30% of plan-gen on these shapes is H2's dialect-translation conversion (`toPostgresModel`), an overhead no other dialect pays. (§2.6)
8. **A fourth round ran the dialect matrix locally** (H2 vs DuckDB vs Snowflake SQL generation — no cloud connection needed for plan-gen): no new cloud-dialect hotspot; cloud dialects are consistently *faster* than H2, whose dialect-translation tax measures +30–80%; the H6 union quadratic is dialect-independent; Snowflake's `aliasLimit` post-processor adds nothing measurable. §7 proposes a CI job built on scaling-ratio canaries to keep all of this locked. (§2.7, §7)

---

## 1. Methodology

### 1.1 Harness

A standalone driver (`PipelineBench.java`, kept outside the repo) replicates exactly what the engine does per request, using the same entry points as `AlloyTestServer` and the HTTP `Execute` resource, but with each stage timed independently:

| Phase | Entry point |
|---|---|
| parse | `PureGrammarParser.newInstance().parseModel(grammar)` |
| compile | `Compiler.compile(pmcd, …)` → `new PureModel(...)` (multi-pass graph build + validation) |
| lambda | `HelperValueSpecificationBuilder.buildLambda(...)` |
| extensions | `relationalExtensionsWithLegendJavaPlatformBinding(...)` (Pure extension list) |
| planPure | `PlanGenerator.generateExecutionPlanAsPure(...)` — preeval, routing, clustering, pure-to-SQL, SQL-to-string, all in compiled Pure |
| javaBind | `PlanPlatform.JAVA.bindPlan(...)` — Java platform binding |
| serialize | `PlanGenerator.serializeToJSON(...)` — protocol transform + Pure `toJSON` |
| deserialize | `PlanGenerator.stringToPlan(...)` — Jackson |
| execute | `PlanExecutor.execute(...)` against in-memory H2 (baseline "the DB itself") |

Workload: synthetic model generated at scale *N* — *N* classes (5 primitive properties each + a `next` association-style property), *N* tables, *N−1* joins, a full relational mapping, H2 LocalH2 runtime. Query shapes: `simple` (filter + 3-column project), `joinK` (project navigating a K-deep chain), `projW` (W-column project), `aggK` (groupBy with K aggregates). 12 iterations per cell in one JVM: iteration 0 = cold, medians of the rest = warm steady state.

Environment: Apple M-series (arm64), Temurin 17, `-Xmx8g -Xss8m`, engine @ `master` 172cf31b4ad (4.141.1-SNAPSHOT), relational extension set only.

### 1.2 Profiling and Pure attribution

- **async-profiler 4.5, wall-clock mode, 5ms sampling, per-thread** — main-thread stacks only (wall-clock matters here: JFR's CPU sampler under-reports classloading/lock/IO waits).
- Plan generation executes as **Java code generated from Pure source**, so raw profiles show frames like `org.finos.legend.pure.generated.core_pure_router_router_main.Root_meta_pure_router_routeFunction_…`. A demangler script post-processes collapsed stacks back to **Pure function FQNs** (`meta::pure::router::routeFunction`) and their `.pure` source class, then re-aggregates self/inclusive time per Pure function. All findings below are stated in Pure terms where the hotspot is Pure code. See §6 for the mechanism and how to keep it.

---

## 2. Results

**How to read every number in this section.** Unless a column header says otherwise, a number is the **median wall-clock duration of one pipeline phase, in milliseconds**, taken over the warm iterations of a run (each run executes the full pipeline 8–12 times in one JVM; iteration 0 is "cold", the median of the remaining iterations is "warm"). Plan-size columns are the size of the generated plan JSON in kilobytes (KB). All runs on the same machine (Apple M-series, Temurin 17 — see §1.1), so numbers are comparable across tables; ratios and scaling shapes matter more than absolute values.

**"Scale N" is the size of the synthetic model the query runs against**: N classes (5 primitive properties each, plus a `next` property to the following class), N tables, N−1 joins, and a mapping with all N classes fully mapped — so scale 1000 approximates a large production workspace by element count. The query itself stays fixed (e.g. `simple` = one filter + 3-column project on `C0`); scale isolates how each phase reacts to *model* size, independent of query complexity. Query-shape knobs (`joinK`, `projW`, union K, …) are varied separately in §2.3 onward, usually at a small fixed scale so model-size cost doesn't drown the query-shape signal.

### 2.1 Warm per-request cost vs model scale — phase medians in ms (`simple` query)

| Phase | scale 10 | scale 100 | scale 500 | scale 1000 | scaling |
|---|---|---|---|---|---|
| parse | 4 | 13 | 59 | 116 | linear in model size |
| compile | 8 | 11 | 36 | 84 | linear-ish (superlinear terms exist, §H2/H3) |
| lambda | 0 | 0 | 0 | 0 | flat |
| extensions | 0 | 0 | 0 | 0 | flat (warm) |
| planPure | 11 | 10 | 12 | 12 | **flat — independent of model size** |
| javaBind | 4 | 4 | 4 | 4 | flat |
| serialize | 1 | 1 | 1 | 1 | flat |
| deserialize | 4 | 3 | 4 | 4 | flat |
| execute (H2) | 6 | 6 | 6 | 6 | flat |

Warm total at scale 1,000 ≈ **230ms**, of which parse+compile = 200ms (~92% excluding execution). Plan generation (planPure+javaBind+serialize+deserialize) ≈ 21ms.

### 2.2 Cold vs warm, first JVM iteration — phase times in ms (scale 1000)

| Phase | cold (ms) | warm (ms) |
|---|---|---|
| parse | ~490 | 116 |
| compile | ~1330 | 84 |
| extensions | ~285 | 0 |
| planPure | ~980 | 12 |
| javaBind | ~315 | 4 |
| serialize | ~150 | 1 |
| **total pipeline** | **~3550** | **~220** |

Wall-clock profile of the cold run (main thread ≈ 4.1s): dominated by **classloading and JIT of the generated Pure codebase** — JAR inflation (`Inflater`, `ZipFile`), class parse/verify (`ClassFileParser`, `ClassVerifier`, `SymbolTable`), JIT code-cache writes (`pthread_jit_write_protect_np`, ~20% of main-thread samples), and Pure binary metadata reads (`StreamBinaryReader`). Within cold planPure, real one-time Pure work is visible: `meta::relational::functions::sqlQueryToString::createDbConfig` / `loadDbExtension` / `createDbExtensionForH2` (per-dialect DbExtension registry construction), `getContextBasedSupportedFunctions` (pure-to-SQL dispatch table), and `engineConventions` (Java platform-binding conventions).

### 2.3 Query complexity — warm phase medians in ms (scale 100)

| Query | compile (ms) | planPure (ms) | plan JSON (KB) |
|---|---|---|---|
| join5 | 12 | 24 | 2 |
| join10 | 15 | 34 | 3 |
| **join20** | **4832** | 60 | 5 |
| proj20 | 11 | 17 | 4 |
| proj50 | 17 | 32 | 8 |
| proj100 | 12 | 39 | 14 |
| agg10 | 11 | 17 | 3 |
| agg30 | 11 | 24 | 5 |

planPure scales gently and ~linearly with query size (~2–3ms per join level, ~0.3ms per projected column). Compile does not: the join20 row is not a fluke — it is the H1 exponential (×1.8 per navigation level, so join20 ≈ 2¹⁰× join10’s work) caught mid-jump; the cell is a stable median (<2% spread across iterations), §2.4 fills in the curve between 10 and 20, and flipping the chain’s multiplicity to `[1]` collapses it to ~13ms.

### 2.4 The compile cliff — warm phase medians in ms (scale 30, a tiny model)

| join depth | compile (ms) | buildLambda (ms) | planPure (ms) |
|---|---|---|---|
| 12 | 33 | ~30 | 60 |
| 14 | 94 | ~90 | 58 |
| 16 | 300 | 277 | 62 |
| 18 | 1,184 | 1,140 | 69 |
| **22** | **18,051** | **17,900** | 80 |

Growth ≈ ×1.8 **per navigation level** — exponential, not polynomial. And it is paid **twice per run in this harness**: once compiling the model's query function, and again in the separate `buildLambda` call, which re-builds the query lambda from protocol through the same code path (the columns track each other exactly). On the production execute path the lambda arrives in the request and is built at least once per request — services whose function is also part of the compiled model pay it on both sides. A user-visible "simple query" (one filter, one project) whose lambda navigates a deep chain hits 10–20s+ on a warm server, regardless of model size. Deep chains arise naturally from Studio-generated projections over nested associations and from milestoned/structured models.

Milestoning stacks on top: at depth 16, a business-temporal chain measures compile 429 ms / buildLambda 394 ms / planPure 227 ms vs the plain chain's 300 / 277 / 62 — the cliff is ~1.4× steeper and pure-to-SQL a further ~3.5× at that depth.

### 2.5 Second round — complex mapping shapes

A follow-up sweep covered the mapping features the first round deliberately left out:

| Workload | compile (ms) | planPure (ms) | javaBind (ms) | plan size (KB) | verdict |
|---|---|---|---|---|---|
| include chains, 5→40 mappings deep (scale 200) | 19 | 18 | 4 | 2 | **flat — includes are free** |
| milestoned chain, join2→join10 (scale 30) | 9→16 | 20→84 | 4 | 2–5 | planPure ≈ **2× non-milestoned** at every depth, linear |
| plain chain control, join2→join10 | 9→17 | 15→40 | 4 | 2–3 | baseline |
| graphFetch→serialize, tree depth 1→6 (scale 30) | 8 | 22→61 | **25→59** | **87→314** | all phases linear in tree size; plans ~60× TDS size |
| Relation `~func` + ModelJoin chain, 1→6 hops (scale 30) | ~33 | 18→57 | 4 | 2–5 | linear, ~2× classic-join slope; **no H1 cliff** (association ends are `[1]`) |
| Relation `~func` mappings, scale 10→500, simple query | **21→505** | 15 | 4 | 2 | compile ≈ **1 ms/class — ~10× classic relational mappings**, mildly superlinear |
| EMIT `relation-modelJoin-chained` (2-hop) | 13–23 | 20–29 | 4 | — | unremarkable |
| EMIT `relational-aggregation-aware` (groupBy hits agg table) | 9–13 | 12–16 | 4 | — | unremarkable |
| EMIT `relational-polymorphic-query` (`subType()` projection) | 8–14 | 15–19 | 4 | — | unremarkable |
| M2M view over relational (m2m2r, graphFetch) | 9–14 | 16–20 | 23–32 | 55 | javaBind is the larger half, as with all graphFetch |

Notable attributions:

- **Relation `~func` mapping compile cost** — profiled at scale 200: 67% of the mapping compile pass sits in `ClassMappingSecondPassBuilder.compileRelationPropertyLambda`, which compiles a fresh lambda (full function-expression inference + `RelationType` construction) for **every mapped column**. ~0.9ms per class — ~10× the per-class cost of classic relational mappings — and mildly superlinear (75 → 182 → 505 ms at 100 → 200 → 500 classes; ×2.4–2.8 per ×2–2.5 scale, consistent with the H2/H3 quadratic terms starting to show). A 5,000-class model in the new Relation mapping style would pay ≥5s compile where classic mappings pay ~0.4s.
- **Milestoning** doubles pure-to-SQL work per navigation at shallow depths, and at cliff depths compounds with H1: depth-16 milestoned = 429 ms compile / 394 ms buildLambda / 227 ms planPure vs plain 300 / 277 / 62 (§2.4).
- **graphFetch plans are a different regime**: 87–314 KB of plan JSON (vs 2–5 KB for TDS), with the Java platform binder doing real in-memory codegen (`javaBind` ≈ `planPure`). Services are typically graphFetch-shaped, so service plan generation costs ~2–4× ad-hoc TDS queries — still tens of ms warm, but the cold first generation pays ~0.5s in javaBind alone.

### 2.6 Third round — semi-structured (Binding / SEMISTRUCTURED) access

The one axis §2.5 left open, and the one the repo's own docs flag as the union blow-up's historical partner (the DAG-not-tree warning in `router-and-pure-to-sql.md` §4.6). Workload: `C0.det` mapped `Binding : [DB]T0.sdet` over a `SEMISTRUCTURED` column, with a JSON model nested D levels deep (`det.child.child…`, links `[1]` deliberately, so the H1 `[0..1]` cliff cannot contaminate the plan-gen measurement):

| Workload (scale 10) | compile (ms) | planPure (ms) | plan size (KB) | verdict |
|---|---|---|---|---|
| path depth 2→14 (`semi2`→`semi14`) | 7–8 | 8→12 | 2 | **flat** — depth is free |
| access width 5→40 columns (`semiw5`→`semiw40`) | 8–9 | 14→46 | 3→15 | linear, ~1 ms/column |
| union 5/10/20 × 10 semi columns | 8–9 | 28/39/70 | 6→18 | grows with union K only |
| union 10 × depth-8 path | 8 | 26 | 5 | unremarkable |
| **union 20 × join2 × 10 semi columns** | 8 | **221** | — | vs union20×join2 alone = **201** — semi adds its standalone ~20 ms, **purely additive** |

**Verdict: semi-structured access is not a hotspot on this path — including the §4.6 scenario.** Depth is flat, width is linear, and the combined union × join-navigation × semi-structured cell (the documented shared-operand DAG shape) is purely additive: 221 ms vs 201 ms for the union×join control, the difference being the semi columns' standalone cost. The profile of union20×semiw10 confirms why: plan-gen time is dominated by SQL-to-string (`meta::relational::mapping::toSqlString`, 57% inclusive), and the union join-tree machinery (`buildUniqueName`/`reprocessJoinTreeNode`, the H6 drivers) only engages for join navigation — semi-structured sharing does not multiply through it.

Two observations worth keeping:

- **H2's dialect-translation tax**: ~30% of plan-gen for this shape is `meta::relational::functions::toPostgresModel::convertSqlQuery` — the SelectSQLQuery→Postgres-model AST conversion that only the H2 dialect performs (`useDialectTranslation`; every other dialect renders via the legacy dynaFunc dispatch). A real but H2-only overhead — it does not exist on production cloud dialects, and conversely these H2 numbers slightly *overstate* SQL-to-string for other dialects.
- **Variant functions, both paths probed.** Through the relation path (`#>{test::DB.T0}#->extend(~v:r|$r.p0->toOne()->fromJson()->get('k')->to(@String))`) Variant plans generate fine on H2: warm planPure 9–14 ms — benign. Through the **classic mapping path**, Variant functions compile but **fail at routing** (`router_routing.pure:584 — "Error mapping not found for class"`): the store-mapping router has no support for them, so classic-mapped queries simply cannot use Variant today — a functionality gap rather than a performance one.
- **Scope**: the historical exponential in this area (`reAliasColumnName::replace` re-materializing shared DAG nodes per reaching path) fires only on `aliasLimit` dialects (e.g. Snowflake) and is already identity-memoized (§4.6 of the router doc). Round 4 (§2.7) exercised both the Snowflake `aliasLimit` path and Variant on DuckDB/Snowflake dialects locally — all benign.

### 2.7 Fourth round — cloud dialects (DuckDB and Snowflake SQL generation, locally)

Plan generation is executable locally for *any* dialect — SQL-to-string needs no database connection — so the same workloads ran against the DuckDB and Snowflake dialects (DuckDB/Snowflake grammar+pure modules added to the classpath; Snowflake connection declared but never opened). Cells are warm `planPure` medians:

| Workload | H2 (ms) | DuckDB (ms) | Snowflake (ms) | reading |
|---|---|---|---|---|
| simple (scale 10) | 11 | 6 | 6 | H2 pays ~2× on small plans |
| join10 (scale 30) | 41 | 32 | 34 | tax shrinks as pure-to-SQL dominates |
| union20 × join2 | **195** | **125** | **155** | **H6 is dialect-independent** — it lives in `pureToSqlQuery`, before any dialect renders |
| 10 semi-structured columns | 21 | 12 | 12 | semi lowering cheap everywhere |
| 30 × 390-char aliases | 22 | 15 | 14 | Snowflake's `aliasLimit`/`trimColumnName` post-processor **verifiably fires** (generated SQL carries aliases trimmed to ≤255 chars with `_N` suffixes, per `aliasLimit = 255` in `snowflakeExtension.pure:48`) and adds **nothing measurable** — the §4.6 identity-memoization holds |
| Variant via relation | 9 | 5 | 5 | Variant benign on all three |

Conclusions:

- **No new cloud-dialect hotspot.** Snowflake and DuckDB SQL generation are consistently *faster* than H2's.
- **H2's dialect-translation tax quantified**: +30–80% on planPure across every shape (the `toPostgresModel` conversion from §2.6). Rounds 1–3's H2-measured plan-gen numbers are therefore mild *overestimates* for production dialects.
- **H6 confirmed dialect-independent**: the union quadratic costs 125–195ms at K=20 on every dialect — fixing it in `pureToSQLQuery.pure` pays everywhere at once.
- Curiosity, not a cost: Snowflake's union plan JSON is ~3× larger (62KB vs 20KB) from more verbose SQL text; serialization stays ~1–2ms.

Still genuinely out of local reach: cloud-dialect *execution* characteristics (network, warehouse spin-up) — different problem, different tooling.

---

## 3. Hotspots, ranked by bang-for-buck

### H1 — Exponential lambda compilation on deep property-navigation chains
**Where:** engine compiler (`ValueSpecificationBuilder` path), attribution below.
**Evidence:** §2.4 — ×1.8 per navigation level; 18s at depth 22 on a 30-class model.
**Impact:** turns seconds-to-tens-of-seconds into milliseconds for the worst-affected real queries; this is the only measured effect that reproduces the complaint at full size on a warm server.
**Attribution (wall profile @ depth 18, main thread):** the cost is in protocol→graph expression building with type inference, not in the Pure graph or plan generation. Stacks reach 400+ frames of mutual recursion cycling through:

```
ValueSpecificationBuilder.visit
→ HelperValueSpecificationBuilder.processProperty
→ CompileContext.buildFunctionExpression
→ Handlers.buildFunctionExpression → UnifiedInferenceFunctionExpressionBuilder
→ Handlers static inference lambdas ("update"/"apply" pairs)
→ ValueSpecificationBuilder.visit (re-processes the navigation prefix)
```

Hot leaves are type lookups: `PureModel.getClass`, `getType_safe`, `CompiledProcessorSupport.getClassifier`, `C3Linearization`.

**Trigger isolated — optional multiplicity.** The same depth-18 chain with `next: C[1]` compiles in **13ms**; with `next: C[0..1]` it takes **1,184ms** (~90×).

**Mechanism confirmed in code** (`HelperValueSpecificationBuilder.processProperty`):
1. `HelperValueSpecificationBuilder.java:186` — compiles all parameters, *including the whole navigation prefix*, to infer the source's type and multiplicity.
2. `:284` — if the inferred source multiplicity is not `PureOne`, takes the auto-map branch.
3. `:313-315` — constructs a **protocol-level** `map(, automapLambda)` and calls `context.buildFunctionExpression("map", …)`, which compiles the raw prefix **again from scratch**; the compiled prefix from step 1 is discarded on this path.

Each `[0..1]`/to-many level therefore compiles its prefix twice: T(d) = 2·T(d−1) ⇒ 2^d, matching the measured ×1.8–2 per level. Since real Legend models use optional/to-many associations pervasively (and milestoning adds generated qualified properties on top), moderately deep navigations in Studio-generated projections hit this in practice.
**Fix shape (for a later session):** reuse the already-compiled source from step 1 when building the auto-map expression (inject the compiled `ValueSpecification` instead of the raw protocol node), or memoize protocol-node → compiled-result in `ProcessingContext`.

### H2 — Quadratic mapping validation: `classMappingById` per property mapping
**Where:** `RelationalValidator.validateRelationalPropertyMapping` (`legend-engine-xt-relationalStore-grammar/.../validation/RelationalValidator.java:146`) calls Pure `meta::pure::mapping::classMappingById` → `classMappingByIdRecursive` (`platform_dsl_mapping`, upstream legend-pure), which `select`s over **all** class mappings with Pure equality, per property mapping validated.
**Evidence:** warm wall profile @ scale 1000: 12.3% of *total* main-thread time (≈⅓ of the compile phase) is inside `classMappingById(Recursive)`; leaf = `CompiledSupport.equal` under `select`.
**Fix shape (for a later session):** index class mappings by id once per mapping (a `Map`), or memoize in the validator loop. Low-risk, pure win at model scale.

### H3 — Quadratic table/join resolution in relational compilation
**Where:** `HelperRelationalBuilder.findRelation` / `getRelationSilent` (`HelperRelationalBuilder.java:255-317`) — every column/table reference resolves via `detect` (linear scan) over all tables, then views, then tabular functions, across all included databases; same pattern for `findJoin`.
**Evidence:** warm profile @ scale 1000: ~5% of total main-thread time under `processRelationalPropertyMapping → getRelation → findRelation → detect`. O(tables × mapped-columns) overall.
**Fix shape:** name→relation index per schema/database, built once per compile.

### H4 — Parse + compile run on every request at all
**Where:** architectural — the pipeline re-parses and re-compiles the full model per request; at scale 1000 that is 92% of the warm request (parse 58% of main-thread samples — almost all ANTLR: `Lexer.emit`, `ParserRuleContext.getRuleContexts`, token-stream materialization; compile 34%).
**Evidence:** §2.1; both phases scale linearly with model size, so real deployments with 5–20k-element models pay 0.5–2s+ per request before plan generation even starts.
**Fix shape:** cache the compiled `PureModel` keyed by model hash (the SDLC/metadata pointer already provides identity); or cache parse output (`PureModelContextData`). Open question for the team: which production deployments already cache, and where the cache misses are — worth confirming against production telemetry before investing (see §5).

### H5 — Cold-start / first-request cost (~2.5–3.5s + H2/JIT ramp)
**Where:** JVM classloading + JIT of ~15k generated Pure classes; first-use construction of the DbExtension registry (`createDbConfig`/`loadDbExtension` — Pure reflection over `<>` stereotypes), pure-to-SQL dispatch tables (`getContextBasedSupportedFunctions`), extension lists, platform-binding conventions.
**Evidence:** §2.2 cold profile.
**Impact:** every freshly started/scaled pod serves its first queries 3–5s slower; in dev/ephemeral setups this reads as "the platform is slow". Fix shapes: AppCDS/CRaC or warmup requests at startup; memoize DbExtension registry per DatabaseType (it is rebuilt per plan-generation today — visible warm too, just small).

### H6 — Pure-to-SQL is quadratic in union set-implementations
**Where:** `meta::relational::functions::pureToSqlQuery::union::buildSQLQueryOutManySetImplementations` in `pureToSQLQuery.pure` — specifically `buildUniqueName` (**52% inclusive** of plan-gen time in the union profile) and `buildUnionJoin → reprocessJoinTreeNode` (**50%**), which re-process join trees and rebuild alias names per set implementation over structures that grow with set count. A further ~20% is SQL-to-string over the K-fold-larger AST (`toSqlString` → `toPostgresModel::convertSqlQuery` → dialect translation).
**Evidence:** class mapped as an Operation union over K identical relational sets, 2-join query, warm medians for planPure: K=2 → 25ms, 5 → 39ms, 10 → 67ms, 20 → 227ms, 40 → **970ms** (~×4 per doubling ⇒ O(K²)); deeper navigation multiplies it further (join5 through K=20: 341ms vs join2's 227ms). Extrapolated: a federated model unioning ~100 sources spends ~6s in pure-to-SQL per query, warm.
**Fix shape:** memoize `reprocessJoinTreeNode` by node identity (the same DAG-vs-tree lesson already documented for `reAliasColumnName` in `docs/engineering/architecture/router-and-pure-to-sql.md` §4.6) and hoist `buildUniqueName` string construction out of the per-set recursion.

### H7 — Relation `~func` mapping compile: per-column lambda compilation
**Where:** `ClassMappingSecondPassBuilder.compileRelationPropertyLambda` (engine compiler) — 67% of the mapping compile pass for Relation-style mappings; a full lambda build with inference per mapped column.
**Evidence:** §2.5 — ~0.9ms/class vs ~0.08ms/class for classic relational mappings (10×), linear in model size. Matters as models migrate to the new Relation mapping style.
**Fix shape:** batch or cache the per-column lambda compilation (the columns of one class mapping share the same relation type and inference context).

### H8 — Plan generation for the remaining measured shapes is *not* a hotspot
**Evidence:** flat ~10–20ms warm across model scales (10 → 1000 classes); linear in query size (~2–3ms per join level, ~0.3ms per projected column); largest warm inclusive Pure functions are `executionPlan` (4%), `generateExecutionNodeFromCluster` (2.9%), `planExecution` (2.8%), `toSqlString` (1.2%), `routeFunction` (1.0%). The second-round sweep (§2.5) adds: mapping includes free; milestoning a 2× linear multiplier; ModelJoin/relation-func linear; aggregation-aware, polymorphic `subType`, and M2M-over-relational unremarkable; graphFetch linear in tree size with real but bounded javaBind cost.
**Scope caveat:** measured with the relational extension set only (production routing sweeps every registered StoreContract's `supports`). Semi-structured Binding access is measured and benign (§2.6); DuckDB and Snowflake dialect SQL generation, the `aliasLimit` post-processor, and Variant across dialects are measured and benign (§2.7). Remaining unmeasured: very large real production mappings, and cloud *execution* characteristics (network/warehouse), which need different tooling.

---

## 4. What the harness deliberately did not measure

The production request path adds, on top of everything above: HTTP + Jackson deserialization of `ExecuteInput`, `ModelManager` "Load Model" (fetching the model from SDLC/metadata services — network-bound and potentially seconds on its own), authorization (`Authorize Plan Execution` span), the full extension/StoreContract set, and result serialization back to the client. If production telemetry shows 10–20s on queries *without* deep navigation chains, model load + parse/compile per request (H4) plus metadata fetch are the primary suspects, and the existing OpenTracing spans (`Load Model`, `Generate Plan`) can already split that coarsely.

## 5. Open questions for the team

1. Do production engine deployments cache compiled `PureModel`s across requests (per project/workspace revision)? If not, H4 dominates everything else at real model sizes.
2. What are real model sizes (elements, classes, mappings) in the affected deployments? The linear parse/compile curves here let you extrapolate directly.
3. Do the slow queries navigate deep association/milestoning chains (H1)? A single sample of slow-query lambdas would confirm.

## 6. Pure-code introspection mechanism (proposed to keep)

Plan generation and pure-to-SQL run as Java generated from Pure, so JVM profilers only ever show mangled generated frames. This analysis used a small demangler over async-profiler collapsed output:

- `org/finos/legend/pure/generated/.Root_meta___` → `meta::::…::` + originating `.pure` source class;
- re-aggregation produces self/inclusive-time tables **per Pure function** and a Pure-level flamegraph.

Recommendations:
1. **Adopt the demangler as a repo tool** (e.g. `docs/engineering/tools/` or a small module) so any engineer can turn a JFR/async-profiler capture into Pure-attributed output.
2. **Add `traceSpan` phase boundaries in Pure** around routing, clustering, per-cluster `planExecution`, `toSQLQuery`, and `sqlQueryToString` (today only preeval has a span, `'preval'` in `preeval.pure`). This gives per-request phase timing in production via the existing OpenTracing plumbing with negligible overhead, closing the observability gap that made this analysis require a custom harness.
3. Longer-term: emit a Pure→Java symbol map at codegen time (function FQN → generated class/method) so tooling does not depend on name-mangling heuristics.

## 7. Proposal — a CI job that flags performance deviations

The regressions this analysis found are of two kinds, and they need different guards:

1. **Complexity-class regressions** (the ones that hurt): an algorithm silently becomes exponential (H1) or quadratic (H2/H3/H6) in some dimension. These produce 10×–1000× blowups at realistic sizes.
2. **Scalar regressions**: a phase gets uniformly slower (extra pass, lost cache, heavier serialization). These produce 1.2×–3× drift.

### 7.1 Design principle: scaling ratios, not stopwatch numbers

Shared CI runners (GitHub Actions) have 2–3× run-to-run variance — absolute thresholds either false-alarm constantly or get widened until useless. But a **ratio between two workloads measured in the same JVM on the same runner** cancels machine speed entirely. The cliff canaries from this analysis translate directly:

| Canary (same JVM, warm medians) | Healthy today | Fail if | Guards |
|---|---|---|---|
| `compile(join16) / compile(join8)` | ~27 (exp. cliff already present) | > 60 | H1 worsening; after an H1 fix, re-baseline to ~2 and guard against reintroduction |
| `compile(scale 1000) / compile(scale 100)` | ~8 | > 20 | H2/H3 quadratic terms growing |
| `planPure(union40) / planPure(union10)` | ~14 | > 30 | H6 worsening beyond O(K²) |
| `planPure(join10) / planPure(join2)` | ~2.7 | > 8 | pure-to-SQL superlinearity appearing |
| `compile(relfunc 200) / compile(relfunc 50)` | ~4 | > 10 | H7 going superlinear |
| `javaBind(graph4) / javaBind(graph1)` | ~2 | > 8 | platform-binder blowup |
| `planPure(semijoin10 u20) − planPure(join2 u20)` | ~+20 ms (additive) | superadditive ×3 | semi-structured amplification appearing |

Scalar drift still deserves a softer signal: warm-median absolute times per phase, compared against a **rolling baseline** (median of the last N nightly runs on the same runner class), warn at +30%, fail at +100%.

### 7.2 Mechanics

- **Promote the harness into the repo** as a small test-scope module (e.g. `legend-engine-config/legend-engine-perf-benchmark`), depending on `legend-engine-xt-relationalStore-executionPlan` (+ duckdb/snowflake modules for the dialect cells). Plain JVM harness with warmup + ≥10 iterations and median reporting is adequate at these magnitudes (10 ms–18 s); JMH adds rigor only below ~1 ms and complicates the phase decomposition.
- **Output**: one `perf-results.json` per run — `{workload, phase, median_ms, iterations, cold_ms}` plus the computed canary ratios; uploaded as a build artifact and appended to a history branch (same pattern as the PCT report pipeline, which already copies `target/pct` to the docs site).
- **Schedule**: nightly on `master` (full matrix, ~5 min of measurement after a cached build) + on-demand on PRs via a `perf-check` label — not on every PR (the module build dominates; use the CI build cache).
- **Flagging**: canary-ratio violations fail the check outright (they are noise-immune); scalar-drift warnings post a PR/commit comment with the per-phase delta table. On any failure, the job re-runs the offending workload under async-profiler (wall, collapsed; on GitHub-hosted runners `perf_events` is typically restricted, so use `ctimer`/`itimer` mode), demangles frames to Pure FQNs with the demangler from §6, and attaches the top-20 table to the check output — so the developer sees *which Pure function* regressed, not just that something did.
- **Baseline policy**: `perf-baseline.json` is checked in and updated by PR only — an intentional perf change (or fix) updates the expected ratios in the same PR that changes the code, reviewed like any other diff.
- **Cold-start guard**: one dedicated cell measures iteration-0 total on a fresh JVM (guards H5); it is the noisiest number, so warn-only with a generous +100% bar against the rolling baseline.

### 7.3 What this catches that PCT/unit tests cannot

PCT locks *behavior* across stores; nothing today locks *cost*. None of the costs in this document (H1, H6, H7, the historical `reAliasColumnName` DAG blowup) is guarded by any test today. Each would trip a canary ratio the night it appeared.

---

## Appendix A — Reproduction

- The harness is one Java file (`PipelineBench.java`, ~600 lines) plus a run script that compiles it against `legend-engine-xt-relationalStore-executionPlan`'s test classpath (`mvn dependency:build-classpath`), and a small Python demangler for async-profiler collapsed stacks. Not yet in the repo — shareable on request, and the natural seed for the CI module proposed in §7.
- Workload knobs: model scale N; query shapes `joinK`/`projW`/`aggK`/`graphK`/`semiK`/`semiwK`/`semijoinK`/`longprojW`/`variantrel`; mapping-shape flags for `[0..1]` vs `[1]` chain multiplicity (the H1 trigger toggle), Operation unions over K sets (H6), K-deep mapping-include chains, business-temporal milestoning, Relation `~func` + ModelJoin mappings (H7), M2M-over-relational via ModelChainConnection, SEMISTRUCTURED column + JSON Binding, and target dialect (`H2`/`DuckDB`/`Snowflake`).
- Environment: Apple M-series (arm64), Temurin 17, engine @ `master` 172cf31b4ad (4.141.1-SNAPSHOT). Absolute numbers are machine-specific; the scaling shapes and ratios are the findings.
- Classpath gotcha for dialect runs: `duckdb-execution` does not depend on `duckdb-grammar` (the connection-spec compiler extension) nor `duckdb-pure`/`duckdb-sqlDialectTranslation-pure` — all three must be added explicitly.

Start by reading the measured pipeline entry points: PureGrammarParser, Compiler.compile, PlanGenerator.generateExecutionPlanAsPure, bindPlan, serializeToJSON, and PlanExecutor. Review the reported join-depth, mapping-resolution, and union timings, then inspect the proposed §7 scaling-ratio CI canaries. Done means the analysis is validated or corrected and the CI proposal's scope and acceptance criteria are settled.

Written by the indexing model from the issue text.