![[assets/ex_recursiveunion.png|256]] ### The Explain Trace ```sql -- Generating a sequence using a recursive CTE EXPLAIN (ANALYZE, COSTS, BUFFERS, VERBOSE) WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 10 ) SELECT * FROM t; ``` ```text -> Recursive Union (cost=1.00..3.65 rows=31 width=4) (actual time=1.001..1.005 rows=10 loops=1) -> Result (...) -> WorkTable Scan on t t_1 (cost=1.00..1.23 rows=3 width=4) (actual time=1.000..1.000 rows=1 loops=10) ``` --- - **Description**: Handles recursive CTEs. - **Performance**: Efficiency depends on the recursion depth and the size of the "WorkTable" (the intermediate result set). - **Factors**: Recursion depth, memory available for the WorkTable, and the complexity of the recursive step. - **Cost**: `iteration cost * number of iterations` - ![[assets/ex_recursive_union.svg|200x200]] ![[assets/ex_worktable_scan.svg|200x200]] - **Operates on**: [[Structures/Result Set]] - **Workloads**: - [[Workloads/IO/BufFile/BufFileRead|IO: BufFileRead]] - [[Workloads/IO/BufFile/BufFileWrite|IO: BufFileWrite]] - [[Workloads/LWLock/Buffers/BufferContent|LWLock: BufferContent]]