KEEL · 龙骨 · A CURRICULUM FOR THE AI ERA

04 · 索引与执行计划 — keel 龙骨

前面三章讲的是「写进去的东西为什么正确」。这一章换个方向：读出来为什么快，或者为什么慢。

前面三章讲的是「写进去的东西为什么正确」。这一章换个方向：读出来为什么快，或者为什么慢。

PostgreSQL 的查询规划器不做魔法，它做的是算术——给每种可能的执行方式估一个代价，挑最便宜的。所以「为什么选了全表扫描而不是我刚建的索引」这个问题，答案永远是「在你的统计信息下，它算出来全表扫描更便宜」。要诊断计划，先要能把它的算术读出来。

现场：一条索引，两种命运

造一张 100 万行的表：

CREATE TABLE big(id int, tenant int, created timestamptz, payload text);
INSERT INTO big SELECT g, g % 10000, now() - (g || ' seconds')::interval, repeat('p',40)
FROM generate_series(1,1000000) g;

tenant 有 1 万个不同值，平均每个值 100 行——选择率 0.01%。建索引前：

===== 1) 没有索引时：顺序扫描 =====
 Gather  (cost=1000.00..17582.33 rows=100 width=57) (actual time=0.319..355.531 rows=100 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on big  (cost=0.00..16572.33 rows=42 width=57) (actual time=0.028..18.658 rows=33 loops=3)
         Filter: (tenant = 1234)
         Rows Removed by Filter: 333300
 Execution Time: 355.578 ms

Rows Removed by Filter: 333300 是重点：为了找 100 行，三个并行进程各扫了 33 万行然后扔掉。建索引后：

===== 2) 建 tenant 索引后：选择率高的查询改走索引 =====
 Bitmap Heap Scan on big  (cost=5.19..374.71 rows=99 width=57) (actual time=0.044..0.113 rows=100 loops=1)
   Recheck Cond: (tenant = 1234)
   Heap Blocks: exact=100
   Buffers: shared hit=100 read=3
   ->  Bitmap Index Scan on big_tenant_idx  (cost=0.00..5.17 rows=99 width=0) (actual time=0.036..0.036 rows=100 loops=1)
 Execution Time: 0.133 ms

355 ms → 0.133 ms。这里选的是 Bitmap Heap Scan 而不是 Index Scan：走索引拿到 100 个位置后，先攒成位图、再按物理顺序访问堆页，比「取一条位置就跳一次堆」少很多随机 IO。小结果集、多页命中时这是常见选择。

同一个索引，换个条件就不用了

现在把条件换成 tenant >= 0——它匹配全部 100 万行：

===== 3) 同一个索引，换个不选择的条件：它又回去顺序扫描 =====
 Seq Scan on big  (cost=0.00..23864.00 rows=1000000 width=57) (actual time=0.012..69.054 rows=1000000 loops=1)
 Execution Time: 90.016 ms

走索引意味着 100 万次索引条目读取 + 100 万次堆页访问，比顺序扫一遍贵得多。规划器算对了。

要看清它到底算出了什么差别，可以用 enable_seqscan=off 强制禁用顺序扫描（这只是一个调试开关，它给顺序扫描加一个巨大的惩罚代价，不会真的禁止）：

===== 4) 强制关掉顺序扫描，看索引计划到底贵在哪 =====
 Bitmap Heap Scan on big  (cost=11106.42..34970.43 rows=1000000 width=57) (actual time=22.729..78.642 rows=1000000 loops=1)
   Heap Blocks: exact=11364
   ->  Bitmap Index Scan on big_tenant_idx  (cost=0.00..10856.42 rows=1000000 width=0)
 Execution Time: 99.561 ms

估计代价 34970 vs 顺序扫描的 23864——规划器认为索引方案贵 46%。代价数字是模型算的，不是实测的，所以它可能算错；这台机器上实测 99.6 ms vs 90.0 ms，方向是一致的，但比例对不上。这就是为什么调优的最后一步永远是实测，而不是盯着 cost 念。

代价模型长什么样

默认参数（本机 pg_settings 实测）：

 seq_page_cost          | 1        -- 顺序读一个 8KB 页的相对代价
 random_page_cost       | 4        -- 随机读一个页，默认是顺序的 4 倍
 cpu_tuple_cost         | 0.01     -- 处理一行的代价
 cpu_index_tuple_cost   | 0.005    -- 处理一条索引条目的代价
 cpu_operator_cost      | 0.0025   -- 一次操作符/函数求值的代价
 effective_cache_size   | 524288   -- 8KB 页数，约 4GB，告诉规划器"大概有多少缓存可用"

