索引与 EXPLAIN

这一课你将能做到

1. 用 EXPLAIN / EXPLAIN ANALYZE 读懂 Seq Scan、Index Scan、Bitmap Index Scan
2. 为等值/范围条件建 B-tree，为 JSONB 包含条件建 GIN，并知道何时不该建
3. 对照 sql-index 的联合索引前缀原则，在 PG 里验证一条计划

为什么要学

没有索引，大表过滤会变成全表扫描；乱建索引，写入变慢、空间膨胀。PostgreSQL 用 EXPLAIN 把优化器的选择摊开给你看——猜不如测。

本站 MySQL 课讲过 B-tree 直觉；PG 额外要会选 GIN（JSONB/数组/全文）。官方手册 Indexes 与 Using EXPLAIN 是本课对标。

先建立图像

查询：WHERE slug = 'sql-essentials'
        │
        ▼
   优化器估算代价
        │
   ┌────┴────┐
   │         │
Seq Scan   Index Scan (B-tree on slug)
扫全表      先定位索引再回表

查询：WHERE metadata @> '{"featured":true}'
        │
        ▼
   GIN (metadata) → Bitmap Index Scan → 回表

索引类型	擅长	典型列
B-tree（默认）	=、<、>、BETWEEN、IN	slug、(category, sort_order)
GIN	包含、数组成员、全文	JSONB、TEXT[]、tsvector
Hash 等	特殊场景	入门可先不管

跟着做

步骤 1：准备可观测的表

沿用 app.cf_track（有 slug、category、sort_order、metadata）。数据太少时可多插几行练习（slug 唯一）。

SELECT COUNT(*) FROM app.cf_track;
ANALYZE app.cf_track;   -- 更新统计信息

步骤 2：无业务索引时看 slug 查询

若存在旧索引先记下名字；对比用：

-- 可能已有 UNIQUE 约束自带索引；用 EXPLAIN 观察即可
EXPLAIN
SELECT id, title FROM app.cf_track
WHERE slug = 'sql-essentials';

有唯一约束时，通常已是 Index Scan / Index Only Scan。这正好说明：主键/唯一约束会自动带索引。

步骤 3：联合索引与范围

CREATE INDEX IF NOT EXISTS idx_track_cat_sort
  ON app.cf_track (category, sort_order);

EXPLAIN ANALYZE
SELECT id, title FROM app.cf_track
WHERE category = 'language' AND sort_order < 100;

关注：

• 是否出现 Index Scan 或 Bitmap Index Scan using idx_track_cat_sort
• rows 估算与实际（ANALYZE 时）差距

前缀原则：条件只有 sort_order 而没有 category 时，该联合索引往往用不上——与 MySQL 类似。

步骤 4：GIN 服务 JSONB

CREATE INDEX IF NOT EXISTS idx_track_meta_gin
  ON app.cf_track USING GIN (metadata);

EXPLAIN
SELECT slug FROM app.cf_track
WHERE metadata @> '{"featured": true}'::jsonb;

再对比等值：

EXPLAIN
SELECT slug FROM app.cf_track WHERE slug = 'postgresql-essentials';

后者应走 B-tree（唯一索引），不要幻想一个 GIN 包打天下。

步骤 5：读计划关键词

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title FROM app.cf_track
WHERE category = 'language' AND sort_order < 100;

节点	含义
Seq Scan	顺序扫表
Index Scan	索引定位 + 回表
Bitmap Index Scan	先建位图再堆取
cost / rows	估算代价与行数
Buffers	实际读了多少页（开发库参考）

步骤 6：刻意「不建」的索引

在笔记写两条「不该单独建索引」的例子，例如：

• 单独的布尔 is_member（区分度极低）
• 几乎每次全表导出都不带 WHERE 的列

低基数列更常作为联合索引的后续列，而不是单列索引明星。

讲清楚要点

1. 因为 优化器基于统计信息估代价，所以 大变更后要 ANALYZE；生产还有 autovacuum 维护。
2. 约束带来的索引别重复建：PRIMARY KEY / UNIQUE 已有 B-tree。
3. 联合索引最左前缀：(category, sort_order) 利于 category=? 与 category=? AND sort_order<?。
4. GIN 不对等值字符串取代 B-tree：类型选错，计划不会奇迹出现。
5. 小表 Seq Scan 可以是正确选择：磁盘/缓存下全表更快时，不要为了「看起来有 Index」强行逼计划。
6. 写多读少慎索引：每个二级索引都增加插入/更新维护成本。

你可能会懵的地方

• Index Only Scan 是什么？
  需要的列都在索引里（且可见性图允许）时可少回表。入门知道「更省」即可。

• Bitmap 听起来像 MySQL？
  概念相近：多条件先位图交集再取行。细节实现不同，读计划时认名字。

• EXPLAIN 和 EXPLAIN ANALYZE 区别？
  后者真执行并标实际时间/行数；对写操作的 ANALYZE 会真的改数据——只对 SELECT 放心用，或放事务里回滚。

• 为何加了索引应用还是慢？
  可能没走索引、选错列、网络/应用层问题。先 EXPLAIN，再谈调优。

• 和 sql-essentials 重复吗？
  直觉复用；本课补 PG 节点名与 GIN。

错法对照

错法	问题	正解
给所有列建索引	写慢、空间涨	只为高频过滤/排序列建
用 B-tree 期望加速 @>	计划仍差	USING GIN
从不 ANALYZE 却抱怨计划傻	统计过期	变更后分析
看见 Seq Scan 就恐慌	小表正常	看代价与行数
复制 MySQL 的 FORCE INDEX 思维	PG 语法不同	先修正统计与索引设计

自测三问

1. (category, sort_order) 对「只有 sort_order < 10」的查询通常够不够？为什么？
2. EXPLAIN ANALYZE 对 UPDATE 语句有什么风险？
3. 为 JSONB 包含查询与为 slug= 查询各应倾向哪种索引？

小结一句话

B-tree 管等值与范围，GIN 管 JSONB/数组；用 EXPLAIN 验证，而不是凭感觉堆索引。

官方延伸

• PostgreSQL：Using EXPLAIN
• PostgreSQL：Indexes