What is the difference between a logical query plan and a physical query plan?

A logical query plan represents relational algebra operations (Filter, Project, Join) independently of hardware or algorithms. A physical query plan specifies concrete execution algorithms—such as choosing an Index Scan instead of a Seq Scan, or a Hash Join instead of a Nested Loop.

How does a Cost-Based Optimizer (CBO) estimate query cost?

The CBO calculates cost based on table statistics stored in system catalogs (e.g. pg_statistic). It estimates required disk page I/O reads (random vs sequential) and CPU operations using statistical histograms, most-common-value (MCV) lists, and distinct value counts.

What is the Volcano iterator model in database engines?

The Volcano model (pipeline processing) executes queries via a tree of physical operators. Each operator exposes three methods: open(), next(), and close(). The parent operator calls next() on its children, pulling rows one at a time up the tree, minimizing memory overhead.

Why is vectorized query execution faster for analytical databases?

Traditional row-by-row Volcano execution suffers from heavy function call overhead and poor CPU cache locality. Vectorized execution (used in BigQuery, DuckDB, and Snowflake) processes batches of thousands of column values at a time using CPU SIMD instructions.

Engineering Workflow

How SQL Query Engines Actually Execute Queries: The Complete Lifecycle

By Sharon Ben-Moshe · October 13, 2026 · 2 min read

Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.

Journey through the SQL query lifecycle. Explore lexical parsing, Abstract Syntax Trees, logical optimization, Cost-Based Optimizers, and Volcano execution.

Quick answer

SQL is a declarative language: you specify WHAT data you want, not HOW to retrieve it. The database engine executes queries through a five-stage pipeline: 1) Parser & Lexer (generates the Abstract Syntax Tree), 2) Semantic Analyzer (validates schemas and resolves types), 3) Rewriter (expands views and folds constants), 4) Cost-Based Optimizer (evaluates statistical costs to choose physical algorithms), and 5) Execution Engine (processes rows via Volcano iterator or vectorized models).

Key takeaways

The query engine translates declarative SQL into an executable tree of relational algebra operators.

The Cost-Based Optimizer (CBO) tests dozens of plan combinations to find the lowest estimated disk and CPU cost.

Stale statistics cause the CBO to select disastrous query plans; regular ANALYZE runs are vital.

Execution engines pull rows dynamically through buffer pool caches before falling back to physical disk reads.

The 5 stages of SQL query execution

Lexing and Parsing: The query string is tokenized and verified against SQL grammar rules to build an Abstract Syntax Tree (AST). Syntax errors fail here.

Semantic Analysis (Binding): The engine checks system catalogs to verify that referenced tables and columns exist, resolves aliases, and confirms user permissions.

Query Rewriting: Views are expanded into the AST, constant expressions are pre-computed (constant folding), and redundant subqueries are flattened.

Cost-Based Optimization (CBO): The optimizer explores join order permutations and index choices, assigning a numerical cost estimate based on statistics.

Execution Engine: The chosen physical plan executes, streaming rows from disk or the shared buffer memory pool to the client.

Visualizing declarative query versus physical execution tree

-- Declarative SQL query submitted by client:
SELECT
  c.name,
  SUM(o.amount) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE c.country = 'US'
GROUP BY c.name;

-- Physical Execution Plan compiled by CBO (Volcano Tree):
-- -> Aggregate (HashAggregate on c.name)
--    -> Hash Join (Inner Join on c.id = o.customer_id)
--       -> Bitmap Heap Scan on customers c (Filter: country = 'US')
--          -> Bitmap Index Scan on idx_customers_country
--       -> Hash (Builds in-memory hash table on orders)
--          -> Seq Scan on orders o

How SQL Query Engines Actually Execute Queries: The Complete Lifecycle

Quick answer

FAQs

What is the difference between a logical query plan and a physical query plan?

How does a Cost-Based Optimizer (CBO) estimate query cost?

What is the Volcano iterator model in database engines?

Why is vectorized query execution faster for analytical databases?

SQL Query Optimization and Tuning: The End-to-End Practitioner's Guide

Relational Database Schema Design and Normalization: From 1NF to 5NF and Pragmatic Denormalization

The Complete Guide to SQL Transactions and Concurrency: ACID, Isolation Levels, and Locks

Key takeaways

The 5 stages of SQL query execution

The role of the buffer pool

Official references

Quick answer

FAQs

What is the difference between a logical query plan and a physical query plan?

How does a Cost-Based Optimizer (CBO) estimate query cost?

What is the Volcano iterator model in database engines?

Why is vectorized query execution faster for analytical databases?

Keep reading

SQL Query Optimization and Tuning: The End-to-End Practitioner's Guide

Relational Database Schema Design and Normalization: From 1NF to 5NF and Pragmatic Denormalization

The Complete Guide to SQL Transactions and Concurrency: ACID, Isolation Levels, and Locks

Key takeaways

The 5 stages of SQL query execution

The role of the buffer pool

Related SQL guides

Official references