PHP小码农

Appearance

扫描类型（Scan Types） ​

执行计划最底层的节点永远是「扫描节点」——它们负责从表、索引、函数或常量列表中取出原始行。上层所有的连接、排序、聚合都建立在扫描节点的输出之上，因此读懂扫描类型，是优化 SQL 的第一步。

一条慢 SQL 的根因，八成可以归结为「该走索引却全表扫」或「走了索引却回表百万次」。本节系统梳理 PostgreSQL 的全部扫描节点，并给出「选择率与计划选择」的经验法则。

前置知识

阅读本节前，建议先了解：EXPLAIN 输出解读（成本 / 行数 / 时间）

基础概念 ​

先建立术语表。PostgreSQL 的扫描节点在计划输出里以固定英文名出现，中文社区译法不一，下表给出对照与判定特征。

扫描节点中文访问对象关键特征
Seq Scan顺序扫描表从头到尾读所有数据块
Index Scan索引扫描索引 + 表先查索引定位，再回表取行
Index Only Scan仅索引扫描索引所需列全在索引里，可能回表
Bitmap Index Scan位图索引扫描索引只产出位图，不出行（width=0）
Bitmap Heap Scan位图堆扫描表按物理顺序读表，带 Recheck Cond
Tid Scan元组 ID 扫描表按 ctid 直接定位
Subquery Scan子查询扫描子计划外层对子查询结果再包一层
Function Scan函数扫描函数FROM 里出现返回集合的函数
Values Scan值列表扫描常量VALUES (...) 作为数据源
CTE ScanCTE 扫描CTE读取 WITH 定义的临时结果集
WorkTable Scan工作表扫描递归 CTE递归 CTE 的自引用工作区
Sample Scan采样扫描表TABLESAMPLE 触发的抽样
Foreign Scan外部表扫描FDW访问外部数据源
Custom Scan自定义扫描扩展扩展注册的扫描方法

扫描节点的「成本结构」由三部分组成，理解它才能理解为什么规划器会在两种扫描之间摇摆：

text
总成本 ≈ 读块成本 + 处理行成本 + 运算成本
        │            │             │
        │            │             └─ rows * cpu_operator_cost  (每个条件判断)
        │            └─ rows * cpu_tuple_cost   (每行处理)
        └─ pages * seq_page_cost 或 pages * random_page_cost

一个典型的计划树，扫描节点位于树叶位置：

text
                     Sort
                       │
                   Hash Join
                  ┌────┴─────┐
                  │          │
      Seq Scan on orders    Hash
                            │
                     Bitmap Heap Scan
                            │
                     Bitmap Index Scan   ← 最底层，只产生位图

语法与用法 ​

扫描类型不是由 SQL 语法直接指定的，而是规划器根据代价模型自动选择。要做对比实验，需要知道「如何观察选择率」与「如何临时干预计划」。

查看某列的选择率估算（规划器决策的原料）：

sql
-- 查看单列统计信息：n_distinct、直方图、最常见值
SELECT attname, n_distinct, null_frac, correlation,
       most_common_vals, most_common_freqs, histogram_bounds
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'orders' AND attname = 'status';

估算「某个条件命中多少行」（用 EXPLAIN 的 rows= 反推）：

sql
EXPLAIN
SELECT * FROM orders WHERE status = 'paid';

临时禁用某种扫描以观察替代计划的代价（仅用于诊断）：

sql
BEGIN;
SET LOCAL enable_seqscan = off;   -- 强迫规划器优先考虑索引
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'paid';
ROLLBACK;

要构造一个「位图扫描」而非「索引扫描」的计划，通常需要把返回比例控制在中低区间：

sql
-- 返回约 5%~10% 的行时，位图扫描往往胜出
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE amount BETWEEN 100 AND 300;
text
 Bitmap Heap Scan on orders  (cost=123.45..1200.67 rows=4200 width=64) (actual time=1.2..8.4 rows=4188 loops=1)
   Recheck Cond: ((amount >= 100) AND (amount <= 300))
   Heap Blocks: exact=1050
   ->  Bitmap Index Scan on idx_orders_amount  (cost=0.00..122.40 rows=4200 width=0) (actual time=0.9..0.9 rows=4188 loops=1)
         Index Cond: ((amount >= 100) AND (amount <= 300))

