Parameter · Query Tuning / Planner Method Configuration
enable_hashagg
Enables the planner's use of hashed aggregation plans.
- Default:
on - Type: bool
- Applies: Session (any user)
In plain words
To compute GROUP BY or DISTINCT, PostgreSQL can throw every row into a hash table keyed by the group (HashAggregate), or sort the rows and count runs of equal values (GroupAggregate). Hashing is usually faster when there are not too many groups. This switch forbids the hash method for testing.
For DBAs and operators
HashAggregate reads its input once and keeps one entry per group. Its memory limit is work_mem × hash_mem_multiplier; since PostgreSQL 13 it spills to disk in batches instead of growing without limit, and EXPLAIN ANALYZE reports, for example, Batches: 65 Memory Usage: 2073kB Disk Usage: 28584kB (a real line from grouping 1 million distinct values with work_mem = 1MB). Many spilled batches means the group estimate was wrong or the memory too small.
Measured on 18.6, SELECT c, count(*) FROM u GROUP BY c (100,000 rows, 10 groups) planned a HashAggregate over a Seq Scan, cost 2,041. With enable_hashagg = off it had to sort all rows first: GroupAggregate over Sort, cost 10,596.
When to look at it: a query that got slower after an upgrade or stats change and now shows a HashAggregate with many batches. Fix the cause (better n_distinct statistics, more hash_mem_multiplier) rather than turning hash aggregation off globally. The switch also affects hashed set operations: with it off, INTERSECT changed from HashSetOp Intersect to a sorted SetOp on 18.6. Hashed subplans (NOT IN (SELECT …) shown as hashed SubPlan) were not affected.
How to change it
Any user can change it for their session with SET.
See it in context
Related parameters
hash_mem_multiplier work_mem enable_sort enable_group_by_reordering