SQL 108 🛢️ Query Execution Engine: Volcano Iterator Model vs Vectorized Execution

The query execution engine is the component that takes the physical plan chosen by the optimizer and actually runs it. The optimizer decides what to do—which join algorithm, which index, which order. The execution engine decides how fast to do it. Two execution models dominate the landscape: the Volcano iterator model and vectorized execution. The difference between them is not a minor implementation detail. The original MonetDB/X100 paper measured the Volcano model spending 50–80% of CPU time on interpretation overhead rather than actual computation . For an analytical query scanning 100 million rows across five operators, that overhead alone can account for minutes of wasted CPU time .

The Volcano model is tuple-at-a-time. Each operator implements an open() / next() / close() interface, and each call to next() returns exactly one tuple to the parent operator . The vectorized model keeps the same iterator shape but changes the granularity: each next() call returns a batch of tuples—typically 1024 to 4096—and the operator processes the entire batch in a tight inner loop . The batch amortizes the per-call overhead, and the tight loop allows the compiler to emit SIMD instructions and the CPU to prefetch data into cache. The result is an order-of-magnitude difference on analytical workloads, while the tuple-at-a-time model remains adequate for transactional point queries where the overhead is negligible relative to the I/O cost .

Key point: The Volcano model processes one tuple per next() call through a chain of virtual function calls; vectorized execution processes a batch of 1024–4096 tuples per call, amortizing interpreter overhead and enabling SIMD and cache-friendly loops. PostgreSQL, MySQL, and SQLite use the Volcano model; ClickHouse, DuckDB, Snowflake, and Databricks Photon use vectorized execution.

---

Why the execution model matters

The interpretation tax problem. In the Volcano model, every tuple passes through a chain of virtual function calls. The Project operator calls Agg.next(), which calls Scan.next(), which returns one tuple, which is then processed by Agg, which returns one tuple to Project . Each of those calls is an indirect branch, which defeats branch prediction, and the code jumps between operator implementations, which defeats instruction cache locality. The MonetDB/X100 paper measured this overhead at 50–80% of total CPU time on analytical queries . The engine spends more time deciding what to do than doing it.

The SIMD problem. A tight loop over an array of integers can be vectorized by the compiler into SIMD instructions that process 4, 8, or 16 values per cycle. A loop that calls a virtual function per row cannot. The compiler cannot see across the virtual call boundary, so it cannot auto-vectorize. Vectorized execution pulls the loop inside the operator, where the compiler can see the entire computation and emit the SIMD instructions . The SIMD is not the only benefit—even scalar code in a tight batch loop beats the iterator model because the loop overhead is amortized—but SIMD is the multiplier on top.

The cache problem. The Volcano model keeps the state of every operator in the chain active simultaneously. The hash table of a join, the cursor of a scan, the partial aggregate of a group-by—all of these are live at the same time, and their combined working set may not fit in L1 or L2 cache . Vectorized execution materializes a batch of tuples, processes it through the entire pipeline, and then moves to the next batch. The working set of a batch is small enough to stay in cache between operators, which means the memory bandwidth is used for data, not for operator state .

The transactional exception. The Volcano model is not obsolete. A point query that touches three rows does not care about per-tuple overhead. The virtual call cost is dwarfed by the network round trip and the disk seek. PostgreSQL, MySQL, and SQL Server’s default rowstore all use the Volcano model because their primary workload is transactional, and the model’s simplicity and bounded memory are more valuable than the analytical throughput that vectorization provides . SQL Server’s columnstore index uses batch-mode execution for analytical queries while the rowstore uses row-mode execution, combining both models in one engine .

---

a. The Volcano iterator model

The Volcano model defines every operator with three functions: open(), next(), and close(). The open() function initializes the operator and its children. The next() function returns one tuple or an end-of-stream marker. The close() function releases resources .

// A simplified scan operator in the Volcano model
// Each call to next() returns one tuple
Tuple* Scan_next(ScanState* state) {
    // Read the next row from the table
    Row* row = read_next_row(state->cursor);
    if (row == NULL) {
        // No more rows; signal end of stream
        return NULL;
    }
    // Convert the row into a tuple for the parent operator
    return make_tuple(row);
}

// The parent operator calls next() on its child
Tuple* Project_next(ProjectState* state) {
    // Get one tuple from the child
    Tuple* input = state->child->next(state->child);
    if (input == NULL) return NULL;
    // Evaluate the projection expression on the tuple
    return evaluate_projection(state->expr, input);
}