粗略地说，一次顺序扫描的代价 ≈ seq_page_cost × 页数 + cpu_tuple_cost × 行数；索引扫描还要算索引页读取、条目处理，以及回表时的随机页访问。random_page_cost=4 是机械盘时代的默认值。在 SSD / 云盘上，随机读和顺序读的差距远小于 4 倍，这个值偏大，会让规划器过度偏好顺序扫描——很多「明明有索引却不用」的疑问，根子在这个参数。它和 effective_cache_size 一样，属于「按你的硬件和缓存情况校准」的一类参数，没有普适最优值。

index-only scan：Heap Fetches: 0 是怎么来的

如果一条查询需要的列都在索引里，本来可以完全不碰堆表。前提是索引知道这些行对当前快照是否可见——索引里没有 xmin/xmax。这个信息存在堆页的**可见性映射（visibility map）**里，只有 VACUUM 才会去置位。

刚建完索引还没 VACUUM：

===== 5) index-only scan：刚建索引还没 VACUUM，Heap Fetches 不为 0 =====
 Bitmap Heap Scan on big  (cost=5.19..374.71 rows=99 width=4) (actual time=0.033..0.156 rows=100 loops=1)
   ->  Bitmap Index Scan on big_tenant_idx  (cost=0.00..5.17 rows=99 width=0)
 Execution Time: 0.191 ms

注意它根本没选 index-only scan，还是老老实实回表。因为可见性映射是空的，走 index-only 就得对每一行回表确认可见性，规划器算下来不划算。VACUUM 之后：

===== 6) VACUUM 之后（可见性映射置位）再看同一条 =====
 Index Only Scan using big_tenant_idx on big  (cost=0.42..6.16 rows=99 width=4) (actual time=2.031..2.038 rows=100 loops=1)
   Index Cond: (tenant = 1234)
   Heap Fetches: 0
   Buffers: shared hit=4
 Execution Time: 2.071 ms

计划变了，而且多了 Heap Fetches: 0——这三个字就是「这些页在可见性映射里都被标成 all-visible，一行都不用回表」。

这里要诚实说一句：这次实测里 index-only scan 的墙钟时间（2.071 ms）并不比之前的 bitmap scan（0.191 ms）短。两者都在毫秒级、只有 100 行、数据全在缓存里，这点差异是缓存和噪声，不能用来证明谁快。能确凿证明的是两件事：规划器的估计代价从 374 降到 6.16；以及 Heap Fetches: 0 意味着这次查询真的没访问堆。如果表大、查询频繁、堆页不在缓存里，不回表的收益才会显现出来。

反过来说：一张频繁更新的表，可见性映射很难保持置位，index-only scan 的 Heap Fetches 会一直很高，收益也就没了。指望它加速写多读少的表，是常见误判。

建了索引却用不上

两种最典型的情况，都在同一个索引上翻车。

===== 7) 建了索引却用不上：函数包裹列 =====
   ->  Parallel Seq Scan on big  (cost=0.00..18655.67 rows=2083 width=57) (actual time=0.046..28.970 rows=33 loops=3)
         Filter: ((tenant)::text = '1234'::text)
         Rows Removed by Filter: 333300
 Execution Time: 303.763 ms

===== 8) 建了索引却用不上：前导通配符 =====
 Seq Scan on big  (cost=0.00..23864.00 rows=1000000 width=57) (actual time=0.012..94.928 rows=1000000 loops=1)
         Filter: (payload ~~ '%pppp%'::text)
 Execution Time: 118.847 ms

第 7 条：索引建在 tenant 上，查询却写成 tenant::text = '1234'。索引的排序键是整数，条件里的表达式是 text，规划器匹配不上，只能全表过滤。正确写法是 tenant = 1234，或者建一个表达式索引 CREATE INDEX ... ON big ((tenant::text))。

第 8 条：LIKE '%pppp%' 前面带通配符，B-tree 无法定位起点。要做这类子串检索得换索引类型——pg_trgm 的 GIN/GiST 索引能支持 %...%。

