HashAggregate

A HashAggregate computes GROUP BY by building a hash table keyed on the grouping columns and updating each group's count or sum as rows arrive, in any order. The sample's GROUP BY c.country is a HashAggregate: 620,640 rows in, one per country out, 24 at most. A GroupAggregate does the same on input sorted by the grouping columns, so it needs a Sort or an ordered index scan underneath, but holds one group at a time. A plain Aggregate has no GROUP BY: one row out, SELECT count(*) FROM commerce.orders.

Why it matters

The hash table holds one entry per group, so the number of groups is what counts. 24 countries fit anywhere. 398,000 customers, the n_distinct of orders.customer_id, are another matter at work_mem = 4MB, and since PostgreSQL 13 a HashAggregate that outgrows work_mem spills to disk. The planner chooses hashing or grouping on its estimate of the group count, so a wrong n_distinct gets you the wrong one. Either a HashAggregate that spills, or a GroupAggregate paying for a sort it did not need. Look for Disk Usage on the node.

What to do

1. If the aggregate spilled, compare estimated rows out with actual. Far apart means n_distinct is wrong. ANALYZE the table, or pin the value:

ALTER TABLE commerce.orders ALTER COLUMN customer_id SET (n_distinct = 398000);
ANALYZE commerce.orders;

2. If the estimate is right, raise work_mem for the session and run again.
3. If an index delivers the input in group order, the GroupAggregate is the cheaper plan.

In the Plan tab

The node is labelled HashAggregate, GroupAggregate or Aggregate by its strategy. An aggregate that went to disk carries the spill label and the finding HashAggregate spilled to disk, with a Retry · button.