Learn Logical Query Plans | Planning and Optimization

Before a SQL query is executed, the engine first creates a logical query plan. This plan is an abstract representation of what the query intends to accomplish, independent of how the operations will be performed. Logical plans focus on what needs to be done—such as which tables to read, how to combine data, and which columns or rows to return—without specifying the precise algorithms or physical methods that will be used. This separation allows the optimizer to consider multiple strategies for executing the query efficiently.


              123
            
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id;

To illustrate how a logical query plan is constructed, consider the query above that joins the employees and departments tables on their department ID. The logical plan for this query typically involves several key operations:

Scan the employees table to retrieve all rows;
Scan the departments table to retrieve all rows;
Perform a join operation between employees and departments using the condition e.department_id = d.id;
Project (select) only the columns e.name and d.name from the joined result.

Each step in this logical plan describes what needs to happen, not how it will be carried out. The logical plan serves as a blueprint that the optimizer can later transform into a physical plan with specific access paths and algorithms.


              1234
            
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.name = 'Engineering';

When you add conditions to your query, such as filtering departments by name, the logical plan adapts by introducing an additional operation:

Scan the employees table;
Scan the departments table;
Apply a filter to the departments table to keep only rows where name = 'Engineering';
Join the filtered departments rows with employees on department_id = id;
Project the specified columns from the result.

This modular approach makes logical plans flexible and easy to optimize, since each operation is clearly defined and can be rearranged or combined as needed before the query is executed.

1. What is the main purpose of a logical query plan?

2. Which operation is typically included in a logical plan for a join query?

3. Fill in the blank: Logical plans describe ________ operations without specifying how they are performed.

Everything was clear?

Thanks for your feedback!

Section 2. Chapter 1

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Suggested prompts:

Can you explain the difference between a logical and a physical query plan?

How does the optimizer use the logical plan to improve query performance?

Can you show how the logical plan would change with more complex queries?

Swipe to show menu


              123
            
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id;

Scan the employees table to retrieve all rows;
Scan the departments table to retrieve all rows;
Perform a join operation between employees and departments using the condition e.department_id = d.id;
Project (select) only the columns e.name and d.name from the joined result.


              1234
            
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.name = 'Engineering';