Vectorized Execution
Operators that process a batch of column values per call instead of one row at a time — and why that changes what the CPU is able to do.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
Two engines run the same plan over the same columnar files. Why is one of them spending most of its time on work that has nothing to do with the data?
Nobody asks for this by name. It is why an analytical engine over the same files can be in a different performance class from a naive one, and it is the reason a columnar *format* only pays off when it meets a columnar *executor* (Columnar Execution).
The unit is the batch: a few thousand values of one column, held contiguously, passed between operators as a unit. Everything in this lesson follows from replacing "one row per operator call" with "one column-batch per operator call" — the rows are the same rows, the interface is not (Row vs Column Storage).
The textbook execution model is the Volcano iterator: every operator exposes next() and returns one row. It composes beautifully, it is easy to reason about, it makes a new operator trivial to write, and for a transactional query touching a handful of rows it is entirely appropriate (How a Query Executes: Planner and Executor).
Every next() call is a virtual call through an operator interface. For a query touching a hundred million rows through a plan five operators deep, the call overhead is paid five hundred million times, and it is paid whether the row survives the filter or not (IPC: Instructions Per Cycle).
- Every
next()call is a virtual call through an operator interface. For a query touching a hundred million rows through a plan five operators deep, the call overhead is paid five hundred million times, and it is paid whether the row survives the filter or not (IPC: Instructions Per Cycle). - Each row is processed as a struct of mixed types, so a filter on one column touches a cache line containing seven other columns' worth of bytes that this operator will never use (Memory Moves in Lines, Not Variables).
- The comparison inside the filter is a data-dependent branch taken once per row. On selective, unpredictable data the branch predictor is wrong often, and each mistake costs a pipeline flush (Misprediction: What a Wrong Guess Costs).
- The compiler cannot vectorise anything, because the loop body is a call through a function pointer into unknown code rather than a tight loop over a contiguous array (Auto-Vectorization: Verify, Do Not Assume).
- The interpreter re-decides the types on every row. A row-at-a-time expression evaluator dispatches on "what type is this" a hundred million times to answer a question that was fixed at planning time.
- None of this is visible as a hotspot in the data. Profiles show time in the iterator machinery rather than in the arithmetic anybody wrote (Self Time, Total Time, and Where the CPU Went).
What is actually happening
- Vectorized execution keeps the operator tree and changes the unit of exchange.
next()returns a batch — a set of columns, each an array of a few thousand values of one type, plus a selection vector or null mask saying which positions are live (Data-Oriented Design, Without the Dogma). The per-call overhead is immediately amortised across the whole batch: one virtual call, thousands of values, which is why the batch exists at all and why it is thousands rather than millions. - Inside an operator the work becomes a tight loop over a contiguous, uniformly typed array. That is the shape compilers vectorise and CPUs pipeline well: no type dispatch inside the loop, predictable strides, and independent iterations that superscalar hardware can overlap (Instruction-Level Parallelism).
- Filters are typically evaluated branchlessly: rather than branching per row, compute the comparison into a mask or append the surviving index to a selection vector. A misprediction that never happens costs nothing (Branchless Code: A Trade, Not an Upgrade).
- Data-parallel instructions apply naturally once values of one type sit contiguously — one instruction comparing or adding several values at a time. This is the part people name first and it is genuinely the smaller half of the win (SIMD: One Instruction, Many Elements).
- A batch is deliberately sized to sit in cache. Too small and dispatch dominates again; too large and the batch stops fitting, so each operator streams it from memory and the locality benefit disappears (What a Cache Actually Is, Working Set: Why Performance Falls Off a Cliff).
- The other major approach is compilation: generate machine code specialised to this plan, fusing operators so intermediate values stay in registers. Vectorization and compilation solve the same overhead in different ways and modern engines mix them (Query Optimizers).
The interface change that changes everything
The Volcano model is one of the most successful ideas in database implementation: every operator exposes next() and returns one row, so operators compose without knowing anything about each other. It is the reason a query engine can be built out of small, independently testable pieces, and it is entirely appropriate for a transactional query that touches ten rows.
It becomes the bottleneck when the same interface is asked to move a hundred million rows. The problem is not that the interface is slow; it is that the *unit* is one row, so every per-call cost — the virtual call, the type dispatch, the branch, the failure to keep useful data in cache — is paid a hundred million times for work that is often a single comparison.
The vectorized model keeps the tree and changes the unit. next() returns a batch of a few thousand values per column, and the inner loop of each operator becomes a loop over a contiguous array of one type. The sketch below is deliberately schematic — no real engine looks like this — but the shape of the difference is exactly right, and it is the shape that matters.
Per row: a virtual call through an operator interface, a type dispatch inside the expression evaluator, a data-dependent branch, and a cache line that carries every column of that row whether or not the operator needs them.
Per few thousand rows: one virtual call, types resolved once at plan time, a tight loop over one contiguous typed array, a comparison written to a mask rather than branched on, and cache lines that carry only the column being processed.
The work the query asks for — compare a number to a threshold — is identical in both. What differs is the overhead around it, and in the upper form that overhead is paid per row while the useful work is a single instruction. Batching amortises the dispatch, uniform typing removes the interpretation, contiguity fixes the locality, and masking removes the branch. These are four separate wins that happen to arrive together (Data-Oriented Design, Without the Dogma).
1# Row at a time. One call per row, per operator. The comparison is a2# data-dependent branch, and each row is a mixed-type object, so the3# cache line fetched for one column also carries the other seven.4def next_row(child, threshold):5 while True:6 row = child.next() # virtual call, once per row7 if row is None:8 return None9 if row["revenue"] > threshold: # unpredictable branch, once per row10 return row11 12# Batch at a time. One call per few thousand rows. The inner loop walks a13# contiguous array of one type, with no dispatch and no branch inside it:14# the comparison is written to a mask rather than used to jump.15def next_batch(child, threshold):16 batch = child.next_batch() # virtual call, once per ~2048 rows17 if batch is None:18 return None19 revenue = batch.column("revenue") # one contiguous typed array20 mask = [0] * len(revenue)21 for i in range(len(revenue)): # tight, uniform, vectorisable22 mask[i] = revenue[i] > threshold # branchless: store, do not jump23 return batch.with_selection(mask)Python is the wrong language for this and the right pseudocode: the point is the loop shape, not the syntax. The lower form has no per-row dispatch, no per-row type decision, one column's values contiguous in memory, and an inner loop a compiler can turn into data-parallel instructions (Auto-Vectorization: Verify, Do Not Assume).
Where the win comes from
It is worth being precise about this, because "vectorized" is usually explained as "it uses SIMD" and that is the smallest of the four contributions. The table maps each change in the execution model to the hardware behaviour it improves, and each row is a link into Computer Architecture, where the mechanism itself is taught properly.
The ordering is roughly by contribution for a typical analytical filter-and-aggregate, and the top row is the one people underrate. Removing hundreds of millions of virtual calls and type dispatches is a large fraction of the total before any instruction-level parallelism enters the picture (CPI and IPC: The Number Everyone Misreads).
The last row is the constraint that appears once the others are addressed. When per-row overhead is gone, a scan is frequently limited by how fast bytes arrive from memory — at which point the useful moves are reading fewer columns and encoding them better, which returns the argument to layout and format (Why Analytical Data Compresses).
| Change in the execution model | What the CPU stops doing | Where the mechanism is taught |
|---|---|---|
| One operator call per batch instead of per row | Hundreds of millions of virtual calls and returns, each with its own indirect jump. | Instruction pipelines and the cost of indirect control flow (IPC: Instructions Per Cycle, Instruction-Level Parallelism). |
| Types resolved at plan time, not per value | Re-deciding "what type is this" inside the innermost loop. | Why a uniform loop body is what hardware and compilers can optimise (The Compiler Reordered It Before the CPU Did). |
| One column's values stored contiguously | Fetching cache lines full of columns this operator will never read. | Cache lines, spatial locality and struct-of-arrays layout (Memory Moves in Lines, Not Variables, Spatial Locality, Array of Structs, or Struct of Arrays?). |
| Comparisons written to a mask instead of branched on | Mispredicting a data-dependent branch once per row and flushing the pipeline. | Branch prediction and branchless code (Misprediction: What a Wrong Guess Costs, Branchless Code: A Trade, Not an Upgrade). |
| A tight loop over a typed array | Executing one comparison per instruction when several fit. | Data-parallel instructions and how compilers emit them (SIMD: One Instruction, Many Elements, Auto-Vectorization: Verify, Do Not Assume). |
| Batches sized to fit in cache | Streaming every intermediate from main memory between operators. | The memory hierarchy and the working set (The Memory Hierarchy, Working Set: Why Performance Falls Off a Cliff). |
| Everything above, once it is fast enough | Nothing — the scan becomes limited by memory bandwidth instead. | Bandwidth-bound workloads and why more cores stop helping (When the Memory Bus Is the Bottleneck). |
What vectorization does not fix
This lesson sits at the end of a chain for a reason. Pruning decides how many bytes exist; projection decides how many of them are fetched; vectorization decides how efficiently the fetched bytes are processed. Applying them in the wrong order is the classic mistake — a faster executor over a full-table scan is a more efficient way to read data the query never needed (Predicate Pushdown).
The failure table below is mostly about the fast path being silently abandoned. Engines maintain a vectorized implementation for common expressions and a row-at-a-time fallback for everything else, and a single unsupported function in the middle of a plan can put the whole pipeline on the slow path with no warning anywhere.
The final row is the correctness one, and it is worth knowing about before it surprises you during a reconciliation. Floating-point addition is not associative; a batched or parallel sum adds in a different order than a sequential one, so the low-order digits differ. This is not a defect and it is not fixable — it is a reason to use exact decimal types for money (Why 0.1 + 0.2 Is Not 0.2 + 0.1's Problem).
| Trigger | Symptom | Cause | Response |
|---|---|---|---|
| A user-defined function in the projection or filter. | A query that is dramatically slower than a structurally identical one without it. | The UDF has no vectorized implementation, so rows are materialised and processed one at a time through it. | Express it in native SQL functions where possible; where not, apply it after aggregation so it runs over far fewer rows (SQL Transformations). |
| An expression the vectorized path does not implement. | Silent fallback; nothing in the query text suggests anything unusual. | Engines keep a row-at-a-time fallback for completeness and do not always advertise when it is used. | Compare plans and timings for small variations of the expression. This is one of the few cases where empirical comparison is the only diagnostic. |
| A row-oriented source format. | A vectorized engine performing like a row-at-a-time one. | The reader must transpose rows into batches, paying per-row costs before execution begins (Avro). | Convert to a columnar format for anything scanned repeatedly. Keep the row format for the raw landing zone (The Raw Landing Zone). |
| A batch size raised well above the default. | Throughput improves briefly and then degrades; memory use per task rises. | Batches no longer fit in cache, so each operator streams them from main memory (Cache Thrashing: Load, Evict, Reload, Repeat). | Return to the default unless a measurement on your workload says otherwise. This is a cache-fitting parameter, not a throughput dial. |
Very wide batches from SELECT *. | Fewer rows per batch fit in cache; per-row cost rises even though the executor is unchanged. | Batch memory is shared across the projected columns. | Project narrowly. Every other lesson in this module says the same thing for its own reasons (Projection Pushdown). |
| A float aggregate that differs between engines or runs. | Reconciliation fails by an amount too small to be a business event. | Floating-point addition is not associative and batched or parallel summation reorders it (Reduction Ordering: The Sum Changed When the Worker Count Did). | Use exact decimal types for money, and never compare float aggregates for exact equality (The Dimensions of Data Quality). |
How to build it
Most important first.
- This is an engine property, not a knob — the main design decision available to you is choosing an engine whose executor matches your workload, and then not defeating it (Choosing an Analytical Platform).
- Feed it columnar data. A vectorized executor reading a row-oriented format spends its time converting rows into batches, which is exactly the per-row work it exists to avoid (Parquet).
- Prefer expressions the engine can evaluate over a whole batch: native functions and simple arithmetic over columns. A row-at-a-time user-defined function in the middle of a plan forces materialisation per row and can dominate the query (SQL Transformations).
- Project narrowly. Fewer columns per batch means more rows fit in the same cache footprint, which compounds with everything else in this lesson (Projection Pushdown).
- Do not reason about this before reasoning about bytes read. Vectorization changes the cost per byte processed; pruning changes how many bytes there are, and it is by far the larger lever (Predicate Pushdown).
- Where a batch size is exposed, treat the default as informed and change it only with a measurement — its correct value is a cache-fitting question, not a throughput dial (Measure Before You Optimize).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- Vectorized execution is semantics-preserving for well-defined operations: the same plan over the same data returns the same rows.
- It does not guarantee identical results for floating-point aggregation. Summing in a different order gives different low-order bits, and a vectorized or parallel sum reorders by construction (Why 0.1 + 0.2 Is Not 0.2 + 0.1's Problem).
- Row order is not guaranteed and becomes less incidentally stable, because batches complete in whatever order the workers produce them (Ordering Guarantees: Four Levels, Four Prices).
- Nothing guarantees that a given operator is vectorized. Engines commonly have a vectorized fast path and a row-at-a-time fallback for expressions the fast path does not implement, and falling back is silent.
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The check is a cross-engine result comparison on a representative set of aggregates: run them on the vectorized engine and on a reference implementation and compare. It catches an incorrect fast path, a null-handling difference in a specialised operator, and an overflow that only appears in a specialised integer path.
- It misses floating-point differences in the last digits, which are expected rather than defects, and it misses anything about data that is not in the sample (Distribution Tests).
- For money, the real check is upstream of all of this: use exact decimal types, so that summation order cannot change the answer at all (The Dimensions of Data Quality).
- No effect on freshness. This is a pure execution-efficiency concern and changes nothing about when data arrives.
- It changes what freshness is *affordable*: an executor that needs fewer core-seconds for the same scan can run the same refresh more often within the same budget (Cost vs Freshness).
- For interactive querying it moves the boundary of what feels live, which changes how people use the data rather than how current the data is.
- Schema changes affect this only through types. A column that becomes a string moves from a fixed-width specialised path to a variable-length one, which is a different and generally slower code path (Breaking Schema Changes).
- Adding a column widens every batch for
SELECT *consumers, reducing how many rows fit in the same cache footprint — another way in which a harmless schema addition is not harmless (Projection Pushdown). - Engine upgrades change which expressions have a vectorized path. A query can get materially faster or slower with no change to data or SQL, which is a reason to record plan and timing baselines for scheduled work (Regression or Tuesday? Telling a Real Change from Noise).
- Nothing to recover. Execution efficiency has no durable state and no corruption mode.
- The one exception worth naming: if a specialised path produced a wrong result — an overflow, a null-handling bug — then every table computed with it needs recomputation, and that is an ordinary backfill (Backfills).
- This is a strong argument for the cross-engine comparison above being run once at adoption rather than never, because the failure it catches is silent and its blast radius is every derived table (Reconciliation).
What can go wrong
- A row-at-a-time user-defined function in the middle of an otherwise vectorized plan, forcing per-row materialisation and dominating the query.
- A silent fallback to the non-vectorized path for one unsupported expression, with no indication in the plan that the fast path was abandoned.
- A batch size tuned upward "for throughput" until batches no longer fit in cache and every operator streams from memory (Cache Thrashing: Load, Evict, Reload, Repeat).
- Reading a row-oriented format, so the executor spends its time transposing rows into batches (Parquet vs Avro).
- Floating-point sums that differ between engines or between runs, investigated as a data quality incident (Two Dashboards, Two Numbers).
- The mitigation failing: switching to a faster engine and concluding the layout problem is solved, when the query is still reading the whole table and now does so more efficiently (Predicate Pushdown).
- "Vectorized means SIMD." Data-parallel instructions are one contributor and usually not the largest. Removing per-row dispatch, keeping types out of the inner loop, and improving locality and branch behaviour matter at least as much (SIMD: One Instruction, Many Elements).
- "A columnar format makes queries fast." A columnar format lets a reader fetch less. It is the columnar *executor* that turns those bytes into work the CPU can do efficiently — the format and the engine are two halves of the same idea (Parquet).
- "We switched engines, so performance is solved." A faster executor over an unpruned scan is a faster way to read the whole table. Layout and pushdown dominate, and no executor recovers bytes that should never have been read (Physical Data Layout).
- "Bigger batches are faster." Only until the batch stops fitting in cache. Beyond that point every operator streams from main memory and the locality benefit is gone (Cache Thrashing: Load, Evict, Reload, Repeat).
- "The results are identical either way." For integers and decimals, yes. For floating-point sums, reordering changes the low-order digits, and any check comparing float aggregates for exact equality will eventually fail (Floating Point: Trading Precision for Range).
Operating it
- Rows processed per second per core for a pure scan-and-filter query, as a relative baseline for your own platform over time (Benchmarking: Does This Number Answer My Question?).
- Whether the plan indicates a vectorized operator or a fallback. Engines that expose this expose it in the plan and nowhere else (Reading EXPLAIN ANALYZE).
- CPU profiles of a slow analytical query. Time in iterator or expression-evaluation machinery rather than in arithmetic is the signature of a row-at-a-time path (Reading a Flame Graph).
- Instruction-level counters where you have them — cache misses and branch mispredictions per row are the mechanisms this lesson is about, made visible (The CPU Counts Itself).
- At 10x data, per-row overhead scales linearly with everything else, so the gap between a vectorized and a row-at-a-time executor widens in absolute terms and stays constant in relative terms.
- At high core counts the constraint moves to memory bandwidth, which is shared. Adding cores to a bandwidth-bound scan produces less than proportional improvement (Memory Bandwidth: More Cores, Same Bus).
- At small scale none of this matters. For a query over a few thousand rows the fixed costs of planning and coordination dominate everything the executor does (Microbenchmark or End-to-End: Why p99 Did Not Move).
- CPU time per byte processed. This is the driver vectorization moves, and it is the only one it moves (Computing or Waiting?).
- Memory bandwidth becomes the binding constraint once per-row overhead is gone: a well-vectorized scan is frequently limited by how fast data arrives from memory rather than by the arithmetic (When the Memory Bus Is the Bottleneck).
- Compute hours, indirectly and substantially — a query that needs fewer core-seconds needs fewer workers to hold the same concurrency (Compute Waste).
- Bytes read is untouched. Vectorization makes processing cheaper and reading no cheaper at all, which is why it is the second thing to think about (Scan Cost).
- Vectorization buys throughput on scans and costs engine complexity: a specialised operator per type combination, a fallback path, and a much larger surface for correctness bugs in code most users never think about.
- Batching buys amortised dispatch and costs latency granularity — an operator cannot emit until it has a batch, which matters for a streaming engine emitting per event and not at all for an analytical scan (Stream Processing).
- Compiling a plan buys the same overhead reduction plus operator fusion and costs compilation time on every query, which is a poor trade for short queries and a good one for long ones (JIT and Warm-Up: The First Thousand Requests Are a Different Program).
Vectorised execution
Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- ENGINE-SPECIFICBatch sizes, which operators have vectorized implementations, and whether the engine also compiles plans are per-engine design choices. Two engines described as vectorized can differ in which expressions fall back to a row-at-a-time path, and the fallback is silent in both.
- GENERALThe underlying reasons — amortised dispatch, uniformly typed contiguous data, predictable branches, better locality — are properties of how CPUs execute code and hold well beyond query engines. The same reasoning drives columnar data structures in numerical libraries and game engines.
- SIMPLIFIEDPresented as a clean split between row-at-a-time and vectorized. Real engines are hybrids: a vectorized executor with compiled expression evaluation, or a compiled pipeline operating on batches, and the boundary between the two techniques is not sharp.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.