SQL Query Executor: Volcano Iterator Model

In relational database management systems (RDBMS), the Volcano Iterator Model (also known as the Pipeline or Iterator Model) is the classic framework for query execution. Designed in the 1990s when memory was highly constrained, it structures physical database operators as a tree of iterators that process data by pulling tuples up the plan tree.

Mental Model

Think of the Volcano model like an assembly line with a demand-driven pull system. The worker at the end of the line (the root node, e.g., the Project operator) asks the worker before them for a single item (next()). That worker asks the next, and so on, propagating requests all the way down to the warehouse (table scan). If an operator needs to sort or join items (a pipeline breaker like Hash Join), it must consume enough input to build its state before it can pass the first result forward; cancellation must then close that state to release buffers.

Components and Why They Exist

Project Operator

Evaluates target expressions and prepares the final column list to return to the caller at the root of the query plan. Pulls tuples from its child, computes projections (such as selecting specific fields or applying scalar functions), and yields them. In the demo, watch how it initiates the execution by requesting a row from its child and formatting the final output.

Hash Join Operator

Joins tuples from two separate relation streams by matching join keys in memory. Consumes the entire build relation to construct an in-memory hash table, then probes it using tuples from the probe relation. In the demo, observe the build phase accumulate tuples and pause streaming (pipeline breaker) before probing begins.

Filter Operator

Discards rows that do not satisfy query predicate conditions early in the query pipeline. Repeatedly requests rows from its child, testing each against a boolean expression, and only returns rows that pass. In the demo, watch how it filters out rows that do not match the search condition, dropping them instead of passing them up.

Seq Scan (Sequential Scan)

Serves as a data source that reads all records directly from raw storage when no index can be leveraged. Scans the table block by block, retrieving and returning one row at a time on each parent request. In the demo, watch it retrieve sequential row IDs from disk to satisfy the pull requests from the Filter.

Index Scan

Provides fast, filtered data access by traversing an index structure (like a B+ tree) instead of scanning the full table. Traverses the index to locate matching record pointers, retrieving only the matching rows. In the demo, observe it scan index entries and fetch target tuples directly to act as the probe input.

Edge Cases and Misconceptions

• Volcano is Pull-Based, not Push-Based: Unlike push-based stream processing systems, Volcano is entirely demand-driven. Rows are only computed when a parent operator requests them via a next() call.
• Pipelining is not Instantaneous: While streaming operators (Filter, Project) process and forward rows instantly, blocking operators (Sort, Hash Join, Aggregation) must accumulate all input rows before producing their first output row, breaking the pipeline and creating memory and latency spikes.
• High CPU Overhead on Large Datasets: Because Volcano makes a virtual function call (next()) for every single row, processing millions of rows incurs severe CPU branch mispredictions and instruction cache misses, making it less efficient for analytical workloads (OLAP) than columnar vectorization or JIT compilation.