诊断方法固定：看 EXPLAIN 里 Filter: 出现在 Seq Scan 上、而 Index Cond: 缺席，就是「条件没吃到索引」。Index Cond 和 Filter 是两个不同的位置，前者是索引层过滤，后者是拿到行之后的过滤——把这个区分记住，能省很多冤枉路。

索引类型各擅长什么

类型	擅长的条件	典型场景	限制
B-tree	等值、范围、`ORDER BY`、前缀 `LIKE 'abc%'`	绝大多数普通索引	不支持 `%abc`、不支持多值列整体检索
Hash	仅等值（`=`）	极少数等值热点	不支持范围、排序
GIN	「一个列里很多值」：数组、`jsonb`、`tsvector`、`pg_trgm`	全文检索、标签数组、JSON 包含查询	写放大明显，更新代价高
GiST	几何、范围类型、全文、KNN 最近邻	地理位置、区间重叠	有损索引，可能返回假阳性需回表确认
SP-GiST	非平衡结构：点、前缀	空间分区、电话前缀	适用面窄
BRIN	物理有序的超大表	按时间追加的日志表	只对物理有序数据有效，精度低

选型的第一问永远是「条件是什么形状」，而不是「哪个索引更快」。索引类型不匹配时，调任何参数都没用。

统计信息：规划器的算术依赖它

规划器的估算全部来自统计信息，统计过期，算出来的计划就可能是错的。ANALYZE 前后对比：

--- 还没 ANALYZE：pg_stats 里没有这行的统计 ---
 stat_rows_before_analyze
--------------------------
                        0
--- 没有统计时的估计 ---
 Bitmap Heap Scan on stat_t  (cost=20.17..959.75 rows=1000 width=8) (actual time=0.030..0.031 rows=0 loops=1)
--- ANALYZE 之后 ---
 attname | n_distinct | correlation
---------+------------+-------------
 a       |         -1 |           1
 b       |  -0.846655 | 0.001743166
--- 有统计后的估计 ---
 Index Scan using stat_t_b_idx on stat_t  (cost=0.42..8.44 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=1)

n_distinct 有两套表示：正数是「不同值的个数」，负数是「不同值占总行数的比例取负」。b 的 -0.846655 表示它大约有 84.7% 的不同值，接近唯一；a 的 -1 表示完全唯一。correlation 衡量列的物理顺序与逻辑顺序的相关程度，取值 -1..1，a 是 1（插入时就是递增的），b 是 0.0017（随机）。

基于这些统计，b = 5 的估计行数从「无从判断」的默认 1000 变成 1，计划也从 Bitmap Heap Scan 换成 Index Scan。

correlation 直接影响计划选择，这点最容易被忽略。同一批数据，一份按 a 物理有序，一份打乱：

--- a 列物理有序（correlation≈1）: 范围查询 ---
 Index Scan using stat_t_a_idx on stat_t  (cost=0.42..40.24 rows=991 width=8) (actual time=0.050..0.144 rows=1001 loops=1)

--- 造一张同样数据、但物理顺序打乱的表 ---
 Bitmap Heap Scan on stat_shuf  (cost=22.66..1477.20 rows=999 width=8) (actual time=0.260..1.016 rows=1001 loops=1)
   Heap Blocks: exact=731

同一个条件、同样 1001 行，有序表估代价 40，乱序表估代价 1477，计划也随之从 Index Scan 变成 Bitmap Heap Scan。原因很直白：有序时回表是顺序 IO，乱序时是 731 次随机 IO。

统计过期的影响更直接：

 planner_estimate | real_count | mod_since_analyze
------------------+------------+-------------------
           350000 |     450000 |            100000
--- 统计过期时对 a > 400000 的估计 ---
 Index Scan using stat_t_a_idx on stat_t  (cost=0.42..101.56 rows=1951 width=12)
--- ANALYZE 之后的估计 ---
                                    Index Scan using stat_t_a_idx on stat_t  (cost=0.42..2339.33 rows=50852 width=12)

实际有 5 万多行符合条件，过期统计估成 1951——差了 26 倍。规划器不是不会算，是喂给它的数据不对。

