Module 4 · Query optimization

Episode 27 Statistics and Cost Models

9 min · SQL · Postgres

Title card of Episode 27: Statistics and Cost Models

▶ This video is coming to YouTube soon.

EXPLAIN says a scan of our customers table costs 184.00 and returns 10,000 rows, without reading it. This episode shows where those numbers come from: ANALYZE's two-stage sample, the columns of pg_stats (MCV lists, equi-depth histograms, n_distinct), how =, < and BETWEEN become row estimates, and the cost units. Then it reproduces Postgres's exact estimates and costs from the real statistics.

Chapters

  1. 0:00Intro
  2. 0:15Numbers without reading a row
  3. 0:36ANALYZE reads a sample
  4. 1:42From a sample to the MCV list
  5. 2:40What pg_stats keeps, per column
  6. 3:17Equal width, or equal depth
  7. 4:13A range, read off the histogram
  8. 5:06The code: a range from a histogram
  9. 5:43What a unit of cost is
  10. 6:44Proof: where 184.00 comes from
  11. 7:15Proof: estimates from pg_stats
  12. 7:57Knobs, and blind spots
  13. 8:35Recap
  14. 9:03Next: Cardinality Estimation