Postgres中WITH子句与独立OVER窗口函数哪个性能更优？

我有一张大表，需要为每个字段添加月度、年度两种不同粒度的聚合值，想对比两种实现方式的计算量差异：是先用WITH子句预计算聚合表再关联原表，还是直接用OVER窗口函数在原表中添加聚合列？

原表结构 #

year,
month,
total,
count

期望结果表结构 #

year,
month,
total,
total_per_month,
total_per_year,
count,
count_per_month,
count_per_year

方式一：使用WITH子句预聚合后关联 #

with
data_per_month as
(select
    year,
    month,
    sum(total) as total_per_month,
    sum(count) as count_per_month
from data
group by year, month),

data_per_year as
(select
    year,
    sum(total) as total_per_year,
    sum(count) as count_per_year
from data
group by year)

select
    d.year,
    d.month,
    d.total,
    m.total_per_month,
    y.total_per_year,
    d.count,
    m.count_per_month,
    y.count_per_year
from data as d
join data_per_month as m
    on d.year = m.year
    and d.month = m.month
join data_per_year as y
    on d.year = y.year

（注：原代码中data_per_year的select包含month但仅按year分组，属于语法错误，已修正）

这种方式的计算逻辑：

• 对原表进行三次全表扫描：一次主查询取原始数据，两次WITH子句分别计算月度、年度聚合
• 两次JOIN操作：需要处理数据的关联匹配、排序或哈希计算，大表场景下JOIN的IO和CPU开销极高

方式二：使用OVER窗口函数 #

select
    year,
    month,
    total,
    sum(total) over(partition by year, month) as total_per_month,
    sum(total) over(partition by year) as total_per_year,
    count,
    sum(count) over(partition by year, month) as count_per_month,
    sum(count) over(partition by year) as count_per_year
from data

这种方式的计算逻辑：

• 仅对原表进行一次全表扫描
• 数据库查询优化器可合并相同分区的聚合计算（比如partition by year, month下的两个sum），无需重复遍历分区数据
• 无JOIN操作，完全避免关联带来的额外开销

计算量对比结论 #

对于大表场景，窗口函数的方式计算量明显更低：

1. 全表扫描次数从3次降至1次，大幅减少磁盘IO消耗
2. 省去JOIN操作的关联匹配、数据合并成本，降低CPU和内存占用
3. 同分区聚合可合并执行，进一步提升计算效率

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