### Aggregate ![[assets/ex_aggregate.png|256]] ### The Explain Trace ```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 (...) ``` --- - **Description**: Performs aggregation functions like SUM, AVG, COUNT, etc. - **Performance**: CPU-intensive for complex aggregations; memory-intensive if using HashAggregate. - **Factors**: Size of dataset, number of groups, aggregation complexity. - **Cost**: `cpu_tuple_cost * number of tuples + cpu_operator_cost * number of groups` ![[assets/ex_aggregate.svg|256]] - **Operates on**: [[Structures/Result Set]] - **Workloads**: - [[Workloads/IO/BufFile/BufFileRead|IO: BufFileRead]] - [[Workloads/IO/BufFile/BufFileWrite|IO: BufFileWrite]] - [[Workloads/LWLock/Buffers/BufferContent|LWLock: BufferContent]]