PgRelationalQuery rebuilds the entire SQL tree on every execute() — relational queries have no build-result cache

[tsushanth]

Performance: PgRelationalQuery.execute() rebuilds the full SQL tree on every call — no caching

Affected: drizzle-orm pg relational API (db.query.<table>.findMany/findFirst)
Severity: performance — ~0.36 ms wasted per execute on a 3-level relation tree regardless of result size
Type: redundant work (the query config and schema are immutable after construction)

---

Benchmark

Stub driver (zero latency) isolates SQL-build overhead:

Schema: users(30 cols) → posts(30 cols) → comments(30 cols), 3-level with-tree

N executes   total_ms   per_exec_ms   verdict
──────────   ────────   ───────────   ───────────────────
10           5.07       0.507         FLAT (no caching)
50           18.66      0.373         FLAT (no caching)
100          35.88      0.359         FLAT (no caching)
500          177.49     0.355         FLAT (no caching)

per_exec_ms stays flat at ~0.36 ms across all N. With a cache, the first call would pay the build cost and subsequent calls would approach 0 ms — the flat line proves no caching is happening.

---

Root cause

PgRelationalQuery.execute() calls _prepare() → _toSQL() → _getQuery() → PgDialect.buildRelationalQueryWithoutPK() on every invocation, even though this.config, this.schema, and this.tableConfig are all set at construction time and never mutate.

buildRelationalQueryWithoutPK (≈ 300 lines in dialect.ts) performs on each call:

1. Object.fromEntries(Object.entries(tableConfig.columns).map(...)) — allocates 2 Proxy layers per column across every table in the with-tree
2. normalizeRelation(...) — scans all relations of the referenced table to find the reverse relation (O(R) per relation, fully static)
3. Constructs hundreds of SQL template-literal objects and chunk arrays
4. sqlToQuery — re-walks the full chunk tree to build the final SQL string

None of this result is stored. A second .execute() of the same query object repeats 100 % of the work.

Calling .getSQL() triggers _getQuery() yet again, so embedding a relational query doubles the build cost.

---

Suggested fix

Cache the built query on the instance after the first call:

class PgRelationalQuery<TResult> {
  #cachedQuery: { query: Query; mapper: ... } | undefined;

  private _prepare() {
    if (this.#cachedQuery) return this.#cachedQuery;
    // ... existing build logic ...
    this.#cachedQuery = { query, mapper };
    return this.#cachedQuery;
  }
}

The cache is safe because config, schema, and tableConfig are readonly after construction. For users who mutate a query object across executes (chaining .where() etc.), each chain returns a new instance so the cached result on the old instance is never stale.

Using .prepare() is a workaround that achieves similar amortization, but requires callers to opt in — the default path should not rebuild unconditionally.

---

Impact

• High-QPS APIs serving relational queries with small result sets pay more build time than driver round-trip time
• Serverless functions (short-lived, many executions of the same query object) waste CPU budget proportional to schema size
• The issue compounds with the proxy-layer overhead and normalizeRelation scan (both also run per-execute)

benchmark confirmed