recursive_worktable_factor is a planner hint, and the people who committed it said so at the time. It went into PostgreSQL 15 because nobody on the thread could think of anything better, which is a more honest origin than most parameters get, and it tells you how to use it: one query at a time, after you have looked at the plan.
The thing it hints about is the one table in your query that the planner cannot have statistics for. A recursive CTE runs its recursive term over and over, and on each pass the term reads the working table: the rows the previous pass produced, and nothing else. (In EXPLAIN it is the WorkTable Scan node.) That table is different on every iteration, it exists for exactly one of them, and ANALYZE has never heard of it. The planner still has to choose how to join it to the real tables in the recursive term, and it chooses exactly once; the same plan runs for every iteration until the term comes back empty. So it guesses. The guess is that the working table holds recursive_worktable_factor times as many rows as the non-recursive term is estimated to return. The default is 10.0, the context is user, and the range is 0.001 to 1000000. From 8.4, when recursive CTEs arrived, through 14, the 10 was a constant in costsize.c with a comment admitting it was arbitrary; 15 made it a parameter and left the default behaviour alone.
What the guess decides
Say a document store keeps every version of a document as a row pointing at the previous one, and you want the full history of the 10,000 documents in two collections. The non-recursive term fetches each document’s current version; the recursive term joins the working table back to the ten-million-row versions table on prev_id. Histories run from one to forty versions, so the working table starts at 10,000 rows and shrinks as histories end, averaging about 5,000. The planner, told none of this, guesses ten times the non-recursive term:
1 -> Merge Join (cost=10820.95..399844.26 rows=104670 width=20) (actual rows=4875.00 loops=40) 2 Merge Cond: (v_1.id = h.prev_id) 3 -> Index Scan using versions_pkey on versions v_1 (cost=0.43..361567.94 rows=10249630 width=16) (actual rows=799480.53 loops=40) 4 -> Sort (cost=10820.52..11082.19 rows=104670 width=12) (actual rows=4876.00 loops=40) 5 Sort Key: h.prev_id 6 -> WorkTable Scan on history h (cost=0.00..2093.40 rows=104670 width=12) (actual rows=5125.00 loops=40)
At an estimated 104,670 rows per iteration, probing the primary key once per row looks too expensive, so the planner sorts the working table and, on each of the forty iterations, walks versions_pkey from its start until it has passed the largest prev_id in the frontier. That is about 820,000 index entries per pass, 32 million in all, to find 195,000 rows: four to five seconds on 18.6. With SET recursive_worktable_factor = 1 the estimate is 10,467 rows, the nested loop with an index probe comes back, and the same query runs in under 0.6 seconds. At 100, the planner hashes all ten million rows of versions in eight batches and rebuilds that hash on every one of the forty iterations: two and a half minutes.
The same query over one collection, 5,000 documents, runs the nested loop at the default and finishes in 0.2 seconds. Doubling the documents did not change the shape of the problem; it moved the estimate from 55,500 rows to 104,670, and somewhere between those two numbers (at a factor of 9, as it happens) the nested loop’s cost crossed the merge join’s. The cliff is in the estimate, not in the data.
Everything you need in order to set the parameter is on the WorkTable Scan line, which is the rare case where the estimate and the actual measure the same thing. rows=104670 is the guess. actual rows=5125.00 loops=40 is the truth: the average number of rows in the working table per iteration, and the number of iterations. (A row sits in the working table for exactly one iteration, so the average frontier is the CTE’s total output divided by the depth of the recursion.) The factor you want is that average divided by the actual size of the non-recursive term: 5,125 over 10,000, call it 0.5. The floor is one row of working table, not a factor of one; clamp_row_est() is applied to the product, so small factors stop mattering only when the non-recursive term is a handful of rows.
The parameter leaves the other guess in the same file alone. cost_recursive_union() still assumes ten iterations, in a constant whose comment calls the assumption “mighty shaky”, so the Recursive Union node’s estimate is the non-recursive term plus ten times the recursive term: 10,467 + 10 × 104,670 = 1,057,167 in the plan above, against 205,000 actual rows. That number matters only to the query around the CTE, when it joins or sorts what the CTE returns; the join inside the recursion is decided by the working-table estimate alone. Simon Riggs left the ten iterations untouched in his patch because he expected an objection to two knobs. The one reviewer who raised the subject asked whether the knob should cover the iteration count as well, and nobody answered him.
The other direction
The documentation says graph analytics queries may benefit from larger values, and that is true, with a condition the documentation does not mention. Take breadth-first reachability from one node over a random graph of a million nodes and four million edges. The non-recursive term is a single row, so the default guess is a working table of ten rows. The real one averages 54,000 rows across eighteen iterations and peaks at 385,000. Believing in ten rows, the planner picks a nested loop with an index probe into edges, one probe per node reached, 980,000 in all: ten to twelve seconds. Once the factor passes about 25,000, with work_mem raised to 512MB for the query, it switches to a hash join with edges on the hashed side, and that plan runs in five to six seconds, for a reason the planner does not know about. ExecReScanHashJoin() keeps the hash table across rescans when it fits in a single batch and nothing on the inner side has changed, and here nothing ever does, because the hashed side is edges and not the working table. The four-million-row hash is built on the first iteration and every later iteration only probes it; the Hash node reports loops=1 under a Hash Join that ran eighteen times.
That holds only when the hash fits. With work_mem left at 64MB (and hash_mem_multiplier at its default of 2, so 128MB for the hash) the hash of edges, 189,018 kB by EXPLAIN’s count, went to two batches, was rebuilt on every iteration, spilled to temp files eighteen times, and took 31 seconds: three times slower than the default. At that work_mem the planner also holds out for a factor of about 90,000 before it will pick the hash join, well past the true average, since it charges for the batching, and it charges the full hash build on every iteration besides: the recursive term is costed as ten independent executions, and the rescan logic that knows a single-batch hash survives a rescan is consulted only for the inner side of a nested loop. The larger-value advice is sound for queries whose frontier grows to a decent fraction of the graph, but it comes bundled with a work_mem large enough to hold the hashed side in one batch, set in the same transaction.
A hint, and how to use one
Simon Riggs posted the patch in October 2021 after a shortest-path query he was investigating went from 1,775 ms to 17.2 ms with the factor set to 1: a hundredfold improvement from changing one estimate. He had measured the usual workarounds too. enable_seqscan = off bought about 20%, work_mem = 512MB made the bad hash plan merely bad, and the correct plan was twenty times faster than either. The review thread is short and unusually candid. Peter Eisentraut said it smelled like a planner hint, but that graph-aware selectivity estimation was not arriving any time soon, so it would do. Robert Haas called it “a bit lame, but seems pretty harmless”. Tom Lane agreed, on the grounds that there is no reason to expect one multiplier to suit every recursive query, floated putting the number in the query itself as WITH RECURSIVE cte1 AS SCALE 1.0 (SELECT ..., and rejected his own idea in the same message, because syntax, once shipped, can never be taken back. He renamed Simon’s recursive_worktable_estimate to recursive_worktable_factor to leave room for other knobs in the same area, had the documentation paragraph rewritten after declining to believe its original explanation (that a smaller value favoured fast-start plans), and committed it on 24 March 2022. No other knob in that area has appeared since.
Tom’s objection is the whole operating manual. If no single multiplier suits every recursive query, a value in postgresql.conf is just a different global guess, and 10 is already one of those. Set it per query, with SET LOCAL in the transaction that runs it, or hang it on the function that owns the query with ALTER FUNCTION ... SET recursive_worktable_factor = 1, and pick the number by reading the WorkTable Scan line of an EXPLAIN (ANALYZE): average actual rows divided by the non-recursive term’s actual rows. Hierarchy walks, version chains, and shortest paths land at or below 1. Transitive closures and anything else whose frontier swallows a good part of the graph land in the tens of thousands, and need the work_mem to go with them.
Two things to know before you do. The parameter carries the GUC_EXPLAIN flag, so EXPLAIN (SETTINGS) and auto_explain.log_settings print it whenever it is not 10; the next person to read the plan sees the hint, which is as it should be. And a plan already in the cache keeps the old guess: a prepared statement that got its generic plan at 10 keeps its merge join after you SET the factor to 1, until you prepare it again (the mechanics are in plan_cache_mode).
It is a hint. The people who committed it said so, and they were right. Use it like one: one query at a time, with the number the plan gave you, and nowhere near postgresql.conf.