本节目标：用真实数据量和
EXPLAIN (ANALYZE, BUFFERS)读懂 Seq Scan、Index Scan、Bitmap Index Scan 与 BitmapOr，为libraryd的模糊搜索和活动借阅补上正确的索引。
适用版本：JDK 25、PostgreSQL 17、PostgreSQL JDBC 42.7.14。
承接的问题
3.3 的仓储里已经有几类查询：按 ISBN 精确查书、按标题或作者模糊搜索、按 ISBN 查活动借阅、按会员查当前借阅。开发库只有几十行数据时，它们全都「很快」，加不加索引看不出区别；一旦图书到十万行，没有索引的查询会退化成全表扫描，响应时间随数据量线性增长。
索引不是越多越好。每个索引都会占空间、拖慢写入，而且优化器不一定用。判断一个索引该不该建，不能靠「这个列经常出现在 where 里」，要看查询形状和真实执行计划。这一节用 10 万本图书、10 万条借阅记录，逐步把 EXPLAIN 读成可行动的信息。
数据准备和测量都在同一个 postgres:17-alpine 容器里完成，端口 55433，shared_buffers = 128MB。下面的耗时是本机这一次运行的结果，不是跨环境基准；换成不同的机器、缓存状态和并发，数字会变，但计划形状和判断方法不变。
核心机制
EXPLAIN 的输出怎么读
EXPLAIN 显示优化器选出的计划，EXPLAIN ANALYZE 会真正执行并补充实际耗时和行数。只看 EXPLAIN 容易被估算误导，尤其是统计信息过期的时候；排查性能问题至少要用 ANALYZE，需要判断 I/O 时再加 BUFFERS。
一个计划节点通常长这样：
Seq Scan on books (cost=0.00..2485.00 rows=10 width=52)
(actual time=4.251..43.358 rows=10 loops=1)
Filter: ((title)::text ~~* '%quantum ledger%'::text)
Rows Removed by Filter: 99990
Buffers: shared hit=1235
几个字段的含义：
| 字段 | 读法 |
|---|---|
cost=起..止 | 优化器估算的相对成本，单位不是毫秒，只用于比较计划 |
rows | 估算返回行数；actual ... rows 是实际行数 |
actual time=首行..全部 | 返回第一行和返回全部行的累计毫秒 |
loops | 该节点被执行了多少次；嵌套循环里会被放大 |
Rows Removed by Filter | 扫描后又被过滤掉的行数，越大越说明索引没帮上忙 |
Buffers: shared hit | 命中的共享缓冲区块数；read 才是从磁盘读 |
计划是一棵树，缩进表示父子关系。读的时候先看最内层节点在做什么，再看上层怎样消费它的输出。Limit 是最外层时，不代表子节点只算了 20 行；它可能先扫描了 10 万行，再丢掉前面的部分。
下面是同一个搜索在加索引前后的对照。查询是「标题里包含 quantum ledger 的 10 本书」：
select isbn, title, author, published_year
from books
where title ilike '%quantum ledger%'
order by title, isbn
limit 20;
加索引前：
Limit (cost=2485.17..2485.19 rows=10 width=52) (actual time=43.397..43.399 rows=10 loops=1)
Buffers: shared hit=1235
-> Sort (cost=2485.17..2485.19 rows=10 width=52) (actual time=43.395..43.396 rows=10 loops=1)
Sort Key: title, isbn
Sort Method: quicksort Memory: 26kB
-> Seq Scan on books (cost=0.00..2485.00 rows=10 width=52)
(actual time=4.251..43.358 rows=10 loops=1)
Filter: ((title)::text ~~* '%quantum ledger%'::text)
Rows Removed by Filter: 99990
Buffers: shared hit=1235
Execution Time: 43.416 ms
Seq Scan 读了整张表，Rows Removed by Filter 是 99990，说明只有 10 行命中。shared hit=1235 表示这些页都在缓存里；如果数据更大、缓存装不下，read 会让耗时再上一个台阶。43 毫秒对单次查询似乎还能接受，但这是 10 万行；到 100 万行时，扫描和过滤的成本会同比增加。
B-tree、复合索引与部分索引
B-tree 是 PostgreSQL 的默认索引，适合等值、范围、前缀匹配和排序。它不适合 %keyword% 这种前置通配符，因为索引按完整值排序，无法从中间片段定位。
先看已经存在的部分索引。4.1 定义了：
create unique index ux_loans_active_isbn on loans (isbn) where returned_on is null;
create index ix_loans_member_active on loans (member_id, due_on) where returned_on is null;
按 ISBN 查活动借阅的计划：
Index Scan using ux_loans_active_isbn on loans
(cost=0.29..8.30 rows=1 width=45) (actual time=0.019..0.019 rows=1 loops=1)
Index Cond: ((isbn)::text = '9780000000010'::text)
Buffers: shared hit=3
Execution Time: 0.035 ms
只读了 3 个缓冲区块，和全表 10 万行没有关系。部分索引的谓词 returned_on is null 和查询里的条件一致，所以优化器可以直接用它。
按会员查活动借阅的计划稍有不同：
Sort (cost=41.85..41.88 rows=10 width=45) (actual time=0.150..0.150 rows=10 loops=1)
Sort Key: due_on, id
-> Bitmap Heap Scan on loans (cost=4.36..41.69 rows=10 width=45)
Recheck Cond: ((member_id)::text = 'm-0001'::text) AND (returned_on IS NULL)
Heap Blocks: exact=10
-> Bitmap Index Scan on ix_loans_member_active
(cost=0.00..4.36 rows=10 width=0) (actual time=0.092..0.092 rows=10 loops=1)
Index Cond: ((member_id)::text = 'm-0001'::text)
Execution Time: 0.184 ms
这里没有用普通 Index Scan，而是 Bitmap Index Scan 加 Bitmap Heap Scan。位图扫描先收集匹配行的位置，再按物理页顺序回表，适合命中行数中等、分散在多个页的情况。计划形状由优化器根据统计信息选择，不是我们能直接指定的。
复合索引 (member_id, due_on) 的顺序也有讲究。查询同时过滤 member_id 并按 due_on 排序，索引先按会员分组、组内按到期日有序，正好匹配。如果反过来写成 (due_on, member_id)，按会员过滤时就用不上最左前缀。
小表不一定要用索引。本机建了一张 50 行的演示表，主键查询仍然走 Seq Scan：
Seq Scan on demo_small (cost=0.00..1.62 rows=1 width=14)
(actual time=0.006..0.007 rows=1 loops=1)
Filter: (id = 42)
Rows Removed by Filter: 49
Execution Time: 0.020 ms
50 行只占一个页，顺序扫描一次就读完，走索引反而多一次查找。这是优化器的正确选择，不是「索引失效」。不要为了看到 Index Scan 在生产环境关掉 enable_seqscan。
GIN 与 pg_trgm：让 %keyword% 走索引
前置通配符的搜索需要另一种索引。pg_trgm 把文本拆成三个字符的片段（trigram），GIN 索引记录每个片段出现在哪些行。查询 %quantum% 时，先找包含 qua、uan、ant 等片段的行，再回表复核。
V2 迁移：
create extension if not exists pg_trgm;
create index ix_books_title_trgm on books using gin (title gin_trgm_ops);
create index ix_books_author_trgm on books using gin (author gin_trgm_ops);
gin_trgm_ops 是操作符类，告诉 GIN 索引怎样把文本拆成键。create extension 需要相应权限；容器里的 libraryd 用户是超级用户，托管数据库通常需要管理员预先允许 pg_trgm。
加索引后，同一个查询的计划变成：
Limit (cost=158.33..158.36 rows=10 width=52) (actual time=0.061..0.063 rows=10 loops=1)
Buffers: shared hit=35
-> Sort (cost=158.33..158.36 rows=10 width=52) (actual time=0.060..0.061 rows=10 loops=1)
-> Bitmap Heap Scan on books (cost=120.74..158.17 rows=10 width=52)
(actual time=0.032..0.049 rows=10 loops=1)
Recheck Cond: ((title)::text ~~* '%quantum ledger%'::text)
Heap Blocks: exact=10
-> Bitmap Index Scan on ix_books_title_trgm
(cost=0.00..120.74 rows=10 width=0) (actual time=0.024..0.024 rows=10 loops=1)
Index Cond: ((title)::text ~~* '%quantum ledger%'::text)
Execution Time: 0.089 ms
执行时间从 43.416 ms 降到 0.089 ms，缓冲区块从 1235 降到 35。Recheck Cond 是因为 trigram 索引可能返回误匹配（片段相同但完整字符串不同），PostgreSQL 必须回表再确认一次；这不是索引失效，而是 GIN 的正常工作方式。
索引有成本。100000 行、约 9.9 MB 的 books 表上，本机测得的索引大小是：
books 9880 kB
ix_books_title_trgm 3864 kB
ix_books_author_trgm 1760 kB
两个 GIN 索引约占表大小的一半。每次插入、更新标题或作者，都要维护这些索引；批量导入时这个成本会体现出来。所以只给真正需要模糊搜索的列建 GIN，不要给每个 varchar 都来一个。
OR 查询的陷阱
3.3 最初的搜索同时匹配三列：
select isbn, title, author, published_year
from books
where title ilike '%quantum ledger%'
or author ilike '%quantum ledger%'
or isbn ilike '%quantum ledger%'
order by title, isbn
limit 20;
建完 title 和 author 的 GIN 索引后，这个查询仍然走全表扫描：
Seq Scan on books (cost=0.00..2985.00 rows=30 width=52)
(actual time=12.827..119.359 rows=10 loops=1)
Filter: (((title)::text ~~* '%quantum ledger%'::text)
OR ((author)::text ~~* '%quantum ledger%'::text)
OR ((isbn)::text ~~* '%quantum ledger%'::text))
Rows Removed by Filter: 99990
Execution Time: 119.437 ms
原因在 isbn ilike 这一支：isbn 只有 B-tree 主键，前置通配符用不上；优化器无法只对两个有索引的分支做 BitmapOr，于是整条查询退回顺序扫描。一个没有索引的 OR 分支，会把另外两个索引一起废掉。
修复方式不是给 isbn 也加 GIN，而是重新审视查询语义：ISBN 是精确标识，应该走 /api/books/{isbn} 的等值查询，不需要模糊匹配。把搜索收敛到 title 和 author 两列后：
Bitmap Heap Scan on books (cost=237.30..309.97 rows=20 width=52)
(actual time=0.052..0.070 rows=10 loops=1)
Recheck Cond: (((title)::text ~~* '%quantum ledger%'::text)
OR ((author)::text ~~* '%quantum ledger%'::text))
-> BitmapOr (cost=237.30..237.30 rows=20 width=0)
-> Bitmap Index Scan on ix_books_title_trgm
-> Bitmap Index Scan on ix_books_author_trgm
Execution Time: 0.121 ms
BitmapOr 把两个 GIN 索引的结果合并，再统一回表。计划从 119.437 ms、1235 个缓冲区块，降到 0.121 ms、60 个缓冲区块。
这个例子说明：索引设计不是「给每个过滤列建索引」，而是让整条查询的形状可被索引满足。一个用不上的分支，足以让其他索引白建。
在 libraryd 里落地
V2 迁移文件放在：
libraryd-persistence/src/main/resources/db/migration/V2__book_search_index.sql
内容就是前面的三条语句。它排在 V1__init.sql 之后执行，只新增扩展和索引，不改表结构；已经应用过 V1 的环境可以直接升级。
仓储的搜索查询同步收敛为两列：
boolean search = keyword != null && !keyword.isBlank();
String sql = search
? "select isbn, title, author, published_year from books"
+ " where title ilike ? or author ilike ?"
+ " order by title, isbn limit ? offset ?"
: "select isbn, title, author, published_year from books"
+ " order by title, isbn limit ? offset ?";
count 查询必须使用同一套谓词，否则分页总数和实际列表会对不上，索引也无法复用：
String countSql = search
? "select count(*) from books where title ilike ? or author ilike ?"
: "select count(*) from books";
likePattern 负责转义用户输入里的 %、_ 和反斜杠。转义后的模式仍然以 % 开头，所以仍然需要 GIN；如果用户输入只有一个普通词，trigram 能有效缩小范围；如果输入太短（少于 3 个字符），trigram 退化为扫描较多行，这也是搜索接口通常要求至少 2 到 3 个字符的原因。
四类查询的最终计划可以放在一张表里对照（数据量：10 万本图书、10 万条借阅，PostgreSQL 17.11 容器，本机单次测量）：
| 查询 | 计划 | 执行时间 | 缓冲区块 |
|---|---|---|---|
| 标题模糊搜索，无索引 | Seq Scan + Sort | 43.416 ms | 1235 |
| 标题模糊搜索，GIN | Bitmap Index Scan + Heap | 0.089 ms | 35 |
| 搜索三列 OR，缺 isbn 索引 | Seq Scan | 119.437 ms | 1235 |
| 搜索两列 OR，两个 GIN | BitmapOr | 0.121 ms | 60 |
| 按 ISBN 查活动借阅 | Index Scan（部分唯一索引） | 0.035 ms | 3 |
| 按会员查活动借阅 | Bitmap Index Scan（部分复合索引） | 0.184 ms | 12 |
这些数字只说明计划形状和数量级，不是容量规划依据。真正的容量测试要在目标硬件、目标数据量和并发下做。
还有一个容易忽略的动作：批量导入后要 ANALYZE。自动清理和统计收集是后台任务，不会在插入完成的瞬间更新统计信息；没有统计信息，优化器可能低估或高估行数，选错计划。第 11 章的批处理会在大批量写入后显式执行 analyze。
常见错误与边界
「只看 EXPLAIN，不看 EXPLAIN ANALYZE。」 EXPLAIN 只有估算，统计信息过期时会给出错误的行数和成本。判断真实性能至少要看 actual time 和 Rows Removed by Filter；怀疑 I/O 时再加 BUFFERS。
「在列上套函数。」 where lower(title) like '%clean%' 会让普通 B-tree 和 GIN 都无法直接匹配，除非建表达式索引。要么把函数移到索引定义里，要么在写入时存一份规范化列。
「以为 B-tree 能处理前置通配符。」 text_pattern_ops 只对 like 'abc%' 这类前缀匹配有帮助；%abc 需要 trigram 或其他全文检索方案。索引类型和查询形状必须匹配。
「部分索引的谓词和查询不一致。」 索引写 where returned_on is null，查询却写 where returned_on is null or returned_on > '2026-01-01'，优化器无法证明查询只涉及索引覆盖的行，就不会使用它。查询谓词必须蕴含索引谓词。
「小表上也强求 Index Scan。」 几十行的表顺序扫描更快，优化器选择 Seq Scan 是合理的。用 enable_seqscan = off 强制索引只适合诊断，不适合生产配置。
「建完索引不维护统计信息。」 大批量写入、删除或更新后，统计信息可能严重偏离。ANALYZE 是免费且安全的操作，批处理和迁移之后都应该执行。
小结
EXPLAIN ANALYZE给实际行数和耗时，BUFFERS给缓存命中；只看估算容易误判。- B-tree 适合等值、范围、前缀和排序；前置通配符需要 trigram 或全文索引。
- 部分索引的谓词必须被查询条件蕴含，
returned_on is null在三处出现时含义要一致。 - 复合索引按最左前缀工作，
(member_id, due_on)同时服务过滤和排序。 - GIN +
pg_trgm能把%keyword%从全表扫描变成位图索引扫描，但索引占空间、拖慢写入。 - 一个没有索引的
OR分支会让整条查询退回 Seq Scan，先收敛查询语义，再补索引。 - 小表的
Seq Scan是正常选择；批量写入后要ANALYZE。
索引解决了单条查询的访问路径，下一节要处理另外三个容易在数据量上来后暴露的问题：深分页、批量写入和 N+1 查询。
阅读导航：上一节：4.1 表设计：约束、范式与 libraryd 的 schema · 下一节：4.3 分页、批量写与 N+1 。
继续阅读
探索更多技术文章
浏览归档，发现更多关于系统设计、工具链和工程实践的内容。