Module 4 · Query optimization

Episode 26 From SQL to Relational Algebra

9 min · SQL · Postgres

Title card of Episode 26: From SQL to Relational Algebra

▶ This video is coming to YouTube soon.

Postgres never runs the SQL you wrote. This episode turns a query into relational algebra and animates the rewrites on a tiny copy of the store: predicate and projection pushdown, EXISTS and IN into semi joins, NOT EXISTS into an anti join, why NOT IN can't follow, and views melting into the query. Then real EXPLAIN output proves every one.

Chapters

  1. 0:00Intro
  2. 0:14A billion pairs, never built
  3. 0:40SQL, read literally
  4. 1:27The operators, and their plan nodes
  5. 1:59Rewrites that never change the answer
  6. 2:27Pushing a filter down
  7. 3:21Only the columns you need
  8. 3:59EXISTS becomes a semi join
  9. 4:37NOT EXISTS, and why NOT IN is different
  10. 5:18A view is pasted in
  11. 5:50The code: pushing a selection down
  12. 6:19Proof: the filter runs inside the scan
  13. 6:55Proof: subqueries and views become joins
  14. 7:53What you write, what Postgres plans
  15. 8:20Recap
  16. 8:49Next: Statistics and Cost Models