PHP小码农

Appearance

SQL 改写优化（Query Rewriting） ​

当索引与统计信息都已到位，查询仍然慢，问题往往出在 SQL 的写法本身——同一种业务语义有若干种等价写法，而它们生成的执行计划可能截然不同。改写（rewriting）是成本最低、收益最直接的优化手段：它不依赖参数调优，也不必新建索引，只要让 SQL 更贴近规划器「看得懂」的形式。

本节聚焦 PostgreSQL 的改写要点：CTE 的内联与物化、子查询形式的等价变换、UNION/UNION ALL 的选择、分页与批量处理的写法、以及 MERGE 这类新语法带来的改写空间。

前置知识

阅读本节前，建议先了解：连接算法（Join Algorithms）

基础概念 ​

SQL 的「等价改写」有两个层次，理解它们的区别是本节的核心：

层次含义是否改变结果典型手段
语义等价改写结果集完全相同，只是写法不同否NOT IN → NOT EXISTS、子查询 → JOIN、UNION → UNION ALL
语义修正改写原写法有 NULL 陷阱或错误语义，改写后结果正确是（修正错误）NOT IN 的 NULL 问题、LEFT JOIN 条件位置
结构改写不改变语义，但改变规划器可优化的空间否CTE 内联、谓词下推、子查询扁平化
分批改写把一个大查询拆成多批小查询否keyset 分页、批量 DML 分片

改写的收益来源可以概括为一张图：

text
改写前的执行路径
    SQL 写法 ──► 优化器看到的形状（形状受限）──► 执行计划

改写后的执行路径
    SQL 写法（优化器友好的形状）──► 优化器可选的计划空间更大 ──► 更优计划

常见的改写方向与「为什么有效」：

改写方向为什么有效
让谓词下推到扫描节点扫描时就过滤，避免把大量行送到上层再丢弃
让规划器能利用索引索引只在「裸列 vs 常量/参数」的等值或范围条件上可用
消除重复计算（如物化中间结果）避免同一子查询被多次执行
减少中间结果行数（提前聚合/去重）连接与排序的代价与行数直接相关
提供更多可选计划形状（如拆开 OR）让规划器可以分别优化每个分支

一个关键认知：PostgreSQL 的规划器不是「SQL 重写器」。它做的变换很保守，尤其是越过子查询边界、CTE 边界、外连接边界时。SQL 里写出的结构，很大程度上决定了优化器能看到什么。

语法与用法 ​

改写的过程是「观测 → 假设 → 改写 → 对比验证」的循环。这里给出标准的操作方法。

第一步，建立基线（改造前必须先记下数据）：

sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
<原始 SQL>;

第二步，观察是否有「不该出现」的节点特征：

text
-- 这些信号说明 SQL 写法限制了优化空间
Filter: (上层才能判断的条件)          -- 谓词没下推
SubPlan                              -- 相关子查询被逐行执行
Materialize  (loops 很大)            -- 中间结果被反复重扫
CTE Scan  / 显式的临时结果           -- CTE 被物化，谓词无法下推
HashAggregate                        -- 本可用 UNION ALL 避免去重
Batches: N (N > 1)                   -- 哈希表落盘

第三步，改写并对比。建议把「改写前后对照」固化为调优流程的一部分：

sql
-- ===== 改造前 =====
EXPLAIN (ANALYZE, BUFFERS) <旧写法>;

-- ===== 改造后 =====
EXPLAIN (ANALYZE, BUFFERS) <新写法>;

IN 子查询是观察规划器改写能力的经典例子（PostgreSQL 会把简单 IN 扁平化为半连接）：

sql
-- 写法 A：IN 子查询。规划器通常能把它变成 Semi Join
SELECT * FROM customers c
WHERE c.id IN (SELECT o.customer_id FROM orders o WHERE o.amount > 100);
sql
-- 写法 B：IN 的值列表。规划器会转成 ScalarArrayOpExpr（= ANY），走同一个索引
SELECT * FROM customers c
WHERE c.id IN (101, 102, 103, 104, 105);
text
 Index Scan using customers_pkey on customers c  (cost=0.29..40.50 rows=5 width=40)
   Index Cond: (id = ANY ('{101,102,103,104,105}'::integer[]))

= ANY 是「单次索引扫描匹配多个值」，比 5 次独立的 OR 等值条件更容易被优化（OR 展开为多个索引扫描后还要 BitmapOr 合并）。

详细说明 ​

CTE 的物化与内联（MATERIALIZED / NOT MATERIALIZED） ​

WITH（公共表表达式，Common Table Expression，CTE）在 PostgreSQL 12 之前的版本里总是被物化——即使只引用一次，也会先算成临时结果，等于在查询里插入一道「优化屏障」，父查询的谓词无法下推到 CTE 内部。

PostgreSQL 12 起，非递归、无副作用的 WITH 查询默认可以被内联（fold / inline）到父查询。规则如下：

情况默认行为可用于预判的信号
父查询只引用 CTE 一次内联（除非显式 MATERIALIZED）计划里看不到 CTE Scan
父查询引用 CTE 多次物化计划里出现 CTE Scan
CTE 含 VOLATILE 函数或有副作用物化必须物化以保证只执行一次
递归 CTE（WITH RECURSIVE）总是物化出现 CTE Scan + WorkTable Scan

显式干预的两个关键字：

sql
-- NOT MATERIALIZED：强制内联，让父查询的谓词下推
WITH w AS NOT MATERIALIZED (
    SELECT * FROM big_table
)
SELECT * FROM w WHERE key = 123;
-- 等价于 SELECT * FROM big_table WHERE key = 123; 索引可用
sql
-- MATERIALIZED：强制物化，避免昂贵的 CTE 被重复计算
WITH w AS MATERIALIZED (
    SELECT key, very_expensive_function(val) AS f FROM some_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;
-- 物化保证 very_expensive_function 每行只算一次

判断是否需要物化，只需问一句：这个 CTE 会被引用几次？它的计算是否昂贵、是否有副作用？

  • 引用一次 + 廉价 → 内联（默认行为即可，或显式 NOT MATERIALIZED）。
  • 引用多次 + 昂贵 → 物化（MATERIALIZED）。
  • 引用多次 + 廉价 → 视情况，通常 NOT MATERIALIZED 加上重复计算会更划算。

子查询形式的等价变换 ​

同一个语义可以写成相关子查询、非相关子查询、JOIN、EXISTS 等，规划器的处理能力差异很大。

非相关子查询可以放心使用，规划器会把它当作常量或半连接处理：

sql
-- 非相关子查询：只执行一次
SELECT * FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

相关子查询（Correlated Subquery）要警惕，它出现在 SELECT 列表或 WHERE 里时，可能被逐行执行：

sql
-- 相关子查询出现在 SELECT 列表：每行执行一次
SELECT c.id, c.name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

虽然 PostgreSQL 常能把上述形式优化成一次 GROUP BY 加左连接（计划里表现为 Aggregate + Left Join），但当子查询逻辑复杂或带 LIMIT 时，就会退化成 SubPlan 逐行执行：

text
 Seq Scan on customers c  (cost=0.00..162500.00 rows=50000 width=40) (actual time=0.03..5200.10 rows=50000 loops=1)
   SubPlan 1
     ->  Aggregate  (cost=16.25..16.26 rows=1 width=8) (actual time=0.09..0.09 rows=1 loops=50000)
           ->  Index Only Scan using idx_orders_customer_id on orders o  (actual time=0.05..0.08 rows=150 loops=1)
                 Index Cond: (customer_id = c.id)

loops=50000 是危险信号。改写为显式的聚合 + 左连接，让规划器一次算完：

sql
-- 推荐：聚合一次，然后左连接
SELECT c.id, c.name, COALESCE(o.order_count, 0) AS order_count
FROM customers c
LEFT JOIN (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
) o ON o.customer_id = c.id;

EXISTS 优于 IN 的场合：子查询结果集大、只需要存在性判断时。规划器会把 EXISTS 变成 Semi Join，找到第一行即可短路：

sql
-- 推荐：存在性判断用 EXISTS
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.amount > 1000);

IN 与 EXISTS 的 NULL 差异同样重要：

sql
-- 不推荐：若子查询返回 NULL，NOT IN 会让整个结果为空
SELECT * FROM customers c
WHERE c.id NOT IN (SELECT customer_id FROM orders);

-- 推荐：NOT EXISTS 不受 NULL 影响
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

三种写法（IN / EXISTS / JOIN）的选择速查：

需求推荐写法规划器会生成的节点
判断存在性，不需要右表列EXISTSSemi Join
判断不存在，需要 NULL 安全NOT EXISTSAnti Join
需要右表的列JOIN三种连接算法之一
判断是否在「小值列表」中IN (v1, v2, ...)= ANY / BitmapOr
判断不存在且子查询列非空NOT IN + IS NOT NULLAnti Join

UNION 与 UNION ALL 的选择 ​

UNION 会做去重（隐式 HashAggregate 或 Sort + Unique），UNION ALL 只是简单地拼接结果。如果业务上不需要去重，一定要写 UNION ALL。

sql
-- 不推荐：UNION 触发去重，等价于多了一次全量排序/哈希
SELECT id FROM orders_2025
UNION
SELECT id FROM orders_2026;
sql
-- 推荐：确认无重复（或允许重复）时用 UNION ALL
SELECT id FROM orders_2025
UNION ALL
SELECT id FROM orders_2026;

计划差异非常直观：

text
-- UNION：多一层去重
 HashAggregate  (cost=...)          ← 去重开销
   Group Key: orders_2025.id
   ->  Append  (cost=...)
         ->  Seq Scan on orders_2025
         ->  Seq Scan on orders_2026
text
-- UNION ALL：直接拼接
 Append  (cost=...)
   ->  Seq Scan on orders_2025
   ->  Seq Scan on orders_2026

如果两个分支来自同一张表的互斥条件（如按时间分区），更好的写法是用 OR 或直接用范围条件，让规划器看到单一数据源：

sql
-- 不推荐：两次扫描同一张表
SELECT * FROM orders WHERE created_at < DATE '2026-01-01'
UNION ALL
SELECT * FROM orders WHERE created_at >= DATE '2026-01-01';
sql
-- 推荐：单次扫描即可覆盖全部数据
SELECT * FROM orders;

UNION ALL 还有一个重要用途：把一个大查询拆成多个小查询并行跑，或让每个分支单独用上不同的索引。这在「OR 条件无法同时利用两个索引」时特别有效：

sql
-- 不推荐：OR 两个不同列，规划器可能退化成一个索引扫描 + Filter
SELECT * FROM orders WHERE customer_id = 42 OR status = 'vip';
sql
-- 推荐：拆成 UNION ALL，两个分支各自走自己的索引
SELECT * FROM orders WHERE customer_id = 42
UNION ALL
SELECT * FROM orders WHERE status = 'vip' AND customer_id IS DISTINCT FROM 42;

注意第二个分支要排除第一个分支已覆盖的行（IS DISTINCT FROM 处理 NULL），否则会产生重复。

分页：OFFSET 深翻页的替代方案 ​

LIMIT n OFFSET m 的问题在于：数据库必须先扫描并丢弃前 m 行。m 达到百万级时，即使有索引，也要走完百万次索引查找。

sql
-- 不推荐：偏移 100 万行，需扫描并丢弃 100 万行
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 1000000;
text
 Limit  (cost=48500.00..48500.50 rows=20 width=64) (actual time=3200.15..3200.16 rows=20 loops=1)
   ->  Index Scan using orders_pkey on orders  (cost=0.42..121000.00 rows=5000000 width=64) (actual time=0.05..2800.30 rows=1000020 loops=1)

rows=1000020 明确暴露了「扫描了 100 万行却只返回 20 行」。

keyset 分页（keyset pagination） 用「上一页最后一行的排序键」作为起点，把 OFFSET 换成 WHERE 条件：

sql
-- 推荐：keyset 分页。用上一页最后一行的 id 作为游标
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;
text
 Limit  (cost=0.42..8.60 rows=20 width=64) (actual time=0.02..0.10 rows=20 loops=1)
   ->  Index Scan using orders_pkey on orders  (cost=0.42..121000.00 rows=4999000 width=64) (actual time=0.02..0.07 rows=20 loops=1)
         Index Cond: (id > 1000000)

rows=20——索引直接定位到起点，读取 20 行即停。耗时从 3200ms 降到 0.1ms。

唯一的约束是：排序键必须唯一且稳定，否则需要多列组合键（如 (created_at, id)）来打破并列：

sql
-- 推荐：多列 keyset，确保顺序稳定
SELECT * FROM orders
WHERE (created_at, id) > (TIMESTAMP '2026-01-01 10:00:00', 1000000)
ORDER BY created_at, id
LIMIT 20;

聚合前置：把过滤与收敛做在连接之前 ​

连接、排序、聚合的代价都与输入行数成正比。能提前收敛行数的地方，绝不拖到后面。

sql
-- 不推荐：先连接全部数据，再 GROUP BY 收敛
SELECT c.id, COUNT(*)
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items i ON i.order_id = o.id
GROUP BY c.id;
sql
-- 推荐：先各自聚合/收敛，再连接。中间结果行数大幅减少
SELECT c.id, o.order_count
FROM customers c
JOIN (
    SELECT o.customer_id, COUNT(*) AS order_count
    FROM orders o
    JOIN order_items i ON i.order_id = o.id
    GROUP BY o.customer_id
) o ON o.customer_id = c.id;

同理，DISTINCT 常常可以用 EXISTS 或 GROUP BY 替代，从而避免一次全量排序：

sql
-- 不推荐：DISTINCT 需要排序或哈希去重整个结果集
SELECT DISTINCT c.id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id;
sql
-- 推荐：存在性判断即可，可短路
SELECT c.id, c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

在 WHERE 里做「先过滤再连接」，比「先连接再过滤」高效；但要注意谓词下推的边界：

sql
-- 推荐：过滤条件写在子查询内，先收敛再连接
SELECT c.id, o.amount
FROM customers c
JOIN (
    SELECT * FROM orders WHERE created_at >= DATE '2026-01-01'
) o ON o.customer_id = c.id;

PostgreSQL 通常能把外层的 WHERE 条件下推到内联的子查询，但一旦子查询带 LIMIT、DISTINCT、窗口函数或聚合，下推就会被阻断，此时把条件写进子查询内部是必要的：

sql
-- 窗口函数会阻断谓词下推，条件必须写在窗口函数之前
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
    FROM orders
    WHERE created_at >= DATE '2026-01-01'   -- 必须写在窗口函数内部
) t WHERE rn <= 3;

批量 DML 与 MERGE ​

INSERT、UPDATE、DELETE 也有改写空间。核心原则是避免逐行操作。

sql
-- 不推荐：逐行 UPDATE（在应用层循环执行 10000 次）
UPDATE orders SET status = 'archived' WHERE id = 12345;
sql
-- 推荐：一条语句批量更新
UPDATE orders SET status = 'archived'
WHERE created_at < DATE '2024-01-01' AND status <> 'archived';

用 UPDATE ... FROM 按另一张表的数据批量更新（比相关子查询快得多）：

sql
-- 推荐：用 UPDATE ... FROM 做「按表更新」
UPDATE orders o
SET status = s.new_status
FROM order_status_stage s
WHERE s.order_id = o.id
  AND o.status IS DISTINCT FROM s.new_status;

IS DISTINCT FROM 这个条件很关键：它避免了对「值没变」的行做无意义的写入，从而大幅减少 WAL 与死元组（dead tuple）的产生。

MERGE（PostgreSQL 15 起支持）把「存在则更新、不存在则插入」的 upsert 逻辑写成单条语句，比应用层的「先 SELECT 再分支」更高效且无竞态：

sql
-- PostgreSQL 15 起支持 MERGE
MERGE INTO inventory AS t
USING daily_sales AS s
ON t.product_id = s.product_id
WHEN MATCHED THEN
    UPDATE SET quantity = t.quantity - s.sold
WHEN NOT MATCHED THEN
    INSERT (product_id, quantity) VALUES (s.product_id, -s.sold);

对于简单的 upsert，INSERT ... ON CONFLICT 更简洁：

sql
INSERT INTO inventory (product_id, quantity)
VALUES (42, 100)
ON CONFLICT (product_id) DO UPDATE
SET quantity = inventory.quantity + EXCLUDED.quantity;

大表 DELETE 建议分批，避免单事务持有大量锁和产生巨大 WAL：

sql
-- 推荐：分批删除，每批提交
DELETE FROM orders
WHERE id IN (
    SELECT id FROM orders WHERE created_at < DATE '2023-01-01' LIMIT 10000
);
-- 重复执行直到影响行数为 0

让索引可用的改写规则 ​

索引能否被使用，取决于条件是否写成「裸列 vs 常量/参数」。以下改写几乎总能带来收益。

规则一：不要在索引列上套函数。

sql
-- 不推荐：索引列被函数包裹，无法使用 idx_orders_created_at
SELECT * FROM orders WHERE DATE(created_at) = DATE '2026-01-01';
sql
-- 推荐：改成范围条件
SELECT * FROM orders
WHERE created_at >= DATE '2026-01-01' AND created_at < DATE '2026-01-02';

规则二：避免在索引列上做隐式类型转换。

sql
-- 不推荐：phone 是 varchar，写成数字会触发隐式转换
SELECT * FROM users WHERE phone = 13800138000;
sql
-- 推荐：类型匹配
SELECT * FROM users WHERE phone = '13800138000';

规则三：前置模糊查询要用 text_pattern_ops 或显式转换。

sql
-- 默认排序规则（如 en_US.UTF-8）下 LIKE 'x%' 用不上普通索引
CREATE INDEX idx_users_name_pattern ON users (name text_pattern_ops);

规则四：!= 与 NOT IN 几乎无法用索引，改成 NOT EXISTS 或范围。

sql
-- 不推荐：不等于条件的可选择性差，索引通常帮不上忙
SELECT * FROM orders WHERE status != 'cancelled';
sql
-- 推荐：若是枚举列，用正列举（可走索引）
SELECT * FROM orders WHERE status IN ('paid', 'shipped', 'pending');

规则五：复合索引的列顺序要与条件形态匹配。

sql
-- 索引 (status, created_at)：等值列在前，范围列在后
CREATE INDEX idx_orders_status_created ON orders (status, created_at);

-- 推荐：等值 + 范围，两列都能作为索引条件
SELECT * FROM orders
WHERE status = 'paid' AND created_at >= DATE '2026-01-01';

只读视图与函数的下推边界 ​

视图本质上是一个命名的子查询，规划器会把视图展开（除非是物化视图），谓词下推通常正常。但以下结构会阻断下推：

结构是否阻断谓词下推说明
普通视图否展开后参与整体优化
物化视图是已是独立存储的数据，谓词只能在读取后应用
含 LIMIT 的子查询是行数不确定，不能下推
含窗口函数的子查询是窗口函数在过滤前计算
含 DISTINCT 的子查询视情况可能被合并，也可能阻断
含聚合的子查询是除非是「可以合并的聚合」
含 VOLATILE 函数的表达式是不能改变执行次数

物化视图的改写策略是：把过滤条件下推到「刷新物化视图的查询」里，而不是在读取时过滤：

sql
-- 推荐：定义时就用 WHERE 收敛，让物化视图本身更小
CREATE MATERIALIZED VIEW mv_active_orders AS
SELECT * FROM orders WHERE status IN ('paid', 'shipped');

大表上的 SUM、COUNT 也可以预先算好，用物化视图替代实时聚合：

sql
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT DATE(created_at) AS d, SUM(amount) AS total, COUNT(*) AS cnt
FROM orders
GROUP BY DATE(created_at);

用 EXPLAIN 验证改写效果 ​

改写后必须用 EXPLAIN (ANALYZE, BUFFERS) 对比，而不是凭直觉判断「这样写应该更快」。关注四个指标：

sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <改写后的 SQL>;
text
-- 对比清单
① Execution Time          总耗时是否下降？
② Buffers: shared hit/read 实际 I/O 是否减少？
③ 估算 rows 与实际 rows  偏差是否缩小？
④ 节点形状               是否去掉了 SubPlan / 多余的 Sort / HashAggregate？

一个容易被忽略的验证点：改写后的计划是否对参数化查询稳定。预处理语句（prepared statement）在 PostgreSQL 里可能使用通用计划（generic plan），它的代价估算不考虑具体参数值，可能导致计划与「自定义计划（custom plan）」不同：

sql
-- 观察是否使用通用计划
PREPARE p (int) AS SELECT * FROM orders WHERE customer_id = $1;
EXPLAIN (ANALYZE) EXECUTE p (42);
EXPLAIN (ANALYZE) EXECUTE p (42);   -- 第 6 次执行后通常会切换为通用计划

PostgreSQL 16 起，EXPLAIN 支持 GENERIC_PLAN 选项，可以直接查看通用计划而不必真的执行：

sql
-- PostgreSQL 16 起支持 GENERIC_PLAN
EXPLAIN (GENERIC_PLAN) SELECT * FROM orders WHERE customer_id = $1;

这填补了「通用计划难以观测」的空白——过去要看到通用计划，必须把同一个预处理语句执行 6 次以上。如果发现通用计划明显劣于自定义计划，可以考虑 plan_cache_mode = force_custom_plan。

实战示例 ​

场景一：CTE 物化导致的谓词无法下推 ​

需求是「统计 2026 年下单且消费超过 1000 的客户，及其订单数」。开发者为了可读性用了 CTE。

sql
-- 改造前：CTE 被引用两次 → 物化 → 谓词无法下推
EXPLAIN (ANALYZE, BUFFERS)
WITH recent_orders AS (
    SELECT customer_id, amount, created_at
    FROM orders
)
SELECT c.id,
       (SELECT COUNT(*) FROM recent_orders r
        WHERE r.customer_id = c.id AND r.amount > 1000) AS big_orders
FROM customers c
WHERE EXISTS (SELECT 1 FROM recent_orders r
              WHERE r.customer_id = c.id
                AND r.created_at >= DATE '2026-01-01');
text
 Seq Scan on customers c  (cost=0.00..187500.00 rows=25000 width=40) (actual time=0.05..6420.30 rows=18200 loops=1)
   Filter: (SubPlan 2)
   Rows Removed by Filter: 31800
   CTE recent_orders
     ->  Seq Scan on orders  (cost=0.00..121000.00 rows=5000000 width=24) (actual time=0.02..780.50 rows=5000000 loops=1)
   SubPlan 2
     ->  Aggregate  (cost=1450.00..1450.01 rows=1 width=8) (actual time=0.35..0.35 rows=1 loops=50000)
           ->  CTE Scan on recent_orders r  (cost=0.00..125000.00 rows=2500 width=0) (actual time=0.20..0.30 rows=150 loops=50000)
                 Filter: ((customer_id = c.id) AND (amount > 1000))
                 Rows Removed by Filter: 4999850
   SubPlan 3
     ->  CTE Scan on recent_orders r_1  (cost=0.00..125000.00 rows=2500 width=0) (actual time=0.30..0.30 rows=1 loops=50000)
           Filter: ((customer_id = c.id) AND (created_at >= '2026-01-01'::date))
   Buffers: shared hit=250000 read=4800000
 Planning Time: 1.204 ms
 Execution Time: 6420.552 ms

逐行解读：

  1. CTE recent_orders 被物化：Seq Scan on orders 一次读取了全部 500 万行，构建出一个临时结果。因为 CTE 被引用两次（SubPlan 2 与 SubPlan 3），规划器按默认规则选择了物化。
  2. 谓词完全无法下推：amount > 1000 与 created_at >= '2026-01-01' 都只能在 CTE Scan 上作为 Filter 应用。每个 SubPlan 的单次执行都看到 500 万行（Rows Removed by Filter: 4999850），这就是 Buffers: read=4800000 的来源。
  3. loops=50000：两个 SubPlan 各被外表循环 5 万次，单次 0.30~0.35ms，总计约 3 万次 × 0.3ms。
  4. 总耗时 6420ms，而这张查询的「语义复杂度」其实很低。

改写：把过滤条件下推到 CTE 内部，并避免 CTE 被多次引用。

sql
-- 改造后：条件写进 CTE，并用 NOT MATERIALIZED + 聚合一次算完
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.id, COALESCE(o.big_orders, 0) AS big_orders
FROM customers c
JOIN (
    SELECT customer_id,
           COUNT(*) FILTER (WHERE amount > 1000) AS big_orders,
           BOOL_OR(created_at >= DATE '2026-01-01') AS has_recent
    FROM orders
    GROUP BY customer_id
) o ON o.customer_id = c.id
WHERE o.has_recent;
text
 Hash Join  (cost=152000.00..198000.00 rows=18200 width=40) (actual time=920.40..1480.25 rows=18200 loops=1)
   Hash Cond: (o.customer_id = c.id)
   ->  HashAggregate  (cost=121000.00..131000.00 rows=1000000 width=25) (actual time=480.10..820.30 rows=995180 loops=1)
         Group Key: orders.customer_id
         ->  Seq Scan on orders  (cost=0.00..121000.00 rows=5000000 width=21) (actual time=0.02..320.15 rows=5000000 loops=1)
   ->  Hash  (cost=1250.00..1250.00 rows=50000 width=8) (actual time=10.20..10.20 rows=50000 loops=1)
         Buckets: 65536  Batches: 1  Memory Usage: 2864kB
         ->  Seq Scan on customers c  (cost=0.00..1250.00 rows=50000 width=8) (actual time=0.02..4.10 rows=50000 loops=1)
   Buffers: shared hit=78000 read=125000
 Planning Time: 0.880 ms
 Execution Time: 1480.878 ms

关键变化：

  1. 只扫一遍 orders。条件被合并进一次 HashAggregate，COUNT(*) FILTER (...) 与 BOOL_OR(...) 都在同一次扫描里计算。
  2. SubPlan 完全消失，loops=50000 的逐行执行被消除。
  3. Buffers 从 505 万降到 20 万，执行时间从 6420ms 降到 1480ms。

这里没有新建任何索引。仅靠「让过滤条件进入扫描层、让聚合只做一次」，就获得了 4 倍以上的提升。

场景二：OR 条件导致索引失效，改写为 UNION ALL ​

需求是「查出 VIP 客户的订单，或金额超过 10000 的订单」。

sql
-- 改造前：OR 两侧中有一侧无索引
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, amount, status
FROM orders
WHERE status = 'vip'
   OR amount > 10000;
text
 Seq Scan on orders  (cost=0.00..246500.00 rows=250000 width=64) (actual time=0.03..3150.44 rows=248100 loops=1)
   Filter: ((status = 'vip'::text) OR (amount > 10000))
   Rows Removed by Filter: 4751900
   Buffers: shared hit=1200 read=240000
 Planning Time: 0.420 ms
 Execution Time: 3150.612 ms

逐行解读：

  1. Seq Scan 读了全部 500 万行，Rows Removed by Filter: 4751900——48 万行有用，95% 是白读的。
  2. 两个条件都有索引（idx_orders_status、idx_orders_amount），但 BitmapOr 只在「两个分支都可索引且估计合并代价更低」时才会被选。这里 amount > 10000 的估算选择率不够低（约 5%），BitmapOr 合并的估算代价超过了顺序扫描，所以规划器放弃了索引。

改写一：降低 random_page_cost，让索引看起来更便宜（若硬件是 SSD）。

sql
SET LOCAL random_page_cost = 1.1;

改写二（更通用）：拆成 UNION ALL，让每个分支独立优化。

sql
-- 改造后：拆成两个分支，各自走自己的索引
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, amount, status
FROM orders
WHERE status = 'vip'
UNION ALL
SELECT id, customer_id, amount, status
FROM orders
WHERE amount > 10000
  AND status IS DISTINCT FROM 'vip';   -- 排除第一分支已覆盖的行，避免重复
text
 Append  (cost=0.42..42800.00 rows=248100 width=64) (actual time=0.05..620.85 rows=248100 loops=1)
   Buffers: shared hit=18500 read=42000
   ->  Index Scan using idx_orders_status on orders  (cost=0.42..9850.00 rows=48500 width=64) (actual time=0.04..145.20 rows=48620 loops=1)
         Index Cond: (status = 'vip'::text)
   ->  Index Scan using idx_orders_amount on orders  (cost=0.00..29500.00 rows=199600 width=64) (actual time=0.06..420.30 rows=199480 loops=1)
         Index Cond: (amount > 10000)
         Filter: (status IS DISTINCT FROM 'vip'::text)
         Rows Removed by Filter: 120
 Planning Time: 0.950 ms
 Execution Time: 621.102 ms

关键变化：

  1. 计划变成 Append + 两个 Index Scan，两个分支各自用上了自己的索引。这正是 BitmapOr 合并失败时最有效的替代方案。
  2. Buffers 从 24 万降到 6 万，执行时间从 3150ms 降到 621ms（约 5 倍）。
  3. status IS DISTINCT FROM 'vip' 是保证结果正确性的关键：它用 NULL 安全的方式排除了第一分支已经输出的行。如果只写 status <> 'vip'，status IS NULL 的行会被两个分支都漏掉。

这个例子展示了改写的核心价值：当规划器的自动变换（BitmapOr）失败时，SQL 结构层面的改写（UNION ALL 拆分）可以人工补上这个变换。

注意事项 ​

  • 不要用 WITH 包裹「只引用一次」的简单子查询而不加 NOT MATERIALIZED（PostgreSQL 12 之前尤其危险）。 物化会阻断谓词下推。

    sql
    -- 不推荐（PG 12 之前必然物化；PG 12+ 若被多次引用也会物化）
WITH w AS (SELECT * FROM big_table)
SELECT * FROM w WHERE key = 123;

  • 不要对「本就有唯一性保证」的结果集使用 UNION。 多余的 HashAggregate/Sort 是纯浪费。

    sql
    -- 不推荐：两个分支的主键本就互不重复
SELECT id FROM orders_2025 UNION SELECT id FROM orders_2026;

  • 不要用 NOT IN 处理可能含 NULL 的子查询。 会静默返回空结果，是最隐蔽的「性能问题」——其实是正确性问题。

    sql
    -- 错误！
SELECT * FROM customers c WHERE c.id NOT IN (SELECT o.customer_id FROM orders o);

  • 不要在 UNION ALL 拆分 OR 时忘记去重条件。 两个分支的重叠部分会产生重复行。

    sql
    -- 错误！status = 'vip' 且 amount > 10000 的行会出现两次
SELECT * FROM orders WHERE status = 'vip'
UNION ALL
SELECT * FROM orders WHERE amount > 10000;

  • 不要在大表的窗口函数子查询外部写过滤条件。 窗口函数会阻断谓词下推。

    sql
    -- 不推荐：条件写在窗口函数外层，无法下推
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) rn
    FROM orders
) t WHERE rn <= 3 AND created_at >= DATE '2026-01-01';

  • 不要用 SELECT * 制造「无法覆盖索引」的查询。 返回列越少，越可能命中 Index Only Scan。

    sql
    -- 不推荐：需要回表取全部列
SELECT * FROM orders WHERE status = 'paid';

  • 不要在 MERGE 的源侧保留重复键。 同一目标行被匹配两次会直接报错。

    sql
    -- 错误！源侧 product_id 重复时会报：
-- ERROR: MERGE command cannot affect row a second time
MERGE INTO inventory t USING daily_sales s ON t.product_id = s.product_id ...

  • 不要为了「可读性」把热路径查询写成多层嵌套 CTE。 每一层 CTE 都可能是物化屏障，代价会随层数放大。

    sql
    -- 不推荐：三层 CTE，每层都可能物化
WITH a AS (...), b AS (SELECT * FROM a ...), c AS (SELECT * FROM b ...)
SELECT * FROM c WHERE ...;

  • 不要在应用层逐行执行 DML。 一万次单行 UPDATE 的开销远超一次批量语句。

    sql
    -- 不推荐：应用层循环一万次
UPDATE orders SET status = 'archived' WHERE id = ?;

  • 不要忽略批量 DML 写放大。 对「值没变」的行执行 UPDATE 会额外产生死元组与 WAL。

    sql
    -- 推荐：加上 IS DISTINCT FROM 过滤掉无变化的行
UPDATE orders o SET status = s.new_status
FROM stage s WHERE s.order_id = o.id
  AND o.status IS DISTINCT FROM s.new_status;

最佳实践 ​

  • 改写前先固定基线。 把改造前的 EXPLAIN (ANALYZE, BUFFERS) 完整保存，改写后逐项对比。

    sql
    EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <SQL>;
-- 记录 Execution Time、Buffers、节点形状三项

  • 优先做「谓词下推」类改写。 让过滤尽早发生，往往是最立竿见影的手段。

    sql
    -- 条件写进子查询/CTE 内部，而不是最外层
JOIN (SELECT * FROM orders WHERE created_at >= DATE '2026-01-01') o ON ...

  • CTE 引用一次就用默认内联；引用多次且昂贵才显式 MATERIALIZED。

    sql
    WITH delta AS NOT MATERIALIZED (SELECT ... )       -- 引用一次，要下推
WITH expensive AS MATERIALIZED (SELECT slow_fn(..)) -- 引用多次，要复用

  • 存在性判断统一用 EXISTS / NOT EXISTS。 既避免 NULL 陷阱，又便于生成 Semi/Anti Join。

    sql
    WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)

  • 不需要去重时一律写 UNION ALL。 这是零风险、零成本的改写。

    sql
    SELECT ... UNION ALL SELECT ...

  • OR 拆分是「索引没被用上」的通用兜底方案。 拆分时务必用 IS DISTINCT FROM 去重。

    sql
    SELECT ... WHERE a = 1
UNION ALL
SELECT ... WHERE b = 2 AND a IS DISTINCT FROM 1;

  • 深分页一律改 keyset 分页。 排序键要唯一且稳定，必要时用多列组合。

    sql
    WHERE (created_at, id) > (:last_created_at, :last_id) ORDER BY created_at, id LIMIT 20

  • 连接前先收敛行数。 尽量让 GROUP BY、DISTINCT、LIMIT 在连接之前完成。

    sql
    JOIN (SELECT customer_id, COUNT(*) c FROM orders GROUP BY customer_id) agg ON ...

  • 用 EXPLAIN (GENERIC_PLAN)（PostgreSQL 16 起支持）检查预处理语句的通用计划。 通用计划与自定义计划的差异是常见的「线上慢、本地快」根因。

    sql
    EXPLAIN (GENERIC_PLAN) SELECT * FROM orders WHERE customer_id = $1;

  • 物化视图把过滤与聚合前置。 让视图本身尽量小，而不是在读取时过滤。

    sql
    CREATE MATERIALIZED VIEW mv_active_orders AS
SELECT * FROM orders WHERE status IN ('paid', 'shipped');

  • 用 pg_stat_statements 找出「最值得改写」的查询。 按总耗时排序，优先优化占比最高的那些。

    sql
    SELECT queryid, calls, total_exec_time, mean_exec_time, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

下一节

继续学习：并行查询概览（Parallel Query Overview）

参考链接 ​