Reading plans
- SQL course
- Expert
- Lesson 20/45
- 5 min
Inspect the slow plan with EXPLAIN.
Read the plan
In the second step you read the query plan with EXPLAIN. The plan shows whether the database uses an index or scans the whole table.
EXPLAIN SELECT * FROM orders
WHERE customer_id = 5;The plan shows Seq Scan or Index Scan.
All columns are selected from orders. WHERE keeps only the rows where customer_id = 5 holds.
Read the plan — details
If the plan shows Seq Scan, no index is used. Index Scan means an index is in play. The cost numbers show how cheap the plan is.
Practice Read the plan
Run EXPLAIN SELECT * FROM orders WHERE customer_id = 5 on the previous orders table.
Starter code
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INT,
amount NUMERIC,
created_at TIMESTAMP
);
-- read plan
EXPLAINSample solution
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INT,
amount NUMERIC,
created_at TIMESTAMP
);
-- read plan
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 Plan command
Question
____ SELECT * FROM orders WHERE customer_id = 5;
Answer
EXPLAIN SELECT * FROM orders WHERE customer_id = 5;Explanation: EXPLAIN is the right answer.
2 What does Seq Scan indicate?
- An index scan
- A full table scan Correct answer
- A hash join
- A merge join
Explanation: Seq Scan indicates a full table scan.
3 Reading plans: match the terms
| Term | Meaning |
|---|---|
EXPLAIN |
plan |
Seq Scan |
full scan |
Index Scan |
index |
cost |
cost |