work_mem and Disk Spills: Reading Sort Method external merge in a Plan
work_mem is how much memory a single sort, hash or grouping step may use, and it defaults to only 4 MB. When a step needs more, it does not fail; it spills to temporary files on disk and gets much slower, which EXPLAIN ANALYZE reports as 'Sort Method: external merge Disk: 84320kB' or as extra batches under a Hash node. This is one of the few slow-query causes you can see as a single word in the plan. It also multiplies: the limit is per step and per parallel worker, not per query.
Questions this Concept answers
Why does a step that exceeds `work_mem` slow down rather than fail?
J
jeremy
Video
Why PostgreSQL work_mem Multiplies Across Sessions and Parallel Workers
PostgreSQL's work_mem is not a shared server-wide memory pool but a per-operation allowance: it is granted separately to every memory-consuming node (sort, hash) in every backend, and every parallel …