recursive_worktable_factor

Definition PG 18 manual

Sets the planner's estimate of the average size of the working table of a recursive query, as a multiple of the estimated size of the initial non-recursive term of the query. This helps the planner choose the most appropriate method for joining the working table to the query's other tables. The default value is 10.0. A smaller value such as 1.0 can be helpful when the recursion has low “fan-out” from one step to the next, as for example in shortest-path queries. Graph analytics queries may benefit from larger-than-default values.

How it works

recursive_worktable_factor estimates the average recursive working-table size as a multiple of the non-recursive seed term. The estimate feeds costs for joining the worktable to other relations.

It does not cap recursion, memory, rows, or iterations. A low-fan-out traversal can be modeled with a smaller value, while graph expansion with high fan-out may need a larger estimate.

The value is used during planning; actual recursive growth still depends on data and termination predicates. Misestimation can select an unsuitable join method inside the recursive term. Its user context permits session- or transaction-local changes; newly performed or newly planned work sees the value.

Operational considerations

Treating recursive_worktable_factor as an executor resource limit rather than a planning assumption or policy.

Testing only one parameter set or one data distribution.

Expecting an already cached plan to be rewritten automatically.

Using a global override to hide stale statistics or fragile SQL structure.

Workload guidance

OLAP: Analytical SQL can make recursive_worktable_factor more visible because joins, cursors, or recursion are larger. Benchmark the full statement family and inspect estimates rather than copying a single successful value.

OLTP: Keep recursive_worktable_factor at its upstream default unless representative plans show a repeatable, workload-wide problem. Test a local override first and include planning latency as well as execution latency.

SMALL: On a small host, avoid increasing planning search or memory pressure through recursive_worktable_factor without a measured benefit. Prefer query-local structure or a scoped role setting over a cluster-wide override.