Module 4 · Query optimization

Episode 28 Cardinality Estimation

8 min · SQL · Postgres

Title card of Episode 28: Cardinality Estimation

▶ This video is coming to YouTube soon.

Postgres estimated 200 customers in Paris and France; there were 1,000. This episode shows how row estimates multiply up a plan, why the independence assumption breaks on correlated columns, how two errors compound to an estimate of 6 rows for 29,755, and how CREATE STATISTICS (dependencies, MCV lists, ndistinct, expressions) fixes what it can, all checked against real EXPLAIN ANALYZE output.

Chapters

  1. 0:00Intro
  2. 0:13Paris is in France
  3. 0:33Estimates multiply up the plan
  4. 1:18How a join is estimated
  5. 2:14The independence assumption
  6. 2:56Errors compound across joins
  7. 3:40Where every estimate comes from
  8. 4:16Proof: estimated against actual rows
  9. 4:50Statistics on pairs of columns
  10. 5:46The code: AND with a dependency
  11. 6:16Proof: CREATE STATISTICS
  12. 7:23Which statistic fixes what
  13. 7:51Recap
  14. 8:16Next: Join Ordering