优化SQL查询：合并tbd.citizens_vk表的多个LEFT JOIN

你当前的查询对tbd.citizens_vk做了4次独立的子查询和LEFT JOIN，每次都要扫描整张表、分组计算，这会产生大量重复IO和计算开销，是导致查询慢的核心原因。通过条件聚合（CASE WHEN + COUNT），只需要扫描一次tbd.citizens_vk就能算出所有需要的统计值，直接把4次关联缩减为1次。

优化后的完整SQL #

SELECT 
    ot.id, 
    ot.parent_id, 
    ot.id_object_types AS object_type, 
    ot.object_code, 
    ot.name,
    u.login, 
    u.last_seen, 
    u.pass_upd, 
    u.pass_default, 
    ct.citizens_total, 
    fl.downloaded as files_downloaded, 
    fl.total AS files_total,
    ct.type0, 
    ct.type1, 
    ct.type2 
FROM public.object_tree AS ot
LEFT JOIN admin.admin_user AS u ON ot.object_code = u.r8012
LEFT JOIN (
    SELECT 
        object_code, 
        count(uploaded)::int as total, 
        count(downloaded)::int as downloaded 
    FROM web.files f 
    GROUP BY object_code
) as fl ON fl.object_code = ot.object_code
-- 把原来4次LEFT JOIN合并成这一次
LEFT JOIN (
    SELECT 
        ct.code_obj AS object_code,
        -- 统计所有actual=1的公民总数
        count(ct.citizens_id)::int as citizens_total,
        -- 条件统计type_db='0'且actual=1的数量
        count(CASE WHEN ct.type_db = '0' THEN ct.citizens_id END)::int as type0,
        -- 条件统计type_db='1'且actual=1的数量
        count(CASE WHEN ct.type_db = '1' THEN ct.citizens_id END)::int as type1,
        -- 条件统计type_db='2'且actual=1的数量
        count(CASE WHEN ct.type_db = '2' THEN ct.citizens_id END)::int as type2
    FROM tbd.citizens_vk ct 
    WHERE ct.actual = 1 
    GROUP BY ct.code_obj
) as ct ON ct.object_code = ot.object_code
WHERE ot.id IN ( $1:csv )
ORDER BY ot.id

关键优化点说明 #

• 减少表扫描次数：原来的4个子查询需要扫描tbd.citizens_vk4次，现在只需要扫描1次，大幅降低磁盘IO开销。
• 合并分组计算：一次GROUP BY就能算出所有统计字段，避免重复分组的计算成本。
• 移除多余的DISTINCT：原来子查询里的DISTINCT是多余的——GROUP BY ct.code_obj已经保证了每个code_obj只返回一行，完全不需要再加DISTINCT。
• 简化关联逻辑：从4次LEFT JOIN变成1次，减少了数据库关联操作的复杂度，执行计划会更高效。

如果tbd.citizens_vk数据量较大，还可以考虑给(code_obj, actual, type_db)创建联合索引，进一步加速聚合查询的速度。

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