Comparing results
- SQL course
- Expert
- Lesson 23/45
- 5 min
Compare before and after plans.
Compare results
In the last step you compare estimated and actual time with EXPLAIN ANALYZE. You should see the indexed query truly speed up.
EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 5;EXPLAIN ANALYZE gives actual time.
All columns are selected from orders. WHERE keeps only the rows where customer_id = 5 holds.
Compare results — details
EXPLAIN gives only an estimate; EXPLAIN ANALYZE runs the query and measures actual time. The gap shows how fresh the statistics are and how accurate the plan is.
Practice Compare results
Run EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5 to see the actual time.
Starter code
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;
-- add index
CREATE INDEX idx_orders_customer ON orders (customer_id) INCLUDE (amount);
-- sargable rewrite
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02';
-- compare
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;
-- add index
CREATE INDEX idx_orders_customer ON orders (customer_id) INCLUDE (amount);
-- sargable rewrite
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02';
-- compare
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5;Expected output
Index Scan on orders (actual time=0.015..0.020 rows=3)
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Compare command
Question
EXPLAIN ____ SELECT * FROM orders WHERE customer_id = 5;
Answer
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5;Explanation: ANALYZE is the right answer.
2 What does EXPLAIN ANALYZE measure?
- The actual execution time Correct answer
- The table size
- The column name
- The user
Explanation: EXPLAIN ANALYZE measures actual time.
3 Comparing results: match the terms
| Term | Meaning |
|---|---|
EXPLAIN |
plan |
ANALYZE |
actual |
actual |
measured |
compare |
compare |