为何JOIN查询比子查询慢数百倍？求技术解析

你的JOIN写法语法上没有错误，但执行逻辑与子查询版本完全不同，这是性能差异悬殊的核心原因。

执行计划差异分析 #

JOIN版本的执行逻辑（低效根源） #

从EXPLAIN ANALYZE结果可以看到：

• 先全量扫描posting表的86802行数据，再关联符合条件的39246条journal，最后关联account，得到78492行中间数据
• 对这7万多行数据执行GroupAggregate，将所有posting聚合到对应的journal
• 对聚合后的39246条journal做全局排序，最后才取前100条
• 本质是：明明只需要100条journal，但JOIN版本先处理了几乎所有相关数据，做了大量不必要的计算和排序

子查询版本的执行逻辑（高效关键） #

• 先通过journal_time_id_desc_idx索引直接定位并获取符合条件的前100条journal（利用LIMIT提前终止扫描）
• 对每条journal，通过posting_journal_id_idx索引只查询该journal对应的2条左右posting，再关联account
• 仅对这100条journal对应的posting做聚合，计算量极小

优化后的JOIN写法 #

要让JOIN版本保持可读性的同时获得和子查询相当的性能，只需要提前应用LIMIT限制journal的数量，避免全量处理数据：

select j.id,
       j.time,
       j.text,
       j.amount,
       jsonb_agg(jsonb_build_object(
         'id', p.id,
         'note', p.note,
         'amount', p.amount,
         'account', jsonb_build_object(
           'id', a.id,
           'name', a.name,
           'kind', a.kind,
           'parent_id', a.parent_id
       )) order by p.id) as postings
from (
  -- 先筛选并获取目标的100条journal，提前缩小数据范围
  select id, time, text, amount
  from journal
  where (time, id) >= ('2021-06-01 05:00:00 +00', 2154)
  order by time asc, id asc
  limit 100
) j
join posting p on p.journal_id = j.id
join account a on a.id = p.account_id
group by j.id, j.time, j.text, j.amount
order by j.time asc, j.id asc;

优化逻辑说明 #

• 先通过内层子查询获取需要的100条journal，将数据量限制在极小范围
• 仅关联这100条journal对应的posting（约200行）和account，避免全量扫描
• 最后聚合排序的计算量和子查询版本一致，性能会接近子查询的水平

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