flowchart TD
    Q["一条 SQL 进来"] --> ST["读统计：pg_class.reltuples<br/>pg_statistic 的 n_distinct / correlation"]
    Q --> COND["把 WHERE 拆成可用的索引条件<br/>（函数包裹、类型转换会在这里断掉）"]
    COND --> CAND{"有哪些候选访问路径？"}
    CAND -->|"有匹配索引"| IDX["估索引代价：<br/>索引页 + 回表随机页（按 correlation 打折）"]
    CAND -->|"没有"| SEQ["估顺序扫描代价：<br/>页数 × seq_page_cost + 行数 × cpu_tuple_cost"]
    ST --> IDX
    ST --> SEQ
    IDX --> PICK{"哪个估代价低？"}
    SEQ --> PICK
    PICK -->|"顺序扫描低"| RUN1["Seq Scan / Parallel Seq Scan"]
    PICK -->|"索引低"| RUN2{"索引覆盖了查询的列<br/>且可见性映射可用？"}
    RUN2 -->|"是"| IOS["Index Only Scan，Heap Fetches=0"]
    RUN2 -->|"否，命中行少"| BIT["Bitmap Index Scan + Bitmap Heap Scan"]
    RUN2 -->|"否，命中行少且有序"| IS["Index Scan"]
    ST -.->|"统计过期或样本不足"| BAD["估错行数 → 挑错计划"]
    COND -.->|"列被函数/类型转换包住"| NOMATCH["匹配不到索引条件 → 只能全表过滤"]

    style Q fill:#e3f2fd,color:#0d3b66
    style ST fill:#ffe0b2,color:#8a4b00
    style COND fill:#e3f2fd,color:#0d3b66
    style CAND fill:#fff3e0,color:#8a4b00
    style IDX fill:#e8f5e9,color:#1b5e20
    style SEQ fill:#e8f5e9,color:#1b5e20
    style PICK fill:#fff3e0,color:#8a4b00
    style RUN1 fill:#f1f8e9,color:#33691e
    style RUN2 fill:#fff3e0,color:#8a4b00
    style IOS fill:#e8f5e9,color:#1b5e20
    style BIT fill:#e8f5e9,color:#1b5e20
    style IS fill:#e8f5e9,color:#1b5e20
    style BAD fill:#ffebee,color:#b71c1c
    style NOMATCH fill:#ffebee,color:#b71c1c

生产边界

教学表 100 万行、数据全在缓存。生产表可能是几亿行且只有一小部分在缓存，Buffers: shared read 和 hit 的比例比毫秒数更能说明问题——EXPLAIN (ANALYZE, BUFFERS) 里的 read 才是真的碰到磁盘。
enable_seqscan=off 只用于诊断。别在生产里把它当成配置项。
上线要盯的：pg_stat_user_tables.last_autoanalyze 距今多久、n_mod_since_analyze、以及 pg_stat_statements 里按总耗时排序的慢查询。只看单条 SQL 的 EXPLAIN 容易优化错地方。
调 random_page_cost / effective_cache_size 是有用的手段，但它们是全局或按表的估计，改了之后要用同一批 SQL 复测，别一次改一堆参数。

动手

造一张 10 万行表，分别用「条件命中选择率高的列」和「命中全表的列」跑 EXPLAIN (ANALYZE, BUFFERS)，把两次的 cost 与 actual time 抄下来对比。
对一张表建索引后不 VACUUM 跑一次覆盖查询，再 VACUUM 跑一次，记录 Heap Fetches 的变化。
把一条查询的列包上函数，跑 EXPLAIN，指出 Index Cond 消失、Filter 出现的位置；再把条件改成直连列，确认索引恢复可用。

可观察结果：给定一份 EXPLAIN ANALYZE 输出，你能指出「估计行数与实际行数差多少」「是估计错了还是条件没吃到索引」，并给出对应的下一步动作。

自测

为什么 tenant >= 0 这种条件即使有索引也不走索引？规划器用哪些量做的判断？
Index Cond 和 Filter 的区别是什么？哪一个说明索引真的被用上了？
Heap Fetches: 0 依赖哪个结构？为什么频繁更新的表很难拿到这个 0？
correlation 从 1 变成 0，为什么同样的查询会从 Index Scan 变成 Bitmap Heap Scan？
n_distinct 为负数时代表什么？统计过期 26 倍时，最可能的后果是计划选错还是结果算错？

↓ 下一步：05 章 · 锁与并发冲突 —— 计划管的是「一条 SQL 怎么跑」，锁管的是「两条并发 SQL 谁先跑」。

进入 keel 阅读