PHP小码农

Appearance

CTE 物化与优化 ​

本节讲解 CTE（Common Table Expression）的两种执行方式——内联（Inlining）与物化（Materialization）——的判定规则、MATERIALIZED / NOT MATERIALIZED 关键字的强制手段、物化作为优化栅栏（Optimization Fence）对谓词下推的阻断效应，以及如何用 EXPLAIN 判断并修复 CTE 引起的性能退化。学完本节，你将能准确控制每一段 CTE 的执行方式。

前置知识

阅读本节前，建议先了解：递归 CTE（WITH RECURSIVE）

基础概念 ​

CTE 物化（CTE Materialization） 指 PostgreSQL 先把 CTE 的查询结果完整计算出来，存成一张临时结果集（内存或磁盘），后续主查询再从这张结果集里读取。与之相对的是 内联（Inlining）：把 CTE 的查询体直接展开进主查询，让优化器像处理普通子查询一样统一优化。

概念术语说明
内联Inlining把 CTE 查询体展开到主查询中，优化器可跨边界优化
物化Materialization先算出 CTE 全部结果存起来，主查询再读取
优化栅栏Optimization Fence物化形成的边界，栅栏内外无法互相优化
谓词下推Predicate Pushdown把 WHERE 条件下推到数据源以减少读取行数
强制物化AS MATERIALIZED显式要求物化
禁止物化AS NOT MATERIALIZED显式要求内联
CTE 扫描CTE Scan计划中读取物化结果集的节点
单次求值Single Evaluation物化的核心收益：CTE 只被计算一次

内联与物化的对比：

对比项内联物化
执行次数每个引用点各算一次（可能重复计算）只算一次，多个引用点共享
谓词下推可以下推到基表无法下推，只能在结果集上过滤
索引可用性可用基表索引结果集无索引（除 ANALYZE 统计外）
优化自由度高，优化器可重排连接顺序低，CTE 内部被固定
计划节点无独立节点（被展开）CTE Scan（可能带 CTE <名称> 子节点）
内存占用随主查询结果集可能占用内存并溢出到临时文件
典型收益单引用、需要下推过滤多引用、含重计算、含易变函数

PostgreSQL 12 带来的关键变化：在 PostgreSQL 12 之前，CTE 总是物化的，因此 CTE 常被当成「优化栅栏」使用。从 PostgreSQL 12 起，只要 CTE 满足条件就会被自动内联，性能大幅改善，但那些依赖物化来强制执行顺序的老写法也随之失效。

sql
-- PostgreSQL 12+：单引用且无副作用的 CTE 会被自动内联
WITH big AS (
  SELECT * FROM orders WHERE created_at >= DATE '2025-01-01'
)
SELECT * FROM big WHERE customer = 'Alice';

-- 计划里看不到 CTE Scan，条件被下推到 orders 上
--           ->  Index Scan using orders_created_at_idx on orders
--                 Index Cond: (created_at >= '2025-01-01'::date)
--                 Filter: (customer = 'Alice'::text)

语法与用法 ​

text
WITH cte_名称 AS [ [NOT] MATERIALIZED ] ( 查询语句 )
[, cte_名称2 AS [ [NOT] MATERIALIZED ] ( 查询语句 ) ]
SELECT ... FROM cte_名称 ...;

MATERIALIZED 与 NOT MATERIALIZED 是可选修饰符，写在 AS 与左括号之间：

sql
-- 强制物化：CTE 只算一次
WITH top_customers AS MATERIALIZED (
  SELECT customer, sum(amount) AS total
  FROM orders
  GROUP BY customer
)
SELECT * FROM top_customers WHERE total > 10000;

-- 强制内联：允许谓词下推与索引使用
WITH recent AS NOT MATERIALIZED (
  SELECT * FROM orders WHERE created_at >= DATE '2025-01-01'
)
SELECT * FROM recent WHERE customer = 'Alice';

准备本节贯穿使用的示例表：

sql
-- 用户表
CREATE TABLE users (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  username   TEXT NOT NULL,
  city       TEXT NOT NULL,
  status     TEXT NOT NULL,
  created_at DATE NOT NULL
);

-- 订单表
CREATE TABLE orders (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no   TEXT NOT NULL,
  user_id    INTEGER NOT NULL REFERENCES users (id),
  amount     NUMERIC(10, 2) NOT NULL CHECK (amount >= 0),
  status     TEXT NOT NULL,          -- pending / paid / shipped / cancelled
  created_at DATE NOT NULL
);

-- 订单明细表：用于构造有计算代价的 CTE
CREATE TABLE order_items (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id   INTEGER NOT NULL REFERENCES orders (id),
  sku        TEXT NOT NULL,
  qty        INTEGER NOT NULL CHECK (qty > 0),
  unit_price NUMERIC(10, 2) NOT NULL
);

CREATE INDEX ON users (city);
CREATE INDEX ON orders (user_id);
CREATE INDEX ON orders (created_at);
CREATE INDEX ON order_items (order_id);
sql
-- 写入 5 万名用户
INSERT INTO users (username, city, status, created_at)
SELECT
  'user_' || i,
  (ARRAY['北京', '上海', '广州', '深圳', '杭州'])[1 + (i % 5)],
  CASE WHEN i % 20 = 0 THEN 'inactive' ELSE 'active' END,
  DATE '2024-01-01' + (i % 365)
FROM generate_series(1, 50000) AS i;

-- 写入 20 万条订单
INSERT INTO orders (order_no, user_id, amount, status, created_at)
SELECT
  'SO' || lpad(i::text, 8, '0'),
  1 + (i % 50000),
  round((random() * 900 + 100)::numeric, 2),
  (ARRAY['pending', 'paid', 'shipped', 'cancelled'])[1 + (i % 4)],
  DATE '2024-06-01' + (i % 400)
FROM generate_series(1, 200000) AS i;

ANALYZE users;
ANALYZE orders;

详细说明 ​

内联与物化的判定规则 ​

优化器按固定规则决定一个 CTE 是内联还是物化。判断依据是「引用了多少次」和「有没有副作用」：

条件默认行为原因
只被引用一次，且不含易变函数内联不会有重复计算，内联可换取更多优化机会
被引用多次物化避免同一份查询被反复执行
含数据修改语句（INSERT / UPDATE / DELETE / MERGE）强制物化必须保证副作用只发生一次
含易变函数（Volatile Function）物化PostgreSQL 12~17 视为有副作用，重复调用结果可能不同
递归 CTE强制物化递归本身要求逐轮累积
显式写了 AS MATERIALIZED物化用户显式要求
显式写了 AS NOT MATERIALIZED内联用户显式要求（数据修改语句除外）

关于易变函数：PostgreSQL 12 至 17 把含易变函数的 CTE 视为必须物化。从 PostgreSQL 18 起，如果 CTE 语义上安全，含易变函数的单引用 CTE 也允许内联，具体行为以实际执行计划为准。

先看「引用一次」与「引用两次」的计划差异：

sql
-- 引用一次：内联，条件直接下推到 orders
EXPLAIN (COSTS OFF)
WITH paid AS (
  SELECT id, user_id, amount, created_at
  FROM orders
  WHERE status = 'paid'
)
SELECT * FROM paid WHERE created_at >= DATE '2025-01-01';
text
Index Scan using orders_created_at_idx on orders
  Index Cond: (created_at >= '2025-01-01'::date)
  Filter: (status = 'paid'::text)
sql
-- 引用两次：物化，CTE 只算一次，两个引用点共享结果
EXPLAIN (COSTS OFF)
WITH paid AS (
  SELECT id, user_id, amount FROM orders WHERE status = 'paid'
)
SELECT
  (SELECT count(*) FROM paid) AS 笔数,
  (SELECT sum(amount) FROM paid) AS 总额;
text
Result
  CTE paid
    ->  Seq Scan on orders
          Filter: (status = 'paid'::text)
  InitPlan 1 (returns $0)
    ->  Aggregate
          ->  CTE Scan on paid paid_1
  InitPlan 2 (returns $1)
    ->  Aggregate
          ->  CTE Scan on paid paid_2

计划中出现了 CTE paid 节点，说明它被物化了；两个 CTE Scan on paid 都从同一份结果集读取，orders 只被扫描一次。

物化作为优化栅栏 ​

