عرض عملي: تقدير خاطئ وكيف تصلحه الإحصائيات

خطوة بخطوة

1. Create correlated columns (country, currency) and a query filtering on both; the planner estimates 5 rows, actual is 300 000.
2. Show the nested loop chosen because of the underestimate and its runtime.
3. Run CREATE STATISTICS s (dependencies) ON country, currency FROM orders; ANALYZE orders;.
4. Planned mistake: forget to run ANALYZE after creating the statistics; the estimate does not change. Fix: analyze.
5. Re-run: estimate close to actual, plan switches to a hash join, time drops.

اختبر فهمك