myDBA
4 minutes
work_mem Is Per Operation, Not Per Query
work_mem is the memory each sort, hash or grouping step is allowed before it spills to disk, and a single query can contain many such steps while many queries run at once — so the true worst case is work_mem multiplied by operations multiplied by connections. When the plan says 'Sort Method: external merge Disk: 240MB', raising work_mem for that statement or that session turns a disk sort into a memory sort; raising it globally is how people run their server out of memory. Set it per session or per role for the heavy reports.