All Your GUCs in a Row: hash_mem_multiplier

PostgreSQL's hash_mem_multiplier GUC (default 2.0) lets hash-based operations use a multiple of work_mem while sort-based operations use plain work_mem. This separation exists because hashes and sorts have fundamentally different memory relationships: sorts barely benefit from extra memory once past a low threshold, while hash operations see dramatic performance degradation when they spill to disk. The parameter was introduced in PostgreSQL 13 alongside disk-based hash aggregation to prevent regressions, and its default was raised to 2.0 in PostgreSQL 15. Practical guidance: use EXPLAIN (ANALYZE) to detect hash spills (Batches > 1 or disk usage on HashAggregate nodes), then raise the multiplier per session or role rather than globally raising work_mem. Values up to 8.0 or beyond are reasonable when work_mem is already at 40MB+, but beware the per-node, per-connection nature of the allowance which can multiply memory usage rapidly under concurrent load.

Nguồn: https://postgr.es/p/9qH. 8sync News chỉ tóm tắt và dẫn link; bản quyền nội dung thuộc tác giả và nguồn gốc.