Module 3 · Query execution

Episode 19 Processing Models

10 min · SQL · Postgres

Title card of Episode 19: Processing Models

▶ This video is coming to YouTube soon.

Why does LIMIT 10 touch only 2 of 651 pages? This episode runs one small plan three ways (the iterator / Volcano model, materialization and vectorized execution), counts the calls and memory each one costs, shows why sorts and hash tables break the pipeline, and checks it all with EXPLAIN ANALYZE in Postgres.

Chapters

  1. 0:00Intro
  2. 0:15Ten rows, two pages
  3. 0:35A plan is a tree of operators
  4. 1:22Iterator: one row per next()
  5. 2:52The code: every operator is an iterator
  6. 3:19The code: Limit just stops asking
  7. 3:43Materialization: finish, then hand up
  8. 4:29Vectorized: a batch per call
  9. 5:18What each model pays
  10. 6:06Pipeline breakers: Sort must read everything
  11. 7:24Proof: Postgres pulls
  12. 8:02Proof: the operators that can't wait
  13. 8:31Trade-offs
  14. 9:07Recap
  15. 9:34Next: Access Paths