Demo: a bad estimate and how statistics fix it
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
diagnose a bad plan caused by a wrong row estimate.
Draft outline details
Step by step
- Create correlated columns (
country,currency) and a query filtering on both; the planner estimates 5 rows, actual is 300 000. - Show the nested loop chosen because of the underestimate and its runtime.
- Run
CREATE STATISTICS s (dependencies) ON country, currency FROM orders; ANALYZE orders;. - Planned mistake: forget to run
ANALYZEafter creating the statistics; the estimate does not change. Fix: analyze. - Re-run: estimate close to actual, plan switches to a hash join, time drops.
Check your understanding
Why was the estimate wrong?
Show answer
The planner assumed the two columns were independent.
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.