PostgreSQL递归查询优化：能否用1个递归替代3个实现累计核算？

完全可以用单个递归CTE实现需求，而且能尽量保留你原查询的输出格式，避免重复执行三次相同的递归逻辑，大幅提升查询效率。

优化思路 #

原查询中三个递归子查询的逻辑完全一致，仅最后聚合的字段不同。我们可以：

1. 用一个递归CTE一次性获取每个科目及其所有子科目的borc和alacak，同时标记每个记录对应的根科目ID（即主查询中当前行的hesap_kodu.id）。
2. 对递归结果按根科目ID分组，一次性计算出sum(borc)、sum(alacak)、sum(borc - alacak)三个聚合值。
3. 将聚合结果关联到主查询中，替换原来三个独立的子查询。

优化后的查询代码 #

WITH recursive tree_hk AS (
    -- 初始成员：获取当前科目及其关联的预算数据，标记根ID为自身ID
    SELECT 
        h.id AS node_id,
        h.id AS root_id,
        b.borc,
        b.alacak
    FROM hesap_kodu h
    JOIN butce b ON h.id = b.hesap_kodu_id
    WHERE h.active = true

    UNION ALL

    -- 递归成员：获取子科目及其预算数据，继承根ID
    SELECT 
        hk.id AS node_id,
        tr.root_id,
        bc.borc,
        bc.alacak
    FROM hesap_kodu hk
    JOIN butce bc ON hk.id = bc.hesap_kodu_id
    JOIN tree_hk tr ON tr.node_id = hk.parent_hesap_kodu_id
    WHERE hk.active = true
),
-- 预计算每个根科目的累计值
aggregated_tree AS (
    SELECT
        root_id,
        SUM(borc) AS total_borc,
        SUM(alacak) AS total_alacak,
        SUM(borc - alacak) AS total_butce
    FROM tree_hk
    GROUP BY root_id
)

-- 主查询：关联预计算结果，保留原输出字段和格式
select 
    hkodu.id,
    hkodu.hesap_kodu           as hesapKodu,
    hkodu.hesap_adi            as hesapAdi,
    hkodu.parent_hesap_kodu_id as parentId,
    btc.butce_yili             as butceYili,
    hkodu.active               as active,
    hkodu.owner_birim_id       as birimId,
    COALESCE(at.total_butce, 0) as butce,
    COALESCE(at.total_alacak, 0) as alacak,
    COALESCE(at.total_borc, 0) as borc
from hesap_kodu hkodu
left join butce btc on btc.hesap_kodu_id = hkodu.id
left join aggregated_tree at on at.root_id = hkodu.id;

关键说明 #

1. 递归CTE的改进：新增root_id字段，确保每个子科目记录都能关联到它的顶层父科目，分组聚合时可准确汇总每个父科目下所有子科目的数据。
2. 预聚合的好处：仅执行一次递归，再通过分组计算三个累计值，彻底消除原查询中三次重复递归的性能浪费。
3. 格式兼容性：主查询的字段列表、别名、关联逻辑基本和原查询一致，仅替换三个子查询为预聚合结果的关联，满足你“不改变当前查询格式”的需求。
4. COALESCE处理：避免无科目或预算数据时出现NULL值，用0替代，和原查询的聚合逻辑保持一致。

内容的提问来源于stack exchange，提问作者Buğra Taşdemir