PostgreSQL 中文模糊搜索 LIKE '%向日葵%' 怎么加速：pg_trgm GIN 把 41 ms 降到 0.05 ms，但两个字的关键词和 LC_CTYPE=C 会让索引白建
在 PostgreSQL 17.11 测试库（自造 100 万行中英文商品名）上实测 LIKE '%关键词%'：无索引 Parallel Seq Scan 41–53 ms；pg_trgm GIN（43 MB，建 6.8–7.2 s）0.05–0.12 ms，中文 '%向日葵%' 0.02 ms，ILIKE 同样走索引；两个字的关键词（'%台灯%'）拆不出三元组，强制走 GIN 要 434–455 ms，比全表 91 ms 慢约 5 倍；LC_CTYPE=C 的库里 show_trgm('向日葵') 返回空，中文搜索全部退化。附 GiST 对比（141 MB、5.8 ms）、fastupdate 写入代价（10 万行批量 0.13 s / 1.2 s / 8.1–9.0 s）、一键复跑脚本、只读巡检 SQL 和 CSV。
PostgreSQL 17.11 实测（测试库、自造数据，2026-10-29）：100 万行商品名上 WHERE name LIKE '%sunflower%'，无索引 41–53 ms；建 GIN (name gin_trgm_ops) 后 0.05–0.12 ms，'%向日葵%' 0.02 ms。三个坑：① 关键词少于 3 个字符（如「台灯」）时 pg_trgm 拿不到可用三元组，优化器选全表 91 ms，强制走索引反而 434–455 ms；② 数据库 LC_CTYPE=C 时中文不生成三元组（show_trgm 返回 {}），中文模糊搜索等于没建索引；③ GIN 让 10 万行批量写入从 0.13 s 变成 1.2 s，关掉 fastupdate 更是 8.1–9.0 s。
适用对象
- pg_trgm GIN 对 3 个字符以上的关键词效果最好：'%sunflower%' 从 41–53 ms 到 0.05–0.12 ms，读 28 个缓冲块；ILIKE '%SunFlower%' 同样走索引（0.07–0.08 ms）。
- 中文能用，前提是库的 LC_CTYPE 不是 C：本机 C.UTF-8 库里 show_trgm('向日葵') 给出 4 个三元组，'%向日葵%' 0.02 ms；LC_CTYPE=C 的库里返回 {}，只能全表或全索引扫描。
- 两个字符的关键词是硬伤：LIKE '%台灯%' 的 Bitmap Index Scan 把 100 万行全部列为候选，再 Recheck 掉 916666 行，434–455 ms；优化器自己选的 Seq Scan 只要 91 ms。
- GIN 比 GiST 更适合这个场景：同一查询 GIN 0.05–0.12 ms / 43 MB，GiST 5.8–6.0 ms / 141–142 MB，GiST 建索引也更慢（9.7–10.4 s）。
- 写入代价要提前算：10 万行批量 INSERT，只有主键 0.13 s，加 GIN（默认 fastupdate=on）1.15–1.24 s，fastupdate=off 8.1–9.0 s；默认模式下新行先进 pending list（本次 119 页、1782 条）。
同一条 LIKE，在 6 种索引状态下的耗时
每条查询先 EXPLAIN (ANALYZE, BUFFERS) 跑一次热缓存，再记录后续几次的 Execution Time。数据全部在 shared buffers 里（shared hit，无 read），所以这里比的是 CPU 和扫描量，不是磁盘。
本节判断
- 罕见关键词（50 行）收益最大：GIN 只读 28 个缓冲块，全表要读 11365 个。
- 常见关键词（83333 行）走索引也要 36 ms，因为要回表 8 万多行；搜索框一般还要配 LIMIT 和排序，别只盯着索引。
| 查询 | 索引 | 计划 | 命中行 | 耗时 |
|---|---|---|---|---|
| LIKE '%sunflower%' | 无 | Parallel Seq Scan | 50 | 40.6–52.6 ms |
| LIKE '%sunflower%' | GIN gin_trgm_ops（43 MB） | Bitmap Index Scan | 50 | 0.05–0.12 ms |
| ILIKE '%SunFlower%' | 同上 | Bitmap Index Scan | 50 | 0.07–0.08 ms |
| LIKE '%向日葵%' | 同上 | Bitmap Index Scan | 50 | 0.02 ms |
| LIKE '%bottle%'（8.3% 的行） | 同上 | Bitmap Index Scan | 83333 | 36.1–36.3 ms |
| LIKE '%sunflower%' | GiST gist_trgm_ops（141–142 MB） | Bitmap Index Scan | 50 | 5.76–5.95 ms |
| LIKE '%台灯%'（2 个字） | GIN，优化器自选 | Seq Scan | 83334 | 91.0–91.5 ms |
| LIKE '%台灯%'（2 个字） | GIN，enable_seqscan=off 强制 | Bitmap Index Scan（全索引） | 83334 | 433.5–454.5 ms |
btree 为什么帮不上：前置 % 没有「起点」，C.UTF-8 下连前缀都不行
btree 按值排序，LIKE 'abc%' 可以变成「>= 'abc' 且 < 'abd'」的范围扫描，LIKE '%abc' 和 '%abc%' 没有起点，只能逐行比。
更容易踩的是前缀查询：本机库的排序规则是 C.UTF-8（libc），不是 C。我在 name 上建了普通 btree，并用 enable_seqscan=off 逼优化器别走全表，计划仍然是 Seq Scan（LIKE 'wireless charger 0%'，1047 行，132.8–341.4 ms，耗时偏高是因为被 disable 惩罚后的计划）——普通 btree 根本不被当作可用路径。改成 btree (name text_pattern_ops)（52 MB）后，Index Cond 变成 name ~>=~ 'wireless charger 0' AND name ~<~ 'wireless charger 1'，0.67–3.22 ms。
顺带一个现象：GIN trgm 索引也能服务前缀 LIKE（同一条 'wireless charger 0%' 走 GIN 24.4 ms），但比 text_pattern_ops 慢一个数量级。只有前缀搜索时，用 text_pattern_ops 就够了。
本节判断
- 排序规则的细节在 10 题练习的 Q4 有单独实测：/resources/postgresql-index-not-used-practice-10-questions。
- text_pattern_ops 索引不能用于 < > 这类按语言规则的比较排序；如果同一列既要前缀搜索又要 ORDER BY name，可能需要两个索引。
中文能不能用 pg_trgm：看 LC_CTYPE，不看 LC_COLLATE
pg_trgm 先把字符串拆成「词」，只有被当作字母数字的字符才进三元组。哪些字符算字母数字，由数据库的字符分类（LC_CTYPE）决定。
在 C.UTF-8 的库里，show_trgm('向日葵') 返回 {0xbbb60d,0xc8a856,0x3f1d3e,0x6707a7}：多字节字符的三元组以哈希值显示，能用，'%向日葵%' 走 Bitmap Index Scan，0.02 ms。
在 LC_CTYPE=C 的对照库里，同一个函数返回 {}，英文 show_trgm('sunflower') 照常。建了 GIN 的 20 万行表上，优化器选 Seq Scan（15.8 ms）；强制走索引时，Bitmap Index Scan 返回全部 200000 行，Recheck 去掉 199990 行，34.7–35.3 ms，比全表还慢一倍多。也就是说，索引建成功了、不报错，但对中文完全无效。
本节判断
- 上线前先跑：SELECT datctype FROM pg_database WHERE datname = current_database(); 再跑 SELECT show_trgm('你的中文关键词');，返回 {} 就别建了。
- 很多人为了排序性能把库建成 C，这对中文模糊搜索是个隐性代价；库的 LC_CTYPE 建库后不能直接改，只能新建库迁移。
| 库 | datctype | show_trgm('向日葵') | '%向日葵%' 能否用 GIN |
|---|---|---|---|
| pg_trgm_lab | C.UTF-8 | 4 个三元组（哈希显示） | 能：0.02 ms |
| pg_trgm_lab_c | C | {} | 不能：全表 15.8 ms / 强制全索引 34.7–35.3 ms |
两个字的关键词：show_trgm 有结果，LIKE 却用不上
这是我这次最没想到的地方。show_trgm('台灯') 返回 3 个三元组，看上去没问题；但 LIKE '%台灯%' 走 GIN 时，Bitmap Index Scan 的 rows=1000000——索引把每一行都当成候选，Heap 再 Recheck 掉 916666 行。
原因是 show_trgm 按「词」处理，会在词首补两个空格、词尾补一个空格，所以「台灯」能拼出 3 个带空格的三元组；LIKE '%台灯%' 是任意位置的子串，不能假设前后有词边界，pg_trgm 只能取模式内部连续的 3 个字符，2 个字符拼不出一个。英文 '%a1%' 同理：强制走索引 447.0–461.9 ms，优化器自选的并行全表 50.7 ms。
好消息是优化器在两种情况下都自己选了 Seq Scan，没有走进坑里。坏消息是：如果有人为了「让它走索引」全局调低 random_page_cost 或者在会话里关 enable_seqscan，两字搜索会慢约 5 倍。
本节判断
- 中文搜索框里两个字的关键词很常见（台灯、毛巾、音箱）。要么在应用层要求至少 3 个字符，要么两字关键词改走别的方案（如分词后的全文检索），不要指望 pg_trgm。
- 压测时要专门放一批 1–2 个字符的关键词，否则平均耗时会很好看。
GIN 还是 GiST：LIKE 场景选 GIN
同一张表、同一条 '%sunflower%'：GIN 0.05–0.12 ms、索引 43 MB、建 6.8–7.2 s（maintenance_work_mem=256MB）；GiST 5.76–5.95 ms、索引 141–142 MB、建 9.7–10.4 s，读了 1444 个缓冲块（GIN 28 个）。
GiST 的优势在相似度排序（ORDER BY name <-> '关键词' LIMIT 10 这类 KNN 查询），本次没有测这个场景，所以不下结论；只做 LIKE / ILIKE 过滤时，这组数据里 GIN 在大小、建索引时间、查询耗时上都更好。
'%sunflower%' 执行耗时（ms，越低越好）
无索引
40.6–52.6
GiST trgm
5.76–5.95
GIN trgm
0.05–0.12
PostgreSQL 17.11，100 万行自造数据，热缓存；柱高取区间内代表值。
写入要付多少：GIN 让批量写入慢约 9 倍，关 fastupdate 慢约 65 倍
一个商品名会拆出十几个到几十个三元组，每个都要写进 GIN。我建了三张同结构的空表（只有主键 / 加 GIN 默认 fastupdate=on / 加 GIN fastupdate=off），各插入同样的 10 万行，再各逐行插入 2000 次。
第一轮测试我用了 CREATE TABLE … (LIKE products INCLUDING ALL)，结果把 products 上的 GIN 也复制过去了，「无索引」表其实带着 GIN，数据作废重测——自己做对照实验时，先看一眼 pg_stat_user_indexes。
本节判断
- 默认 fastupdate=on 时，新条目先写进 pending list，攒到 gin_pending_list_limit（默认 4MB）或 VACUUM 时再合并。本次插入完成后 pgstatginindex 显示 pending_pages=119、pending_tuples=1782；手动 SELECT gin_clean_pending_list(...) 清掉 119 页后归零。
- pending list 里的条目查询时要顺序扫，写多读少、autovacuum 跟不上的表要盯这个数；只读巡检 SQL 第 6 段给了查看方法。
- 大批量导入（迁移、初始化）先导数据再建 GIN，比带着 GIN 导快得多；本次 100 万行建 GIN 只要 7 秒左右。
| 表 | 10 万行批量 INSERT | 逐行 INSERT 2000 次 | 完成后 trgm 索引大小 |
|---|---|---|---|
| 只有主键 | 131.8–135.6 ms | 11.3–12.0 ms | — |
| +GIN（fastupdate=on，默认） | 1154.7–1242.8 ms | 62.7–69.2 ms | 11 MB |
| +GIN（fastupdate=off） | 8063.5–8968.8 ms | 124.2–148.5 ms | 7.8 MB |
上 pg_trgm 之前的 5 步核对
下面是我现在给搜索框加模糊查询时的顺序。前 3 步都是只读的，可以直接在生产只读账号下跑（下载区的只读 SQL 已包含）。
pg_trgm 上线前核对
- 01
1
查 LC_CTYPE
datctype 是 C / POSIX 时，中文不会生成三元组；先 show_trgm 一个真实中文关键词，返回 {} 就停。
- 02
2
统计关键词长度
从搜索日志里看 1–2 个字符的关键词占比；占比高就在应用层设最短长度，或另选方案。
- 03
3
看写入量
按本次量级估算：带 GIN 的批量写入约慢 9 倍；写多的表评估是否接受，别轻易关 fastupdate。
- 04
4
测试库建索引
用 CREATE INDEX CONCURRENTLY 在维护窗口建 GIN (col gin_trgm_ops)，记录大小和耗时。
- 05
5
EXPLAIN 复核
用真实的 3 字、2 字、常见词三类关键词跑 EXPLAIN (ANALYZE, BUFFERS)，看 Rows Removed by Index Recheck 和耗时。
演示脚本会 DROP/CREATE 测试库，只能在测试实例运行；脚本要求库名以 _lab 结尾，否则拒绝执行。
觉得有用？转给正在做这件事的同事或朋友
参考依据
以下来源用于确认市场趋势、政策背景和术语边界；具体落地方案仍以客户的数据范围、权限和交付目标为准。
常见问题
PostgreSQL LIKE '%关键词%' 为什么不走索引？
前面带 % 时 btree 没有扫描起点，只能全表扫。要加速任意位置的子串匹配，需要 pg_trgm 扩展的 GIN 或 GiST 索引；本次 100 万行实测，GIN 把 41–53 ms 降到 0.05–0.12 ms。
pg_trgm 支持中文吗？
支持，但取决于数据库的 LC_CTYPE。本次在 C.UTF-8 库里 show_trgm('向日葵') 正常返回三元组，'%向日葵%' 0.02 ms；在 LC_CTYPE=C 的库里返回 {}，索引对中文无效。
为什么两个字的关键词建了 pg_trgm 还是慢？
LIKE '%台灯%' 这种子串至少要 3 个连续字符才能拼出可用的三元组。本次强制走 GIN 时索引把 100 万行全当候选，434–455 ms，比全表 91 ms 还慢。
pg_trgm 用 GIN 还是 GiST？
只做 LIKE / ILIKE 过滤时，本次 GIN 更好：0.05–0.12 ms、43 MB；GiST 5.8–6.0 ms、141–142 MB。GiST 的相似度 KNN 排序场景本次没测。
GIN 索引会让写入慢多少？
本次 10 万行批量 INSERT：只有主键 0.13 s，加 GIN（默认 fastupdate=on）约 1.2 s，fastupdate=off 8.1–9.0 s。这是测试库自造数据的量级，生产以你自己的压测为准。
下一步
推荐动作
你提供只读巡检 SQL 的结果和几条慢搜索的 EXPLAIN (ANALYZE, BUFFERS)（可脱敏），我们判断该用 text_pattern_ops、pg_trgm 还是改查询方式，并估算写入代价；生产变更由你方授权执行。