Adding indexes

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.

Exercise Add an index

Add CREATE INDEX idx_orders_customer ON orders (customer_id) INCLUDE (amount) to the previous solution.

Show example 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);

Test yourself

What does a covering index enable?
- Seq scan
- Full scan
- Index-only scan
- Cross join
Show answer

Index-only scan — A covering index enables index-only scan.

INCLUDE clause

CREATE INDEX idx_orders_customer ON orders (customer_id) ____ (amount)

Show answer

CREATE INDEX idx_orders_customer ON orders (customer_id) INCLUDE (amount)

Adding indexes: match the terms

covering

covering
INCLUDE

add
index-only

index-only
cost

cost

Next lesson: Rewriting queries

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies