PostgreSQL中关联另一表行计数的SQL查询优化咨询

问题背景 #

给定的数据表结构如下：

CREATE TABLE A (id int, a1 int, name text, a3 int);
INSERT INTO A VALUES (1, 100, 'Annie', 3);

CREATE TABLE B (id int, b1 int, b2 int);

CREATE TABLE C (id int, c1 int, c2 int);

CREATE TABLE D (d1 int, name text);
INSERT INTO D VALUES(2000, 'Annie');
INSERT INTO D VALUES(2050, 'Annie');

需求很明确：仅知道id=1这个条件，要查询该对象的b1、c2字段值，以及对应的d1计数。

原使用的查询语句：

SELECT A.id, A.name, B.b1, C.c2, dd.total
FROM A
LEFT JOIN B on A.id=B.id
LEFT JOIN C on A.id=C.id
LEFT JOIN (SELECT count(d1) total, name FROM D GROUP BY name) dd ON (dd.name = A.name)
WHERE A.id=1

优化方案与性能说明 #

1. 改写子查询，避免全表分组 #

原SQL里的子查询会对整个D表做全量分组统计，哪怕我们只需要和A表id=1对应的name相关的计数，这在D表数据量大的时候会非常慢——毕竟要遍历所有记录再分组，完全是做无用功。

可以改成直接针对目标name的计数查询，这样数据库会先找到A表id=1的记录，拿到对应的name，再去D表统计这个name的d1数量，只会扫描D表中匹配的行：

SELECT 
    A.id, 
    A.name, 
    B.b1, 
    C.c2,
    (SELECT COUNT(d1) FROM D WHERE D.name = A.name) AS total
FROM A
LEFT JOIN B ON A.id = B.id
LEFT JOIN C ON A.id = C.id
WHERE A.id = 1

2. 加索引是性能提升的关键 #

不管用哪种写法，大数据量下想要快，一定要给这些字段加索引：

• 给B、C表的id字段加主键或普通索引，关联查询时能快速定位匹配的记录，避免全表扫描B、C表。
• 给D表的name字段加普通索引，这样统计特定name的count时，数据库能通过索引直接找到所有匹配行，不用扫整个D表。

3. 原SQL的性能隐患 #

原SQL的子查询会对D表做全表扫描+分组，生成所有name的统计结果。当D表数据量上去后，这个操作的耗时会跟着数据量线性增长，分组后的结果集也会变大，后续和A表JOIN的开销也会增加。明明只需要一个name的统计，却要先处理整个D表，完全是资源浪费，数据量越大，这个问题越明显。

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