Module 3 · Query execution

Episode 24 Aggregation

8 min · SQL · Postgres

Title card of Episode 24: Aggregation

▶ This video is coming to YouTube soon.

How does GROUP BY fold 100,000 rows into 3 without keeping them? We animate sort-based and hash-based aggregation, show that avg is just a count and a sum, combine partial states the way parallel workers do, and spill a hash table to disk in batches, then prove each step in Postgres with HashAggregate, GroupAggregate and pg_aggregate.

Chapters

  1. 0:00Intro
  2. 0:17100,000 rows in, 3 rows out
  3. 0:33Why not keep each group's rows?
  4. 1:10Sort-based: sort, then one pass
  5. 1:56The code: sort-based aggregation
  6. 2:20Hash-based: one entry per group
  7. 3:10The code: hash aggregation of avg
  8. 3:39Parallel: partial states, combined
  9. 4:12When the groups don't fit
  10. 5:16Proof: aggregation in Postgres
  11. 6:33Trade-offs
  12. 7:08Recap
  13. 7:35Next: Lab: The Query That Spilled to Disk