PostgreSQL已知其他列时编写递归函数有没有更高效的方案？

• 方案1：优化递归CTE，预加载A/B序列为数组避免重复关联
  现有递归写法的性能瓶颈来自每次迭代都要和data表做关联查询，哪怕加了LIMIT 1也无法避免重复的表扫描开销。可以先将按顺序排列的A、B值预聚合为数组，递归时直接通过下标取值，完全消除关联开销，代码示例如下：

WITH RECURSIVE
prep AS (
    -- 按行号顺序聚合A、B为数组，同时统计总行数
    SELECT 
        array_agg(A ORDER BY row) AS a_list,
        array_agg(B ORDER BY row) AS b_list,
        count(*) AS total_cnt
    FROM data
),
recursive_calc (idx, current_balance) AS (
    SELECT 0, 0::numeric
    UNION ALL
    SELECT 
        idx + 1,
        current_balance * a_list[idx + 1] + b_list[idx + 1]
    FROM recursive_calc, prep
    WHERE idx < total_cnt
)
-- 取最后一行计算结果即为最终余额
SELECT current_balance AS final_balance
FROM recursive_calc
ORDER BY idx DESC
LIMIT 1;

• 方案2：自定义聚合函数（性能最优）
  这类递推累积计算的场景，用PostgreSQL自定义聚合函数可以实现单次表扫描出结果，没有递归和关联开销，性能远高于递归CTE方案，适合流水量更大的场景。
  首先创建聚合函数：

-- 定义状态转换逻辑：上一期余额 * 利率乘数 + 当期转入转出金额
CREATE OR REPLACE FUNCTION balance_calc_step(state numeric, a numeric, b numeric)
RETURNS numeric AS $$
SELECT state * a + b;
$$ LANGUAGE sql IMMUTABLE;

-- 注册聚合函数，初始余额为0
CREATE AGGREGATE calc_final_balance(a numeric, b numeric) (
    SFUNC = balance_calc_step,
    STYPE = numeric,
    INITCOND = '0'
);

使用时直接调用即可，注意必须指定排序规则保证计算顺序和流水顺序一致：

SELECT calc_final_balance(A, B ORDER BY row) AS final_balance
FROM data;

• 原有递归关联写法的临时优化
  如果坚持使用原有关联写法，只需给data表的row字段创建唯一索引，即可将每次迭代的关联查询从全表扫描优化为唯一索引扫描，性能有明显提升，但仍弱于上述两种方案。

内容的提问来源于stack exchange，提问作者Andy Zou