How the planner thinks
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
explain how PostgreSQL picks a plan.
Planned topics
- The planner estimates the cost of many possible plans and picks the cheapest estimate.
- Costs come from table statistics and settings such as
random_page_cost. - Selectivity is the fraction of rows a condition keeps; low selectivity (many rows) favours a sequential scan.
- A sequential scan is correct when you need a large share of the table; an index is not always better.
- The plan is only as good as its estimates.
Draft outline details
Demonstration
a diagram: SQL, parse, rewrite, plan (candidates with costs), execute. Show WHERE status = 'PAID' on a table where 90% of rows are paid, and why the planner scans.
Check your understanding
Why can the planner prefer a sequential scan even when an index exists?
Show answer
The condition matches so many rows that reading the index plus the heap costs more.
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.