Bitmap Index Scan 的 width=0 是识别它最直接的标志：它不产出真正的行，只产出「哪些块需要读」的位图。

详细说明 ​

Seq Scan：顺序扫描 ​

Seq Scan 从表的第一个数据块读到最后一个，逐行判断过滤条件。它的成本与表大小线性相关，与条件的严格程度无关——即使只要 1 行，只要规划器选了 Seq Scan，就必然读完整张表（除非有 LIMIT 提前中止）。

text
 Seq Scan on orders  (cost=0.00..2450.00 rows=100000 width=64)
   Filter: (status = 'paid'::text)
   Rows Removed by Filter: 98765

Rows Removed by Filter 高达 98765 说明：扫了 100000 行，只有 1235 行最终有用。这就是「全表扫 + 高丢弃率」的典型信号，应当检查 status 上是否有索引。

Seq Scan 合适的场景：

  • 表很小：几页到几十页的表，走索引的额外开销（读索引页 + 回表随机读）反而更贵。
  • 需要返回大比例的行：比如返回 60% 的行，顺序读远比「索引定位 + 大量随机回表」便宜。
  • 没有可用索引：条件列无索引，或条件形式无法被索引使用。
  • 需要全表聚合：如 SELECT count(*) FROM t 且无更优路径。

Index Scan：索引扫描 ​

Index Scan 分两步：先在索引 B 树里定位符合条件的条目，拿到 ctid（行在堆表中的物理位置），再逐条回表读取完整的行。因为索引条目按索引键顺序排列，回表时的物理顺序是随机的，所以成本模型使用 random_page_cost（默认 4.0）而非 seq_page_cost（默认 1.0）。

text
 Index Scan using orders_pkey on orders  (cost=0.42..8.44 rows=1 width=64)
   Index Cond: (id = 10086)

Index Cond 表示这个条件被下推到了索引层，能显著缩小索引扫描范围。与之相对的 Filter 只能逐行事后筛选。

Index Scan 的天然优势是有序输出：索引本身就是有序的，因此下面的查询不需要额外的 Sort 节点。

sql
EXPLAIN
SELECT id, created_at FROM orders ORDER BY id LIMIT 10;
text
 Limit  (cost=0.42..1.02 rows=10 width=16)
   ->  Index Scan using orders_pkey on orders  (cost=0.42..6000.00 rows=100000 width=16)

Index Only Scan：仅索引扫描 ​

如果查询需要的所有列都在索引里，PostgreSQL 就可以只读索引、完全不回表，这就是 Index Only Scan（覆盖索引扫描）。它需要满足两个条件：

  1. 索引包含 SELECT 列表与 WHERE 用到的全部列（普通索引可用 INCLUDE 子句扩展非键列）。
  2. 对应数据块在可见性映射表（Visibility Map, VM）中被标记为全可见（all-visible）。

第 2 条是关键：PostgreSQL 的索引不存储行的可见性（由 MVCC 决定），因此即使索引里有值，也必须确认该版本对当前事务可见。若 VM 未标记，就要回表检查——这个次数就显示为 Heap Fetches。

text
 Index Only Scan using idx_orders_status_amount on orders  (cost=0.42..1520.00 rows=42000 width=12)
   Index Cond: (status = 'paid'::text)
   Heap Fetches: 0

Heap Fetches: 0 是理想状态，说明 VM 全可见，真正的「零回表」。如果这个数字接近输出行数，说明表刚写入大量数据还没 VACUUM，此时 Index Only Scan 的性能优势会大打折扣。

sql
-- 查看表的 VM 可见比例
SELECT relname, relpages,
       n_dead_tup,
       last_vacuum, last_autovacuum
FROM pg_stat_user_tables WHERE relname = 'orders';

Bitmap Index Scan 与 Bitmap Heap Scan ​

这是一对必须成对出现的节点。执行流程：

  1. Bitmap Index Scan 扫一个或多个索引，不返回行，只构建一个位图，标记「哪些堆块包含匹配行」。
  2. Bitmap Heap Scan 拿到位图后，按物理块号顺序读取堆表，从而把随机 I/O 转化为近似顺序 I/O。

