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 的很小一部分数据,内联可能减少扫描;如果 CTE 内有昂贵计算并被多次复用,物化一次反而更合适。

用执行计划而不是习惯做决定

不要把 WITH 当成天然的性能优化,也不要机械地给所有 CTE 加 NOT MATERIALIZED。对候选写法分别执行 EXPLAIN (ANALYZE, BUFFERS),重点比较实际耗时、实际行数、共享缓冲区读取以及是否出现 CTE Scan、索引扫描和过滤下推。

当目标只是提升可读性时,先写清楚 CTE,再让默认优化策略工作;只有执行计划证明优化边界造成额外扫描,或重复计算确实昂贵时,才显式选择 MATERIALIZED 或 NOT MATERIALIZED。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

CTE 与物化控制

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通