如何在PostgreSQL CTE查询的执行计划中获取内存使用数据？

原因分析 #

该差异是PostgreSQL对CTE和子查询的默认优化策略不同导致的：

• 低于12版本的PostgreSQL中，CTE默认是「优化栅栏」，会被独立物化，不会和外层UPDATE逻辑合并执行。你的CTE写法中用了关联子查询匹配CTE和my_table的id，执行时是逐行扫描CTE结果匹配，没有构建哈希表的操作，自然不会输出哈希内存占用统计。
• UPDATE FROM的写法里，优化器直接将子查询结果和外层表做哈希连接，构建哈希表的操作触发了内存使用统计的输出。

解决方法 #

你可以通过两个方案让CTE版本也输出内存使用信息：

• 开启EXPLAIN的详细统计参数，执行时用EXPLAIN ANALYZE BUFFERS 代替普通的EXPLAIN ANALYZE，可以强制输出包括内存、缓存命中情况在内的完整统计信息，即使没有哈希/排序溢出也会显示相关内存指标。
• 如果你使用的是PostgreSQL 12及以上版本，可以临时关闭CTE物化策略，执行语句前先运行SET cte_materialized = off;，优化器会把CTE展开成和UPDATE FROM写法一致的执行计划，自然就会显示内存使用信息。

另外从你提供的执行计划可以看出，UPDATE FROM的写法执行效率比CTE关联子查询的写法高4倍左右，生产环境优先使用UPDATE FROM的写法性能表现更好。

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