因为位图扫描按物理顺序读，回表成本接近 seq_page_cost 而非 random_page_cost，所以它在中低选择率（经验上约 5%~40%）时特别有优势。

text
 Bitmap Heap Scan on orders  (cost=45.20..880.10 rows=3200 width=64)
   Recheck Cond: (status = 'paid'::text)
   Filter: (amount > 100)
   Rows Removed by Filter: 400
   Heap Blocks: exact=180
   ->  Bitmap Index Scan on idx_orders_status  (cost=0.00..44.40 rows=3600 width=0)
         Index Cond: (status = 'paid'::text)

注意 Recheck Cond：位图是有损的（因为它只记录块号，不记录精确的元组位置），所以回表后必须重新校验原条件。位图还可能退化为「页级」精度，此时 Heap Blocks: exact 会变成 Heap Blocks: lossy，回表量激增。

多个索引的布尔组合会引入 BitmapAnd 与 BitmapOr 节点：

text
 Bitmap Heap Scan on tenk1  (cost=25.07..60.11 rows=10 width=244)
   Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
   ->  BitmapAnd  (cost=25.07..25.07 rows=10 width=0)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
               Index Cond: (unique1 < 100)
         ->  Bitmap Index Scan on tenk1_unique2  (cost=0.00..19.78 rows=999 width=0)
               Index Cond: (unique2 > 9000)

Tid Scan：按物理位置定位 ​

Tid Scan 直接用 ctid（块号 + 元组槽位）定位行，跳过索引查找：

sql
EXPLAIN SELECT * FROM orders WHERE ctid = '(42,7)';
text
 Tid Scan on orders  (cost=0.00..4.01 rows=1 width=64)
   TID Cond: (ctid = '(42,7)'::tid)

它的典型用途是批量按 ctid 删除重复行。注意 ctid 会随 VACUUM FULL、UPDATE 而改变，不能长期保存。

Subquery Scan、Function Scan 与 Values Scan ​

Subquery Scan 出现在 FROM 里有子查询且规划器未能内联它时：

sql
EXPLAIN
SELECT * FROM (SELECT id, amount FROM orders WHERE amount > 500 OFFSET 0) sub;
text
 Subquery Scan on sub  (cost=0.00..2450.00 rows=5000 width=12)
   ->  Seq Scan on orders  (cost=0.00..2450.00 rows=5000 width=12)
         Filter: (amount > 500::numeric)

OFFSET 0 常被用来阻止子查询被内联（在 PG12 之前是一个常见技巧）。看到 Subquery Scan 就应追问：这层包装是否必要？

Function Scan 出现在 FROM 里有返回集合的函数时：

sql
EXPLAIN SELECT * FROM generate_series(1, 3) AS g(n);
text
 Function Scan on generate_series g  (cost=0.00..0.03 rows=3 width=4)

Values Scan 出现在 VALUES 作为数据源时，常用于「常量驱动的批量更新」：

sql
EXPLAIN
SELECT * FROM (VALUES (1, 'a'), (2, 'b'), (3, 'c')) AS v(id, name);
text
 Values Scan on "*VALUES*"  (cost=0.00..0.04 rows=3 width=36)

CTE Scan 与 WorkTable Scan：WITH 的两种形态 ​

CTE Scan 读取 WITH 子句定义的结果集。PostgreSQL 12 起，非递归 CTE 默认可以被内联，因此不再必然出现 CTE Scan：

sql
-- PG12+：此 CTE 会被内联，计划里不会出现 CTE Scan
EXPLAIN
WITH paid AS (SELECT * FROM orders WHERE status = 'paid')
SELECT count(*) FROM paid;

若显式要求物化，则会出现 CTE Scan：

sql
EXPLAIN
WITH paid AS MATERIALIZED (SELECT * FROM orders WHERE status = 'paid')
SELECT count(*) FROM paid;
text
 Aggregate  (cost=2450.00..2450.01 rows=1 width=8)
   CTE paid
     ->  Seq Scan on orders  (cost=0.00..2450.00 rows=10000 width=64)
           Filter: (status = 'paid'::text)
   ->  CTE Scan on paid  (cost=0.00..200.00 rows=10000 width=0)

