In a vectorized engine, how does the batch size affect CPU cache behaviour, and why not use very large batches?

## What batch size is trading off Batch-at-a-time execution exists to sit between two bad extremes. Row-at-a-time pays an operator call, a type dispatch and a branch per value. Full-column-at-a-time — materializing an entire column's worth of intermediate results per operator — amortizes all of that away, but produces intermediates far larger than any cache, so every operator boundary becomes a round trip to main memory. The batch is the compromise: large enough that per-call overhead is negligible when divided across it, small enough that the values stay in fast cache from one operator to the next. ## The cache arithmetic Modern cores have a small L1 data cache (tens of kilobytes), a mid-sized private L2 (hundreds of kilobytes to a few megabytes), and a larger shared L3 that is several times slower than L1. The quantity that must fit is not one vector but **all the vectors live at once** in the operator chain: the input columns being scanned, the intermediate results (comparison masks, computed expressions, selection vectors), and the output columns being built. A rough calculation makes the shape obvious. Take a batch of 2048 values, eight projected columns of 8 bytes each, plus a couple of intermediates: ``` 2048 values x 8 columns x 8 bytes = 131,072 bytes (~128 KB) + masks and selection vector = a few KB ``` That already exceeds a typical L1 and is living in L2. Multiply the batch by a hundred and the same working set is measured in megabytes, sits in L3 or RAM, and each operator now streams its input in from memory rather than reading it out of the cache the previous operator just left it in. The instruction count is identical; the memory stalls are not, and stalls are the dominant cost in a scan-heavy engine. Going the other way is equally bad in a different currency. With a batch of 8 values, the fixed cost of entering the operator, resolving the type and setting up the loop is divided across 8 values instead of 2000, and the loop is too short to amortize its own prologue or to let the prefetcher get going. You have paid for the complexity of a vectorized engine and are getting interpreter-like throughput. ## Other pressures on the number Cache is the primary constraint but not the only one: - **Concurrency and memory footprint.** Each concurrent thread or pipeline holds its own batches, and per-core private caches are not shared. Total resident memory scales with batch size × columns × threads, and on a highly concurrent warehouse node that multiplication is what causes memory pressure, not the batch alone. - **Column width and count.** A projection of forty columns, or wide decimal and long string values, blows the same batch size past cache far sooner than a projection of four narrow integers. This is one reason `SELECT *` hurts an execution engine as well as the scan. - **Time to first row.** Bigger batches delay the first output, which matters for interactive queries with `LIMIT`. - **Selectivity.** After a very selective filter, a large batch may yield only a handful of surviving rows, so downstream operators run with poor lane utilisation regardless of nominal batch size. ## Why the number rarely needs tuning Engines pick a default in the region of one to a few thousand values because that is where the curve is flat: performance rises steeply from tiny batches, plateaus across a wide band, and degrades slowly once the working set leaves cache. Being anywhere in the plateau is fine, which is why this is a *mechanism* question rather than a tuning knob you are expected to twiddle. If you find yourself hand-tuning it, the more likely problems are an over-wide projection, an operator that materializes far more than it should, or a workload that is not CPU-bound at all. ## Connecting it to the plan A useful diagnostic instinct: when an engine's profile shows high cycles-per-instruction or a large share of time in memory stalls during a filter-and-project pipeline, the cause is usually the working set, not the arithmetic. Narrowing the projection reduces bytes per batch directly, which both shrinks the working set and reduces the bytes read from storage — the same fix helping at two levels. ## How to answer Say the goal in one line — keep the live vectors of the operator chain in per-core cache so the next operator reads them from cache, not RAM — then give both failure directions, then note the multiplication by columns and threads that determines the real footprint.