HashAggregate vs GroupAggregate: The Two Ways a Database Does GROUP BY
GROUP BY can be done two ways. HashAggregate keeps one entry per group in a hash table in memory, which is fast and needs no sorted input but grows with the number of distinct groups. GroupAggregate requires the rows sorted by the grouping key and then collapses runs of equal keys, so it uses almost no memory but may pay for a sort. The planner picks using its estimate of how many groups there will be, so a query that groups by something far more varied than expected can blow past memory and slow to a crawl.
Questions this Concept answers
- Why does `HashAggregate` use more memory than `GroupAggregate`?
External Merge Sort and Hash Aggregation for Query Execution in Disk-Oriented Databases
In a disk-oriented database, physical operators cannot assume that base tables or intermediate results fit in memory, so the right cost measure for an operator algorithm is pages read and written, an…