PostgreSQL层级账户递归查询优化：提升预算查询性能

一、现有查询的核心性能问题 #

你的代码重复执行了三次完全相同的递归CTE tree_hk，分别计算butce、alacak、borc三个汇总值，相当于做了三倍的递归遍历，这是性能慢的核心原因。另外主查询中hesap_kodu与butce的左连接可能产生重复行，进一步加重计算和排序的负担。

二、递归查询的优化方案 #

优化思路：单次递归+批量汇总 #

只执行一次递归遍历，同时计算所有需要的汇总字段，再与主表关联，彻底避免重复递归的浪费。

优化后的代码：

WITH recursive tree_hk AS (
    SELECT 
        h.id, 
        h.hesap_kodu, 
        h.hesap_adi, 
        h.parent_hesap_kodu_id,
        h.duzenleyici_hesap,
        h.active,
        h.owner_birim_id,
        b.borc, 
        b.alacak,
        b.butce_yili
    FROM hesap_kodu h
    JOIN butce b ON h.id = b.hesap_kodu_id
    WHERE h.active = true

    UNION ALL

    SELECT 
        hk.id,
        hk.hesap_kodu,
        hk.hesap_adi,
        hk.parent_hesap_kodu_id,
        hk.duzenleyici_hesap,
        hk.active,
        hk.owner_birim_id,
        bc.borc,
        bc.alacak,
        bc.butce_yili
    FROM hesap_kodu hk
    JOIN butce bc ON hk.id = bc.hesap_kodu_id
    JOIN tree_hk tr ON tr.id = hk.parent_hesap_kodu_id
    WHERE hk.active = true
),
summary AS (
    SELECT
        parent_hesap_kodu_id AS root_id,
        SUM(borc - alacak) AS butce,
        SUM(alacak) AS alacak,
        SUM(borc) AS borc,
        MAX(butce_yili) AS butceYili  -- 若同一层级存在多年度数据，需调整过滤逻辑
    FROM tree_hk
    GROUP BY parent_hesap_kodu_id
)
SELECT 
    hk.id,
    hk.hesap_kodu AS hesapKodu,
    CASE WHEN hk.duzenleyici_hesap THEN concat(hk.hesap_adi, ' (-)') ELSE hk.hesap_adi END AS hesapAdi,
    hk.parent_hesap_kodu_id AS parentId,
    s.butceYili,
    hk.active,
    hk.owner_birim_id AS birimId,
    COALESCE(s.butce, 0) AS butce,
    COALESCE(s.alacak, 0) AS alacak,
    COALESCE(s.borc, 0) AS borc
FROM hesap_kodu hk
LEFT JOIN summary s ON hk.id = s.root_id
WHERE hk.owner_birim_id = :birimId
ORDER BY hk.hesap_kodu;

额外性能提升建议 #

• 创建复合索引：CREATE INDEX idx_hesap_kodu_recursive ON hesap_kodu(id, parent_hesap_kodu_id, active, owner_birim_id);，加速递归的连接与过滤。
• 给butce表创建索引：CREATE INDEX idx_butce_hesap_kodu ON butce(hesap_kodu_id);，优化表连接速度。
• 如果只需要特定年度的数据，在递归CTE的JOIN butce条件中添加b.butce_yili = :targetYear，减少遍历的数据量。

三、非递归实现的可能性 #

PostgreSQL中处理层级结构的非递归方案，需要预先维护层级元数据，适合层级稳定的场景：

• 路径枚举法：在hesap_kodu表新增path字段（如text类型，存储根节点到当前节点的ID路径，格式类似'1/5/12/'），通过LIKE匹配所有子节点后汇总数据。但需要通过触发器维护path字段的一致性，层级深时性能未必优于优化后的递归。
• 嵌套集合模型：维护lft和rgt区间字段，通过区间查询获取子节点。同样需要预先维护，修改层级结构时成本较高。

综合来看，优化后的递归查询更适配动态层级的场景，性能提升显著，是更优的选择。

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