WorkTable Scan 只出现在递归 CTE 中，代表对自引用工作区（working table）的读取：

sql
EXPLAIN
WITH RECURSIVE t(n) AS (
  VALUES (1)
  UNION ALL
  SELECT n + 1 FROM t WHERE n < 10
)
SELECT sum(n) FROM t;
text
 Aggregate  (cost=2.95..2.96 rows=1 width=8)
   CTE t
     ->  Recursive Union  (cost=0.00..2.51 rows=21 width=4)
           ->  Result  (cost=0.00..0.01 rows=1 width=4)
           ->  WorkTable Scan on t  (cost=0.00..0.20 rows=10 width=4)
                 Filter: (n < 10)
   ->  CTE Scan on t  (cost=0.00..0.45 rows=21 width=4)

选择率与计划选择：经验法则 ​

「选择率（selectivity）」是 匹配行数 / 总行数。规划器并不直接比较「选择率阈值」，而是比较两种计划的估算代价。但为了人脑快速判断，可以用下面的经验区间：

返回行占比通常倾向原因
< 1%Index Scan回表次数少，随机 I/O 可忽略
1% ~ 5%Index Scan 或 Bitmap Heap Scan取决于索引顺序与相关性
5% ~ 40%Bitmap Heap Scan位图把随机 I/O 变为准顺序 I/O
> 40%Seq Scan读索引页 + 回表的成本超过全表扫

这个「40%」不是硬编码，而是由代价参数推导出来的：当选择率 s 满足 s * random_page_cost > seq_page_cost 时，随机访问的成本就超过了顺序访问。代入默认值 random_page_cost=4.0，s > 0.25 就开始不利；再计入 cpu_index_tuple_cost 后，转折点通常在 25%~40% 之间浮动。

相关性（correlation）会显著移动这个转折点：

sql
SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'created_at';

correlation 取值 -1 ~ 1：

  • 接近 ±1：列值与物理存储顺序高度一致，回表近似顺序 I/O，索引扫描的性价比大幅提升。
  • 接近 0：列值与物理顺序无关，回表是纯随机 I/O，索引扫描代价高。

这也解释了为什么「按自增主键查询」比「按随机 UUID 查询」快得多。

为什么索引没被使用 ​

这是最常被问到的问题。按排查优先级排列：

1. 类型不匹配导致隐式转换

sql
-- 不推荐：phone 是 varchar，但传的是整数，可能触发类型转换
SELECT * FROM users WHERE phone = 13800138000;

-- 推荐：类型严格对齐
SELECT * FROM users WHERE phone = '13800138000';

2. 索引列被函数或表达式包裹

sql
-- 错误！索引列被函数包裹，索引失效
SELECT * FROM orders WHERE date(created_at) = DATE '2026-01-01';

-- 推荐：改写为范围条件（可走索引）
SELECT * FROM orders
WHERE created_at >= DATE '2026-01-01' AND created_at < DATE '2026-01-02';

-- 或者建立表达式索引
CREATE INDEX idx_orders_created_date ON orders ((created_at::date));

3. 统计信息过期

sql
-- 大量写入/删除后手动更新统计
ANALYZE orders;

4. 选择率确实过高

如果条件命中了 80% 的行，Seq Scan 是正确的选择，强行改计划只会更慢。

5. enable_* 参数被关闭

sql
SHOW enable_indexscan;   -- 应为 on

6. 排序规则（collation）不一致

索引按某种排序规则建立，查询用了另一种规则，索引无法用于排序或范围比较：

sql
-- 索引默认排序规则与下面查询要求的不一致，可能导致索引失效
SELECT * FROM users WHERE name LIKE 'A%';

7. LIKE 模式的前缀问题

sql
-- 可能走索引（前缀固定）
SELECT * FROM users WHERE name LIKE 'Ali%';

-- 错误！左模糊无法使用 B-Tree 索引
SELECT * FROM users WHERE name LIKE '%li%';

即使前缀固定，B-Tree 索引还需要排序规则支持前缀比较。默认的 libc 排序规则在部分区域设置下无法使用 LIKE 'x%' 优化，需要 text_pattern_ops 操作符类或 C（POSIX）排序规则：

