Cracking the PostgreSQL Optimizer’s Blind Spot: A Deep Dive into recursive_worktable_factor

In the complex ecosystem of database optimization, few mechanisms are as notoriously opaque or frustrating as recursive Common Table Expressions (CTEs). For decades, PostgreSQL database administrators and developers have watched query planners struggle with a fundamental limitation: the optimizer is flying blind when it comes to the intermediate working tables generated during recursive operations.

That long-standing structural deficit changed with the introduction of PostgreSQL 15, which quietly introduced a new planner hint parameter: recursive_worktable_factor. Conceived out of pragmatic necessity and frank developer discussions, this parameter provides a manual steering wheel for a process that has historically relied on guesswork. Far from a silver bullet, it is a surgical tool designed to fix catastrophic execution plans caused by blind mathematical assumptions baked deep within the database engine’s C code.

Main Facts: What is recursive_worktable_factor?

At its core, recursive_worktable_factor is a configuration parameter designed to address an unyielding reality of database query planning: the PostgreSQL query optimizer cannot have pre-existing statistical profiles for the intermediate "working table" created during a recursive CTE loop.

When a recursive CTE executes, it runs its recursive term iteratively. On each pass, the term reads exclusively from a temporary structure known as the working table—composed precisely of the rows produced by the immediately preceding pass. In an EXPLAIN execution plan, this appears as the WorkTable Scan node.

Because this table is dynamically generated, evolves on every iteration, and vanishes the moment execution completes, PostgreSQL’s statistics collector (ANALYZE) has never seen it. The planner must make an educated guess about how many rows this working table will contain when deciding how to join it with other persistent tables in the database.

For versions ranging from PostgreSQL 8.4—when recursive CTEs were first introduced—up to PostgreSQL 14, that guess was hardcoded as a constant 10.0 in the engine’s source code (costsize.c), accompanied by comments admitting the number was entirely arbitrary. PostgreSQL 15 transformed this constant into a user-configurable parameter, setting the default value to 10.0, with a valid operational range spanning from 0.001 to 1,000,000 at a user configuration context.

Chronology: The Road to PostgreSQL 15

The introduction of recursive_worktable_factor was born not from theoretical computer science, but from the messy reality of production performance troubleshooting.

October 2021: The Discovery

The parameter’s genesis dates back to October 2021, when veteran PostgreSQL core contributor Simon Riggs was investigating a slow-running shortest-path graph query. By manually adjusting the internal estimation factor for the recursive worktable down to 1, Riggs watched query execution times plummet from an abysmal 1,775 milliseconds down to a blistering 17.2 milliseconds—a staggering hundredfold performance improvement achieved solely by correcting a flawed row estimate.

Riggs tested standard industry workarounds. Toggling enable_seqscan = off yielded a marginal 20% improvement, while aggressively expanding work_mem to 512MB merely made an inherently bad hash plan less destructive. The correct execution plan, however, was up to twenty times faster than either workaround. Recognizing a systemic issue, Riggs formulated a patch to expose the internal multiplier to users.

The Developer Review Thread

The subsequent patch review thread on the PostgreSQL hackers mailing list was characterized by unusual candor and pragmatism among core maintainers:

• Peter Eisentraut noted that the feature closely resembled a "planner hint"—a concept historically treated with skepticism in the PostgreSQL community—but conceded that true graph-aware selectivity estimation was a distant prospect, making the patch a necessary evil.
• Robert Haas described the solution as "a bit lame, but seems pretty harmless," acknowledging its utility in real-world scenarios.
• Tom Lane agreed, pointing out that no single multiplier could possibly suit every conceivable recursive query. Lane initially floated the idea of embedding the multiplier directly into SQL syntax—proposing something akin to WITH RECURSIVE cte1 AS SCALE 1.0 (SELECT ...)—before immediately rejecting his own idea, noting that database syntax, once shipped, becomes an immutable contract.

Lane ultimately renamed Riggs’ proposed parameter from recursive_worktable_estimate to recursive_worktable_factor to reserve naming conventions for future knobs in the same domain. Following a rewrite of the documentation paragraph, Tom Lane officially committed the patch on March 24, 2022. No other companion knobs have been introduced in that architectural area since.

Supporting Data: Understanding the Cliff

To comprehend why recursive_worktable_factor yields such dramatic optimizations, one must examine how the parameter influences join strategies.

The Version-Chain Document Store

Consider a document store maintaining every historical version of a record, where each row points back to its predecessor via a prev_id foreign key. A developer querying for the complete revision history of 10,000 documents across two collections constructs a recursive CTE.

The non-recursive term fetches the current version of each document. The recursive term joins the working table back to a ten-million-row versions table on prev_id. Because historical chains range from 1 to 40 iterations, the working table starts at 10,000 rows and gradually shrinks, averaging roughly 5,000 rows per iteration.

Operating blindly, the planner applies its default factor of 10, estimating that the working table will hold roughly 104,670 rows per iteration:

->  Merge Join  (cost=10820.95..399844.26 rows=104670 width=20) (actual rows=4875.00 loops=40)
      Merge Cond: (v_1.id = h.prev_id)
      ->  Index Scan using versions_pkey on versions v_1  (cost=0.43..361567.94 rows=10249630 width=16) (actual rows=799480.53 loops=40)
      ->  Sort  (cost=10820.52..11082.19 rows=104670 width=12) (actual rows=4876.00 loops=40)
            Sort Key: h.prev_id
            ->  WorkTable Scan on history h  (cost=0.00..2093.40 rows=104670 width=12) (actual rows=5125.00 loops=40)

