PHP小码农

Appearance

SELECT 写法优化 ​

同样的业务需求，SQL 写法不同，执行时间可能相差几十倍——因为优化器只能在你的写法允许的范围内做优化。本节聚焦 SELECT 语句的常见写法陷阱：让索引失效的 WHERE 条件、隐式类型转换、OR 与 LIKE 的选择率问题，以及 DISTINCT / UNION / EXISTS 的取舍。学完本节，你将能用 EXPLAIN (ANALYZE, BUFFERS) 量化验证每一次改写。

前置知识

阅读本节前，建议先了解：GROUPING / GROUPING_ID

基础概念 ​

SQL 优化有一条第一性原则：让数据库少干活。扫描更少的行、读取更少的列、排序更少的数据、跨网络传更少的字节——任何一个环节减少，性能都会改善。

概念术语说明
可索引条件SargableSearch ARGument ABLE，条件能被索引直接利用
不可索引条件Non-Sargable列上套函数/表达式，索引无法匹配
隐式类型转换Implicit Cast类型不匹配时自动转换，常导致索引失效
选择率Selectivity满足条件的行占全表比例，越低越适合索引
索引扫描Index Scan通过索引定位行，适合低选择率
顺序扫描Seq Scan全表逐行读，适合高选择率或小表
仅索引扫描Index Only Scan所需列都在索引中，免回表
位图扫描Bitmap Heap Scan先建位图再批量回表，适合中等选择率
工作内存work_mem排序与哈希的可用内存，超出则落盘

先看总览：常见写法的代价对照

写法典型问题相对代价推荐改写
SELECT *多读无用列、破坏仅索引扫描1~3×显式列出所需列
WHERE date(created_at) = '2026-01-01'列上套函数，索引失效10~100×改写为范围条件
WHERE id = '123'（id 是 int）隐式类型转换10~100×字面量类型对齐
WHERE a = 1 OR b = 2难以走索引5~50×拆 UNION ALL
WHERE name LIKE '%张'前置通配符索引全废倒排/GIN/三元组索引
WHERE status != 'done'高选择率，索引无益可能更慢改 IN 或直接 Seq Scan
SELECT DISTINCT ...隐含全量排序/哈希1~2×能用 GROUP BY 就走索引
UNION额外去重排序1.5~3×无重复语义时用 UNION ALL
IN (SELECT ...)可能退化为逐行子查询1~100×用 EXISTS 或 JOIN
ORDER BY ... LIMIT n 无索引全表排序后再截断10~100×建与排序列一致的索引

改写的基本判断流程：

text
WHERE 条件能否直接匹配索引列？
  ├─ 否 → 检查：列上是否有函数/表达式？类型是否一致？是否有 OR / LIKE '%x'？
  │        → 改写条件，让索引可用
  └─ 是 → 选择率是否足够低（经验阈值 5% 以下）？
           ├─ 是 → 走 Index Scan / Bitmap Heap Scan
           └─ 否 → Seq Scan 可能才是最优，别硬套索引

语法与用法 ​

优化不是「照着技巧清单改」，而是「先测量、再改写、再测量」。标准工作流：

sql
-- 第 1 步：打开精确统计与缓冲信息
\timing on

-- 第 2 步：测量原始写法
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT ... FROM ... WHERE ...;

-- 第 3 步：按本节技巧改写后再次测量，对比 actual time 与 Buffers
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT ... FROM ... WHERE ...;

准备本节贯穿使用的示例表（在 demo 库中执行）：

sql
-- 订单主表：100 万行，覆盖常见查询模式
CREATE TABLE orders (
  id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no    TEXT           NOT NULL,
  user_id     INTEGER        NOT NULL,
  status      TEXT           NOT NULL,        -- pending / paid / shipped / done / cancelled
  channel     TEXT           NOT NULL,        -- 线上 / 门店 / 分销
  amount      NUMERIC(12, 2) NOT NULL,
  remark      TEXT,
  is_paid     BOOLEAN        NOT NULL DEFAULT false,
  created_at  TIMESTAMPTZ    NOT NULL
);

