Partial index
- SQL course
- Expert
- Lesson 9/45
- 5 min
Index a subset of rows.
Partial index
A partial index indexes only rows that meet a condition. A WHERE clause shrinks the index size and trims maintenance cost.
CREATE INDEX idx_users_active
ON users(email)
WHERE active = true;A partial index indexes fewer rows.
The index idx_users_active is created. WHERE keeps only the rows where active = true holds.
Partial index — details
If active users dominate, a partial index helps little. But when inactive users are a small minority that is queried often, a partial index saves both space and time.
Practice Partial index
Create an email index on users for active users only: WHERE active = true.
Starter code
-- partial index
CREATE INDEX idx_users_active ON users(email)Sample solution
CREATE INDEX idx_users_active ON users(email) WHERE active = true;Expected output
CREATE INDEX
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Partial index: match the terms
| Term | Meaning |
|---|---|
partial |
partial |
WHERE |
condition |
subset |
subset |
small |
small |
2 Partial index condition
Question
CREATE INDEX idx_users_active ON users(email) ____
Answer
CREATE INDEX idx_users_active ON users(email) WHERE active = trueExplanation: WHERE active = true is the right answer.
3 When is a partial index useful?
- When the whole table is read
- When there are many writes
- When a few rows are queried often Correct answer
- Never
Explanation: A partial index helps when a small subset is queried often.