PostgreSQL中CTE用IN()还是JOIN过滤？性能与可读性分析

首先得说，你的思路很务实——既然是cron自动执行的任务，可读性优先完全合理，不用为了那点性能牺牲代码的可维护性。咱们先拆解三个方案的执行计划，再聊聊哪种更适合你的场景，以及通用情况下的选择逻辑。

先看三个方案的执行计划细节 #

1. 使用IN()过滤的CTE方案 #

with /* cte for filtering all events that happened on this date only */ filter_sessions as (
 select dimension1 from ga_flagship_ecom.sessions where date = '2020-01-16'
), ee as (
 select dimension1, dimension3,
 case when sum(case when metric1 = 0 then 1 else 0 end) > 0 then 1 else 0 end as zero_val_product
 -- roll up to event level
 from ga_flagship_ecom.ecom
 where dimension1 in (select dimension1 from filter_sessions)
 group by 1,2
) select * from ee;

对应的执行计划解读：

• filter_sessions用了Index Only Scan，靠sessions_date_idx索引直接拿到目标日期的dimension1，成本极低（0.56..2.76），这部分三个方案都一样。
• 到ee部分，PostgreSQL把IN子查询转换成了「HashAggregate + Nested Loop」的组合：先对filter_sessions的结果做聚合去重（虽然这里只返回1行，这个步骤有点多余，但优化器还是走了流程），再通过ecom_pk索引匹配ecom表的行，之后排序、分组聚合。
• 顶层成本是61757.45..61758.23。

2. 使用INNER JOIN过滤的CTE方案 #

with /* cte for filtering all events that happened on this date only */ filter_sessions as (
 select dimension1 from ga_flagship_ecom.sessions where date = '2020-01-16'
), ee as (
 select e.dimension1, e.dimension3,
 case when sum(case when e.metric1 = 0 then 1 else 0 end) > 0 then 1 else 0 end as zero_val_product
 -- roll up to event level
 from ga_flagship_ecom.ecom e
 join filter_sessions f on f.dimension1 = e.dimension1
 group by 1,2
) select * from ee;

对应的执行计划解读：

• filter_sessions的扫描逻辑和第一个方案完全一致，成本相同。
• ee部分用了直接的Nested Loop关联：先扫filter_sessions，再用ecom_pk索引查匹配的ecom行，省去了第一个方案里多余的HashAggregate步骤，所以顶层成本比第一个略低（61757.43..61758.21），差异极小。
• 整个执行路径更简洁，没有冗余操作。

3. 同时使用JOIN和IN()过滤的CTE方案 #

with /* cte for filtering all events that happened on this date only */ filter_sessions as (
 select dimension1 from ga_flagship_ecom.sessions where date = '2020-01-16'
), ee as (
 select e.dimension1, e.dimension3,
 case when sum(case when e.metric1 = 0 then 1 else 0 end) > 0 then 1 else 0 end as zero_val_product
 -- roll up to event level
 from ga_flagship_ecom.ecom e
 join filter_sessions f on f.dimension1 = e.dimension1
 where e.dimension1 in (select dimension1 from filter_sessions)
 group by 1,2
) select * from ee;

对应的执行计划解读：

• 这个方案多做了一次Nested Loop Semi Join，相当于重复过滤了一遍：先通过JOIN拿到匹配的行，又用IN子查询再做一次半连接验证，完全是多余的操作，所以顶层成本略高（61758.32..61759.10），没有任何收益，纯粹是画蛇添足。

核心问题：IN() vs 内连接，哪种更优？ #

你的当前场景 #

从执行计划的成本来看，三个方案的差异微乎其微（不到1个成本单位），说明在你的数据量下（filter_sessions仅返回1行，ecom匹配39行），实际执行时间几乎没区别。结合你偏好可读性的需求，**方案2（纯INNER JOIN的CTE）**是最优选择：它比方案1少了冗余的聚合步骤，比方案3少了重复过滤，同时保持了CTE的清晰结构。

通用场景下的选择逻辑 #

PostgreSQL的查询优化器很聪明，很多时候会把IN子查询和内连接转换成相同的执行计划，但还是有几个关键点要注意：

• 当子查询返回大量重复值时：IN子查询会自动去重（比如方案1里的HashAggregate），而内连接如果不手动加DISTINCT会返回重复行（不过你的查询有GROUP BY，会自动处理重复）。这种情况下，IN子查询和加了DISTINCT的内连接性能差不多，但IN的写法可能更简洁。
• 多字段匹配场景：内连接更灵活，IN子查询只能处理单个字段的匹配。
• 性能的核心是执行计划：最终还是要看优化器生成的执行计划，比如如果filter_sessions返回大量行，优化器可能会选择Hash Join而非Nested Loop，这时候IN和内连接的执行计划会更接近。
• 可读性优先：如果性能差异可以忽略（比如你的cron场景），优先选你读起来更舒服的写法，但像方案3这种重复过滤的写法一定要避免。

给你的最终建议 #

1. 直接放弃方案3，完全是多余操作，只会增加不必要的开销。
2. 优先选择方案2，执行计划更高效，同时保持了CTE的可读性。
3. 既然是cron自动执行，不用为了那点性能换成嵌套查询，CTE的写法已经足够清晰易维护。

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