Demo: one query, five indexes, five plans
On This Lesson 3 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
see how each index design changes the plan for the same query.
Draft outline details
Step by step
- Query: recent paid orders of one customer on the 10 M-row table; the baseline plan is a sequential scan.
- Add a single-column index on
customer_id; compare plan and time. - Add
(customer_id, created_at DESC); the sort step disappears. - Add a partial index on
status = 'PAID', then a covering index withINCLUDE; observe an index-only scan. - Planned mistake: create
(created_at, customer_id); the planner ignores it for this query. Fix: reverse the column order. - Record plan type, buffers, and time for all five.
Prepares MID-012-LAB1.
Check your understanding
Which index gave the cheapest plan and why?
Show answer
The covering composite: right order and no heap visits.
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.