Optimization challenge

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.

Exercise Optimization challenge

Run EXPLAIN SELECT * FROM users WHERE email = 'a@b.com' on the previous users table.

Show example solution

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT UNIQUE,
  password_hash TEXT
);
-- optimization
EXPLAIN SELECT * FROM users WHERE email = 'a@b.com';

Test yourself

Plan command

____ SELECT * FROM users WHERE email = 'a@b.com';

Show answer

EXPLAIN SELECT * FROM users WHERE email = 'a@b.com';

What does a UNIQUE constraint create?
- An automatic index
- A trigger
- A view
- A procedure
Show answer

An automatic index — A UNIQUE constraint auto-creates an index.
Optimization challenge: match the terms

EXPLAIN

plan
UNIQUE

unique
Index Scan

index
Seq Scan

full scan

Next lesson: Security challenge

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies