What is an optimizer?
- SQL course
- Expert
- Lesson 13/45
- 5 min
Learn the role of the query optimizer.
What is the optimizer?
The optimizer takes a SQL query and picks the cheapest execution plan. It generates several plans for the same query and compares their cost.
EXPLAIN SELECT * FROM orders
WHERE customer_id = 5;The optimizer builds and picks a plan.
All columns are selected from orders. WHERE keeps only the rows where customer_id = 5 holds.
What is the optimizer? — details
The optimizer is rule-based (legacy) or cost-based (modern). Modern databases are cost-based; they pick the best plan from statistics.
Practice What is the optimizer?
Use EXPLAIN to see the plan for customer_id = 5 on orders.
Starter code
-- see the plan
EXPLAINSample solution
EXPLAIN SELECT * FROM orders WHERE customer_id = 5;Expected output
Seq Scan on orders
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 What does the optimizer choose?
- The user name
- The table name
- The execution plan Correct answer
- The column type
Explanation: The optimizer chooses the execution plan.
2 See the plan
Question
____ SELECT * FROM orders WHERE customer_id = 5;
Answer
EXPLAIN SELECT * FROM orders WHERE customer_id = 5;Explanation: EXPLAIN is the right answer.
3 What is an optimizer: match the terms
| Term | Meaning |
|---|---|
optimizer |
optimizer |
plan |
plan |
cost |
cost |
rule |
rule |