Partial and covering indexes
On This Lesson 4 topics
This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.
In this lesson
shrink indexes and avoid table visits.
Planned topics
- Partial index: a
WHEREclause on the index so it holds only the rows you query (for example, open orders). - The query must imply the index predicate to use it.
- Covering index:
INCLUDE (cols)stores extra columns in the leaf so the query can read only the index. - An index-only scan also needs the visibility map to be current, so vacuum matters (see MID-014).
Draft outline details
Demonstration
CREATE INDEX idx_orders_open ON orders (created_at) WHERE status = 'OPEN';
CREATE INDEX idx_orders_cust_cov ON orders (customer_id) INCLUDE (total_cents);Check your understanding
What must be true for an index-only scan to avoid heap visits?
Show answer
All needed columns are in the index and the pages are marked all-visible.
Practice
Apply this lesson with the module's lab, exercises and quiz.
Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.