PostgreSQL 15 preview - 递归查询work table记录数评估可自控, 新增GUC 参数 recursive_worktable_factor

原创 digoal 2022-01-20

1075

作者

digoal

日期

2022-03-25

对于递归的图式全展开(每一层都不设置limit)查询, 每一个work table可能是上一级的N倍, 例如每个人关联10个人, 那么第一层10条, 第二层可能100条, 第三层可能1000条.
对于最短路径查询每一层都limit 1条返回, 那么work table记录数就可以取初始记录的倍数为1.

PostgreSQL 15新增GUC recursive_worktable_factor (默认依旧是10), 倍数可以由用户自己控制.

https://www.postgresql.org/docs/devel/queries-with.html#QUERIES-WITH-RECURSIVE

https://www.postgresql.org/docs/devel/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER

recursive_worktable_factor (floating point)  
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.

例子

WITH RECURSIVE t(n) AS (  
    VALUES (1)    -- non-recursive term  , 初始记录数  
  UNION ALL         
    SELECT n+1 FROM t WHERE n < 100  -- recursive query, 产出working table, 也就是要recursive_worktable_factor评估的平均每个working table的记录数.   
)  
SELECT sum(n) FROM t;

/*  
 * set_cte_size_estimates  
 *      Set the size estimates for a base relation that is a CTE reference.  
 *  
 * The rel's targetlist and restrictinfo list must have been constructed  
 * already, and we need an estimate of the number of rows returned by the CTE  
 * (if a regular CTE) or the non-recursive term (if a self-reference).  
 *  
 * We set the same fields as set_baserel_size_estimates.  
 */  
void  
set_cte_size_estimates(PlannerInfo *root, RelOptInfo *rel, double cte_rows)  
{  
    RangeTblEntry *rte;  
    /* Should only be applied to base relations that are CTE references */  
    Assert(rel->relid > 0);  
    rte = planner_rt_fetch(rel->relid, root);  
    Assert(rte->rtekind == RTE_CTE);  
    if (rte->self_reference)  
    {  
        /*  
         * In a self-reference, we assume the average worktable size is a  
         * multiple of the nonrecursive term's size.  The best multiplier will  
         * vary depending on query "fan-out", so make its value adjustable.  
         */  
        rel->tuples = clamp_row_est(recursive_worktable_factor * cte_rows);  
    }  
    else  
    {  
        /* Otherwise just believe the CTE's rowcount estimate */  
        rel->tuples = cte_rows;  
    }  
    /* Now estimate number of output rows, etc */  
    set_baserel_size_estimates(root, rel);  
}

递归用法参考: