pg_trgm 索引建了却没生效：几种写法与对应的排查点

pg_trgm 的 GIN / GiST 索引建好之后，LIKE、ILIKE、% 依然走 Seq Scan，原因通常不在索引本身，而在列类型、WHERE 的写法，以及规划器对成本的估算。下面按官方文档和几处邮件列表、Stack Overflow 的原文，把不生效的具体情形列出来。

官方文档：

先确认这个索引能支持什么

pg_trgm 提取 trigram 时忽略非字母数字字符，每个词计算时视为前加两个空格、后加一个空格。字符串 "cat" 的 trigram 集合是 " c"、" ca"、"cat"、"at "；"foo|bar" 是 " f"、" fo"、"foo"、"oo "、" b"、" ba"、"bar"、"ar "。

Index Support 一节说明：pg_trgm 提供 GiST 与 GIN 两种 operator class，除了相似度操作符，还支持基于 trigram 的 LIKE、ILIKE、~、~* 和 = 查询的索引搜索。默认构建下相似度比较大小写不敏感；不支持不等操作符；这些索引在等值查询上可能不如普通 B-tree 高效。

CREATE INDEX trgm_idx ON test_trgm USING GIN (t gin_trgm_ops);
CREATE INDEX trgm_idx ON test_trgm USING GIST (t gist_trgm_ops);

gist_trgm_ops 把 trigram 集合近似成一个 bitmap signature，可选整数参数 siglen 指定签名长度（字节），默认 12，有效范围 1–2024。签名越长越精确，扫更少的索引与堆页，代价是索引更大，例如 gist_trgm_ops(siglen=32)。

两种典型查询：

SELECT t, similarity(t,'word') AS sml
FROM test_trgm
WHERE t % 'word'
ORDER BY sml DESC, t;

SELECT t, t  'word' AS dist
FROM test_trgm
ORDER BY dist LIMIT 10;

第二种 GiST 索引可以高效实现，GIN 不行；只需要少量最近匹配时通常比第一种更快。

词相似度写作 WHERE 'word' 与 ``。

LIKE/ILIKE 的索引搜索从 PostgreSQL 9.1 起支持，例如 WHERE t LIKE '%foo%bar'，原理是从搜索串里提取 trigram 再查索引；搜索串里的 trigram 越多，索引搜索越有效。与 B-tree 不同，搜索串不必左锚定。正则（~ 与 ~*）自 9.3 起支持，例如 WHERE t ~ '(foo|bar)'。

这里有一条关键限制：对 LIKE 和正则搜索都一样，无法提取任何 trigram 的模式会退化为 full-index scan，即扫描整个索引。

情形一：列是 citext，ILIKE 落到另一套操作符上

Tom Lane 的回复：

报告的现象是列类型为 citext，建了 gin (raw_data gin_trgm_ops)，但 ILIKE 子串匹配不走索引。

原因在列类型上：定义成 citext 之后，ilike 会被解释成 citext 专属的操作符，它不属于 gin_trgm_ops 这个 operator class，因此和这个索引对不上。如果因为其他原因必须保留 citext 列，做法是在 raw_data::text 上建 gin 索引，查询写成 raw_data::text ILIKE '%zagreb%'。

情形二：用 similarity() 代替了 % 操作符

Stack Overflow "Postgres LIKE % operator vs similarity - pg_trgm.similarity_threshold . different query plans"（Laurenz Albe 回答，2023-06-26）。

SET pg_trgm.similarity_threshold = 0.9;
... WHERE column % 'some string s';

这条会走 gin 索引扫描；而语义上等价的 WHERE similarity(column,'some string s') >= 0.9 是顺序扫描。

要让索引扫描成立，WHERE 条件在形式上必须是 ：索引表达式就是建索引的那一列或表达式；操作符必须是该索引 operator class 所属 operator family 里的操作符；常量必须在索引扫描期间保持不变，尤其只能包含 STABLE 函数。

PostgreSQL 里唯一的例外是：WHERE 里用的是返回 boolean 的函数，且该函数带 optimizer support function，可以让 PostgreSQL 替换成索引扫描。similarity 不是这种函数。

情形三：word_similarity() 同样不行，距离操作符还分方向

Stack Overflow "Why is postgres trigram word_similarity function not using a gin index?"

WHERE word_similarity(place.name,'X') > 0.5 不走索引，而 ILIKE 或 %> 会走。原因和上一条一致：PostgreSQL 不会对 WHERE 子句里的函数使用索引扫描，必须改写为操作符形式，并配合 pg_trgm.word_similarity_threshold：

SET pg_trgm.word_similarity_threshold TO 0.1;
... WHERE input ` 能用而 `>` 不能，因为它们是不同的操作，索引只支持其中一个。

WHERE 子句只接受 boolean，所以 `` / `>` 这类返回 real 的距离操作符不能直接出现在 WHERE 里，只能放在 ORDER BY，否则报 `argument of WHERE must be type boolean, not type real`。

## 情形四：查询串变长，成本估算把索引判成更贵

pgsql 邮件列表 "pg_trgm word_similarity query does not use index for input strings longer than 8 characters"：

现象是同样的索引，短查询串会走索引，长度 ≥8 的查询串不走，即使禁掉 seqscan 后能看出索引快很多。

`gincostestimate` 对这类操作符的成本估算，基本由「从查询串里提取出的 trigram 个数」和索引大小决定，再乘以 `random_page_cost` 得到预测的索引页读取成本。查询串越长，提取的 trigram 越多，索引匹配的工作量估计越大，于是看起来比 seqscan 贵。

SSD 上把 `random_page_cost` 调到 1.1 会让规划器更倾向索引扫描；帖子作者实测 1.4 是其场景的最优值，太小反而因为过度用索引而变慢。

## 通用前提与几个容易踩的细节

database-execution-plan.com 的 "Trigram Indexes for LIKE and ILIKE" 里补充了几条：

- B-tree 从左到右排序字符串，可能匹配串中任意位置的搜索没有起点，所以 B-tree 帮不上 `ILIKE '%x%'`。
- `"hans"` 分解为 `" h"`、`" ha"`、`"han"`、`"ans"`、`"ns "`。
- `gin_trgm_ops` 对搜索通常是更好的选择：多数文本下比 GiST 小、查找更快，但不支持排序。
- `gist_trgm_ops` 支持距离排序（`ORDER BY name  'hans' LIMIT 10`），适合「最近匹配」排名；纯过滤场景通常更慢更大。
- 前缀锚定的 `LIKE 'hans%'` 不需要 trigram 索引：`text_pattern_ops` 的 B-tree（或 C collation 的库）可以直接服务前缀搜索，`lower(name) text_pattern_ops` 处理大小写不敏感变体。
- trigram 索引是有损的，每个候选行都要 recheck，计划里表现为 Recheck Cond，GiST 下常见大量 Rows Removed by Index Recheck。
- 少于三个字面的模式（`LIKE '%an%'`）提不出任何 trigram，索引不可用，计划表现为 Seq Scan。
- 常见误用：为 ILIKE 另外建 `lower(name)` 的 trigram 索引。不需要，trigram 默认就大小写不敏感，只会得到重复索引。
- 大文本列上建 trigram 索引会让索引膨胀，文档内容检索应该用全文检索（tsvector + GIN），trigram 适合「名字里含某个片段、含错拼」这类问题。
- 建议在应用层强制最小搜索长度为 3 个字符。

排查时按这个顺序看：先确认列类型和实际解析到的操作符，再看 WHERE 是否写成 `  `，最后看成本估算是否把索引判贵了。