PostgreSQL多表关联查询优化：如何改写子查询实现高效统计？

最优SQL语句 #

SELECT
    a.id AS account,
    COALESCE(b.b_count, 0) AS b_count,
    COALESCE(c.c_count, 0) AS c_count,
    COALESCE(d.d_count, 0) AS d_count
FROM
    a
LEFT JOIN (
    SELECT a_id, COUNT(*) AS b_count
    FROM b
    GROUP BY a_id
) b ON b.a_id = a.id
LEFT JOIN (
    SELECT a_id, COUNT(*) AS c_count
    FROM c
    GROUP BY a_id
) c ON c.a_id = a.id
LEFT JOIN (
    SELECT a_id, COUNT(*) AS d_count
    FROM d
    GROUP BY a_id
) d ON d.a_id = a.id;

写法说明 #

• 原查询使用关联子查询，会对a表的每一行分别执行3次统计查询，总共产生1200×3=3600次小查询，对于几十万行的b/c/d表来说重复扫描开销极大。
• 优化后的写法先对b/c/d表分别做分组预统计，每个表仅需全表扫描一次，再通过LEFT JOIN关联到a表。这样总扫描次数仅为4次（a+b+c+d），大幅降低IO和计算开销。
• 使用COALESCE函数确保当a表的id在关联表中无对应数据时，统计值显示为0，与原查询结果逻辑完全一致。

额外优化建议 #

• 确保b.a_id、c.a_id、d.a_id这三个外键字段都创建了B-tree索引，分组统计时能利用索引快速聚合，进一步提升效率。
• 如果a表存在大量无关联数据的行，可考虑在分组子查询中先过滤与a表存在关联的记录（比如WHERE a_id IN (SELECT id FROM a)），但通常PostgreSQL的查询优化器会自动处理这类场景。

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