PostgreSQL / 系统课时
CTE 与物化控制
CTE 与物化控制
CTE 能把复杂 SQL 拆成有名字的中间结果,但它是否真正“先算一遍再读取”取决于优化器与 MATERIALIZED 控制。本篇用同一组查询观察内联、物化和谓词下推的差异,并建立用执行计划做选择的判断方法。
学习目标
- 使用
WITH把复杂查询拆成可读、可复用的逻辑块 - 判断 PostgreSQL 何时可能内联 CTE、何时倾向物化
- 用
EXPLAIN比较MATERIALIZED与NOT MATERIALIZED的计划差异
CTE 先解决表达问题
CTE 是只在当前 SQL 语句内可见的命名查询结果。它适合把“筛选候选数据、聚合、再关联”这类长 SQL 分段表达,但名字并不意味着一定生成临时表。对非递归、无副作用的 SELECT CTE,优化器可能把它折叠进父查询一起优化。
下面先创建一张临时表和索引,再比较三种写法。该示例可在独立会话中直接执行。
sql
CREATE TEMP TABLE demo_items AS
SELECT i AS id,
i % 100 AS group_id,
repeat('x', 50) AS payload
FROM generate_series(1, 10000) AS g(i);
CREATE INDEX demo_items_group_idx ON demo_items(group_id);
ANALYZE demo_items;
EXPLAIN (COSTS OFF)
WITH filtered AS (
SELECT id, group_id, payload
FROM demo_items
)
SELECT id
FROM filtered
WHERE group_id = 42;
EXPLAIN (COSTS OFF)
WITH filtered AS MATERIALIZED (
SELECT id, group_id, payload
FROM demo_items
)
SELECT id
FROM filtered
WHERE group_id = 42;
EXPLAIN (COSTS OFF)
WITH filtered AS NOT MATERIALIZED (
SELECT id, group_id, payload
FROM demo_items
)
SELECT id
FROM filtered
WHERE group_id = 42;预期结果
text
SELECT 10000
CREATE INDEX
ANALYZE
第一条与 NOT MATERIALIZED:过滤条件通常可下推到 demo_items,计划会直接扫描基表并利用 group_id 索引。
MATERIALIZED:计划会先生成 filtered 的 CTE 结果,再由 CTE Scan 过滤 group_id = 42。
具体节点名称、扫描方式和行数估算会随 PostgreSQL 版本、统计信息与成本参数变化。内联与物化改变优化边界
单次引用且无副作用的 CTE 默认通常会被内联;同一个 CTE 被父查询引用多次时,默认通常会物化一次,避免重复计算。MATERIALIZED 明确保留这道优化边界,NOT MATERIALIZED 则允许优化器把 CTE 合并进父查询。

真正的性能差异通常来自“父查询条件能否下推”和“昂贵表达式是否会重复计算”。如果每次引用只需要 CTE 的很小一部分数据,内联可能减少扫描;如果 CTE 内有昂贵计算并被多次复用,物化一次反而更合适。
用执行计划而不是习惯做决定
不要把 WITH 当成天然的性能优化,也不要机械地给所有 CTE 加 NOT MATERIALIZED。对候选写法分别执行 EXPLAIN (ANALYZE, BUFFERS),重点比较实际耗时、实际行数、共享缓冲区读取以及是否出现 CTE Scan、索引扫描和过滤下推。
当目标只是提升可读性时,先写清楚 CTE,再让默认优化策略工作;只有执行计划证明优化边界造成额外扫描,或重复计算确实昂贵时,才显式选择 MATERIALIZED 或 NOT MATERIALIZED。
按自己的节奏,确认真正理解。