PostgreSQL两查询单独执行快，UNION ALL组合后性能暴跌千倍

问题描述 #

• 两个单独执行仅需1-10秒的查询，使用UNION ALL组合结果后，执行时间飙升至4500秒（超过1小时）。
• 通过EXPLAIN ANALYZE分析发现，系统对仅约11k行的man_jour_tracking表进行了16亿次迭代，仅在使用UNION时会出现每次连接重新生成该表的情况，单独执行则无此问题。
• 暂不确定是否与json_object数据类型有关，但查询优化器未正确处理该场景；两个查询单独执行正常，且所有记录唯一。
• 使用PostgreSQL 13.7版本，单独执行UNION中的两个SELECT语句各耗时约5秒，组合后耗时超1小时。

已尝试的优化方法 #

• 将man_jour_tracking从CTE改为临时表并创建索引，无性能提升；
• 调换UNION中SELECT语句的顺序；
• 用UNION替换UNION ALL，性能无改善；
• 移除man_jour_tracking CTE，组合查询仍耗时极长。

问题解决 #

给CTE添加AS NOT MATERIALIZED后问题解决。这有点违反直觉，推测是物化操作移除了关键索引，导致连接效率变慢。

内容的提问来源于stack exchange，提问作者Scott C.