sql/opt: inverted SpanExpression AND-chain uses O(K^2) heap during planning

[yuzefovich]

Describe the problem

Building an inverted-index SpanExpression from an AND-chain is O(K²) in
retained heap. Leaf builders fold left-associatively
(ret = inverted.And(ret, next)), and each intersection node re-materializes
SpansToRead = unionSpans(left, right) while keeping both children alive. A
left-fold of K leaves of sizes 1, 2, …, K retains 1+2+…+K spans. There is no
size or node guard, and optimizer_span_limit is not wired into this path.

For JSON array containment j @> '[e1, e2, ..., eK]', each element becomes a
leaf AND-ed into the chain, so K is the number of array elements. This is a
planning-time allocation (the SpanExpression is built even when the optimizer
ultimately chooses a full scan) on the plain Go heap, not covered by
--max-sql-memory. EXPLAIN alone triggers it.

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):

python3 - <<'PY' | cockroach demo --no-example-database --insecure --max-sql-memory=512MiB --cache=512MiB
K = 6000
arr = "[" + ",".join(map(str, range(K))) + "]"
print("CREATE TABLE t (k INT PRIMARY KEY, j JSONB, INVERTED INDEX (j));")
print(f"EXPLAIN SELECT * FROM t WHERE j @> '{arr}';")
PY

Growth is O(K²) in retained heap and build time.

Observed

• K = 2000: completes, ~1.0GB peak RSS.
• K = 6000: heap grows past 2GB, OOM-killed during EXPLAIN within ~2s.
• K = 20000: same, OOM within ~2s.

Other uncapped sources reach the same builder: array @>, tsquery &
AND-terms, and trigram LIKE/=.

Environment

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

Code reference

SpansToRead = unionSpans(...) in pkg/sql/inverted/expression.go (the And
combiner); optimizer_span_limit not plumbed here — see
pkg/sql/opt/invertedidx/inverted_index_expr.go.

Jira issue: CRDB-67028