The Project_next function calls state->child->next(), which is a virtual function call. The child pointer could point to a Scan, an Agg, or any other operator. The CPU must resolve the target at runtime, which means branch prediction and instruction prefetching are less effective . The call happens once per tuple, so a million-row scan produces a million indirect calls through the operator chain.

The strength of the Volcano model is its composability and its bounded memory. An operator can stream its input without materializing the entire result set. A Limit operator can stop calling next() after ten tuples, and the upstream operators stop producing. A NestedLoop join can interleave the outer and inner loops without materializing either side . This is why the model dominates transactional systems, where queries are small and the ability to stop early is more valuable than raw throughput.

---

b. Vectorized execution

Vectorized execution keeps the next() interface but changes the return type from one tuple to a batch of tuples. The operator’s internal loop processes the entire batch, and the batch size is chosen to fit in L1 or L2 cache—typically 1024 to 4096 rows .

// A vectorized scan operator
// Each call to next() fills a batch with up to BATCH_SIZE tuples
int Scan_next_batch(ScanState* state, Batch* out) {
    int count = 0;
    // Read up to BATCH_SIZE rows into the batch
    while (count < BATCH_SIZE && state->cursor->has_next()) {
        out->rows[count] = state->cursor->next();
        count++;
    }
    // Return the number of tuples in the batch
    return count;
}

// A vectorized filter processes the entire batch in one loop
int Filter_next_batch(FilterState* state, Batch* in, Batch* out) {
    int count = 0;
    // The loop is tight; no virtual calls per row
    for (int i = 0; i < in->count; i++) {
        // Evaluate the predicate on this row
        if (evaluate_predicate(state->expr, in->rows[i])) {
            // Copy the surviving row to the output batch
            out->rows[count] = in->rows[i];
            count++;
        }
    }
    // Return the number of surviving tuples
    return count;
}

The Filter_next_batch function is a tight loop over an array. The compiler can see the entire loop body, auto-vectorize it, and use SIMD to evaluate the predicate on multiple rows per instruction. The batch size of 1024–4096 is the trade-off between amortizing the call overhead and keeping the working set in cache . DuckDB uses a DataChunk of 2048 rows; ClickHouse uses a Block that is operator-tunable; Apache DataFusion uses an Arrow RecordBatch .

The selection vector is the supporting trick that makes vectorized execution efficient. A filter does not copy the surviving rows into a new batch; it writes the indices of the surviving rows into a selection vector, and the downstream operator uses the selection vector to read the surviving rows from the original batch . This avoids the copy until the data must be materialized, which saves memory bandwidth and keeps the batch in cache.

---

c. The compilation alternative and the hybrid reality

Vectorized execution is not the only alternative to the Volcano model. Code generation—compiling the query plan into machine code—is the third approach, used by Amazon Redshift, HyPer, and Umbra . The compiled code fuses the operators into a single tight loop with no virtual calls, no interpretation, and no batch overhead. HyPer’s data-centric compilation produces “near optimal” code where the data stays in CPU registers as much as possible .

The trade-off is compilation latency. A query that takes 10 milliseconds to execute but 100 milliseconds to compile is not viable for an interactive application. Redshift uses a multi-tenant code cache with a high hit rate to amortize the compilation cost, but even so, about half of its end-to-end latency appears to be compilation . Vectorized execution sits between the two extremes: it removes most of the interpretation overhead without the compilation latency, and it is the practical choice for general-purpose analytical engines.

-- The execution model is not visible in the SQL
-- The same query runs under Volcano or vectorized depending on the engine
SELECT status, COUNT(*), SUM(total)
FROM orders
WHERE created_at > NOW() - INTERVAL '7 days'
GROUP BY status;

-- PostgreSQL (Volcano): one tuple at a time
-- Each row passes through Scan → Filter → Agg through virtual calls
-- Overhead per row: ~20 ns of interpretation

-- ClickHouse (vectorized): 4096 rows at a time
-- Each batch passes through Scan → Filter → Agg in tight loops
-- Overhead per batch: amortized over 4096 rows

The SQL is identical. The engine is the difference. PostgreSQL will run the query correctly, and for a small orders table the difference is imperceptible. For a billion-row table, the vectorized engine completes in seconds while the Volcano engine takes minutes, because the interpretation overhead is proportional to the row count .