Faced with an estimated 104,670 rows per pass, the planner calculates that executing an index probe for every single row would be cost-prohibitive. Consequently, it opts to sort the working table on every single pass, walking the primary key index from its start point until it passes the maximum prev_id. This necessitates traversing roughly 820,000 index entries per iteration—totaling 32 million index visits across 40 passes—to ultimately discover 195,000 actual matching rows. The query takes between 4 and 5 seconds to complete.

The Corrective Power of a Correct Factor

Executing SET recursive_worktable_factor = 1; drops the planner’s estimated row count to 10,467. The optimizer immediately abandons the expensive merge-join-and-sort strategy, reverting to an efficient nested loop utilizing primary key index probes. The exact same query executes in under 0.6 seconds.

Conversely, inflating the factor to 100 forces the planner to hash all ten million rows of the versions table across eight separate batches, rebuilding that massive hash table on every single one of the 40 iterations. This causes query execution times to balloon to two and a half minutes.

The performance cliff is entirely a byproduct of the estimation phase, not an inherent limitation of the underlying data distribution.

Official Recommendations and Operational Best Practices

The core developers who designed and committed recursive_worktable_factor are remarkably unified in how the parameter should—and should not—be deployed in production environments.

Avoid Global Configuration

Because no single scaling multiplier can appropriately fit every recursive workload across a complex database, setting recursive_worktable_factor globally inside postgresql.conf is considered an anti-pattern. Doing so simply replaces one arbitrary global guess with another.

Instead, the parameter should be applied surgically on a per-query basis using SET LOCAL within the executing transaction, or permanently bound to a specific PL/pgSQL function using the syntax:

ALTER FUNCTION get_document_history(integer) SET recursive_worktable_factor = 1;

Deriving the Mathematical Ideal

Calculating the optimal factor requires examining the execution metrics provided directly by the WorkTable Scan line in an EXPLAIN (ANALYZE) report. This is one of the rare instances in database tuning where estimated metrics and empirical measurements can be directly reconciled:

1. Locate the estimated rows (rows=...) versus actual rows (actual rows=... loops=...).
2. Calculate the average actual rows in the working table per iteration by dividing the total output rows by the recursion depth (loops).
3. Divide that derived average by the actual row count returned by the non-recursive base term.

For hierarchical walks, version-chain traversal, and shortest-path graph calculations, this computed factor frequently lands at or below 1.

The Graph Analytics Caveat: work_mem Interactions

While graph analytics queries traversing massive networks can benefit immensely from larger factor values, doing so introduces a secondary bottleneck: memory allocation.

When executing breadth-first reachability searches across graphs containing millions of edges, raising the worktable factor forces the planner to select hash joins instead of nested loops. However, this optimization is entirely contingent upon adequate memory allocation. If work_mem is left at constrained defaults (e.g., 64MB), a massive hash table of edges will fail to fit into a single memory batch.

As a result, the database engine is forced to spill temporary data to disk, rebuilding the hash table repeatedly across every iteration. In benchmark tests, an improperly provisioned memory configuration combined with a high worktable factor caused recursive queries to run up to three times slower than baseline defaults. Database administrators scaling up recursive_worktable_factor for graph traversals must concurrently scale up work_mem within the same transaction block to ensure hash tables execute entirely in-memory.

Implications for Production Database Architecture

The formal acceptance of recursive_worktable_factor into PostgreSQL marks a mature shift in the database community’s philosophy regarding optimizer hints. While traditional database purists argue that query planners should ideally be smart enough to deduce runtime characteristics autonomously, the inclusion of pragmatic escape hatches acknowledges the mathematical limits of cost-based optimization.

Transparency and Plan Caching

The parameter is fully integrated into PostgreSQL’s introspection and logging mechanisms. It carries the GUC_EXPLAIN flag, ensuring that whenever the factor deviates from its default value of 10, EXPLAIN (SETTINGS) and auto_explain.log_settings expose the modification explicitly in the output logs. Subsequent operators reviewing execution plans can immediately identify that manual tuning has been applied.

However, database engineers must remain mindful of plan caching mechanics. A prepared statement that has already cached a generic execution plan calculated under the default factor of 10 will stubbornly retain its inefficient merge join even after a developer executes SET recursive_worktable_factor = 1. The statement must be explicitly re-prepared or invalidated to force the planner to generate a new execution plan reflecting the updated factor.

Summary Checklist for Engineers

• Inspect the Plan: Always run EXPLAIN (ANALYZE) and analyze the WorkTable Scan line.
• Calculate the Ratio: Divide average actual rows per iteration by the non-recursive term’s actual row output.
• Apply Locally: Use SET LOCAL or ALTER FUNCTION ... SET to isolate the configuration to specific recursive queries.
• Match Memory Resources: If increasing the factor for graph-heavy workloads, ensure work_mem is increased proportionally to prevent disk spilling during hash operations.
• Avoid Global Pollution: Never modify recursive_worktable_factor globally in postgresql.conf.

Ultimately, recursive_worktable_factor is precisely what its creators claimed it to be: a hint. Handled with precision, measured empirically from execution plans, and decoupled from global configuration files, it transforms intractable, multi-second recursive queries into high-performance operations with a single line of tuning.