SQL, Refreshed
Indexes & EXPLAIN
Lesson 4 of 5
What you'll learn
- Understand why an index makes lookups fast
- Distinguish a sequential scan from an index scan
- Read query cost with
EXPLAIN
Without an index, finding email = 'ada@example.com' means a sequential scan: Postgres reads every row and checks each one. On a million-row table that's a million comparisons. An index is a sorted side-structure (a B-tree) that lets the database jump near the answer in a handful of steps.
CREATE INDEX idx_users_email ON users (email);
SELECT * FROM users WHERE email = 'ada@example.com';
-- With the index, Postgres does an index scan, not a full table scan.
EXPLAIN shows the plan
EXPLAIN asks Postgres what it would do, without running the real work. You'll see either Seq Scan (read everything) or Index Scan (use the index), along with an estimated cost.
EXPLAIN SELECT * FROM users WHERE email = 'ada@example.com';
-- Index Scan using idx_users_email (cost=0.29..8.30 rows=1 ...)
Indexes aren't free: they take space and slow down writes, since every insert must also update the index. You add them for the columns you filter and join on, not for everything.
Index the columns you search by
A good rule of thumb: index the columns that appear in your WHERE, JOIN, and ORDER BY clauses on big tables. An index on a column you never query is pure overhead.
The win is the difference between checking every row and jumping straight to it. The challenge measures that gap.
Run it. Count comparisons for a linear scan, then for a Map-based index, and compare the cost.
Next: making several writes succeed or fail as one.
Sign in to save your progress across devices.