Parameter · Query Tuning / Other Planner Options
recursive_worktable_factor
Sets the planner's estimate of the average size of a recursive query's working table.
- Default:
10 - Type: real
- Range: 0.001 … 1e+06
- Applies: Session (any user)
In plain words
The planner cannot know how many rows a recursive query (WITH RECURSIVE) will produce in each round. It guesses: the starting rows times this factor. If the guess is far off, the plan for joins inside or above the recursion may be bad. Added in PostgreSQL 15.
For DBAs and operators
The estimated size of the WorkTable Scan (the rows from the previous iteration) is the estimate of the non-recursive term multiplied by this factor; the estimated total of the Recursive Union is based on it too. Verified on 18.6 with WITH RECURSIVE r(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM r WHERE n < 100): factor 10 gave a WorkTable Scan of 3 rows (after the filter) and a total of 31 rows; factor 100 gave 33 and 331 rows. The real answer is 100.
Before PostgreSQL 15 the factor was hard-coded as 10. It matters when the recursive term joins a big table: a small worktable estimate leads to nested loops with index lookups, a large one to hash joins. For graph or hierarchy queries that fan out heavily (bill of materials, social graphs), raising it per query can avoid nested loops that run for hours; for narrow walks such as following a parent chain, lowering it helps. Set it with SET LOCAL around the specific query.
How to change it
Any user can change it for their session with SET.