> [!NOTE] Aggregate > <table> > <tr> > <td width="25%"><img src="assets/ex_aggregate.png"></td> > <td>The engine's mechanism for collapsing multiple rows into a single result (e.g., SUM, COUNT). It can operate by sorting the input (GroupAggregate) or by building an in-memory hash table (HashAggregate) to keep track of running totals for each group.</td> > </tr> > </table> > > ```sql > -- Counting animals grouped by species > EXPLAIN (ANALYZE, COSTS, BUFFERS, VERBOSE) > SELECT species_id, count(*) > FROM animals > GROUP BY species_id; > ``` > > ```text > HashAggregate (cost=448.00..448.05 rows=5 width=12) (actual time=3.200..3.201 rows=5 loops=1) > Output: species_id, count(*) > Group Key: animals.species_id > Batches: 1 Memory Usage: 24kB > Buffers: shared hit=148 > -> Seq Scan on public.animals (...) > ``` > > <table> > <tr> > <td rowspan="5" width="25%"><img src="assets/ex_aggregate.svg"></td> > <td><b>Performance</b></td><td>CPU-intensive for complex aggregations; memory-intensive if using HashAggregate.</td> > </tr> > <tr><td><b>Factors</b></td><td>Size of dataset, number of groups, aggregation complexity.</td></tr> > <tr><td><b>Cost</b></td><td><code>cpu_tuple_cost * number of tuples + cpu_operator_cost * number of groups</code></td></tr> > <tr><td><b>Operates on</b></td><td><a href="Structures/Result Set">Result Set</a></td></tr> > <tr><td><b>Workloads</b></td><td><a href="Workloads/IO/BufFile/BufFileRead">IO: BufFileRead</a>, <a href="Workloads/IO/BufFile/BufFileWrite">IO: BufFileWrite</a>, <a href="Workloads/LWLock/Buffers/BufferContent">LWLock: BufferContent</a></td></tr> > <tr><td colspan="3"><b>Description</b>: Performs aggregation functions like SUM, AVG, COUNT, etc.</td></tr> > </table>