Query Execution Plans and Operators
What you'll learn: How databases transform your SQL query into a sequence of concrete physical operations that actually retrieve and process your data.
What Is a Query Execution Plan?
When you write a SQL query, you're describing what data you want, not how to get it. The database takes your declarative SQL and converts it into a query execution plan—a step-by-step procedure made up of physical operators that actually fetch, filter, join, and aggregate your data.
Think of it like a recipe: your SQL is "I want chocolate cake," and the execution plan is the detailed steps—measure flour, crack eggs, mix, bake—that produce the result.
Physical Operators Explained
The execution plan consists of physical operators, each performing a specific task:
-
Scan operators: Read data from storage
- Sequential Scan: Read every row in a table (like reading a book page-by-page)
- Index Scan: Use an index (B-tree or hash) to find specific rows quickly
-
Join operators: Combine rows from multiple tables
- Nested Loop Join: For each row in one table, scan matching rows in another
- Hash Join: Build a hash table from one input, probe with the other
- Merge Join: Sort both inputs, then merge them together
-
Aggregation operators: Compute sums, counts, averages
- May use sorting or hashing to group rows
-
Filter operators: Apply WHERE conditions to eliminate unwanted rows
The database optimizer analyzes available indexes, table sizes, and statistics to choose which operators to use and in what order—picking the fastest "recipe" to answer your query.
Key Takeaway: Query execution plans break SQL into physical operators (scans, joins, filters, aggregations) that define the actual steps the database takes to retrieve your data—understanding these helps you optimize query performance.