HashAggregate

A versatile node type, the HashAggregate node takes in a set of nodes and outputs a series of derived data in buckets. A major reworking in PostgreSQL 8.4 turned several common types of operations into ones that are implemented using a HashAggregate node. They are now commonly used to compute distinct values, group by results, and unions. The main value is that hashing this way can avoid needing to sort the values, which is sometimes an expensive step. There are plenty of examples of the HashAggregate node type next—in fact, in current PostgreSQL versions, the harder thing to do is not see it used!

Sometimes aggregates that are being computed by GROUP BY or even DISTINCT will use a HashAggregate to compute their output. Consider this example that looks at which category each product belongs to:

EXPLAIN ANALYZE SELECT category, count(*) FROM products GROUP BY category ORDER BY category;
QUERY PLAN                                                        
--------...