PostgreSQL中：相关子查询与连接联合，哪种标签统计性能更优？

问题描述 #

我有一套带标签的链接系统，数据存储在三张表中：

tag: id, name
tag_in_link: tag_id, link_id
link: id, url

现在需要统计各标签的使用次数（包含使用次数为0的标签），我编写了两种查询语句：

方案一 #

select t.id, t.name, count(*) 
from tag as t inner join tag_in_link as tl on tl.tag_id = t.id 
group by t.id, t.name 
union 
select t.id, t.name, 0 
from tag as t left outer join tag_in_link as tl on tl.tag_id = t.id 
where tl.tag_id is null

方案二 #

select t.id, t.name, (select count(*) from tag_in_link as tl where tl.tag_id = t.id ) as count 
from tag as t

这两种查询返回的结果一致（仅记录顺序可能不同），小数据量下性能相近，但未来数据规模会达到：最多100个标签，数百万条链接。想请教在PostgreSQL大表场景下，哪种方案性能更优？

回答 #

在PostgreSQL的大表场景下，方案二的性能会显著优于方案一，尤其是结合你提到的数据规模（最多100个标签、数百万条链接），原因如下：

1. 执行逻辑的开销差异 #

• 方案一的核心问题在于它需要处理全部数百万条tag_in_link数据：先通过INNER JOIN关联标签和关联表，对所有有使用记录的标签做分组统计，再通过UNION拼接无关联的标签。当tag_in_link数据量达到数百万级时，join和group by操作会产生大量IO和计算开销——如果分组所需的数据超过PostgreSQL的work_mem阈值，还会触发磁盘排序，性能会进一步下降。
• 方案二则是针对每个标签（最多100个）执行一次小范围查询：因为标签数量极少，即使每个子查询都去统计对应tag_id的次数，总开销也远低于方案一的全表扫描+分组操作。

2. 索引的放大效应 #

如果给tag_in_link.tag_id建立索引（这是这类标签关联场景的常规优化手段），方案二的优势会更加明显：

• 每个子查询会直接走索引扫描（甚至索引-only扫描），查询时间复杂度仅为O(log N)，100次这样的查询总耗时几乎可以忽略不计。
• 方案一即使有索引，依然需要扫描全部索引数据并完成分组，开销远高于多次小索引查询。

3. 额外的优化小提示 #

其实你还可以把方案一简化成更简洁高效的写法，避免使用UNION：

select t.id, t.name, count(tl.tag_id) 
from tag as t 
left outer join tag_in_link as tl on tl.tag_id = t.id 
group by t.id, t.name

这个写法和你的两个方案结果一致，性能比原方案一要好，但依然不如方案二——因为它还是需要扫描全部tag_in_link数据并完成分组。

总结来说，结合你的数据规模，方案二是更优的选择，尤其是在给tag_in_link.tag_id建立索引的情况下，性能差距会非常明显。

内容的提问来源于stack exchange，提问作者Trident D'Gao