物化最容易被忽视的副作用是形成 优化栅栏（Optimization Fence）：栅栏内外的信息互相不可见。

受影响的能力内联时物化时
谓词下推外层 WHERE 可下推到基表外层 WHERE 只能在 CTE Scan 之上过滤
索引选择可用基表索引定位结果集无索引，只能顺序扫描结果集
连接顺序重排优化器可自由选择CTE 内部的连接顺序被冻结
列裁剪只读用到的列结果集按 CTE 输出列全量保存
行长估算由基表统计信息推导依赖 CTE 自身估算，可能偏差较大

下面这个例子展示了栅栏的代价：

sql
-- 内联形态：条件被下推到基表，索引生效
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM orders
WHERE status = 'paid'
  AND user_id = 42;
text
Aggregate  (cost=8.60..8.61 rows=1 width=8) (actual time=0.036..0.037 rows=1 loops=1)
  Buffers: shared hit=5
  ->  Index Scan using orders_user_id_idx on orders  (cost=0.29..8.60 rows=1 width=0) (actual time=0.030..0.035 rows=1 loops=1)
        Index Cond: (user_id = 42)
        Filter: (status = 'paid'::text)
        Rows Removed by Filter: 1
        Buffers: shared hit=5
Planning Time: 0.180 ms
Execution Time: 0.062 ms
sql
-- 物化形态：user_id 条件无法下推，CTE 先算出全部已支付订单
EXPLAIN (ANALYZE, BUFFERS)
WITH paid AS MATERIALIZED (
  SELECT * FROM orders WHERE status = 'paid'
)
SELECT count(*) FROM paid WHERE user_id = 42;
text
Aggregate  (cost=5330.00..5330.01 rows=1 width=8) (actual time=46.512..46.514 rows=1 loops=1)
  Buffers: shared hit=1264 read=1
  CTE paid
    ->  Seq Scan on orders  (cost=0.00..4917.00 rows=49975 width=42) (actual time=0.015..38.921 rows=50012 loops=1)
          Filter: (status = 'paid'::text)
          Rows Removed by Filter: 149988
          Buffers: shared hit=1264 read=1
  ->  CTE Scan on paid  (cost=0.00..1124.44 rows=250 width=0) (actual time=40.094..46.491 rows=1 loops=1)
        Filter: (user_id = 42)
        Rows Removed by Filter: 50011

同一个查询，执行时间从 0.062 ms 涨到 46.514 ms，差距近 750 倍。原因很清楚：status = 'paid' 先把 20 万行过滤成约 5 万行，外层的 user_id = 42 只能在 5 万行结果集里逐行筛选（Rows Removed by Filter: 50011），而索引 orders_user_id_idx 完全没被用上。

MATERIALIZED 与 NOT MATERIALIZED 的取舍 ​

两个关键字的用途正好相反：

写法作用什么时候用风险
AS MATERIALIZED强制只算一次多引用 CTE、CTE 计算昂贵、需要稳定的执行顺序阻断下推，可能拖慢查询
AS NOT MATERIALIZED强制内联单引用 CTE 但优化器误判为多引用、需要下推过滤多引用时同一份查询被执行多次

先看 MATERIALIZED 的正确用法——多引用且单次计算昂贵时：

sql
-- 明细聚合一次，两处引用共享结果
EXPLAIN (ANALYZE, BUFFERS)
WITH item_stat AS MATERIALIZED (
  SELECT
    oi.order_id,
    sum(oi.qty * oi.unit_price) AS line_total
  FROM order_items oi
  GROUP BY oi.order_id
)
SELECT
  (SELECT count(*) FROM item_stat WHERE line_total > 1000) AS 大单数,
  (SELECT avg(line_total) FROM item_stat) AS 平均单额;
text
Result  (cost=... rows=1 width=16) (actual time=... rows=1 loops=1)
  CTE item_stat
    ->  HashAggregate  (cost=... rows=199995 width=36) (actual time=... rows=199995 loops=1)
          Group Key: oi.order_id
          ->  Seq Scan on order_items oi  (...)
  InitPlan 1 (returns $0)
    ->  Aggregate  (...)
          ->  CTE Scan on item_stat  (...)
                Filter: (line_total > '1000'::numeric)
  InitPlan 2 (returns $1)
    ->  Aggregate  (...)
          ->  CTE Scan on item_stat item_stat_1

order_items 只扫描一次，聚合也只做一次，两份统计共享。

再看 NOT MATERIALIZED 的正确用法——多引用但每次过滤后行数差异巨大时：

sql
-- 每次引用都带上极强的过滤条件，内联才能让索引生效
WITH recent_orders AS NOT MATERIALIZED (
  SELECT * FROM orders WHERE created_at >= DATE '2025-01-01'
)
SELECT
  (SELECT count(*) FROM recent_orders WHERE user_id = 42)  AS 用户42的订单,
  (SELECT count(*) FROM recent_orders WHERE user_id = 77)  AS 用户77的订单;

实战示例 ​

场景：订单看板需要同时展示「按城市的活跃用户订单汇总」与「全局订单汇总」，两个区块共用一段「有效订单」的过滤逻辑。要求优化前后对比 EXPLAIN，并验证结果一致。

步骤 1：准备有效订单的基础数据与统计 ​

sql
ANALYZE users;
ANALYZE orders;

-- 有效订单：排除已取消，且归属活跃用户
SELECT
  count(*)                                            AS 有效订单数,
  count(DISTINCT o.user_id)                           AS 涉及用户数,
  round(sum(o.amount), 2)                             AS 总额
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status <> 'cancelled'
  AND u.status = 'active';
text
 有效订单数 | 涉及用户数 |      总额
------------+------------+---------------
     150012 |      47500 | 82503127.50
(1 row)

步骤 2：用物化 CTE 先把两个区块的公共部分算一次 ​

sql
WITH valid_orders AS MATERIALIZED (
  SELECT o.id, o.user_id, o.amount, o.created_at, u.city
  FROM orders o
  JOIN users u ON u.id = o.user_id
  WHERE o.status <> 'cancelled'
    AND u.status = 'active'
)
SELECT city, count(*) AS 订单数, round(sum(amount), 2) AS 总额
FROM valid_orders
GROUP BY city
ORDER BY 总额 DESC;
text
 city | 订单数 |      总额
------+--------+---------------
 北京 |  30021 | 16501002.30
 上海 |  30015 | 16498233.10
 广州 |  29991 | 16495118.88
 深圳 |  30012 | 16502874.02
 杭州 |  29973 | 16505899.20
(5 rows)

步骤 3：观察物化形态的执行计划 ​

sql
EXPLAIN (ANALYZE, BUFFERS)
WITH valid_orders AS MATERIALIZED (
  SELECT o.id, o.user_id, o.amount, o.created_at, u.city
  FROM orders o
  JOIN users u ON u.id = o.user_id
  WHERE o.status <> 'cancelled'
    AND u.status = 'active'
)
SELECT city, count(*) AS 订单数, round(sum(amount), 2) AS 总额
FROM valid_orders
GROUP BY city
ORDER BY 总额 DESC;
text
Sort  (cost=... rows=5 width=46) (actual time=... rows=5 loops=1)
  Sort Key: (round(sum(valid_orders.amount), 2)) DESC
  Buffers: shared hit=... 
  CTE valid_orders
    ->  Hash Join  (cost=... rows=150000 width=26) (actual time=... rows=150012 loops=1)
          Hash Cond: (o.user_id = u.id)
          ->  Seq Scan on orders o  (cost=...) (actual time=... rows=150012 loops=1)
                Filter: (status <> 'cancelled'::text)
                Rows Removed by Filter: 49988
          ->  Hash  (cost=... rows=47500 width=18) (actual time=... rows=47500 loops=1)
                Buckets: 65536  Batches: 1  Memory Usage: 3105kB
                ->  Seq Scan on users u  (...)
                      Filter: (status = 'active'::text)
                      Rows Removed by Filter: 2500
  ->  HashAggregate  (...)
        Group Key: valid_orders.city
        ->  CTE Scan on valid_orders  (...)

关键读数：Batches: 1 说明哈希表一次装下，没落磁盘；Memory Usage: 3105kB 是物化结果与哈希表的峰值内存；CTE Scan on valid_orders 是端到端消费物化结果的节点。

步骤 4：验证物化与内联的结果等价 ​

sql
-- 分别以 MATERIALIZED 与 NOT MATERIALIZED 执行，比较结果集
EXPLAIN (ANALYZE, BUFFERS)
WITH valid_orders AS NOT MATERIALIZED (
  SELECT o.id, o.user_id, o.amount, o.created_at, u.city
  FROM orders o
  JOIN users u ON u.id = o.user_id
  WHERE o.status <> 'cancelled'
    AND u.status = 'active'
)
SELECT city, count(*) AS 订单数
FROM valid_orders
GROUP BY city
ORDER BY city;
text
GroupAggregate  (cost=... rows=5 width=18) (actual time=... rows=5 loops=1)
  Group Key: u.city
  ->  Sort  (...)
        Sort Key: u.city
        ->  Hash Join  (...)
              Hash Cond: (o.user_id = u.id)
              ->  Seq Scan on orders o  (...)
                    Filter: (status <> 'cancelled'::text)
              ->  Hash  (...)
                    ->  Seq Scan on users u  (...)
                          Filter: (status = 'active'::text)
sql
-- 用 EXCEPT 双向差集做等价性自检，两段都应返回 0 行
WITH v_mat AS MATERIALIZED (
  SELECT o.id, u.city FROM orders o JOIN users u ON u.id = o.user_id
  WHERE o.status <> 'cancelled' AND u.status = 'active'
),
v_inl AS (
  SELECT o.id, u.city FROM orders o JOIN users u ON u.id = o.user_id
  WHERE o.status <> 'cancelled' AND u.status = 'active'
)
SELECT 'only_in_mat' AS 差异方向, count(*) FROM (
  SELECT id, city FROM v_mat EXCEPT ALL SELECT id, city FROM v_inl
) t
UNION ALL
SELECT 'only_in_inl', count(*) FROM (
  SELECT id, city FROM v_inl EXCEPT ALL SELECT id, city FROM v_mat
) t;
text
  差异方向   | count
-------------+-------
 only_in_mat |     0
 only_in_inl |     0
(2 rows)

步骤 5：对比单引用场景两种写法的差距 ​

sql
-- 反例：单引用却强行 MATERIALIZED，条件无法下推
EXPLAIN (ANALYZE, BUFFERS)
WITH paid AS MATERIALIZED (
  SELECT * FROM orders WHERE status = 'paid'
)
SELECT count(*) FROM paid WHERE user_id = 42;
text
Aggregate  (cost=... rows=1 width=8) (actual time=46.512..46.514 rows=1 loops=1)
  CTE paid
    ->  Seq Scan on orders  (actual time=0.015..38.921 rows=50012 loops=1)
          Filter: (status = 'paid'::text)
          Rows Removed by Filter: 149988
  ->  CTE Scan on paid  (actual time=40.094..46.491 rows=1 loops=1)
        Filter: (user_id = 42)
        Rows Removed by Filter: 50011
sql
-- 正例：去掉 MATERIALIZED，走索引
EXPLAIN (ANALYZE, BUFFERS)
WITH paid AS (
  SELECT * FROM orders WHERE status = 'paid'
)
SELECT count(*) FROM paid WHERE user_id = 42;
text
Aggregate  (actual time=0.036..0.037 rows=1 loops=1)
  ->  Index Scan using orders_user_id_idx on orders  (actual time=0.030..0.035 rows=1 loops=1)
        Index Cond: (user_id = 42)
        Filter: (status = 'paid'::text)
        Rows Removed by Filter: 1
Execution Time: 0.062 ms

结论：MATERIALIZED 不是「更保险」的写法，而是「更贵」的写法。只有当 CTE 被多次引用或计算昂贵时才值得付出栅栏的代价。

注意事项 ​

  1. 不要把 MATERIALIZED 当成默认写法。单引用的 CTE 加 MATERIALIZED，几乎必然带来性能下降：
sql
-- 错误！单引用还强制物化
WITH x AS MATERIALIZED (SELECT * FROM orders WHERE status = 'paid')
SELECT * FROM x WHERE user_id = 42;