-- 造数：100 万行，created_at 跨 3 年
INSERT INTO orders (order_no, user_id, status, channel, amount, remark, is_paid, created_at)
SELECT
  'NO' || lpad(i::text, 10, '0'),
  1 + (i % 50000),
  (ARRAY['pending', 'paid', 'shipped', 'done', 'cancelled'])[1 + (i % 5)],
  (ARRAY['线上', '门店', '分销'])[1 + (i % 3)],
  round((random() * 5000 + 50)::numeric, 2),
  CASE WHEN i % 4 = 0 THEN NULL ELSE '备注-' || i END,
  i % 3 <> 0,
  TIMESTAMPTZ '2024-01-01 00:00:00+08' + (i % 1095) * INTERVAL '1 day'
FROM generate_series(1, 1000000) AS i;

-- 常用索引
CREATE INDEX idx_orders_created_at ON orders (created_at);
CREATE INDEX idx_orders_status     ON orders (status);
CREATE INDEX idx_orders_user_id    ON orders (user_id);
CREATE INDEX idx_orders_channel    ON orders (channel);

ANALYZE orders;

最小可运行的对照示例：

sql
-- 改写前：列上套函数，索引失效
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM orders WHERE date(created_at) = DATE '2026-01-01';

-- 改写后：范围条件，可走索引
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM orders
WHERE created_at >= TIMESTAMPTZ '2026-01-01 00:00:00+08'
  AND created_at <  TIMESTAMPTZ '2026-01-02 00:00:00+08';
text
-- 改写前：全表扫描
 Aggregate
   ->  Seq Scan on orders
         Filter: (date(created_at) = '2026-01-01'::date)
         Rows Removed by Filter: 999085
         Buffers: shared hit=1 read=16666
 Execution Time: 152.884 ms

-- 改写后：索引扫描
 Aggregate
   ->  Bitmap Heap Scan on orders
         Recheck Cond: ((created_at >= '2026-01-01 00:00:00+08'::timestamptz) AND (created_at < ...))
         Heap Blocks: exact=863
         ->  Bitmap Index Scan on idx_orders_created_at
               Index Cond: ((created_at >= ...) AND (created_at < ...))
               Buffers: shared hit=3 read=32
 Execution Time: 12.331 ms

耗时从 152.9 ms 降到 12.3 ms，Buffers 从 16667 页降到 35 页——这就是「写法决定计划」的典型幅度。

详细说明 ​

避免 SELECT * ​

SELECT * 有三个具体代价，而不是「风格不好」这种模糊说法：

  1. 多读列 → 多读页：宽表一行的列可能跨多个数据页，只取 3 列却读了 30 列，IO 成倍增加；
  2. 破坏 Index Only Scan：本例的 SELECT count(*) 或 SELECT status 若所需列都在索引里，可免回表；一旦 SELECT *，必须回表取所有列；
  3. 传输出错风险与脆弱性：应用依赖列顺序，表结构一改（加列/删列）就崩。
sql
-- 不推荐：全列读取，无法走 Index Only Scan
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT * FROM orders WHERE status = 'paid';

-- 推荐：只取所需列
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, status, amount FROM orders WHERE status = 'paid';
text
-- SELECT *：必须回表
 Bitmap Heap Scan on orders
   Recheck Cond: (status = 'paid'::text)
   Heap Blocks: exact=12500
   ->  Bitmap Index Scan on idx_orders_status
         Buffers: shared hit=1 read=16705
 Execution Time: 88.442 ms

-- 只取三列（其中两列在索引里）：若建覆盖索引可直接 Index Only Scan
 Bitmap Heap Scan on orders
   Heap Blocks: exact=12500
   Buffers: shared hit=1 read=16705
 Execution Time: 79.116 ms

进一步配合覆盖索引，差距会更明显：

sql
-- 覆盖索引：把查询需要的列全部放进 INCLUDE
CREATE INDEX idx_orders_status_cover ON orders (status) INCLUDE (id, amount);
ANALYZE orders;

-- 现在可以走 Index Only Scan，完全免回表
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, status, amount FROM orders WHERE status = 'paid';
text
 Index Only Scan using idx_orders_status_cover on orders
   Index Cond: (status = 'paid'::text)
   Heap Fetches: 0
   Buffers: shared hit=1 read=612
 Execution Time: 18.771 ms

Buffers 从 16706 页降到 613 页，耗时降到 18.8 ms。

