Summary - Index tuning

Speed up a query with a covering index.

Summary - Index tuning

Index tuning: B-tree as base, covering index for index-only scan, partial index for few rows, selectivity should be high, maintenance with REINDEX. The right index speeds the right query.

-- CREATE INDEX ... INCLUDE ... WHERE ...
-- ANALYZE -> REINDEX

Index design and maintenance go together.

Exercise Summary - Index tuning

Summary task: create a covering index on orders: customer_id as key, amount in INCLUDE.

Show example solution

CREATE INDEX idx_orders_cust ON orders(customer_id) INCLUDE (amount);

Test yourself

Index maintenance

REINDEX INDEX ____

Show answer

REINDEX INDEX idx_products_name

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

Index-only scan — A covering index enables index-only scan.
Summary - Index tuning: match the terms

B-tree

balanced
covering

covering
partial

partial
REINDEX

rebuild

Next lesson: What is an optimizer?

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies