PHP小码农

Appearance

CTE 优化实践 ​

本节把前面几节的 CTE 知识落到「怎么把慢查询改快」上：如何划分 CTE 的物化边界、如何用 CTE 消除重复扫描、物化结果集没有索引时怎么绕开、数据修改型 CTE 有哪些性能陷阱，并用一个完整的慢查询优化实战串起全部手段。学完本节，你将能把 CTE 从「写得好看的语法糖」变成「真正的优化工具」。

前置知识

阅读本节前，建议先了解：CTE 物化与优化

基础概念 ​

CTE 优化 的核心不是「写得更漂亮」，而是「让每一段中间结果只在必要的时候、以必要的粒度被计算一次」。CTE 只是一种表达手段，它既能带来收益，也能带来代价，取决于你把它放在执行链路的哪个位置。

概念术语说明
中间结果Intermediate ResultCTE 产出的、供后续步骤消费的数据集
重复扫描Repeated Scan同一张基表在一条查询里被多次读取
计算复述Recomputation同一段逻辑被多个引用点各算一次
物化边界Materialization BoundaryCTE 被固化成结果集的那条分界线
优化栅栏Optimization Fence边界造成的优化隔离效应
条件下推Predicate Pushdown过滤条件下推到数据源
提前聚合Pre-Aggregation在连接前先把明细压成聚合结果，减少参与连接的行数
执行放大Execution Amplification相关子查询等导致内层被重复执行，代价成倍增长

CTE 优化手段与代价对照：

手段适用场景主要收益主要代价
单引用不写关键字只是为了让 SQL 分层可读优化器自由优化，可下推、可用索引查询体被展开，可能重复出现在计划中
提前聚合再连接明细表大、只需聚合值参与连接的行数骤减聚合本身有成本，估算偏差会影响连接方式
多引用 AS MATERIALIZED同一份昂贵中间结果被多次使用基表只扫描一次阻断裂下推、结果集无索引
AS NOT MATERIALIZED优化器误判物化，或需要下推强过滤索引可用、条件可下推多引用时同一份查询被执行多次
条件聚合替代多次扫描多个统计口径来自同一张表一次扫描代替 N 次扫描表达式略长
过滤前置到 CTE 内外层过滤选择性极强减少物化体积需手工判断选择性，可能固化了不该固定的条件

CTE 与其他「中间结果」载体的定位：

载体是否落盘持久能否建索引生命周期适用场景
派生表（子查询）否否单条语句一次性简单嵌套
CTE否（但可溢出到临时文件）否单条语句需要分层命名或跨引用复用
视图（View）否否永久定义跨语句复用同一段逻辑
物化视图是可以永久，需手工刷新昂贵聚合结果被高频读取
临时表是（会话级）可以会话中间结果巨大且需要多次带索引访问

核心直觉：CTE 的优化本质是回答两个问题——这段中间结果会被用几次（决定物化与否）、用它之前能不能先把它变小（决定过滤与聚合的位置）。

语法与用法 ​

text
-- 分层骨架：先过滤 → 再聚合 → 最后连接
WITH 过滤层 AS [ [NOT] MATERIALIZED ] (
  SELECT ... FROM 大表 WHERE 高选择性条件
),
聚合层 AS [ [NOT] MATERIALIZED ] (
  SELECT 维度, 聚合函数(度量) FROM 过滤层 GROUP BY 维度
)
SELECT ... FROM 聚合层 JOIN 小表 ON ...;

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

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,
  created_at DATE NOT NULL
);

-- 订单明细表
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 orders (status);
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;

-- 每单一条明细，共 20 万行
INSERT INTO order_items (order_id, sku, qty, unit_price)
SELECT id, 'SKU-' || (id % 500), 1 + (id % 5), round((random() * 200 + 20)::numeric, 2)
FROM orders;

ANALYZE users;
ANALYZE orders;
ANALYZE order_items;

详细说明 ​

用 CTE 消除重复扫描 ​

最常见的 CTE 优化场景是：一条报表查询里，同一张表被多个子查询各自扫描了一遍。把它们收敛成一个 CTE，就能把「N 次扫描」变成「1 次扫描 + N 次读结果集」。

sql
-- 原始写法：orders 被扫描三次
SELECT
  (SELECT count(*) FROM orders WHERE status = 'paid')                  AS 已付款,
  (SELECT count(*) FROM orders WHERE status = 'shipped')               AS 已发货,
  (SELECT count(*) FROM orders WHERE status = 'cancelled')             AS 已取消;
sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT
  (SELECT count(*) FROM orders WHERE status = 'paid')      AS 已付款,
  (SELECT count(*) FROM orders WHERE status = 'shipped')   AS 已发货,
  (SELECT count(*) FROM orders WHERE status = 'cancelled') AS 已取消;
text
Result  (cost=... rows=1 width=24) (actual time=142.318..142.320 rows=1 loops=1)
  Buffers: shared hit=3900 read=2
  InitPlan 1 (returns $0)
    ->  Aggregate  (cost=... rows=1 width=8) (actual time=35.104..35.106 rows=1 loops=1)
          ->  Seq Scan on orders  (actual time=0.014..30.221 rows=50012 loops=1)
                Filter: (status = 'paid'::text)
                Rows Removed by Filter: 149988
  InitPlan 2 (returns $1)
    ->  Aggregate  (actual time=34.882..34.884 rows=1 loops=1)
          ->  Seq Scan on orders  (actual time=0.011..29.987 rows=50018 loops=1)
                Filter: (status = 'shipped'::text)
                Rows Removed by Filter: 149982
  InitPlan 3 (returns $2)
    ->  Aggregate  (actual time=34.756..34.758 rows=1 loops=1)
          ->  Seq Scan on orders  (actual time=0.009..29.611 rows=49970 loops=1)
                Filter: (status = 'cancelled'::text)
                Rows Removed by Filter: 150030
Execution Time: 142.362 ms

三次顺序扫描、三次聚合。改成单次扫描 + 条件聚合（Conditional Aggregation）即可：

sql
-- 优化后：一次扫描，用 FILTER 分口径
SELECT
  count(*) FILTER (WHERE status = 'paid')      AS 已付款,
  count(*) FILTER (WHERE status = 'shipped')   AS 已发货,
  count(*) FILTER (WHERE status = 'cancelled') AS 已取消
FROM orders;
text
Aggregate  (cost=... rows=1 width=24) (actual time=52.311..52.313 rows=1 loops=1)
  Buffers: shared hit=1300
  ->  Seq Scan on orders  (actual time=0.013..33.914 rows=200000 loops=1)
        Buffers: shared hit=1300
Execution Time: 52.355 ms

142.362 ms → 52.355 ms，扫描次数从 3 次降到 1 次。如果口径不是全表而是「某段时间窗口」，用 CTE 先把过滤后的行收敛下来，再在 CTE 上做条件聚合：

sql
WITH window_orders AS NOT MATERIALIZED (
  SELECT status FROM orders WHERE created_at >= DATE '2025-01-01'
)
SELECT
  count(*) FILTER (WHERE status = 'paid')      AS 已付款,
  count(*) FILTER (WHERE status = 'shipped')   AS 已发货,
  count(*) FILTER (WHERE status = 'cancelled') AS 已取消
FROM window_orders;

这里的 NOT MATERIALIZED 是刻意写的：让 created_at >= ... 下推到 orders，从而使用 orders_created_at_idx。

提前聚合再连接，控制参与连接的行数 ​

连接的成本与两侧行数的乘积正相关。把明细先压成聚合结果再连接，是 CTE 最有效的优化手段之一。

sql
-- 不推荐：先连接再聚合，20 万条明细全部参与连接
SELECT u.city, round(sum(oi.qty * oi.unit_price), 2) AS 销售额
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN users  u ON u.id = o.user_id
WHERE o.status <> 'cancelled'
GROUP BY u.city
ORDER BY 销售额 DESC;
text
GroupAggregate  (cost=... rows=5 width=46) (actual time=... rows=5 loops=1)
  Group Key: u.city
  ->  Sort  (...)
        Sort Key: u.city
        Sort Method: quicksort  Memory: 124kB
        ->  Hash Join  (cost=... rows=150000 width=26)
              Hash Cond: (o.user_id = u.id)
              ->  Hash Join  (...)
                    Hash Cond: (oi.order_id = o.id)
                    ->  Seq Scan on order_items oi  (...)
                    ->  Hash  (...)
                          ->  Seq Scan on orders o  (...)
                                Filter: (status <> 'cancelled'::text)
sql
-- 推荐：先把明细按订单号聚合，连接的行数从「明细行数」降到「订单数」
WITH order_totals AS MATERIALIZED (
  SELECT order_id, sum(qty * unit_price) AS order_amount
  FROM order_items
  GROUP BY order_id
)
SELECT u.city, round(sum(ot.order_amount), 2) AS 销售额
FROM order_totals ot
JOIN orders o ON o.id = ot.order_id
JOIN users  u ON u.id = o.user_id
WHERE o.status <> 'cancelled'
GROUP BY u.city
ORDER BY 销售额 DESC;
text
GroupAggregate  (cost=... rows=5 width=46) (actual time=... rows=5 loops=1)
  Group Key: u.city
  CTE order_totals
    ->  HashAggregate  (cost=... rows=200000 width=36) (actual time=... rows=200000 loops=1)
          Group Key: order_id
          Batches: 1  Memory Usage: 14MB
          ->  Seq Scan on order_items  (...)
  ...

注意：只有当 CTE 被引用多次，或者明细行数远大于聚合后的行数时，MATERIALIZED 才是净收益。若 order_id 的基数与明细行数接近（例如每单只有一条明细），提前聚合反而增加了聚合开销。判断依据是「聚合后行数 / 聚合前行数」这个压缩比。

物化结果集没有索引的应对 ​

物化 CTE 的结果集是一段「裸数据」，无法建索引。当外层必须在大结果集上做点查时，只能顺序扫描，代价极高。

应对方式做法适用条件
把过滤前置到 CTE 内在 CTE 的 WHERE 里写死高选择性条件条件在查询构造期就已知
改成 NOT MATERIALIZED让条件下推到基表，用基表索引CTE 只被引用一次
改成临时表并建索引CREATE TEMP TABLE + CREATE INDEX中间结果巨大且被多次带条件访问
改成物化视图预先算好并建索引结果跨语句、跨会话复用
改变连接策略让优化器走 Hash Join / Merge Join 而非嵌套循环两侧数据量大、无需索引点查
sql
-- 不推荐：外层对物化结果做点查，只能顺序扫描
WITH all_items AS MATERIALIZED (
  SELECT * FROM order_items
)
SELECT * FROM all_items WHERE order_id = 12345;
text
CTE Scan on all_items  (cost=... rows=1 width=28) (actual time=28.114..28.116 rows=1 loops=1)
  Filter: (order_id = 12345)
  Rows Removed by Filter: 199999
sql
-- 推荐：条件放进 CTE，索引可用
WITH one_order AS (
  SELECT * FROM order_items WHERE order_id = 12345
)
SELECT * FROM one_order;
text
Index Scan using order_items_order_id_idx on order_items  (cost=0.29..8.31 rows=1 width=28) (actual time=0.021..0.022 rows=1 loops=1)
  Index Cond: (order_id = 12345)
Execution Time: 0.048 ms

28.116 ms → 0.048 ms，差别只在于条件下推的位置。

如果中间结果确实巨大且必须多次带条件访问，就不要再纠结 CTE，直接上临时表：

sql
-- PostgreSQL 12+：临时表在会话结束或事务提交时自动清理
CREATE TEMP TABLE tmp_valid_orders ON COMMIT DROP AS
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';

CREATE INDEX ON tmp_valid_orders (user_id);
CREATE INDEX ON tmp_valid_orders (city);
ANALYZE tmp_valid_orders;

SELECT city, count(*) FROM tmp_valid_orders WHERE user_id = 42 GROUP BY city;

数据修改型 CTE 的性能与语义陷阱 ​

数据修改型 CTE（Data-Modifying CTE）在 PostgreSQL 中始终物化，并且有一个反直觉的语义：同一个语句里的多个数据修改 CTE 互相看不到对方的效果。

特性说明影响
强制物化无法用 NOT MATERIALIZED 取消中间结果无法被优化掉
互相不可见各 CTE 基于同一快照执行不能靠 CTE 顺序做级联更新
不支持递归自引用数据修改 CTE 不能引用自身无法做递归写入
只执行一次即使被引用多次副作用可控，但结果集可能被重复读取
可与 RETURNING 配合把写入结果交给后续查询实现「先写后读」的一体化语句
sql
-- 期望：把已发货订单归档，再统计归档条数
-- 实际：DELETE 与 INSERT 基于同一快照，归档数不等于删除数需自行核对
WITH archived AS (
  DELETE FROM orders
  WHERE status = 'cancelled' AND created_at < DATE '2024-08-01'
  RETURNING *
)
INSERT INTO orders_archive SELECT * FROM archived;

-- 随后单独统计
SELECT count(*) FROM orders_archive;
sql
-- 错误！不要指望两个写 CTE 之间能互相看见
WITH a AS (
  INSERT INTO orders (order_no, user_id, amount, status, created_at)
  VALUES ('SO00000001', 1, 88.00, 'paid', CURRENT_DATE)
  RETURNING id
),
b AS (
  -- 这里读不到 a 写入的那一行，快照在语句开始时就固定了
  SELECT count(*) FROM orders WHERE order_no = 'SO00000001'
)
SELECT * FROM b;

对大批量写入，逐条语句执行的性能远低于批量语句，这也是 CTE 无法替代批量写入的原因，详见本阶段后续的《批量操作》一节。

实战示例 ​

场景：运营看板要展示「各城市复购用户的贡献占比」。原始 SQL 用了三层嵌套子查询 + 两个相关子查询，执行 1.2 秒。要求在保持结果完全一致的前提下优化到 100 ms 以内。

步骤 1：确认原始查询与它的执行代价 ​

sql
-- 原始写法：嵌套子查询 + 相关子查询
SELECT
  u.city,
  (
    SELECT count(*)
    FROM orders o
    WHERE o.user_id = u.id AND o.status <> 'cancelled'
  ) AS 该用户订单数
FROM users u
WHERE u.status = 'active'
  AND u.city IN (SELECT city FROM users GROUP BY city HAVING count(*) > 100)
LIMIT 5;
text
Limit  (cost=... rows=5 width=36) (actual time=... rows=5 loops=1)
  ->  Nested Loop Semi Join  (...)
        ->  Seq Scan on users u  (...)
              Filter: ((status = 'active'::text) AND (SubPlan 1))
        SubPlan 1
          ->  Aggregate  (...)
                ->  Seq Scan on orders o  (...)
                      Filter: ((user_id = u.id) AND (status <> 'cancelled'::text))
        SubPlan 2
          ->  Aggregate  (...)
                ->  Seq Scan on orders o  (...)
                      Filter: (user_id = u.id)

相关子查询 SubPlan 的 loops 等于外层行数，每行都要重新扫一次 orders，这正是执行放大的来源。

步骤 2：把「按用户聚合」提前成一份可复用的中间结果 ​

sql
WITH user_stats AS MATERIALIZED (
  SELECT
    user_id,
    count(*)                                                    AS order_cnt,
    count(*) FILTER (WHERE status = 'cancelled')                AS cancelled_cnt,
    round(sum(amount), 2)                                       AS total_amount
  FROM orders
  GROUP BY user_id
)
SELECT * FROM user_stats WHERE user_id IN (1, 42, 777) ORDER BY user_id;
text
 user_id | order_cnt | cancelled_cnt | total_amount
---------+-----------+---------------+--------------
       1 |         4 |             1 |      1832.40
      42 |         4 |             1 |      2107.15
     777 |         4 |             1 |      1594.80
(3 rows)

orders 被扫描一次，所有用户口径都在这一份结果里。

步骤 3：用 CTE 分层重写看板查询 ​

sql
WITH city_totals AS MATERIALIZED (
  SELECT
    u.city,
    count(*)                                        AS 活跃用户数,
    sum(us.order_cnt)                               AS 订单总数
  FROM users u
  JOIN (
    SELECT user_id, order_cnt
    FROM (
      SELECT user_id, count(*) AS order_cnt
      FROM orders
      WHERE status <> 'cancelled'
      GROUP BY user_id
    ) s
  ) us ON us.user_id = u.id
  WHERE u.status = 'active'
  GROUP BY u.city
),
repeat_users AS MATERIALIZED (
  SELECT u.city, count(*) AS 复购用户数
  FROM users u
  JOIN (
    SELECT user_id
    FROM orders
    WHERE status <> 'cancelled'
    GROUP BY user_id
    HAVING count(*) >= 2
  ) r ON r.user_id = u.id
  WHERE u.status = 'active'
  GROUP BY u.city
)
SELECT
  ct.city,
  ct.活跃用户数,
  ru.复购用户数,
  round(100.0 * ru.复购用户数 / ct.活跃用户数, 2) AS 复购率
FROM city_totals ct
JOIN repeat_users ru ON ru.city = ct.city
ORDER BY 复购率 DESC;
text
 city | 活跃用户数 | 复购用户数 | 复购率
------+------------+------------+--------
 北京 |       9500 |       8963 |  94.35
 上海 |       9500 |       8951 |  94.22
 广州 |       9500 |       8944 |  94.15
 深圳 |       9500 |       8938 |  94.08
 杭州 |       9500 |       8927 |  93.97
(5 rows)

步骤 4：合并成单次扫描，消除重复读取 ​

上面两个 CTE 各自扫了一遍 orders。改成一次聚合、两种口径：

sql
WITH user_agg AS MATERIALIZED (
  SELECT
    user_id,
    count(*)                              AS valid_cnt
  FROM orders
  WHERE status <> 'cancelled'
  GROUP BY user_id
),
by_city AS MATERIALIZED (
  SELECT
    u.city,
    count(*)                                          AS 活跃用户数,
    sum(ua.valid_cnt)                                 AS 订单总数,
    count(*) FILTER (WHERE ua.valid_cnt >= 2)         AS 复购用户数
  FROM users u
  JOIN user_agg ua ON ua.user_id = u.id
  WHERE u.status = 'active'
  GROUP BY u.city
)
SELECT
  city,
  活跃用户数,
  复购用户数,
  round(100.0 * 复购用户数 / 活跃用户数, 2) AS 复购率
FROM by_city
ORDER BY 复购率 DESC;
text
 city | 活跃用户数 | 复购用户数 | 复购率
------+------------+------------+--------
 北京 |       9500 |       8963 |  94.35
 上海 |       9500 |       8951 |  94.22
 广州 |       9500 |       8944 |  94.15
 深圳 |       9500 |       8938 |  94.08
 杭州 |       9500 |       8927 |  93.97
(5 rows)

步骤 5：查看优化后的执行计划 ​

sql
EXPLAIN (ANALYZE, BUFFERS)
WITH user_agg AS MATERIALIZED (
  SELECT user_id, count(*) AS valid_cnt
  FROM orders WHERE status <> 'cancelled'
  GROUP BY user_id
),
by_city AS MATERIALIZED (
  SELECT
    u.city,
    count(*)                                  AS 活跃用户数,
    sum(ua.valid_cnt)                         AS 订单总数,
    count(*) FILTER (WHERE ua.valid_cnt >= 2) AS 复购用户数
  FROM users u
  JOIN user_agg ua ON ua.user_id = u.id
  WHERE u.status = 'active'
  GROUP BY u.city
)
SELECT * FROM by_city ORDER BY 复购率 DESC;
text
Sort  (cost=... rows=5 width=52) (actual time=... rows=5 loops=1)
  Sort Key: (round(((100.0 * (count(*) FILTER (WHERE (ua.valid_cnt >= 2)))::numeric) / (count(*))::numeric), 2)) DESC
  Buffers: shared hit=... 
  CTE user_agg
    ->  HashAggregate  (cost=... rows=50000 width=12) (actual time=... rows=49998 loops=1)
          Group Key: orders.user_id
          Batches: 1  Memory Usage: 3737kB
          ->  Seq Scan on orders  (...)
                Filter: (status <> 'cancelled'::text)
                Rows Removed by Filter: 50002
  CTE by_city
    ->  HashAggregate  (...)
          Group Key: u.city
          ->  Hash Join  (...)
                Hash Cond: (ua.user_id = u.id)
                ->  CTE Scan on user_agg ua  (...)
                ->  Hash  (...)
                      ->  Seq Scan on users u  (...)
                            Filter: (status = 'active'::text)
                            Rows Removed by Filter: 2500

关键读数：orders 只出现一次（被 user_agg 消费），user_agg 与 by_city 两组 CTE 各算一次，Batches: 1 表示哈希表未落盘。

步骤 6：用 EXCEPT 验证优化前后结果完全一致 ​

sql
-- 双向差集自检，两段都应返回 0
WITH old_style AS (
  SELECT
    u.city,
    count(*) FILTER (
      WHERE (SELECT count(*) FROM orders o
             WHERE o.user_id = u.id AND o.status <> 'cancelled') >= 2
    ) AS 复购用户数
  FROM users u
  WHERE u.status = 'active'
  GROUP BY u.city
),
new_style AS MATERIALIZED (
  SELECT
    u.city,
    count(*) FILTER (WHERE ua.valid_cnt >= 2) AS 复购用户数
  FROM users u
  JOIN (
    SELECT user_id, count(*) AS valid_cnt
    FROM orders WHERE status <> 'cancelled'
    GROUP BY user_id
  ) ua ON ua.user_id = u.id
  WHERE u.status = 'active'
  GROUP BY u.city
)
SELECT 'only_in_old' AS 差异方向, count(*) FROM (
  SELECT city, 复购用户数 FROM old_style EXCEPT ALL SELECT city, 复购用户数 FROM new_style
) t
UNION ALL
SELECT 'only_in_new', count(*) FROM (
  SELECT city, 复购用户数 FROM new_style EXCEPT ALL SELECT city, 复购用户数 FROM old_style
) t;
text
  差异方向   | count
-------------+-------
 only_in_old |     0
 only_in_new |     0
(2 rows)

步骤 7：汇总优化前后对比 ​

指标优化前（相关子查询）优化后（CTE + 条件聚合）
orders 扫描次数逐用户各一次（执行放大）1 次
是否使用索引基本用不上（SubPlan 内顺序扫描）CTE Scan + Hash Join
执行时间1200 ms 级别约 90 ms
结果一致性—EXCEPT 双向差集 0 / 0

注意事项 ​

  1. 不要为了「SQL 好看」加 MATERIALIZED。分层可读性不需要用物化来换：
sql
-- 不推荐：只是为了分层，却阻断了索引
WITH recent AS MATERIALIZED (SELECT * FROM orders WHERE created_at >= DATE '2025-01-01')
SELECT * FROM recent WHERE user_id = 42;

-- 推荐
WITH recent AS (SELECT * FROM orders WHERE created_at >= DATE '2025-01-01')
SELECT * FROM recent WHERE user_id = 42;
  1. 相关子查询出现在 SELECT 列表里时，代价与外层行数成正比。这类写法优先改写为「先聚合再连接」：
sql
-- 错误！每行都重算一次
SELECT u.username,
       (SELECT count(*) FROM orders o WHERE o.user_id = u.id) AS cnt
FROM users u;

-- 正确：一次聚合 + 连接
SELECT u.username, coalesce(s.cnt, 0) AS cnt
FROM users u
LEFT JOIN (SELECT user_id, count(*) AS cnt FROM orders GROUP BY user_id) s
  ON s.user_id = u.id;
  1. LEFT JOIN 聚合结果时记得 coalesce。聚合子查询对没有订单的用户不产出行，直接取 s.cnt 会得到 NULL：
sql
-- 不推荐：无订单用户显示 NULL
SELECT u.username, s.cnt FROM users u
LEFT JOIN (SELECT user_id, count(*) AS cnt FROM orders GROUP BY user_id) s
  ON s.user_id = u.id;

-- 推荐
SELECT u.username, coalesce(s.cnt, 0) AS cnt FROM users u
LEFT JOIN (SELECT user_id, count(*) AS cnt FROM orders GROUP BY user_id) s
  ON s.user_id = u.id;

  1. CTE 不共享统计信息。物化后的行数估算来自 CTE 查询体，误差会传导给外层的连接方式选择。若发现连接方式明显不合理（例如大表走了嵌套循环），可先 ANALYZE 相关基表，或改用临时表 + ANALYZE。

  2. 数据修改型 CTE 不要用来做「分步写入」。它们互相看不见效果，也无法递归，复杂写入流程应改用事务内的多条语句：

sql
-- 错误！b 读不到 a 写入的行
WITH a AS (INSERT INTO orders (...) VALUES (...) RETURNING id),
     b AS (SELECT count(*) FROM orders WHERE id IN (SELECT id FROM a))
SELECT * FROM b;
  1. 别用 CTE 包裹全表再多次引用。物化一份全表快照，等于放弃了所有索引：
text
-- 计划中若出现 CTE Scan 且 Rows Removed by Filter 达到十万级
-- 基本可以判定：该 CTE 不该物化，或过滤应该前置到 CTE 内

最佳实践 ​

  1. 优化顺序固定为：先减少行数，再减少扫描次数，最后才考虑物化。
text
高选择性过滤（下推到基表） → 提前聚合（压缩行数） → 复用中间结果（避免重复扫描）
  1. 一次查询里同一张基表只扫描一次。如果发现计划中同一张大表出现多次 Seq Scan，优先考虑用 CTE + 条件聚合收敛：
sql
-- 一次扫描，多口径
SELECT
  count(*) FILTER (WHERE status = 'paid')      AS 已付款,
  count(*) FILTER (WHERE status = 'shipped')   AS 已发货
FROM orders
WHERE created_at >= DATE '2025-01-01';
  1. 给 CTE 起有意义的名字，按数据流分层。名字就是文档，能替代大量注释：
sql
WITH valid_orders AS (...),      -- 过滤层
     order_totals AS (...),      -- 聚合层
     city_summary AS (...)       -- 汇总层
SELECT ...;

  1. 每个 CTE 只做一件事。一个 CTE 里同时做过滤、聚合、连接、窗口计算，既难读也难判断该不该物化。

  2. 改写后必须用 EXPLAIN (ANALYZE, BUFFERS) 复核三件事：

检查项期望异常信号
基表扫描次数大表只出现一次同一大表多次 Seq Scan
Rows Removed by Filter数值较小十万级，说明过滤位置不对
Batches / Sort MethodBatches: 1、quicksortBatches > 1、external merge
  1. 结果等价性用 EXCEPT ALL 双向自检，尤其当运行时间差异很大时，避免「改快了但改错了」：
sql
SELECT 'only_in_old', count(*) FROM (SELECT * FROM old_q EXCEPT ALL SELECT * FROM new_q) t
UNION ALL
SELECT 'only_in_new', count(*) FROM (SELECT * FROM new_q EXCEPT ALL SELECT * FROM old_q) t;
  1. 当 CTE 已经无法解决问题时果断换工具：需要索引的中间结果用临时表，跨语句复用的昂贵聚合用物化视图，一次性简单嵌套用派生表即可。

下一节

继续学习：窗口函数概述

参考链接 ​