Adding indexes
- SQL course
- Expert
- Lesson 21/45
- 5 min
Speed up the query with a covering index.
Add an index
In the third step you add a covering index to speed up the customer_id query. INCLUDE (amount) enables an index-only scan.
CREATE INDEX idx_orders_customer
ON orders (customer_id)
INCLUDE (amount);A covering index enables index-only scan.
The index idx_orders_customer is created.
Add an index — details
After the index is added, EXPLAIN turns from Seq Scan to Index Scan. The cost drops and reads speed up. The query now reads from the index without touching the table.
Practice Add an index
Add CREATE INDEX idx_orders_customer ON orders (customer_id) INCLUDE (amount) to the previous solution.
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
CREATESample 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);Expected output
CREATE INDEX
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 What does a covering index enable?
- Seq scan
- Full scan
- Index-only scan Correct answer
- Cross join
Explanation: A covering index enables index-only scan.
2 INCLUDE clause
Question
CREATE INDEX idx_orders_customer ON orders (customer_id) ____ (amount)
Answer
CREATE INDEX idx_orders_customer ON orders (customer_id) INCLUDE (amount)Explanation: INCLUDE is the right answer.
3 Adding indexes: match the terms
| Term | Meaning |
|---|---|
covering |
covering |
INCLUDE |
add |
index-only |
index-only |
cost |
cost |