PostgreSQL中CTE默认可能物化影响性能，从12版本起满足条件可内联以支持条件下推和索引优化；单次引用的简单CTE应使用NOT MATERIALIZED避免物化，递归CTE需索引和层级控制，大型CTE宜改写为子查询或强制内联，结合EXPLAIN ANALYZE分析执行计划。
WITH语句（也称CTE，Common Table Expressions）在PostgreSQL中常用于提升SQL可读性、拆分复杂查询逻辑。但默认情况下，CTE被当作“优化边界”，意味着PostgreSQL会物化CTE的结果，可能影响执行效率。合理使用和优化CTE，能显著改善查询性能。
理解CTE的执行机制
PostgreSQL 12之前，所有WITH语句都会被物化，即使只引用一次，数据库也会先执行CTE并保存结果，再与其他部分连接。这种行为可能导致无法使用索引、失去下推条件的机会。
从PostgreSQL 12开始，如果CTE满足以下条件，优化器可以将其“内联”处理（类似子查询），从而允许谓词下推、索引扫描等优化：
- CTE不包含副作用（如序列调用、写操作）
- CTE没有被多次引用
- CTE未使用ORDER BY / LIMIT / FOR UPDATE等限制内联的操作
若想强制物化，可使用MATERIALIZED关键字；若希望尽可能内联，可用NOT MATERIALIZED。
避免不必要的物化
当CTE仅被引用一次且结构简单时，建议让其被内联以获得更好的执行计划：
-- 推荐：允许内联，便于条件下推 WITH filtered_users AS NOT MATERIALIZED ( SELECT id, name FROM users WHERE status = 'active' ) SELECT * FROM filtered_users WHERE created_at > '2024-01-01';
这样，优化器可将外部WHERE条件created_at > ...下推到CTE内部，可能触发索引扫描。
相反，若写成：
WITH filtered_users AS ( SELECT id, name FROM users WHERE status = 'active' ) ...
PostgreSQL可能会先执行整个CTE并物化结果，导致全表过滤后再应用时间条件，效率低下。
合理使用递归CTE的优化策略
递归CTE（如树形结构遍历）无法内联，必须物化。优化重点在于减少中间数据量和加速查找：
- 确保递归部分的连接字段有索引（如parent_id）
- 尽早过滤起始集，避免无谓递归
- 控制递归深度，防止无限循环或爆炸式增长
WITH RECURSIVE org_tree AS ( -- 起始条件：根节点 SELECT id, name, parent_id FROM departments WHERE parent_id IS NULL UNION ALL -- 递归部分 SELECT d.id, d.name, d.parent_id FROM departments d INNER JOIN org_tree ot ON d.parent_id = ot.id WHERE ot.level < 10 -- 限制层级 ) SELECT * FROM org_tree;
确保departments(parent_id)上有索引，否则每次递归都是全表扫描。
拆分复杂CTE，避免大结果集物化
大型CTE一旦物化，会占用大量临时内存甚至写入磁盘，拖慢整体性能。建议：
- 将大CTE改写为子查询，尤其是仅使用一次的情况
- 对多层嵌套CTE，评估是否可合并或分步执行
- 使用
EXPLAIN ANALYZE检查是否发生意料之外的物化
例如：
-- 不推荐：大范围物化 WITH big_data AS ( SELECT * FROM logs WHERE date >= '2020-01-01' ) SELECT count(*) FROM big_data WHERE app = 'web';-- 推荐：改写为子查询或使用NOT MATERIALIZED WITH big_data AS NOT MATERIALIZED ( SELECT FROM logs WHERE date >= '2020-01-01' ) SELECT count() FROM big_data WHERE app = 'web';
这样可以让app = 'web'条件下推，在扫描时直接过滤。
基本上就这些。关键在于理解CTE何时被物化，主动使用NOT MATERIALIZED引导优化器，配合索引和条件下推，才能发挥最佳性能。
