PostgreSQL视图性能优化求助：5.3万行查询耗时约55秒

可行优化方案 #

1. 修复过滤条件的索引失效问题 #

原WHERE条件对occurrencedate使用了date_part函数，导致无法命中该字段的索引，改写为直接对字段做范围匹配：

-- 替换原WHERE条件
WHERE cc.occurrencedate >= date_trunc('year', CURRENT_DATE - INTERVAL '1 year')

改写后可以直接用上occurrencedate上的索引，大幅减少需要扫描的v_event表行数。

2. 添加缺失的索引 #

针对关联、分组、过滤高频字段添加以下索引：

• process_notes(piid)：加速piid分组聚合的速度
• hist_event_note(eventresultid)：加速eventresultid分组聚合的速度
• process_information(processid)：加速和cc表的FULL JOIN关联速度
• v_event底层表的occurrencedate字段：加速时间范围过滤
• v_method_executions底层表的eventid字段：加速和v_event的关联速度

3. 下推过滤条件到CTE内部 #

PostgreSQL 11的CTE属于优化屏障，会先计算全量CTE结果再执行过滤，导致聚合了大量不需要的历史数据。将时间过滤条件下推到CTE内部：

• 在wf CTE中先过滤process_information.creationdate >= date_trunc('year', CURRENT_DATE - INTERVAL '1 year')，再关联process_notes，仅聚合需要的piid对应的备注
• 在cc CTE中先过滤v_event的时间范围，再关联ccnotes，ccnotes仅聚合符合时间范围的eventresultid对应的备注，避免全表扫描hist_event_note

4. 替换标量子查询为JOIN #

ccnotes CTE中查询dimuser的标量子查询执行效率低，改写为LEFT JOIN：

SELECT hen.eventresultid,
    string_agg((hen.date || ':'::text || COALESCE(du.logonname,'') || ':'::text) || hen.note::text, '\n'::text) AS cckommentar,
    string_agg((hen.date || ':'::text) || hen.note::text, '\n'::text) AS cckommentaranonym
FROM hist_event_note hen
LEFT JOIN dimuser du ON hen.userid = du.userid
GROUP BY hen.eventresultid

5. 物化视图预计算（推荐报表场景使用） #

该视图用于报表生成，通常对实时性要求不高，可以改造成物化视图，定期预计算结果：

-- 创建物化视图
CREATE MATERIALIZED VIEW public.mv_event_executed_methods AS
-- 原视图的完整查询逻辑
WITH DATA;

-- 按需设置定时任务刷新，比如每天凌晨刷新
REFRESH MATERIALIZED VIEW public.mv_event_executed_methods;

改造后直接查询物化视图的耗时可以降至毫秒级。

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