sql
-- 为前缀搜索建立专用索引
CREATE INDEX idx_users_name_pattern ON users (name text_pattern_ops);

enable_* 开关的正确用法 ​

enable_seqscan、enable_indexscan、enable_bitmapscan、enable_tidscan、enable_nestloop、enable_hashjoin、enable_mergejoin 等参数，只应作为诊断手段。它们的语义是「给该计划类型加上一个巨大的惩罚代价」，而不是真正禁用——当没有其他选择时，规划器仍会选它。

sql
-- 诊断：当前计划是否比索引计划更慢？
BEGIN;
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;          -- 默认计划
SET LOCAL enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;          -- 强迫索引
ROLLBACK;

绝对不要在应用里用 SET enable_seqscan = off 来「解决问题」。正确的做法是：修正统计信息、增加合适索引、或调整代价参数。

random_page_cost 对 SSD 的影响 ​

默认 random_page_cost = 4.0 是面向机械硬盘的假设（随机读约为顺序读的 4 倍）。SSD 与 NVMe 的随机读延迟极低，通常建议下调到 1.1 ~ 2.0：

ini
# postgresql.conf（SSD / 全闪存环境）
random_page_cost = 1.1
effective_cache_size = 24GB

下调后规划器会更倾向索引扫描。若数据库完全可缓存于内存，甚至可以令 random_page_cost = seq_page_cost = 1.0。注意：这类参数是全局平均值假设，改动前应结合整体负载评估，避免「修好一条 SQL、拖慢一批 SQL」。

sql
-- 查看当前代价参数
SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN ('seq_page_cost', 'random_page_cost', 'cpu_tuple_cost',
               'cpu_index_tuple_cost', 'cpu_operator_cost', 'effective_cache_size');

实战示例 ​

场景一：从 Seq Scan 到 Index Only Scan 的完整改造 ​

先建立带索引的测试表：