---

Complete Example Session

The following session traces the same query through both execution models, showing the operator calls and the batch processing.

// ============================================
// PART 1: THE QUERY
// ============================================
// SELECT status, COUNT(*) FROM orders WHERE total > 100 GROUP BY status
// The plan is: Scan → Filter → HashAggregate
// The table has 10,000,000 rows

// ============================================
// PART 2: VOLCANO EXECUTION
// ============================================
// HashAggregate.next() calls Filter.next()
// Filter.next() calls Scan.next()
// Scan.next() returns row 1
// Filter evaluates total > 100 on row 1
// If true, returns row 1 to HashAggregate
// HashAggregate updates the hash table for row 1's status
// HashAggregate.next() returns the aggregate for status 1
// ... repeat 10,000,000 times
// Each iteration: 3 virtual calls, 1 predicate evaluation, 1 hash update

// ============================================
// PART 3: VECTORIZED EXECUTION
// ============================================
// HashAggregate.next_batch() calls Filter.next_batch()
// Filter.next_batch() calls Scan.next_batch()
// Scan fills a batch with 2048 rows
// Filter loops over 2048 rows, evaluates total > 100 on each
// Filter writes the surviving indices to a selection vector
// HashAggregate loops over the selection vector, updates the hash table
// HashAggregate returns the aggregates for the batch
// ... repeat 4,882 times (10,000,000 / 2048)
// Each iteration: 3 virtual calls, 2048 predicate evaluations, 2048 hash updates

// ============================================
// PART 4: THE OVERHEAD COMPARISON
// ============================================
// Volcano: 10,000,000 iterations × 3 virtual calls = 30,000,000 calls
// Vectorized: 4,882 iterations × 3 virtual calls = 14,646 calls
// The call overhead is reduced by a factor of ~2048
// The predicate evaluation and hash update work is the same

// ============================================
// PART 5: THE CACHE EFFECT
// ============================================
// Volcano: the hash table, the scan cursor, and the filter state
// are all live at the same time; the working set may not fit in cache
// Vectorized: the 2048-row batch fits in L1/L2; the hash table is
// the only large structure, and it is accessed in a tight loop

The five parts cover the query plan, the Volcano call sequence, the vectorized batch sequence, the overhead comparison, and the cache effect. What the session demonstrates is that the vectorized model does the same work per row but amortizes the per-row overhead over a batch.

---

Quick Reference

Model Comparison

Aspect	Volcano Iterator	Vectorized
Rows per next()	1	1024–4096
Per-row overhead	50–80% of CPU	Negligible
SIMD-friendly	No	Yes
Cache locality	Poor	Tight batch
Memory	Bounded, streaming	Batch-sized
Engines	PostgreSQL, MySQL, SQLite	ClickHouse, DuckDB, Snowflake

When to Use

Workload	Model
OLTP point queries	Volcano
OLAP aggregations	Vectorized
Mixed workload	Hybrid (SQL Server columnstore)
Low compilation latency	Vectorized
Maximum throughput	Compiled (Redshift, Umbra)

Supporting Techniques

Technique	Purpose
Selection vector	Filter without copying
Batch size 1024–4096	Amortize overhead, fit cache
Operator fusion	Tight loop, no calls
Columnar storage	Batch access pattern

---

Best Practices

✅ Do This:

-- Use a vectorized engine for analytical workloads
-- ClickHouse, DuckDB, Snowflake
SELECT status, COUNT(*) FROM orders GROUP BY status;

❌ Don’t Do This:

-- Expect a Volcano engine to match vectorized throughput on scans
-- PostgreSQL will be slower on aggregation-heavy queries

---

Common Pitfalls

Pitfall	Why It Happens	Fix
Slow aggregation on PostgreSQL	Volcano interpretation overhead	Use a vectorized engine
Vectorized engine not faster	Small dataset	Overhead amortization needs scale
Compilation latency	JIT engines	Use vectorized for short queries
Batch size wrong	Cache thrashing	Tune to L1/L2 size

---

Real-World Examples

1. Volcano in PostgreSQL

-- PostgreSQL uses ExecProcNode, a function pointer per operator
EXPLAIN ANALYZE SELECT ...;

2. Vectorized in ClickHouse

-- ClickHouse processes 4096-row blocks
SELECT count() FROM large_table;

3. Vectorized in DuckDB

