In a columnar engine, why does a contiguous column vector let the CPU filter values with SIMD instructions?
answer
- one instruction, many values at once
- the register wants identical, adjacent values
- type is checked once per batch, not per value
- strings and branches break the lanes
- useless if the query is not CPU-bound
basics
~20 sBecause one column's values sit adjacent in memory at identical fixed width and type, a single SIMD instruction can compare eight or sixteen of them per cycle, with no per-value type dispatch, pointer chasing or branching.
solid answer
~50 sSIMD (Single Instruction, Multiple Data) registers are 128–512 bits wide, so one compare instruction evaluates 8 or 16 fixed-width values at once. A column vector is exactly the input that hardware wants: a batch of consecutive values from **one** column, all the same physical type and width, in one buffer. The type check happens once per batch instead of once per value, the loop body is a branch-free `result[i] = value[i] > constant`, and the loads are sequential so the prefetcher keeps the pipeline full. A row-at-a-time engine sees each value on a different cache line, interleaved with columns it does not need, behind a virtual call — nothing there can be widened into a lane. The win is real only when the query is CPU-bound in filter and arithmetic; a scan limited by object-storage fetch or shuffle will not notice vector width.
code
sql · 10 lines-- vectorizes: fixed-width comparisons over column vectors
SELECT customer_id, amount_cents
FROM orders
WHERE amount_cents > 10000
AND order_ts >= DATE '2026-01-01';
-- defeats it: per-value string work inside the hot loop
SELECT customer_id, amount_cents
FROM orders
WHERE lower(customer_name) LIKE '%acme%';go deeper
Know the vocabulary: a column vector is many values of one column laid out side by side, and the CPU can process a whole register of them with one instruction. You are not expected to name instruction sets.
Be ready to explain why identical type, identical width and contiguity are what make a wide compare possible, and to name concrete vectorization killers: per-value type dispatch, branches, and row layout.
Show that you know the limits. Say where SIMD does and does not move the needle, and how you would tell from a profile whether a scan is CPU-bound at all before reaching for execution-level tuning.
Frame it as cost per query. Per-core efficiency is what CPU-second billing charges you for, but it only matters after pruning and encoding decisions; own the judgment about which lever to pull first for a given workload.
## The instruction-level picture Every modern server core carries SIMD registers — 128, 256 or 512 bits wide depending on the instruction set — plus instructions that apply one operation to every *lane* of that register simultaneously. A 256-bit register holds eight 32-bit integers, or four 64-bit doubles. One compare instruction therefore evaluates eight `amount > 100` tests in roughly the time a scalar compare takes to do one. This hardware is present whether or not your engine uses it; the only question is whether the data layout and the generated code allow the compiler to emit those instructions. ## Why a column vector is the right shape A *column vector* is a batch of consecutive values drawn from a single column — typically on the order of a thousand to a few thousand values — held in one flat buffer. Four properties of that buffer are what make SIMD possible: - **Uniform physical width.** Every element is, say, exactly 8 bytes, so lane boundaries are known statically and a wide load fills a register in one instruction. No gather, no per-element offset arithmetic. - **Uniform type.** The engine asks "what type is this?" once per batch, not once per value. In a row-at-a-time interpreter that dispatch is a virtual call or a switch executed per value, and it alone can cost more than the comparison. - **Sequential addresses.** Hardware prefetchers detect the stride immediately and stream the next cache lines in ahead of the loop, so the arithmetic units stay fed. - **Only the needed columns.** The other twenty columns of the row are in different buffers and are never loaded into cache at all. The loop the engine actually runs looks like this, and it is deliberately trivial: ``` // filter kernel over one column vector for (i = 0; i < 2048; i++) match[i] = (amount[i] > 100); ``` There is no function call, no branch and no allocation inside it, so a compiler will unroll it and emit packed compare instructions, or the engine ships a hand-written intrinsic kernel per type. Contrast a row-at-a-time (Volcano-style) engine: each value it wants lives inside a tuple next to unrelated columns, on its own cache line, reached through a pointer, and evaluated by walking an expression tree per row. Even with perfect caching there is no register to fill with eight neighbouring `amount` values, because they are not neighbours. ## What defeats vectorization SIMD is fragile in specific, recognizable ways, and knowing them is the useful half of this answer: - **Variable-length values.** Strings are a pointer plus a length; lanes have no fixed width. Engines work around it by comparing dictionary codes, fixed-width prefixes, or lengths first, and only touching bytes for survivors. - **NULLs treated as a branch.** Handled well, nullability is a separate validity bitmap ANDed with the result bitmap — still branch-free. Handled badly, it becomes an `if (isNull(i))` inside the hot loop and the vectorizer gives up. - **Control flow in the expression.** Short-circuit `AND`, `CASE`, and early exits introduce data-dependent branches. Vectorized engines usually evaluate *both* sides for the whole batch and combine the bitmaps, because computing a few unnecessary results is cheaper than mispredicting. - **Per-value function calls.** An interpreted expression evaluated per row, or a scalar user-defined function, collapses the kernel back to row-at-a-time regardless of how the data is stored. - **Implicit casts.** A predicate comparing a stored `INT` column to a string literal, or mixing decimal scales, can force a per-value conversion path instead of one typed kernel. ## Where the win shows up, and where it does not SIMD multiplies throughput in the *CPU-bound* part of a query: scanning, filtering, arithmetic on projected expressions, hashing, and comparison inside joins and aggregation. If a query spends its life fetching bytes from object storage, decompressing with a codec that is itself the bottleneck, or redistributing rows across the network, wider vectors move almost nothing — Amdahl's law applies to the fraction of time actually spent in the kernels. This is why a serious answer pairs "vectorized execution" with "but first, don't read the data": pruning and encoding decisions dominate, and vectorized execution is what makes the bytes you *do* read cheap to process. One further subtlety worth having ready: SIMD is *intra-core* data parallelism and is orthogonal to multi-core and multi-node parallelism. An engine can be embarrassingly parallel across nodes and still burn 5x more CPU per row than it needs to, and in a cloud warehouse CPU-seconds are the thing you are billed for. ## How to say it in an interview One sentence for the mechanism — same type, same width, contiguous, so one instruction handles a register full of values — one sentence for why row layout cannot do it, and one for the limits: strings, nulls-as-branches, per-row UDFs, and workloads that are not CPU-bound in the first place.
- Does storing data column by column on disk automatically give you SIMD execution?No. Columnar storage is a necessary condition, not a sufficient one. An engine can read a column chunk and then hand values to a row-at-a-time interpreter, paying a virtual call per value and getting none of the benefit. You need batch-at-a-time operators and typed kernels above the storage layer for the layout to turn into instructions.
- How do vectorized engines handle NULLs without putting a branch in the inner loop?They keep nullability out of the value array entirely, in a separate validity bitmap with one bit per position. The comparison kernel runs over all values including the garbage under NULLs, then the result bitmap is ANDed with the validity bitmap. That is two branch-free passes instead of a per-value `if`, and it keeps the kernel vectorizable.
- Why doesn't SIMD help a query that is already reading from a fast local cache at full memory bandwidth?Because the bottleneck is bytes per second, not operations per second. If the core is stalled waiting on memory, widening the arithmetic only makes it stall sooner. The fix there is to read fewer bytes — better encodings, narrower projections, more pruning — not wider registers.
A scalar loop is a cashier scanning one item at a time; SIMD is a scale that weighs sixteen identical apples in one motion. It only works because the apples are identical and already stacked together.
saying these in an interview costs you the question
- Claims columnar storage alone gives you SIMD automatically
- Confuses SIMD lanes with multi-core or multi-node parallelism
- Expects SIMD to speed up an I/O-bound or shuffle-bound query
- Thinks variable-length strings vectorize just like integers
- Says SIMD needs special hardware rather than ordinary server cores