sql
CREATE TABLE orders (
    id         bigserial PRIMARY KEY,
    customer_id bigint NOT NULL,
    status     text NOT NULL,
    amount     numeric(12,2) NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO orders (customer_id, status, amount, created_at)
SELECT (random() * 100000)::bigint,
       (ARRAY['paid','pending','cancelled','refunded'])[1 + (random()*3)::int],
       (random() * 1000)::numeric(12,2),
       now() - (random() * interval '365 days')
FROM generate_series(1, 500000);

ANALYZE orders;

改造前：统计某状态的订单总数。

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM orders WHERE status = 'paid';
text
 Aggregate  (cost=31000.00..31000.01 rows=1 width=8) (actual time=215.442..215.443 rows=1 loops=1)
   Buffers: shared hit=20833 read=1024
   ->  Seq Scan on orders  (cost=0.00..29875.00 rows=125000 width=0) (actual time=0.015..205.123 rows=124876 loops=1)
         Filter: (status = 'paid'::text)
         Rows Removed by Filter: 375124
         Buffers: shared hit=20833 read=1024
 Planning Time: 0.081 ms
 Execution Time: 215.510 ms

逐行解读：

  • Seq Scan 扫了 500000 行，Rows Removed by Filter: 375124 表示丢弃了 75% 的行，效率很低。
  • Buffers: shared hit=20833 read=1024 说明读了约 21857 个块，全部 I/O 都发生在表上。
  • Execution Time: 215.510 ms 是改造的基准。

第一次改造：建普通索引。

sql
CREATE INDEX idx_orders_status ON orders (status);
ANALYZE orders;
text
 Aggregate  (cost=7800.00..7800.01 rows=1 width=8) (actual time=48.221..48.222 rows=1 loops=1)
   Buffers: shared hit=13500 read=12
   ->  Bitmap Heap Scan on orders  (cost=1200.00..7500.00 rows=125000 width=0) (actual time=5.112..41.203 rows=124876 loops=1)
         Recheck Cond: (status = 'paid'::text)
         Heap Blocks: exact=10240
         Buffers: shared hit=13500 read=12
         ->  Bitmap Index Scan on idx_orders_status  (cost=0.00..1168.75 rows=125000 width=0) (actual time=3.921..3.921 rows=124876 loops=1)
               Index Cond: (status = 'paid'::text)
               Buffers: shared hit=1024 read=12

时间从 215ms 降到 48ms。规划器选了 Bitmap Heap Scan——因为要返回 25% 的行，位图扫描比纯索引扫描更划算。

第二次改造：既然只需要 count(*)，用覆盖索引彻底消除回表。

sql
DROP INDEX idx_orders_status;
-- 用 INCLUDE 把非键列带进索引，或者直接让索引「包含」所需信息
CREATE INDEX idx_orders_status_only ON orders (status);
VACUUM (ANALYZE) orders;   -- 让 VM 标记为全可见，Heap Fetches 才会是 0
text
 Aggregate  (cost=4400.00..4400.01 rows=1 width=8) (actual time=18.550..18.551 rows=1 loops=1)
   Buffers: shared hit=1245
   ->  Index Only Scan using idx_orders_status_only on orders  (cost=0.42..4088.00 rows=125000 width=0) (actual time=0.055..12.180 rows=124876 loops=1)
         Index Cond: (status = 'paid'::text)
         Heap Fetches: 0
         Buffers: shared hit=1245
 Planning Time: 0.101 ms
 Execution Time: 18.612 ms

Heap Fetches: 0 证明 VM 已全可见，索引扫描完全没有回表；Buffers 从 13512 降到 1245，减少了约 90% 的 I/O。总耗时 215ms → 48ms → 18ms。

场景二：定位「索引失效」的真实原因 ​

现象：WHERE created_at::date = '2026-01-01' 走了 Seq Scan。

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE created_at::date = DATE '2026-01-01';
text
 Seq Scan on orders  (cost=0.00..29875.00 rows=1370 width=45) (actual time=0.014..201.334 rows=1372 loops=1)
   Filter: ((created_at)::date = '2026-01-01'::date)
   Rows Removed by Filter: 498628
   Buffers: shared hit=20833 read=1024

Filter: ((created_at)::date = ...) 中的 (created_at)::date 就是元凶——函数包裹了索引列，索引无法参与定位。改写为范围条件：

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT * 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
 Index Scan using idx_orders_created_at on orders  (cost=0.42..58.91 rows=1370 width=45) (actual time=0.028..0.914 rows=1372 loops=1)
   Index Cond: ((created_at >= '2026-01-01 00:00:00+08'::timestamp with time zone) AND (created_at < '2026-01-02 00:00:00+08'::timestamp with time zone))
   Buffers: shared hit=44 read=6
 Planning Time: 0.136 ms
 Execution Time: 1.012 ms

Index Cond 中出现范围条件，Rows Removed by Filter 消失，耗时从 201ms 降到 1ms——200 倍。这是 SQL 改写中最立竿见影的一类。

注意事项 ​

  • Seq Scan 不等于「有问题」。 小表全扫、大比例返回、无索引可用的聚合，Seq Scan 都是正确选择，先看 Rows Removed by Filter 再下结论。

    sql
    -- 小表全扫完全正常，不要为此建索引
EXPLAIN SELECT * FROM dict_status;   -- 只有 4 行的字典表

  • Heap Fetches 高说明 Index Only Scan 名不副实。 若该值接近输出行数，实际代价与普通 Index Scan 无异，应排查 VACUUM 是否及时。

    sql
    -- 不推荐：只建了覆盖索引却从不 VACUUM，Heap Fetches 居高不下
CREATE INDEX idx_cover ON orders (status) INCLUDE (amount);
-- 正确做法：写入密集表依赖 autovacuum，必要时手动 VACUUM
VACUUM (ANALYZE) orders;

  • 左模糊 LIKE '%x' 无法使用 B-Tree 索引。 需要 pg_trgm + GIN 索引。

    sql
    -- 错误！B-Tree 对左模糊无能为力
SELECT * FROM users WHERE name LIKE '%zhang%';

-- 推荐：三元组索引
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING gin (name gin_trgm_ops);

  • 不要在索引列上做运算或函数调用。 这是「索引失效」第一大原因。

    sql
    -- 错误！
SELECT * FROM orders WHERE amount * 1.1 > 1000;
-- 推荐：把运算移到常量一侧
SELECT * FROM orders WHERE amount > 1000 / 1.1;

  • ctid 不稳定，不能作为长期标识。 行被 UPDATE 或被 VACUUM FULL 后 ctid 会变化。

    sql
    -- 不推荐：把 ctid 存进应用表当主键
UPDATE app_table SET row_ref = ctid WHERE id = 1;

  • OFFSET 0 阻止子查询内联，多数情况下有害。 除非确实需要物化子查询，否则不要保留这个历史包袱。

    sql
    -- 不推荐：无意义的 Subquery Scan
SELECT * FROM (SELECT id FROM orders OFFSET 0) s;

  • enable_* 参数不可用于生产。 它会给计划类型加惩罚代价，可能让规划器选出更差的替代计划，且掩盖真实问题。

    sql
    -- 错误！永久关闭顺序扫描会导致灾难性计划
ALTER DATABASE mydb SET enable_seqscan = off;

  • 位图会「有损（lossy）」。 当匹配块数超过 work_mem 允许的位图容量，位图降级为页级精度，回表量激增，Heap Blocks: exact 变为 lossy。

    text
    -- 出现 lossy 时，考虑提高 work_mem 或改写查询缩小范围
Heap Blocks: lossy=4096
Rows Removed by Index Recheck: 152340

  • random_page_cost 不能盲目下调。 只在确认存储为 SSD 且缓存充足时下调，否则可能让规划器为小结果集选择高启动成本的计划。

最佳实践 ​

  • 先看 Rows Removed by Filter，再决定是否加索引。 这个数字大且返回行少，就是索引的最佳候选。

    sql
    EXPLAIN (ANALYZE) SELECT * FROM orders WHERE status = 'paid';
-- 观察 Rows Removed by Filter 与 rows 的比例

  • 为高频等值查询建单列索引，为组合条件建多列索引。 多列索引的列顺序遵循「等值列在前、范围列在后」。

    sql
    -- 等值 + 范围条件，等值列（status）放前面
CREATE INDEX idx_orders_status_created ON orders (status, created_at);

  • 让覆盖索引服务「只读少数列」的高频查询。 用 INCLUDE 加入返回列，避免回表。

    sql
    CREATE INDEX idx_orders_status_cover ON orders (status) INCLUDE (amount, created_at);

  • 保持统计信息新鲜。 大表在批量导入后立即 ANALYZE，必要时提高目标列的统计精度。

    sql
    ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

  • 用范围条件替代「列上的函数比较」。 时间、数值区间尤其适用。

    sql
    -- 推荐写法
WHERE created_at >= DATE '2026-01-01' AND created_at < DATE '2026-02-01'

  • 前缀模糊搜索用 text_pattern_ops 或 C 排序规则索引。 不要指望默认排序规则能优化 LIKE 'x%'。

    sql
    CREATE INDEX idx_users_code_prefix ON users (code text_pattern_ops);

  • 左模糊/模糊搜索用 pg_trgm + GIN。 这是全文与模糊匹配的标准组合。

    sql
    CREATE INDEX idx_users_name_trgm ON users USING gin (name gin_trgm_ops);

  • SSD 环境下调 random_page_cost 到 1.1~2.0。 同时合理设置 effective_cache_size 为「shared_buffers + OS 缓存」的估计值。

    ini
    random_page_cost = 1.1
effective_cache_size = 24GB

  • 用 EXPLAIN (ANALYZE, BUFFERS) 做 A/B 对比，而不是凭感觉。 每次改动都要保留 Execution Time 与 Buffers 两个基准数据。

    sql
    -EXPLAIN (ANALYZE, BUFFERS) <改造前 SQL>;
+EXPLAIN (ANALYZE, BUFFERS) <改造后 SQL>;

  • 只读副本上验证计划时注意缓存差异。 副本的 shared_buffers 命中率、统计信息更新时机与主库不同，结论要交叉验证。

  • 优先「加对索引」，而不是「关掉某个扫描类型」。 索引是持久的解决方案，参数开关只是临时的诊断工具。

下一节

继续学习：连接算法（Join Algorithms）

参考链接 ​