WHERE 中列上套函数或表达式 ​

这是最常见的索引失效原因。只要 WHERE 的索引列被函数包裹，B-tree 索引就无法用于定位：

sql
-- 错误！date() 包裹索引列，索引失效，退化为全表扫描
SELECT count(*) FROM orders WHERE date(created_at) = DATE '2026-01-01';

-- 不推荐：字符串拼接同样让索引失效
SELECT count(*) FROM orders WHERE order_no || '' = 'NO0000000001';

-- 不推荐：列参与算术运算，索引失效
SELECT count(*) FROM orders WHERE amount * 1.13 > 5000;

改写原则：把函数从「列的一侧」移到「常量的一侧」，或改写为等价的区间条件。

原写法（索引失效）改写后（索引可用）
date(created_at) = '2026-01-01'created_at >= '2026-01-01' AND created_at < '2026-01-02'
extract(year from created_at) = 2026created_at >= '2026-01-01' AND created_at < '2027-01-01'
amount * 1.13 > 5000amount > 5000 / 1.13
`order_no
created_at::date = current_datecreated_at >= current_date AND created_at < current_date + 1
lower(email) = 'a@b.com'建表达式索引 ON t (lower(email))，保持原写法
sql
-- 验证：改写为区间后走索引
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM orders
WHERE created_at >= TIMESTAMPTZ '2026-01-01 00:00:00+08'
  AND created_at <  TIMESTAMPTZ '2026-01-02 00:00:00+08';
text
 Aggregate
   ->  Bitmap Heap Scan on orders
         Heap Blocks: exact=863
         ->  Bitmap Index Scan on idx_orders_created_at
               Index Cond: ((created_at >= ...) AND (created_at < ...))
               Buffers: shared hit=3 read=32
 Execution Time: 12.331 ms   -- 原 152.884 ms，提升 12.4×

如果业务上确实需要保留函数写法（如 lower(email) 查询），就用表达式索引把函数「预计算」进索引：

sql
-- 表达式索引：让函数写法也能走索引
CREATE INDEX idx_orders_orderno_lower ON orders (lower(order_no));

-- 查询必须写得与索引表达式完全一致
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id FROM orders WHERE lower(order_no) = 'no0000000001';

隐式类型转换导致索引失效 ​

当字面量类型与列类型不匹配时，PostgreSQL 会插入隐式转换。若转换发生在列的一侧，索引就失效了：

sql
-- 假设新加一列 ext_id 为 bigint，并在其上建索引
ALTER TABLE orders ADD COLUMN ext_id BIGINT;
UPDATE orders SET ext_id = id + 100000000;
CREATE INDEX idx_orders_ext_id ON orders (ext_id);
ANALYZE orders;

-- 错误！字面量是 int4，列是 int8，列被隐式转换；索引可能仍可用（int4→int8 是加宽，安全）
-- 真正危险的是相反方向：
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT * FROM orders WHERE ext_id = 100000001;

需要重点防范的情形：

列类型字面量类型后果处理
varchar / text数字常见于字符串列传数字字面量加引号
bigint超大数值字面量被解析为 numeric列转 numeric，索引失效字面量加 ::bigint
timestamptz字符串依赖区域设置，可能全表扫显式加 ::timestamptz
datetimestamp需转换用 ::date 对齐
sql
-- 错误！order_no 是 text，传数字会导致 text 列被转换为 numeric
SELECT * FROM orders WHERE order_no = 1000000001;

-- 正确：字面量加引号，类型对齐
SELECT * FROM orders WHERE order_no = '1000000001';

-- 正确：大整数字面量显式转型，避免被当成 numeric
SELECT * FROM orders WHERE ext_id = 100000001::bigint;

诊断手法是看计划的 Filter 里是否出现 :: 转型：

text
-- 出现 (order_no)::numeric 即说明列被转换，索引失效
 Seq Scan on orders
   Filter: ((order_no)::numeric = '1000000001'::numeric)

OR 条件难以走索引 ​

WHERE a = 1 OR b = 2 要求优化器对两个条件分别取行再合并去重。当两列各有独立索引时，计划可能选 BitmapOr，但更常见的是直接 Seq Scan：

sql
-- 不推荐：OR 跨两列
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, channel, amount FROM orders
WHERE channel = '门店' OR status = 'paid';
text
 Seq Scan on orders
   Filter: ((channel = '门店'::text) OR (status = 'paid'::text))
   Rows Removed by Filter: 333380
   Buffers: shared hit=1 read=16666
 Execution Time: 168.221 ms

改写一：拆成 UNION ALL（注意两个分支都要加去重语义时才用 UNION）：

sql
-- 推荐：拆 UNION ALL
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, channel, amount FROM orders WHERE channel = '门店'
UNION ALL
SELECT id, channel, amount FROM orders WHERE status = 'paid' AND channel <> '门店';
text
 Append
   ->  Bitmap Heap Scan on orders  (channel = '门店')
         Heap Blocks: exact=8330
   ->  Bitmap Heap Scan on orders  ((status = 'paid') AND (channel <> '门店'))
         Heap Blocks: exact=7611
 Buffers: shared hit=4 read=15841
 Execution Time: 71.449 ms   -- 原 168.2 ms，提升 2.4×

拆 UNION ALL 时必须在第二个分支排除重叠条件（这里加了 channel <> '门店'），否则重复行会污染结果；如果业务允许重复或可用 UNION 去重，代价是额外的排序/哈希。

改写二：同列多个值用 IN，PostgreSQL 对同列 IN 会走一次索引扫描：

sql
-- 推荐：同列多值，一次索引扫描
SELECT id, status FROM orders WHERE status IN ('paid', 'shipped', 'done');

-- 等价但更差的写法
SELECT id, status FROM orders
WHERE status = 'paid' OR status = 'shipped' OR status = 'done';

LIKE 前置通配符无法使用 B-tree 索引 ​

B-tree 只能按前缀匹配。LIKE '张%' 可走索引，LIKE '%张' 或 LIKE '%张%' 只能全表扫：

sql
-- 可走索引：前缀匹配
CREATE INDEX idx_orders_remark ON orders (remark text_pattern_ops);

-- 无法走 B-tree 索引
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM orders WHERE remark LIKE '%备注-99%';
text
 Aggregate
   ->  Seq Scan on orders
         Filter: (remark ~~ '%备注-99%'::text)
         Rows Removed by Filter: 999999
         Buffers: shared hit=1 read=16666
 Execution Time: 141.887 ms

可选方案：

方案适用场景示例
pg_trgm 三元组索引任意位置的模糊匹配CREATE INDEX ... USING gin (col gin_trgm_ops)
text_pattern_ops仅前缀匹配（区分大小写）CREATE INDEX ... ON t (col text_pattern_ops)
全文检索 tsvector + GIN分词后的关键词检索to_tsvector('simple', remark)
反转存储 + 前缀匹配后缀匹配场景存 reverse(col)，查 reverse(x) || '%'
sql
-- 使用 pg_trgm 让 %关键字% 也能走索引
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_orders_remark_trgm ON orders USING gin (remark gin_trgm_ops);
ANALYZE orders;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM orders WHERE remark LIKE '%备注-99%';
text
 Aggregate
   ->  Bitmap Heap Scan on orders
         Recheck Cond: (remark ~~ '%备注-99%'::text)
         Rows Removed by Index Recheck: 258
         ->  Bitmap Index Scan on idx_orders_remark_trgm
               Index Cond: (remark ~~ '%备注-99%'::text)
               Buffers: shared hit=2 read=88
 Execution Time: 9.114 ms   -- 原 141.9 ms，提升 15.6×

!= / <> / NOT IN 的选择率问题 ​

比较运算符本身不阻止索引使用，但 != 的选择率天然很高（例：status != 'done' 命中 80% 的行）。此时回表随机 IO 的成本远超顺序读全表，优化器会主动选 Seq Scan——这是正确决策，不是 bug。

sql
-- 高选择率条件：Seq Scan 反而是最优的
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM orders WHERE status <> 'done';
text
 Aggregate
   ->  Seq Scan on orders
         Filter: (status <> 'done'::text)
         Rows Removed by Filter: 200000
         Buffers: shared hit=1 read=16666
 Execution Time: 61.114 ms

优化方向不是「强制走索引」，而是换一个等价的低选择率写法：

sql
-- 推荐：IN 列出一组小集合，走索引
SELECT count(*) FROM orders
WHERE status IN ('pending', 'paid', 'shipped', 'cancelled');

NOT IN 还有一个额外的坑：子查询含 NULL 时结果恒为空。语义安全的替代是 NOT EXISTS：

sql
-- 危险！若子查询返回 NULL，整个结果为空
SELECT id FROM orders
WHERE user_id NOT IN (SELECT user_id FROM blacklist);

-- 推荐：NOT EXISTS 语义明确，且计划通常是 Anti Join
SELECT o.id FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM blacklist b WHERE b.user_id = o.user_id);

DISTINCT vs GROUP BY ​

两者语义在单表投影去重时等价，计划通常都会选 HashAggregate 或 GroupAggregate。差异在于能否复用索引顺序：

sql
-- DISTINCT 写法
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT DISTINCT channel FROM orders;

-- GROUP BY 写法
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT channel FROM orders GROUP BY channel;
text
-- 有 (channel) 索引时，GROUP BY 可走 GroupAggregate 复用索引顺序
 GroupAggregate
   Group Key: channel
   ->  Index Only Scan using idx_orders_channel on orders
         Heap Fetches: 0
         Buffers: shared hit=1 read=12
 Execution Time: 3.882 ms

结论：能用 GROUP BY 就用 GROUP BY，因为它在有合适索引时可以完全避免排序与哈希；DISTINCT 在实践中多为 HashAggregate 全量哈希。

UNION vs UNION ALL ​

UNION 隐含「去重」，需要额外的排序或哈希；UNION ALL 只是简单拼接。当业务上不可能出现重复行（或允许重复）时，一律用 UNION ALL：

sql
-- 不推荐：多余的去重
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT user_id FROM orders WHERE status = 'paid'
UNION
SELECT user_id FROM orders WHERE status = 'shipped';
text
 HashAggregate
   Group Key: orders.user_id
   ->  Append
         ->  Bitmap Heap Scan on orders  (status = 'paid')
         ->  Bitmap Heap Scan on orders  (status = 'shipped')
 Buffers: shared hit=6 read=17611
 Execution Time: 132.884 ms
sql
-- 推荐：UNION ALL，省掉去重
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT user_id FROM orders WHERE status = 'paid'
UNION ALL
SELECT user_id FROM orders WHERE status = 'shipped';
text
 Append
   ->  Bitmap Heap Scan on orders  (status = 'paid')
   ->  Bitmap Heap Scan on orders  (status = 'shipped')
 Buffers: shared hit=6 read=17611
 Execution Time: 68.220 ms   -- 提升 1.9×

EXISTS vs IN vs JOIN ​

三者在很多场景下会被优化器改写成同一种计划（Semi Join），但写法会影响优化空间：

写法优化器改写能力适用场景
EXISTS (SELECT 1 FROM b WHERE b.k = a.k)改写为 Semi Join，遇到匹配即停止「只要存在」，尤其子表大时
IN (SELECT k FROM b)可改写为 Semi Join；子查询含 NULL 时语义敏感子表小、结果集小
JOIN保留完整连接语义，可能放大行数需要取子表字段或做聚合
sql
-- 推荐：EXISTS，语义明确、可提前终止
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT o.id FROM orders o
WHERE o.status = 'paid'
  AND EXISTS (
    SELECT 1 FROM order_items i
    WHERE i.order_id = o.id AND i.qty > 10
  );
text
 Nested Loop Semi Join
   ->  Bitmap Heap Scan on orders o  (status = 'paid')
   ->  Index Scan using idx_items_order_id on order_items i
         Index Cond: (order_id = o.id)
         Filter: (qty > 10)
 Execution Time: 41.220 ms

JOIN 版本在子表存在一对多关系时会产生重复行，必须额外 DISTINCT，反而更慢：

sql
-- 不推荐：JOIN 会放大行数，还要 DISTINCT 去重
SELECT DISTINCT o.id
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'paid' AND i.qty > 10;

LIMIT 与 ORDER BY 配合 ​

ORDER BY ... LIMIT n 若没有与排序一致的索引，必须先把全部行排序（可能落盘），再取前 n 行：

sql
-- 无索引时：全量排序后截断
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, created_at FROM orders ORDER BY created_at DESC LIMIT 20;
text
 Limit
   ->  Index Scan Backward using idx_orders_created_at on orders
         Buffers: shared hit=1 read=32
 Execution Time: 0.418 ms

上面能走索引是因为 created_at 有索引。若排序列没有索引：

sql
-- 排序列无索引：全量排序
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, created_at FROM orders ORDER BY amount DESC LIMIT 20;
text
 Limit
   ->  Sort
         Sort Key: amount DESC
         Sort Method: top-N heapsort  Memory: 27kB
         ->  Seq Scan on orders
               Buffers: shared hit=1 read=16666
 Execution Time: 131.776 ms

PostgreSQL 会选 top-N heapsort（只保留前 N 个候选），比全量排序省内存，但仍需读全表。若这类查询高频，建对应索引：

sql
-- 针对「金额 Top-N」建索引
CREATE INDEX idx_orders_amount_desc ON orders (amount DESC);
ANALYZE orders;

work_mem 对排序与哈希的影响 ​

排序、哈希聚合、哈希连接都受 work_mem 约束。不足时 PostgreSQL 会分批落盘（外部归并排序 / 多批次哈希），耗时可能翻数倍：

sql
-- 默认 work_mem 下的大排序
SET work_mem = '4MB';
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT user_id, count(*) FROM orders GROUP BY user_id ORDER BY count(*) DESC;
text
 Sort
   Sort Key: (count(*)) DESC
   Sort Method: external merge  Disk: 24784kB     -- 落盘了
   ->  HashAggregate
         Group Key: user_id
         Batches: 5  Memory Usage: 4225kB          -- 哈希也分批了
         Disk Usage: 1840kB
         ->  Seq Scan on orders
 Execution Time: 942.118 ms
sql
-- 调大 work_mem 后
SET work_mem = '256MB';
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT user_id, count(*) FROM orders GROUP BY user_id ORDER BY count(*) DESC;
text
 Sort
   Sort Method: quicksort  Memory: 5170kB        -- 内存内排序
   ->  HashAggregate
         Batches: 1  Memory Usage: 3233kB         -- 单批哈希
 Execution Time: 388.442 ms   -- 提升 2.4×

work_mem 是每个操作节点的内存上限，一条复杂查询可能同时有多个排序/哈希节点各占一份。建议按会话或按角色设置，而非全局调大：

sql
-- 会话级设置（推荐做法）
SET work_mem = '128MB';

-- 或按用户/角色设置默认值
ALTER ROLE report_user SET work_mem = '256MB';

实战示例 ​

步骤 1：综合改写——把 4 个反模式一次性修掉 ​

sql
-- 原始写法：SELECT * + 列套函数 + OR + 无索引排序
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT *
FROM orders
WHERE date(created_at) >= '2026-01-01'
  AND (channel = '门店' OR status = 'paid')
ORDER BY amount DESC
LIMIT 20;
text
 Limit
   ->  Sort
         Sort Key: amount DESC
         Sort Method: top-N heapsort  Memory: 27kB
         ->  Seq Scan on orders
               Filter: ((date(created_at) >= '2026-01-01'::date) AND ((channel = '门店'::text) OR (status = 'paid'::text)))
               Rows Removed by Filter: 999121
               Buffers: shared hit=1 read=16666
 Execution Time: 189.442 ms
sql
-- 改写后：显式列 + 区间条件 + UNION ALL + 索引支撑
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, order_no, status, channel, amount, created_at
FROM (
  SELECT id, order_no, status, channel, amount, created_at
  FROM orders
  WHERE created_at >= TIMESTAMPTZ '2026-01-01 00:00:00+08'
    AND channel = '门店'
  UNION ALL
  SELECT id, order_no, status, channel, amount, created_at
  FROM orders
  WHERE created_at >= TIMESTAMPTZ '2026-01-01 00:00:00+08'
    AND status = 'paid'
    AND channel <> '门店'
) t
ORDER BY amount DESC
LIMIT 20;
text
 Limit
   ->  Sort
         Sort Key: orders.amount DESC
         Sort Method: top-N heapsort  Memory: 27kB
         ->  Append
               ->  Bitmap Heap Scan on orders
                     Recheck Cond: ((created_at >= ...) AND (channel = '门店'))
                     ->  BitmapAnd
                           ->  Bitmap Index Scan on idx_orders_created_at
                           ->  Bitmap Index Scan on idx_orders_channel
               ->  Bitmap Heap Scan on orders
                     Recheck Cond: ((created_at >= ...) AND (status = 'paid'))
                     ->  BitmapAnd
                           ->  Bitmap Index Scan on idx_orders_created_at
                           ->  Bitmap Index Scan on idx_orders_status
         Buffers: shared hit=9 read=1820
 Execution Time: 47.118 ms   -- 原 189.4 ms，提升 4.0×
指标原始写法改写后变化
执行时间189.4 ms47.1 ms4.0× 提升
Buffers16667 页1829 页减少 89%
扫描方式Seq Scan + Filter 丢弃 99.9 万行BitmapAnd 索引定位—

步骤 2：用 EXCEPT 验证改写前后结果一致 ​

sql
-- 两种条件下的结果集必须完全一致，否则改写引入了语义变化
WITH old AS (
  SELECT id FROM orders
  WHERE date(created_at) >= '2026-01-01'
    AND (channel = '门店' OR status = 'paid')
),
new AS (
  SELECT id FROM orders
  WHERE created_at >= TIMESTAMPTZ '2026-01-01 00:00:00+08' AND channel = '门店'
  UNION
  SELECT id FROM orders
  WHERE created_at >= TIMESTAMPTZ '2026-01-01 00:00:00+08' AND status = 'paid'
)
SELECT '仅旧写法' AS 差异, * FROM (SELECT * FROM old EXCEPT SELECT * FROM new) x
UNION ALL
SELECT '仅新写法', * FROM (SELECT * FROM new EXCEPT SELECT * FROM old) y;
text
     差异     | id
--------------+----
(0 rows)      -- 双向差集为空，改写语义等价

步骤 3：逐列定位是哪一列在拖慢 ​

sql
-- 基线：只读一列
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM orders WHERE channel = '门店';

-- 逐步加列，观察 Buffers 是否跳变
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(amount) FROM orders WHERE channel = '门店';

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(remark) FROM orders WHERE channel = '门店';
text
-- 第 1 条：只走索引，无需回表
 Aggregate
   ->  Bitmap Index Scan on idx_orders_channel
         Buffers: shared hit=1 read=34
 Execution Time: 6.118 ms

-- 第 3 条：需要回表读 remark（不在索引里），Buffers 跳升
 Aggregate
   ->  Bitmap Heap Scan on orders
         Recheck Cond: (channel = '门店'::text)
         Heap Blocks: exact=8330
         Buffers: shared hit=1 read=8364
 Execution Time: 41.882 ms

Buffers 从 35 页跳到 8365 页，说明代价集中在这一列的回表。若业务上确实高频需要 remark 参与条件而非仅投影，考虑把它纳入覆盖索引或改用 Gin 索引。

步骤 4：确认 Seq Scan 是否为正确选择 ​

sql
-- 命中 60% 的行：Seq Scan 是对的
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM orders WHERE status <> 'done';
text
 Aggregate
   ->  Seq Scan on orders
         Filter: (status <> 'done'::text)
         Rows Removed by Filter: 200000
         Buffers: shared hit=1 read=16666
 Execution Time: 61.114 ms
sql
-- 强制关闭顺序扫描，看「走索引」是否真的更快
SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM orders WHERE status <> 'done';
text
 Aggregate
   ->  Bitmap Heap Scan on orders
         Recheck Cond: (status <> 'done'::text)
         Heap Blocks: exact=16653
         ->  Bitmap Index Scan on idx_orders_status
               Index Cond: (status <> 'done'::text)
         Buffers: shared hit=1 read=33319
 Execution Time: 148.221 ms   -- 比 Seq Scan 慢 2.4×
sql
-- 切勿忘记恢复
RESET enable_seqscan;

这组对照证明：高选择率条件下「强制走索引」是错的。enable_seqscan = off 只应作为诊断手段，绝不可用在生产。

步骤 5：LIMIT 走索引与不走索引的差距 ​

sql
-- 有索引：取前 20 行几乎零成本
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, created_at FROM orders ORDER BY created_at DESC LIMIT 20;
text
 Limit
   ->  Index Scan Backward using idx_orders_created_at on orders
         Buffers: shared hit=34
 Execution Time: 0.418 ms
sql
-- 无索引：读全表 + top-N 排序
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, created_at FROM orders ORDER BY amount DESC LIMIT 20;
text
 Limit
   ->  Sort
         Sort Method: top-N heapsort  Memory: 27kB
         ->  Seq Scan on orders
               Buffers: shared hit=1 read=16666
 Execution Time: 131.776 ms
sql
-- 补索引后
CREATE INDEX idx_orders_amount_desc ON orders (amount DESC);
ANALYZE orders;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, created_at FROM orders ORDER BY amount DESC LIMIT 20;
text
 Limit
   ->  Index Scan Backward using idx_orders_amount_desc on orders
         Buffers: shared hit=34
 Execution Time: 0.512 ms   -- 原 131.8 ms，提升 257×

这就是 ORDER BY ... LIMIT 有索引与无索引的差距量级：0.5 ms 对 131.8 ms。

注意事项 ​

  1. 不要用 SELECT *，尤其在宽表、高频查询、需要走仅索引扫描的场景：
    sql
    -- 不推荐
SELECT * FROM orders WHERE status = 'paid';
  2. 不要在 WHERE 的索引列上套函数，会直接让索引失效：
    sql
    -- 错误！
SELECT * FROM orders WHERE date(created_at) = '2026-01-01';
-- 正确
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02';
  3. 注意隐式类型转换，字面量类型要与列类型一致：
    sql
    -- 错误！order_no 是 text
SELECT * FROM orders WHERE order_no = 1000000001;
  4. != / <> / NOT IN 选择率高时不要强求索引，Seq Scan 可能是最优解。
  5. NOT IN 遇子查询返回 NULL 会导致结果恒空，改用 NOT EXISTS。
  6. UNION 会去重（有排序/哈希成本），无重复语义时用 UNION ALL：
    sql
    -- 不推荐
SELECT user_id FROM orders WHERE status = 'paid'
UNION
SELECT user_id FROM orders WHERE status = 'shipped';
  7. 拆 UNION ALL 替代 OR 时必须排除重叠条件，否则产生重复行：
    sql
    -- 错误！第二分支未排除 channel = '门店'，重复行会污染结果
SELECT id FROM orders WHERE channel = '门店'
UNION ALL
SELECT id FROM orders WHERE status = 'paid';
  8. WHERE 中不能使用聚合函数与 GROUPING()，必须放 HAVING。
  9. enable_seqscan = off 只是诊断开关，绝不可在生产启用；测完立刻 RESET。
  10. 调大 work_mem 前先确认真的落盘了，计划中出现 Sort Method: external merge Disk: ... 或 Batches > 1 才是证据。

最佳实践 ​

  1. 永远先测量再改写：用 EXPLAIN (ANALYZE, BUFFERS) 取基线，改完再测，对比 actual time 与 Buffers 两个数。
  2. 显式列出所需列，不要 SELECT *；高频查询配合覆盖索引 INCLUDE (...) 以实现 Index Only Scan。
  3. 把函数与运算移到常量一侧，让 WHERE 条件对索引「可匹配」（sargable）。
  4. 字面量类型与列类型严格对齐，必要时显式加 ::type；看到计划里出现 (col)::type 就说明列被转换了。
  5. 同列多值用 IN，跨列条件用 UNION ALL，避免 OR 导致的位图合并或全表扫描。
  6. 模糊匹配优先 pg_trgm + GIN，别用 LIKE '%x%' 硬扫全表。
  7. ORDER BY ... LIMIT 一定要有对应索引；排序列方向要与索引一致（ORDER BY amount DESC 配 (amount DESC)）。
  8. DISTINCT 能改 GROUP BY 就改，后者在有索引时可复用索引顺序、免排序。
  9. work_mem 按会话或角色设置，用 SET LOCAL / ALTER ROLE，避免全局调大引发内存风险。
  10. 每次改写后用 EXCEPT 做结果一致性回归，确保优化没有改变业务语义。

下一节

继续学习：避免全表扫描

参考链接 ​