Module 4 · Query optimization

Episode 30 Reading EXPLAIN

8 min · SQL · Postgres

Title card of Episode 30: Reading EXPLAIN

▶ This video is coming to YouTube soon.

A real Postgres plan for a three-table report, drawn as a tree and read number by number: cost=startup..total and where it comes from, actual time per loop × loops, Buffers hit vs. read on a cold and a warm run, self time per node, and an estimate that's 5× too low because city and country are correlated. Fixing it with extended statistics changes the plan.

Chapters

  1. 0:00Intro
  2. 0:1430 lines, one number that matters
  3. 0:37The report we'll read
  4. 0:56The indentation is the tree
  5. 1:23Read it inside-out: rows flow up
  6. 2:06Every number on one line
  7. 3:06loops: multiply before you compare
  8. 3:44Estimated vs. actual rows
  9. 4:30Where the time actually went
  10. 5:03Reading any plan
  11. 5:47Proof: the arithmetic behind the numbers
  12. 6:16Fix the estimate, and the plan changes
  13. 6:50Which EXPLAIN to run
  14. 7:33Recap
  15. 8:00Next: Lab: Fast Yesterday, Slow Today