Optimization challenge
- SQL course
- Expert
- Lesson 43/45
- 6 min
Improve a slow query by reading its plan.
Optimization challenge
Second challenge: view the plan of an email query on users with EXPLAIN. Since email is UNIQUE, an index already speeds it up.
EXPLAIN SELECT * FROM users
WHERE email = 'a@b.com';EXPLAIN shows the plan.
All columns are selected from users. WHERE keeps only the rows where email = 'a@b.com' holds.
Practice Optimization challenge
Run EXPLAIN SELECT * FROM users WHERE email = 'a@b.com' on the previous users table.
Starter code
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email TEXT UNIQUE,
password_hash TEXT
);
-- optimization
EXPLAINSample solution
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email TEXT UNIQUE,
password_hash TEXT
);
-- optimization
EXPLAIN SELECT * FROM users WHERE email = 'a@b.com';Expected output
Index Scan on users_email_key
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Plan command
Question
____ SELECT * FROM users WHERE email = 'a@b.com';
Answer
EXPLAIN SELECT * FROM users WHERE email = 'a@b.com';Explanation: EXPLAIN is the right answer.
2 What does a UNIQUE constraint create?
- An automatic index Correct answer
- A trigger
- A view
- A procedure
Explanation: A UNIQUE constraint auto-creates an index.
3 Optimization challenge: match the terms
| Term | Meaning |
|---|---|
EXPLAIN |
plan |
UNIQUE |
unique |
Index Scan |
index |
Seq Scan |
full scan |