What vectorized execution means for billion-row query performance
When people think about database query performance, the first things that come to mind are usually obvious optimizations such as indexes, partitioning, and parallelism. But in large-scale analytical scenarios, the factor that truly limits performance is often something more fundamental and far less visible — the way the query execution engine itself processes data. OceanBase introduced a vectorized execution framework starting in V3.2, and after several rounds of iteration, it completed a systematic rewrite of the data format, operators, expressions, and storage layer of the execution stack in V4.3, fundamentally improving the data-processing efficiency of the query execution engine. This article starts with the difficulties traditional execution models run into under analytical workloads, then gradually introduces the design philosophy and key technical implementation of OceanBase’s vectorized engine, along with the measured gains this technical evolution delivers.
1. Why a Vectorized Execution Engine Is Needed
To understand the value of a vectorized execution engine, we first need to understand what it replaces — the traditional Volcano model (also known as the Iterator model) — and the fundamental limitations this classic model, in use for three decades, exposes when facing modern analytical workloads. We will begin with how the Volcano model works, trace its execution through a real SQL query, and then analyze, one by one, the four layers of performance cost hidden behind the row-at-a-time processing pattern.
How the Volcano Model Works
Almost all relational databases use the Volcano model to execute queries. The core idea of this model is: each operator implements a next() interface that returns one row of data per call, and an upper-level operator pulls data by repeatedly calling the next() of the operator below it.
In the Volcano model, each operator only needs to care about its own logic; operators are composed through a unified interface, and adding a new operator does not affect the implementation of existing ones. This design works very well in OLTP scenarios (point lookups, small-range scans, processing tens to thousands of rows) — the cost of function calls is negligible compared to disk I/O and network latency.
The problem appears in analytical queries. Take a simple SQL statement as an example:
SELECT SUM(amount) FROM orders WHERE status = 'paid';
Under the Volcano model, the execution proceeds as follows:
The aggregation operator calls the next() of the filter operator
The filter operator calls the next() of the scan operator
The scan operator reads one row from the storage layer and returns it to the filter operator
The filter operator checks status = 'paid'; if not satisfied, it repeats steps 2-3 until it finds a row that meets the condition
The filter operator returns that row to the aggregation operator
The aggregation operator adds amount to the running SUM
Go back to step 1, until the scan is complete
If the orders table has 1 billion rows, of which 30% satisfy status = 'paid', then the scan operator's next() is called 1 billion times and the filter operator's next() is called 300 million times. That is roughly 1.3 billion virtual function calls just to complete a single summation.
Note that the business logic of this SQL statement is essentially just “iterate over an array and sum up the elements that meet a condition.” If you hand-wrote this logic in C, a simple for loop would do the job. But under the Volcano model, the same logic is scattered across three operators and coordinated through 1.3 billion function calls. The cost of this abstraction is acceptable in OLTP scenarios — when processing a few dozen rows, the overhead of a dozen-odd function calls is negligible. But when the data volume scales up to the billion-row level, this cost becomes impossible to ignore.
The Hidden Costs, Exposed Layer by Layer
1.3 billion function calls is already a startling number, but it is only the tip of the iceberg. When we look closely at the actual execution behavior of the Volcano model at the CPU microarchitecture level, we find that the row-at-a-time processing pattern systematically wastes hardware resources across four dimensions.
Function Call Overhead
Each virtual function call involves a series of operations: vtable pointer lookup, indirect jump, stack frame allocation, and register save/restore. On a modern x86 CPU, the cost of one indirect function call is about 5–10 nanoseconds. For 1 billion rows, the function calls alone consume about 5 seconds — 5 seconds during which no useful computation happens at all, with the system merely jumping back and forth between calls and returns.
CPU Cache Invalidation
The cache line of a modern CPU is typically 64 bytes. Under a row-store layout, even if the filter condition depends only on the status column, the system still has to load the entire row into the cache hierarchy, causing a large number of irrelevant fields to consume memory bandwidth and CPU cache. With main-memory access latency around 60-100ns, the cumulative wait time from cache misses over 1 billion rows can reach tens of seconds. If, instead, the same column is stored contiguously, a single cache line can hold 16 INT32 values, improving cache utilization by an order of magnitude.
CPU Pipeline Flushes
Modern CPUs use 15–20 stage pipelines to process instructions in parallel. Row-at-a-time processing introduces a large number of unpredictable branches through per-row NULL checks, filter-condition evaluations, and so on. For a filter condition with a 30% pass rate, the branch misprediction rate is about 20–30%, and each branch misprediction causes a pipeline flush of about 15 clock cycles. The cumulative loss from a single filter condition over 1 billion rows is about 1 second, and the compounding effect of multiple conditions and NULL checks in a complex query significantly slows down execution.
Idle SIMD Capability
Modern CPUs provide SIMD instruction sets (for example, AVX2’s 256-bit registers can process 8 INT32 values at once, and AVX-512 can process 16 at once). But in the row-at-a-time processing mode, a single comparison operation uses only 32 bits of data, leaving 94% of the hardware capability in a 512-bit register completely idle. This is like having a 16-lane highway but allowing only one car through at a time.
AP Workload Characteristics Amplify Every Problem
OLTP queries usually process a few rows to a few thousand rows, so the absolute amounts of function call overhead, cache misses, and pipeline flushes are all small and are masked by I/O latency. But the characteristics of AP queries are entirely different:
Full table scans: involving millions to billions of rows
Aggregation statistics: must traverse all the data
Multi-table JOINs: intermediate results may be larger than the original tables
When the number of rows processed jumps from the thousands to the billions, the per-row 5–10ns function call overhead swells from microseconds to seconds, and the per-row cache miss swells from milliseconds to minutes. There is a fundamental mismatch between the row-at-a-time processing of the Volcano model and the demands of analytical workloads.
The conclusion is now clear: the row-at-a-time processing pattern lags behind the capabilities of modern hardware across all four dimensions — function calls, cache utilization, pipeline efficiency, and SIMD utilization. We need a new execution model that changes, from the ground up, the way data flows between operators — so that every clock cycle of the CPU is used, as much as possible, on useful computation rather than wasted on waiting and jumping.
This is exactly the design starting point of the vectorized execution engine.
2. The Core Ideas of OceanBase’s Vectorized Engine
Once the fundamental problems of the traditional Volcano model are clear, the direction for a solution emerges as well: since the core ills of row-at-a-time processing are “one function call per row, one random memory access per row, one branch evaluation per row,” the most direct response is — to stop processing row by row. OceanBase’s vectorized engine is built around four core principles: batch processing to amortize overhead, a columnar memory layout to improve cache hit rates, SIMD instructions to unleash hardware parallelism, and branch elimination to protect the CPU pipeline. We will expand on each below.
Batch Processing
The core insight of vectorized execution is: amortize the overhead of a function call across N rows of data.
Returning to the earlier example SELECT SUM(amount) FROM orders WHERE status = 'paid', under vectorized execution (assuming a batch size of 1024):
The scan operator reads the status column and the amount column for 1024 rows at once
The filter operator compares 1024 status values in a tight loop and produces a bitmap marking which rows satisfy the condition
Based on the bitmap, the aggregation operator accumulates the amount values of the qualifying rows in a tight loop
The above process repeats until all data is processed
Number of function calls: 1 billion rows / 1024 = about 976,000 (roughly 2.93 million across the three operators combined). Compared with the 1.3 billion calls of the Volcano model, this is a reduction of nearly three orders of magnitude.
More importantly, the tight loops in steps 2 and 3 — simple for loops iterating over arrays — are exactly the pattern that CPUs are best at optimizing. The compiler can apply auto-vectorization, the CPU prefetcher can accurately predict the access pattern, and the branch predictor can maintain 100% accuracy when facing a branch-free loop body.
Columnar Memory Layout (Data Frame)
Inside OceanBase’s execution layer, data is organized by column, in a structure called the Data Frame. A Data Frame is a contiguous block of memory (up to 2MB) that holds the data of all columns in the current batch.
The reason the Data Frame size cap is set to 2MB: the L2 cache of a modern CPU is typically 256KB-1MB (per core). Keeping the hot working set near the L2 capacity minimizes L2 cache misses. The 2MB cap leaves headroom for multi-column scenarios; the effective amount of data actually used is usually smaller than the L2 capacity.
The tuning logic for vector size: suppose a query involves 10 INT64 columns (8 bytes per column). With a vector size of 1024, the data volume of one batch = 10 × 8 × 1024 = 80KB, which fits comfortably in the L2 cache. If the query involves 100 columns, the same vector size would produce an 800KB working set, which may exceed the L2 capacity. In that case the engine automatically reduces the vector size, ensuring the working set always stays resident in L2.
SIMD Instruction Utilization
Columnar batch data is naturally suited to SIMD processing. Take the filter condition WHERE amount > 100 as an example, assuming amount is of type INT32 and the batch size is 1024:
Scalar execution:
for (int i = 0; i < 1024; i++) {
result[i] = (amount[i] > 100) ? 1 : 0; // 1024 comparisons, 1024 branches
}
AVX2 SIMD execution:
__m256i threshold = _mm256_set1_epi32(100);
for (int i = 0; i < 1024; i += 8) {
__m256i data = _mm256_loadu_si256((__m256i*)(amount + i));
__m256i cmp = _mm256_cmpgt_epi32(data, threshold); // 8 comparisons, 1 instruction
// Write the result into the bitmap
}
128 loop iterations replace 1024 iterations, with zero branch instructions, processing 8 values per iteration. The theoretical speedup is 8×; in practice, due to memory bandwidth limits, it is usually 4–6×.
The OceanBase vectorized engine implements SIMD-accelerated paths on x86 (SSE/AVX2), ARM (NEON), and PPC architectures.
Branch Prediction Optimization
Vectorized operators internally adopt a “default-write-then-override” coding pattern. Take processing a column that contains NULL values as an example:
Traditional approach (one branch per row):
for (int i = 0; i < 1024; i++) {
if (is_null[i]) {
result[i] = NULL_VALUE;
} else {
result[i] = compute(val[i]);
}
}
Every iteration of this loop has a conditional branch. If the NULL rate is 5%, the branch predictor has to handle a 95/5 distribution and will still produce about 5% mispredictions.
Vectorized approach (split loops, eliminating branches):
// First loop: branch-free, the CPU pipeline runs at full load
for (int i = 0; i < 1024; i++) {
result[i] = compute(val[i]);
}
// Second loop: only processes NULL rows (usually very few)
for (int i = 0; i < null_count; i++) {
result[null_positions[i]] = NULL_VALUE;
}
The first loop has no branches at all, so the compiler can fully unroll and vectorize it. The second loop only iterates over the positions of NULL rows (usually a tiny minority), and even though it contains a branch, its impact is negligible because the iteration count is small.
3. Key Technical Improvements
V4.3 is a systematic rewrite of the OceanBase vectorized engine. After the previous two versions validated the effectiveness of the core ideas — batch processing, columnar layout, SIMD utilization, and branch elimination — V4.3 shifted its focus to the “last mile” of performance engineering: deep, end-to-end optimization spanning the execution layer’s data format, the internal implementation of core operators, and the interaction between the storage layer and the execution layer. We expand on each technical module below.
Data Format Optimization
The execution-layer data format prior to V4.3 was inherited from an early design and carried indirect-addressing overhead: variable-length types referenced the actual data through an array of pointers, so accessing a single value required two memory hops (first read the pointer, then read the data). When processing billions of rows, this indirect addressing produced a large number of random memory accesses.
V4.3 redesigned the columnar data description format of the execution layer:
Fixed-length types (INT8/16/32/64, FLOAT, DOUBLE, DATE, TIMESTAMP, etc.): use a packed array to store values directly. This eliminates all pointer indirection, and data is laid out strictly contiguously in memory. The 1024 values of an INT32 column occupy only 4KB of contiguous memory, and the CPU prefetcher can perfectly predict the access pattern.
Variable-length types (VARCHAR, TEXT, BLOB, etc.): use a structure of an offset array plus a contiguous data region. The offset array records the starting position of each value within the data region, and the data region stores the actual content of all values. Accessing the i-th value simply requires: data[offset[i] ... offset[i+1]]. Compared with allocating memory row by row, this eliminates memory fragmentation and pointer hops.
The core design goal of these formats is: when the CPU processes a batch of data, all memory accesses are sequential and predictable. Sequential access patterns allow the CPU’s hardware prefetcher to load subsequent data into the cache in advance, reducing the actual cache miss rate to nearly zero.
Exploiting Batch-Level Metadata
In real business data, most batches contain no NULL values, and in most batches all rows pass the upstream filter condition. The V4.3 engine maintains metadata flags at the batch level:
The has_null flag: whether the current batch contains NULL values
The all_active flag: whether all rows in the current batch are unfiltered
When has_null = false, the operator can completely skip the NULL-checking logic internally — not skip it row by row, but skip the entire code path. This means that in the code the compiler generates for the "no NULL" scenario, the NULL-related instructions do not exist at all, occupy no instruction cache, and consume no execution resources.
When all_active = true, the operator does not need to check the skip bitmap and can perform an uninterrupted, continuous operation over the entire array. This allows both compiler auto-vectorization and manual SIMD optimization to reach theoretical peak performance.
These two seemingly simple flag bits have a cumulative effect over 1 billion rows: they skip billions of conditional evaluations and eliminate the corresponding branch-prediction overhead.
Sort Operator Rewrite
A traditional sort implementation moves entire rows of data during comparisons and swaps. Suppose we want to sort 1 million rows by 2 INT64 columns, where each row also has 10 non-sort columns (80 bytes of payload in total):
Total size per row: 2 × 8B (sort key) + 80B (payload) = 96 bytes
Each swap during sorting moves 96 bytes
Average number of comparisons in quicksort: 1 million × log₂(1 million) ≈ 20 million
Volume of data movement: 20 million × 96B = about 1.9GB
V4.3’s approach is to separate the sort key from the payload:
The sort phase materializes only the sort key columns and the row number (row_id): 2 × 8B + 4B = 20 bytes/row
After sorting completes, the payload columns are backfilled according to the sorted row numbers
The volume of data movement in the sort phase: 20 million × 20B = about 400MB, a reduction of nearly 5×. More importantly, 20 bytes/row means the L2 cache (256KB) can hold about 12,800 rows of sort data, whereas 96 bytes/row can hold only about 2,700 rows. The cache hit rate during sorting improves dramatically.
Hash Join Operator Rewrite
Multi-column join conditions are a common pattern in AP queries, for example:
SELECT ... FROM a JOIN b ON a.id = b.id AND a.dt = b.dt
The traditional implementation computes the hash value of the two join key columns separately and then combines them. V4.3 encodes a multi-column fixed-length join key into a single fixed-length column: two INT64 columns are encoded into a 16-byte contiguous value.
The benefits this brings:
Hash computation: a single hash operation on 16 bytes of contiguous memory, rather than two independent hashes plus a merge. This eliminates the overhead of one hash call and the merge operation.
Equality comparison: a single 16-byte memcmp (which can be done with one 128-bit SIMD comparison instruction), rather than two 8-byte comparisons plus a logical AND.
Memory access: probing the hash table only requires reading one contiguous block of memory, rather than jumping to the locations of two different columns.
When the hash table contains millions of records and the probe side has tens of millions of rows, the savings from these single operations accumulate into a substantial overall performance improvement.
Aggregation Operator Optimization
For high-frequency aggregation operations such as COUNT, SUM, and MIN/MAX, V4.3 provides specialized implementations that bypass the abstraction layer of the general aggregation framework. For example, SUM(int64_column) in a scenario with no NULLs and no filtered rows is compiled into a pure array-accumulation loop, and the compiler can directly generate SIMD accumulation instructions.
Storage-Layer Vectorization (Push-Down)
The execution flow before V4.3 was: the storage layer decodes the data → converts it to the execution-layer format → the execution layer applies the filter condition. V4.3 pushes a variety of computations down to the storage layer:
Filter Push-Down: filter conditions are executed directly on the encoded data. For dictionary-encoded columns, WHERE city = 'Shanghai' is converted into WHERE dict_code = 7 (assuming the dictionary code for 'Shanghai' is 7). Integer comparisons are an order of magnitude faster than string comparisons, and dictionary-encoded values are usually INT16/INT32 — more compact than the original strings and offering better cache utilization.
Aggregate Push-Down: leverages pre-aggregated metadata at the micro block level. If the storage layer has already recorded statistics such as the row count, SUM, and MIN/MAX of each micro block at write time, SELECT COUNT(*) FROM t can simply accumulate the row-count metadata of the micro blocks, with no need to decode or scan the actual data rows at all.
GroupBy Push-Down: when the grouping column is dictionary-encoded and the number of distinct values (NDV) is low, aggregation can be completed directly in the storage layer using the dictionary-encoded values as the grouping key, avoiding the overhead of decoding the original values and re-hashing.
SIMD-Accelerated Projection: projection templates are customized by column type and length, using SIMD instructions to decode and copy column data in bulk. For example, extracting 1024 INT32 values from the columnar format requires only 128 SIMD load + store operations using AVX2 instructions.
Adaptive Batch Size
OceanBase serves both TP and AP workloads simultaneously, and the vectorized engine automatically selects the batch size based on the query type:
TP queries (point lookups, short-range scans): use a smaller batch size. A point lookup usually returns only 1 row, and an overly large batch would bring unnecessary initialization overhead.
AP queries (full table scans, large-scale aggregations): use a larger batch size (such as 1024 or larger) to fully exploit the advantages of SIMD and caching.
This adaptive mechanism allows the same engine codebase to serve two completely different workload patterns without any manual tuning.
4. Measured Performance Gains
The following are OceanBase’s internal benchmark results, comparing the overall execution time with the vectorized engine turned on versus off under the same hardware environment:
The differences in speedup across the three benchmarks are worth analyzing. The improvement for TPC-H (40×) is significantly higher than for ClickBench (14×), because TPC-H contains a large number of multi-table JOINs and complex sort operations — exactly the scenarios where the Sort/Hash Join operator rewrites yield the greatest benefit. ClickBench is dominated by single-table, wide-table aggregations, where the bottleneck lies more in the I/O and decoding stages, and the function-call overhead of the Volcano model accounts for a relatively small share; therefore the speedup from vectorization is also relatively low. TPC-DS (26.5×) falls between the two, reflecting its query characteristics that mix simple aggregations with complex JOINs.
The incremental gains of V4.3 relative to the earlier vectorized implementations (V4.0/V4.2):
TPC-H: 68% of queries achieve a 10%-40% performance improvement
TPC-DS: 56% of queries achieve a 10%-40% performance improvement
ClickBench: an overall improvement of about 30%
The significance of this set of data is that V4.0 was already a complete vectorized engine, capable of achieving most of the acceleration shown in the table above. On top of that, V4.3, through its data-format rewrite and coordinated operator design, still achieved an additional 10%-40% improvement. This shows that “last mile” performance engineering — eliminating indirect addressing, optimizing memory layout, and exploiting batch-level metadata — can still unlock considerable performance headroom on top of an existing vectorized framework.
Summary
Through the four core techniques of batch processing, columnar memory layout, SIMD instruction utilization, and branch elimination, the vectorized execution engine frees the CPU from the inefficient state it endures under the Volcano model — where it spends most of its time waiting for data and jumping between functions — enabling it to concentrate its compute power on useful data computation. OceanBase introduced the vectorized framework starting in V3.2 to validate the technical direction, made vectorization the default execution path and implemented TP/AP adaptivity in V4.0, and then completed a systematic, coordinated rewrite of the data format and operator implementation in V4.3, achieving 14× to 40× query acceleration on industry-standard benchmarks such as ClickBench, TPC-H, and TPC-DS. Beyond this, the OceanBase vectorized engine continues to evolve, with broader operator coverage to come; operators that are not yet fully vectorized, such as Window Functions and Recursive CTEs, will be rewritten over time.
For users, all of these improvements take effect by default and require no additional configuration. Whether for everyday report queries, real-time data dashboards, or large-scale data exploration and multi-table correlation analysis, the vectorized engine quietly does its work at the underlying layer, allowing OceanBase to deliver significantly faster analytical query responses under the same hardware resources.
Top comments (0)