perf(formula): SUMIFS/COUNTIFS/AVERAGEIFS/MAXIFS/MINIFS allocate full-length boolean masks per criterion → GC-dominated on large workbooks #7115

Summary

The *IF/*IFS family (SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, MAXIFS, MINIFS) evaluates each formula by materializing full-length boolean mask ArrayValueObjects per criterion, then pick()-ing the target and reducing. On workbooks with many such formulas over tall ranges, the resulting allocation churn makes recalculation garbage-collection–bound and drives peak heap very high.

Present in 0.12.3 and still present on current dev (the hot path is unchanged; dev refactored getBooleanResults → getPairedRangeAndCriteriaResult but keeps the same compare→mask→filter→pick→reduce shape).

Root cause

Per *IFS formula, for each (range, criteria) pair the engine:

1. valueObjectCompare(range, criteria) → a full N-row boolean mask (array-value-object.ts _batchOperator/compare). Even with the inverted index warming the compare, a full-length array is still allocated.
2. filterSameValueObjectResult(...) → ArrayValueObject.mapValue(...), a second full re-scan for number/text type rules (engine/utils/value-object.ts).
3. booleanObjectIntersection(...) → another full array per extra criterion.
4. targetRange.pick(mask) → an array of matched values, then .sum()/.count()/.max()/.min() (functions/math/sumifs/index.ts, statistical/*).

So cost is O(#criteria × rangeHeight) allocations per formula, even when only a handful of rows match. The inverted index already knows the matching rows, but the result is inflated back into a full array and re-scanned by pick.

Evidence

A representative financial workbook with ~27,500 SUMIFS on one sheet over ~1,800-row columns (criteria typically match a handful of rows):

• CPU profile: ~44% garbage collection, ~18% ArrayValueObject.mapValue (the filterSameValueObjectResult re-scan), remainder in compare/pick. Formula setup (parse/dependency) was negligible here — it's pure eval+GC.
• Peak heap ~4.4 GB; recalc ~90–113s and highly variable run-to-run (variance is the GC).

Suggested optimization

Replace the mask round-trip with row-index aggregation for the common scalar-criteria case:

1. For each criterion, get the matching row indices directly (via CELL_INVERTED_INDEX_CACHE for =, or a single allocation-free scan otherwise) instead of building a boolean ArrayValueObject.
2. Intersect the row-index sets across criteria.
3. Reduce the target over just those rows (sum/count/avg/max/min) — no pick, no intermediate arrays, no filterSameValueObjectResult re-scan (fold its type rules into the per-cell match).

Keep the existing mask path as a fallback for cases where it's genuinely needed (array criteria, spilling, wildcards) — masks are the right general representation; they're just overkill for a scalar *IFS that matches few rows.

Prototype result

A prototype doing exactly this (row-index collect + direct reduce, falling back to the stock path for wildcards/array-criteria/odd shapes), validated to produce byte-identical results (0 diffs across ~2M computed cells), on the workbook above:

Happy to share more detail or open a PR if this direction is welcome.