PostgreSQL用CTE避免重复代码却性能极差的问题排查与优化

背景说明 #

我需要创建一个包含计算和聚合值的视图，因此会多次用到某些值（如下例中的total_dist_pts）。现有两张业务表：

• loc_a_run：约350行，数据持续增长
• loc_a_pline：超400万行，数据持续增长

表结构 #

-- 此处保留原表结构代码

当前查询方案（存在重复代码，耗时约14.2秒） #

-- 此处保留原当前查询代码

当前查询执行计划 #

-- 此处保留原执行计划内容

CTE重构尝试（耗时极长） #

为消除重复代码，我尝试用CTE重构查询，但执行耗时大幅增加：

-- 此处保留原CTE查询代码

CTE查询执行计划 #

-- 此处保留原CTE执行计划内容

问题与解答 #

1. 为何CTE方案耗时极长？ #

多数数据库默认会将CTE视为"逻辑视图"而非物理临时表——每次在主查询中引用CTE，数据库都会重新执行一遍CTE内部的查询逻辑。如果你的CTE包含对loc_a_pline大表的聚合操作，多次引用就意味着多次扫描400万行数据，叠加后直接导致耗时暴增。此外，如果CTE的查询未命中合适的索引，或者数据库没有启用CTE物化优化，也会进一步加剧性能问题。

2. 既能消除重复代码又能提升执行效率的最优方案是什么？ #

推荐两种核心方案，按需选择：

• 物化CTE（数据库支持时优先）：如果使用PostgreSQL（支持WITH ... AS MATERIALIZED）或MySQL 8.0.19+（支持WITH ... AS MATERIALIZED），可以在CTE定义前加MATERIALIZED关键字，强制数据库将CTE结果存储为物理临时表，仅扫描大表一次；
• 临时表预计算：通用方案，先将所有需要复用的聚合结果（如total_dist_pts、各类统计值）计算并存入临时表，后续主查询直接引用临时表数据。这种方式完全避免重复扫描大表，性能稳定；
• 辅助优化：为loc_a_pline的关联字段、聚合字段（如pts、loc_a_pline_id）创建联合索引，减少扫描行数。

3. 是否可以只执行一次SUM(dist_pts.pts)？ #

可以。只需将SUM(dist_pts.pts)的计算放在一个独立的子查询或临时表中，后续所有需要total_dist_pts的地方直接引用这个预计算值即可，彻底避免重复执行聚合操作。

4. 是否可以在CTE的计算步骤中同时完成COUNT(pline.loc_a_pline_id)，避免重复访问大表loc_a_pline？ #

可以。在CTE中一次性完成所有需要的聚合计算（包括SUM(dist_pts.pts)和COUNT(pline.loc_a_pline_id)），将结果整合为一行数据，后续查询直接引用该行的对应字段。但必须确保数据库对该CTE执行物化操作——如果数据库不支持物化CTE，就改用临时表存储聚合结果，同样能实现仅扫描大表一次的效果。

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