Module 3 · Query execution

Episode 25 Lab: The Query That Spilled to Disk

7 min · SQL · Postgres

Title card of Episode 25: Lab: The Query That Spilled to Disk

▶ This video is coming to YouTube soon.

A monthly revenue report on our store writes 18 MB of temp files every run. We reproduce it with EXPLAIN (ANALYZE, BUFFERS), see an external merge sort and a 17-batch hash aggregate, learn why work_mem is a budget per node and per process, fix it with SET LOCAL, and measure what really changed, with track_io_timing and log_temp_files.

Chapters

  1. 0:00Intro
  2. 0:14Symptom: a report that writes to disk
  3. 0:37Reproduce: EXPLAIN shows the spill
  4. 1:26Diagnose: one budget per node
  5. 2:15What did the spill cost?
  6. 2:41Why not raise it for everyone?
  7. 3:23Fix: more memory for this query only
  8. 4:05Measure: before and after
  9. 4:43Sizing it: how small can it go?
  10. 5:30Make it stick, for this report only
  11. 5:57What to take away
  12. 6:27Recap
  13. 6:53Next: From SQL to Relational Algebra