### Window Agg ![[assets/ex_window_aggregate.png|256]] ### The Explain Trace ```sql -- Calculating row numbers over a sorted set EXPLAIN (ANALYZE, COSTS, BUFFERS, VERBOSE) SELECT name, row_number() OVER (ORDER BY name) FROM animals; ``` ```text WindowAgg (cost=1.29..852.29 rows=20000 width=19) (actual time=1.014..3.155 rows=20000 loops=1) Output: name, row_number() OVER (?) Buffers: shared hit=63 -> Index Only Scan using idx_animals_name on public.animals (...) ``` --- - **Description**: Computes window functions. - **Performance**: CPU-intensive as it must track state across multiple rows; memory-intensive if using large partitions. - **Factors**: Number of rows, the complexity of the window function, and the size of the window frame. - **Cost**: `window function cost * number of rows` ![[assets/ex_window_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]]