Recursive Union

A Recursive Union evaluates a WITH RECURSIVE query: it runs the non-recursive term once, then repeatedly runs the recursive term against the previous iteration's rows (via a WorkTable Scan) until it produces nothing new.

Only for WITH RECURSIVE common table expressions.

Expected for graph / tree traversals. The danger is runaway recursion — cycles or missing termination conditions — which can balloon the iteration count.

How a recursive CTE iterates

A Recursive Union drives a WITH RECURSIVE query. It runs the non-recursive term once to produce the starting rows, then repeatedly runs the recursive term — which references the result of the previous step via a WorkTable Scan — until an iteration produces no new rows. Each iteration's output becomes the input for the next.

A typical plan shape:

Recursive Union
  ->  Result                       -- non-recursive (anchor) term
  ->  …                            -- recursive term
        ->  WorkTable Scan on …    -- "rows from the previous step"

Watch out for cycles

If the graph being traversed has cycles and you wrote UNION ALL, the recursion never terminates — the same rows keep getting produced. UNION deduplicates each iteration, which prevents infinite loops at the cost of an extra hash/sort per step.

Either way, defend against runaway recursion with explicit guards:

WITH RECURSIVE walk(id, path) AS (
  SELECT id, ARRAY[id] FROM nodes WHERE id = 1
  UNION ALL
  SELECT n.id, w.path || n.id
  FROM walk w
  JOIN edges e ON e.src = w.id
  JOIN nodes n ON n.id = e.dst
  WHERE n.id <> ALL(w.path)             -- cycle guard
    AND array_length(w.path, 1) < 50    -- depth guard
)
SELECT * FROM walk;

Performance

Recursive CTEs do not benefit from indexes on the working table itself — it's ephemeral. They scale by the work done in each iteration's recursive term. For very wide traversals, materializing intermediate steps into indexed temp tables is sometimes the path forward.

• WorkTable Scan
• CTE Scan