PostgreSQL 15 uses recursive_worktable_factor to tune slow recursive CTEs

Introduced in PostgreSQL 15, the recursive_worktable_factor parameter acts as a planner hint for recursive Common Table Expressions (CTEs). It addresses performance issues caused by inaccurate row estimates for the 'WorkTable Scan' node, which the planner cannot gather statistics for via ANALYZE. By adjusting this factor, users can influence whether the planner chooses join strategies like nested loops or merge joins.

Tuning recursive CTE performance

• The parameter targets the WorkTable Scan node in recursive CTEs, where the planner lacks real statistics.
• The default value is 10.0, meaning the planner assumes the working table holds 10 times as many rows as the non-recursive term.
• The valid range for the factor is 0.001 to 1,000,000.
• Adjusting this value can prevent inefficient plans, such as choosing a Merge Join that requires massive index scans when a Nested Loop with an index probe would be significantly faster.
• To calculate the ideal factor, users should divide the average actual rows in the working table by the estimated rows in the non-recursive term.

Why it matters

This provides a surgical tool for database administrators to fix specific, hard-to-optimize recursive queries without changing global settings or rewriting complex logic.