Executor Role

Every SQL query you write—whether a simple SELECT or a complex multi-table JOIN with aggregations—begins its journey as text and ends as a result set. But between these two endpoints lies a sophisticated machinery that transforms optimized query plans into actual data retrieval operations. This machinery is the Query Execution Engine, often simply called the Executor.

The executor is where theoretical planning meets practical reality. While the query optimizer determines what should happen and in what order, the executor determines how it actually happens at the metal level. It orchestrates data access, coordinates operators, manages memory, and assembles the final results that applications consume.

To fully appreciate the executor's role, we must first understand where it fits in the query processing pipeline. Query processing in a relational database management system follows a well-defined sequence of stages, each building upon the output of its predecessor.

The Query Processing Pipeline:

When a SQL query arrives at the database engine, it traverses the following stages:

1. Parsing — The SQL text is analyzed for syntactic correctness and converted into a parse tree
2. Semantic Analysis — The parse tree is validated for semantic correctness (table/column existence, type compatibility)
3. Query Rewriting — The query may be transformed using rules (view expansion, predicate simplification)
4. Query Optimization — The optimizer generates and evaluates multiple execution strategies, selecting the optimal plan
5. Query Execution — The executor takes the optimized plan and produces actual results

The executor is the terminal stage of this pipeline. It receives an optimized physical query plan (also called an execution plan or access plan) and is responsible for carrying out all the operations specified in that plan.

The query execution engine bears numerous responsibilities that collectively ensure queries execute correctly, efficiently, and reliably. Understanding these responsibilities provides crucial insight into database internals and helps explain observed query behavior.

Primary Responsibilities:

Supporting Responsibilities:

Beyond these primary duties, the executor also handles several supporting concerns:

The executor receives its instructions in the form of a physical execution plan—a tree-structured representation of the query with concrete implementation choices for each operation. Understanding this structure is essential for comprehending how the executor functions.

Physical Plan as an Operator Tree:

A physical execution plan is typically represented as a tree where:

• Each node represents a physical operator (e.g., Sequential Scan, Hash Join, Sort)
• Edges represent data flow between operators
• The root produces the final query result
• Leaves typically access base tables or indexes

Data flows from leaves toward the root: leaf operators produce tuples, intermediate operators transform them, and the root delivers the final result.

Operator Nodes Contain:

Each operator node in the physical plan contains detailed information needed for execution:

Modern database systems implement the executor using a modular, extensible architecture that cleanly separates concerns and allows for flexible operator composition. The most prevalent design pattern is the Volcano or Iterator Model, which we'll explore in depth in the next page. Here, we examine the architectural components that support query execution.

Key Architectural Components:

Operator Interface Design:

The elegance of the executor architecture lies in the uniform interface that all operators implement. Regardless of whether an operator is a simple table scan or a complex hash join, it presents the same interface to its parent. This uniformity enables:

1. Composability — Any operator can be connected to any other operator that produces compatible output
2. Extensibility — New operators can be added without modifying existing infrastructure
3. Optimization Transparency — The optimizer can freely substitute equivalent operators without affecting execution machinery
4. Testing Simplicity — Each operator can be tested in isolation against the standard interface

The executor does not operate in isolation—it constantly interacts with other major database subsystems. Understanding these interactions reveals how different parts of a DBMS collaborate to serve queries.

Key Interaction Points:

Buffer Manager Interaction:

The most frequent interaction is with the buffer manager (or buffer pool). The executor continuously requests pages from tables and indexes during query execution. The buffer manager:

• Translates (table_id, page_id) requests into actual disk reads when necessary
• Maintains an in-memory cache of recently accessed pages
• Implements replacement policies (LRU, clock, etc.) to manage cache misses
• Provides pin/unpin semantics so the executor can safely operate on page contents

Efficient buffer management is critical for executor performance. A well-tuned buffer pool can eliminate the majority of physical I/O operations, making the difference between a query completing in milliseconds versus seconds.

The executor's design and implementation profoundly impact query performance. While the optimizer chooses the execution strategy, the executor's efficiency determines how fast that strategy executes. Several factors influence executor performance:

Key Performance Factors:

The Tuple Processing Overhead Problem:

Traditional row-at-a-time execution (which we'll explore in the Iterator Model page) processes each tuple individually through the operator tree. For a 10-operator plan processing 1 million rows, this means:

• 10 million function calls just for tuple passing
• Millions of virtual function dispatches
• Poor instruction cache utilization due to branching
• Memory access patterns that defeat CPU prefetching

Modern systems address this through techniques like:

• Vectorized Execution — Processing batches of tuples together (covered in later chapters)
• Just-In-Time Compilation — Generating optimized machine code for specific query plans
• Push-Based Execution — Inverting control flow for better cache utilization

Understanding how production database systems implement their executors provides practical insight into the concepts we've discussed. While the core principles remain consistent, each system makes distinct engineering trade-offs.

PostgreSQL Executor:

PostgreSQL's executor is a classic implementation of the Volcano iterator model. Key characteristics include:

• Plan nodes implement Init/Exec/End methods
• Tuples flow one at a time from leaves to root
• Expression evaluation uses a tree-walking interpreter (with optional JIT compilation)
• Extensive use of tuple slots for memory efficiency
• Per-query memory contexts prevent leaks and simplify cleanup

MySQL Executor:

MySQL's execution engine has evolved significantly across versions. The modern implementation features:

• Iterator-based execution (refactored from handler-based in MySQL 8.0+)
• Batch-oriented processing capabilities
• Integration with pluggable storage engines (InnoDB, etc.)
• Handler interface abstracts storage engine differences

Oracle Executor:

Oracle's executor is highly sophisticated, featuring:

• Row sources that produce rows on demand
• Adaptive query execution that can modify plans mid-flight
• Extensive statistics collection during execution
• In-memory columnar processing capabilities
• Real-time plan adjustments based on observed cardinalities