cockroachdb / cockroachdb/cockroach

sql/opt: nested views are rebuilt 2^D times during planning and can OOM

Open
#173,725 0 comments 0 reactions 0 assignees View on GitHub
A-sql-optimizer branch-master C-bug O-agent T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

**Describe the problem**

The optbuilder memoizes only a view's AST, not its built relational expression,
so the view body is rebuilt on every reference. A view that references the
previous view twice rebuilds the base view `2^D` times at nesting depth `D`.
Every rebuild mints fresh column IDs, so memo interning cannot collapse the
structurally-identical subtrees — the memo retains `O(2^D)` distinct nodes.

This happens in the build phase, which has no cancellation checks (so
`statement_timeout` does not cover it) and no depth cap; the memory-estimate /
bound-account checks run only post-build. Growth is exponential in `D` while the
SQL text is `O(D)`. Both `CREATE VIEW vN` (which builds its body to validate it)
and `EXPLAIN SELECT ... FROM vN` trigger it. These are plain Go heap allocations
not covered by `--max-sql-memory`.

**To Reproduce**

On a node limited to ~2GB (e.g. `cockroach demo` under a 2GB cgroup, or with
`GOMEMLIMIT=1100000000 --max-sql-memory=512MiB --cache=512MiB`):

```bash
python3 - <<'PY' | cockroach demo --no-example-database --insecure --max-sql-memory=512MiB --cache=512MiB
D = 20
print("CREATE VIEW v0 AS SELECT 1 AS x;")
for i in range(1, D+1):
print(f"CREATE VIEW v{i} AS SELECT a.x FROM v{i-1} AS a, v{i-1} AS b;")
print(f"EXPLAIN SELECT * FROM v{D};")
PY
```

Each view keeps a single output column, so the blowup is purely build fan-out
(`2^D` rebuilds), not column-count growth.

**Observed**

- `D = 18`: completes, ~1.4GB peak RSS.
- `D = 20`: heap grows past 2GB, OOM-killed during planning/build (in the run
above the ceiling was hit while building `v19`/`v20`, before the final
`EXPLAIN`).

The same shape applies to nested inlinable UDFs (each call site rebuilds the
callee body; direct recursion is rejected at `CREATE`, nesting is not).

**Environment**

- CockroachDB `v26.4.0-alpha` (master), CCL, `cockroach demo` single node.
- Client: `cockroach sql`.

**Code reference**

`buildView` in `pkg/sql/opt/optbuilder/select.go` (memoizes AST only; rebuilds
`RelExpr` per reference).

Jira issue: CRDB-67032

Contributor guide

Open the contributing guide

Research direction

Start in pkg/sql/opt/optbuilder/select.go at buildView, then run the supplied Python and cockroach demo reproduction to observe the planning blowup. Done means nested views no longer rebuild their bodies exponentially or exhaust heap memory during CREATE VIEW or EXPLAIN planning.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases
Issue type
Bug
Difficulty
5/5
Estimated time
Over a week
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
38/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.