-- 正确：交给优化器判断
WITH x AS (SELECT * FROM orders WHERE status = 'paid')
SELECT * FROM x WHERE user_id = 42;
  1. NOT MATERIALIZED 在含数据修改的 CTE 上会被静默忽略。有副作用的 CTE 必须物化：
sql
-- 不推荐：期望内联，但 INSERT ... RETURNING 强制物化，NOT MATERIALIZED 无效
WITH new_orders AS NOT MATERIALIZED (
  INSERT INTO orders (order_no, user_id, amount, status, created_at)
  VALUES ('SO99999999', 1, 100.00, 'paid', CURRENT_DATE)
  RETURNING *
)
SELECT * FROM new_orders;
  1. LIMIT 出现在 CTE 内部会阻止谓词下推，此时外层过滤只能在结果集上做：
sql
-- 外层条件无法下推到 orders，只能在 100 行里筛
WITH sample AS (
  SELECT * FROM orders ORDER BY created_at DESC LIMIT 100
)
SELECT * FROM sample WHERE user_id = 42;
text
-- MySQL 与 PostgreSQL 在 CTE 语义上并不一致，跨库迁移时不要假设相同计划
-- PostgreSQL 11 及更早：CTE 一律物化，本页所有「内联」示例都不会成立
  1. 物化结果集没有索引。即使 CTE 内部查的是带索引的表，外层在结果集上的过滤也只能顺序扫描：
text
SubPlan / CTE Scan 上的 Filter 无法借助任何索引
-- 唯一的例外是 PostgreSQL 可以基于 CTE 的估算统计选择 Hash / Merge 连接
  1. 递归 CTE 无法用 NOT MATERIALIZED 取消物化，递归的执行模型要求逐轮累积：
sql
-- 语法上合法，但递归 CTE 仍然会被物化
WITH RECURSIVE t AS NOT MATERIALIZED (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM t WHERE n < 5
)
SELECT * FROM t;
  1. CTE 不产生统计信息。物化后的行数估算来自 CTE 查询体自身的估算，误差可能很大，进而影响外层连接方式的选择。若估算严重偏差，可考虑改为视图 + ANALYZE，或改写为临时表。

最佳实践 ​

  1. 默认不写关键字，让优化器自己判断。PostgreSQL 12+ 的判定规则已经足够聪明，显式干预只在实测有问题时进行。

  2. 用「引用次数」做初筛：

sql
-- 引用一次 → 倾向内联（不写关键字）
-- 引用两次及以上 → 倾向物化（自动或显式 MATERIALIZED）
  1. 判断是否该物化的 checklist：
检查项倾向物化倾向内联
引用次数≥ 21
CTE 内是否有聚合 / 连接有（计算昂贵）无（只是简单过滤）
外层过滤选择性弱（过滤不掉多少）强（能过滤掉大部分）
CTE 输出行数较小很大
是否有 LIMIT / ORDER BY有无

  1. 修改后必须用 EXPLAIN (ANALYZE, BUFFERS) 复核，重点看三处：是否出现 CTE Scan、Rows Removed by Filter 是否骤增、Buffers 中 read 是否上升。

  2. 大结果集物化注意内存。如果 EXPLAIN 里出现 Batches > 1 或计划中包含 Sort Method: external merge，说明数据已溢出到临时文件：

sql
-- 观察是否落盘
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) WITH ... SELECT ...;
-- 关注：Batches、Sort Method、temp read/written
  1. 需要稳定执行顺序或复用结果时，物化是正当手段，但要连同「外层过滤条件会失效」一起评估：
sql
-- 合理：同一份昂贵聚合被两个统计口径复用
WITH stats AS MATERIALIZED (
  SELECT user_id, count(*) AS cnt, sum(amount) AS total
  FROM orders GROUP BY user_id
)
SELECT
  (SELECT count(*) FROM stats WHERE cnt >= 5)  AS 活跃用户,
  (SELECT avg(total) FROM stats)               AS 人均消费;
  1. PostgreSQL 11 及更早版本升级到 12+ 时，必须重新审视旧 CTE 的性能。旧版本中被当作「性能优化」的物化栅栏，在新版本里变成了拖累。

下一节

继续学习：CTE 优化实践

参考链接 ​