sql/opt: multi-column inverted index prefix constraint builds an unbounded span cross-product during planning

[yuzefovich]

Describe the problem

optimizer_span_limit is not enforced when the optimizer builds the
prefix-column constraint for a multi-column inverted / vector / trigram
index. idxconstraint.ConstrainIndexPrefixCols calls the constraint builder
with spanLimit == 0, which disables every span guard in that path, so the full
cross-product of the prefix-column spans is materialized and retained in
ScanPrivate.Constraint during planning.

For a IN (...) AND b IN (...) over a prefix (a, b), this is
len(a-list) × len(b-list) single-key spans. These are plain Go heap
allocations during planning and are not covered by --max-sql-memory, so a
large enough cross-product drives the process to OOM. EXPLAIN alone triggers
it (no execution).

The identical query on a regular composite index is protected: the span set
is collapsed by optimizer_span_limit. Only the inverted / vector / trigram
prefix path removes the protection.

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
n = 3000  # 3000 x 3000 = 9,000,000 prefix spans
a = ",".join(map(str, range(n)))
b = ",".join(map(str, range(n)))
print("CREATE TABLE t (k INT PRIMARY KEY, a INT, b INT, j JSONB, INVERTED INDEX (a, b, j));")
print(f"EXPLAIN SELECT * FROM t WHERE a IN ({a}) AND b IN ({b}) AND j @> '{{\"x\":1}}';")
PY

Growth is O(∏ IN-list cardinalities over the prefix columns), unbounded and
retained.

Observed

• n = 3000 (inverted index): heap grows to ~2.1GB, OOM-killed during EXPLAIN
  within a few seconds.
• Control — same a IN (...) AND b IN (...) on a regular INDEX (a, b):
  completes at baseline RSS, plan shows the scan collapsed to a single span.

Environment

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

Code reference

ConstrainIndexPrefixCols in
pkg/sql/opt/idxconstraint/index_constraints.go (hardcoded 0 /* spanLimit */);
reached from generateInvertedIndexScansImpl,
GenerateTrigramSimilarityInvertedIndexScans (pkg/sql/opt/xform/select_funcs.go)
and TryGenerateVectorSearch (pkg/sql/opt/xform/limit_funcs.go) via
invertedidx.TryFilterInvertedIndex.

Jira issue: CRDB-67026