-- DuckDB uses DataChunk of 2048 rows
SELECT sum(x) FROM generate_series(1, 1000000) t(x);

4. Hybrid in SQL Server

-- Columnstore index uses batch mode
CREATE COLUMNSTORE INDEX ON fact;

5. Compiled in Redshift

-- Redshift compiles queries to machine code
SELECT ...;

6. Selection vector

-- Filter writes indices, not copies
sel[0] = 5; sel[1] = 7; sel[2] = 12;

7. Batch size

-- DuckDB: 2048
-- ClickHouse: 4096
-- Vectorwise: 1024

8. Operator fusion

-- a * b + c + 1 runs as one kernel

9. Columnar storage

-- Columnar layout feeds the batch

10. OLTP point query

-- Volcano is fine for 3 rows
SELECT * FROM users WHERE id = 42;

---

Visual

Volcano Call Chain

┌──────────────────────────────────────────────┐
│  Project.next()                              │
│         │                                    │
│         ▼                                    │
│  Agg.next()                                  │
│         │                                    │
│         ▼                                    │
│  Scan.next() → returns 1 tuple               │
│         │                                    │
│         ▼                                    │
│  Tuple processed, returned to parent         │
│  Repeat per tuple                            │
└──────────────────────────────────────────────┘

Vectorized Batch

┌──────────────────────────────────────────────┐
│  Project.next_batch()                        │
│         │                                    │
│         ▼                                    │
│  Agg.next_batch()                            │
│         │                                    │
│         ▼                                    │
│  Scan.next_batch() → fills 2048-row batch    │
│         │                                    │
│         ▼                                    │
│  Tight loop over 2048 rows                   │
│  No virtual calls per row                    │
└──────────────────────────────────────────────┘

Overhead Comparison

┌──────────────────────────────────────────────┐
│  VOLCANO                                     │
│  overhead × N_rows                           │
│  50-80% of CPU time on interpretation        │
│                                              │
│  VECTORIZED                                  │
│  overhead × (N_rows / 2048) + tight loops    │
│  Negligible per-row overhead                 │
└──────────────────────────────────────────────┘

---

Summary

Item	Value
Volcano model	Tuple-at-a-time, virtual calls
Vectorized model	Batch-at-a-time, tight loops
Batch size	1024–4096 rows
Volcano overhead	50–80% of CPU
Vectorized benefit	Order of magnitude on OLAP
SIMD	Vectorized enables it
Cache	Vectorized keeps batch in L1/L2
OLTP engines	Volcano (PostgreSQL, MySQL)
OLAP engines	Vectorized (ClickHouse, DuckDB)

Key takeaways:

• The Volcano model processes one tuple per next() call. Each operator implements open() / next() / close(), and the parent calls next() on its child through a virtual function call. The model is composable and memory-bounded, but the per-tuple overhead dominates analytical workloads .
• Vectorized execution processes a batch of 1024–4096 tuples per next() call. The batch amortizes the virtual call overhead, and the tight inner loop allows the compiler to emit SIMD and the CPU to prefetch data into cache .
• The MonetDB/X100 paper measured the Volcano overhead at 50–80% of CPU time. The interpretation work—deciding what to do—was larger than the computation itself on analytical queries .
• The batch size is a cache trade-off. It must be large enough to amortize the call overhead and small enough to keep the working set in L1 or L2 cache. DuckDB uses 2048; ClickHouse uses 4096 .
• The selection vector avoids copying. A filter writes the indices of surviving rows into an array, and the downstream operator uses the indices to read the surviving rows from the original batch .
• Volcano is still the right model for OLTP. A point query that touches three rows does not care about per-tuple overhead. PostgreSQL, MySQL, and SQL Server’s rowstore all use the Volcano model because their primary workload is transactional .
• SQL Server’s columnstore index uses batch mode. The same engine can run row-mode for OLTP and batch-mode for OLAP, mixing both models in one plan .

Remember: The execution model is the engine’s decision about how to traverse the plan. The Volcano model traverses it one tuple at a time, with a virtual call per operator per tuple. The vectorized model traverses it one batch at a time, with a tight loop per operator per batch. The SQL is the same. The plan is the same. The difference is the overhead per row. For a transactional query that touches ten rows, the overhead is irrelevant. For an analytical query that touches a billion rows, the overhead is the difference between seconds and minutes. The engine you choose—or the engine you inherit—determines which model runs under your SQL.

---

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!