PostgreSQL 到处搜索：全文、trigram 与部分索引

对内部工具来说，“到处搜索”到底意味着什么

在内部界面中，“到处搜索”通常表示：“帮我快速找到我脑中想的那条记录，即使我记得不完全准确。”人们不是在浏览，而是想直接跳到某个客户、工单、发票或设备。

这就是为什么慢速搜索比慢速页面更让人恼火。页面加载只发生一次，而搜索往往连续多次发生，通常在有人通话或做故障排查时。如果结果需要 2–3 秒，用户就会修改查询、退格、尝试其它术语，结果是更高的负载和更多的挫败感。

用户从一个搜索框里期望一组行为：部分匹配（"alex" 能找到 "Alexander"）、容忍小拼写错误（"microsfot" 仍能找到 "Microsoft"）、合理的“最佳结果”排序（精确 ID 或邮箱排在顶部）、一点时间偏好，以及默认生效的筛选（只看未结工单、活跃客户）。

棘手之处在于一个输入框常常掩盖多种意图。坐席可能粘贴工单号、输入姓名片段、搜索邮箱或输入电话号码。不同意图需要不同策略、不同索引，有时甚至不同的排序规则。

所以不要一开始就讨论索引。先列出用户实际有哪些搜索意图，并把身份字段（ID、邮箱）与模糊字段（姓名、主题）和长文本（备注）区分开来。

先把数据和搜索行为命名清楚

在选择索引之前，写下人们实际会输入的内容。“PostgreSQL 到处搜索”听起来像一个功能，但通常是多种截然不同搜索的混合体。

内部工具把“硬”标识符（订单 ID、工单号、发票码）和“软”文本（客户名、邮箱、备注、标签）混在一起。这些字段在 PostgreSQL 中的行为不同，把它们当成同一种处理是导致慢查询的捷径。

接着，把行为分清：

• 精确查找：有人搜索 TCK-104883 时期望返回一个确定结果。
• 模糊查找：有人输入 john smth 希望在姓名（也可能在邮箱）中得到宽容匹配并浏览一小段结果。
• 以筛选为主的搜索：有人选择 “状态 = Open” 和 “指派给 = 我”，这更多是筛选；文本框是次要的。

及早决定是否需要对结果进行排序（把最佳匹配排到前面）或仅仅筛选。对于备注和较长描述，排序很重要。对于 ID 和邮箱，排序往往看起来随机且增加成本。

一个简短的检查清单通常就够了：

• 哪些字段每天都会被搜索？
• 哪些输入是精确的（ID、代码）、模糊的（姓名）或长文本（备注）？
• 哪些筛选几乎每次都会被应用？
• 你需要“最佳匹配”排序，还是任何匹配都可以？
• 表规模会增长到多少：几千、几十万还是上百万？

如果把这些决策提前说清楚，后续的索引选择就不会像猜谜了。

基线：精确匹配以及为什么 ILIKE 往往不好

先把容易的赢点锁定。对于许多内部界面，普通的 B-tree 索引已经能对 ID、订单号、邮箱和外部引用等精确匹配提供即时结果。

如果用户粘贴的是精确值，确保你的查询确实是精确的。WHERE id = ... 或 WHERE email = ... 在普通索引下可以非常快。对邮箱使用唯一索引通常有双重收益：速度和更好的数据质量。

问题出现在“到处搜索”悄悄变成 ILIKE 的时候。像 name ILIKE '%ann%' 这种带前导通配符的查询，PostgreSQL 无法使用普通的 B-tree 索引，会检查大量行，并且随着表增长而变得明显慢。

前缀搜索可以工作，但只有在模式从开头锚定时才行：name ILIKE 'ann%'。即便如此，细节也很重要（排序规则、大小写处理，以及你是否对相同表达式建立了索引）。如果 UI 必须大小写不敏感，常见做法是查询 lower(name) 并在 lower(name) 上创建匹配的索引。

还要定义什么叫“响应迅速”：

• 对于缓存命中的数据库操作大约 200 ms 或更短
• 包括网络与渲染在内端到端小于 1 秒
• 常见搜索不显示可见的加载状态

有了这些目标，就更容易决定是否仅靠精确和前缀匹配，或是该引入全文搜索或 trigram 索引。

什么时候选择全文搜索

当用户输入自然语言并期望系统找到相关项（而不是仅仅精确匹配）时，全文搜索是最佳选择。想想工单消息、内部备注、较长描述、知识库文章和通话记录。

最大收益是排序。全文搜索可以按相关性排序，而不是返回一个需要人工扫描才能找到最佳项的长列表。在内部工具中，这很重要：用户需要在几秒钟内得到答案，而不是在翻看 50 条记录后。

从高层看，全文搜索有三块核心部分：

• tsvector（可搜索文本，存储或生成）
• tsquery（把用户输入转换成的查询）
• 语言配置（单词如何标准化）

语言配置会直接影响行为。PostgreSQL 会移除常见停用词（如“the”或“and”）并做词干处理，这样 “pay”、“paid” 和 “payment” 可以匹配。这对备注和消息很好，但当用户搜索一个短的常见词时可能得到空结果，会让人感到意外。

同义词是另一个决策点。当公司对同一事物使用不同词时（例如 “refund” 与 “chargeback”），同义词会有帮助，但需要随着时间维护。把同义词列表控制在较短范围，并基于支持或运维团队实际输入的词语来维护。

一个实用例子：搜索 “can’t login after reset” 应该能把包含 “cannot log in after password reset” 的工单拉出来，即便措辞不同。这种“找到相关内容”的行为正是全文搜索的强项，通常比试图让 ILIKE 表现得像搜索引擎要好得多。

什么时候 trigram 索引更合适

当用户输入片段、犯小拼写错误或只记得“差不多的样子”时，trigram 索引非常适合。它们在短文本字段上表现出色，而全文搜索对这些字段往往过于严格：如人名、公司名、工单主题、SKU、订单号和产品代码。

trigram 是由三个字符组成的片段。PostgreSQL 通过比较两个字符串共享了多少 trigram 来判断相似度。因此它能把 "Jon Smth" 匹配到 "John Smith"，或把 "ACM" 匹配到 "ACME"，还能在查询是单词中间的情况下找到结果。

当工作是“找到正确行”而不是“查找有关某个主题的文档”时，trigram 常常是通向宽容搜索框的最快路径。

在哪些场景胜出全文搜索

全文搜索适合较长文本和按意义排序，但它并不自然地处理短字段的部分字符串和小拼写错误。trigram 搜索就是为这种模糊匹配设计的。

控制写入开销

trigram 索引更大，会增加写入负担，所以要有选择地建立。只给人们实际在搜索框里使用的列建立索引：

• 姓名、邮箱、公司名、用户名
• 短标识符（SKU、代码、参考号）
• 简短标题字段（不要对大型备注/评论字段建 trigram 索引）

如果你能明确说出团队在搜索框里会输入哪些字段，通常就能把 trigram 索引控制得既小又快。

为常用筛选建立部分索引

“到处搜索”框通常有隐藏的默认值。人们在某个工作区内搜索、只看活跃项并排除已删除项。如果这些筛选几乎每次都存在，就通过只索引满足这些条件的行来把常见路径做快。

部分索引是带 WHERE 子句的普通索引。PostgreSQL 只为你关心的行存储索引项，从而减小索引体积。这通常意味着要读取的页面更少，缓存命中率更高。

常见的部分索引目标包括活跃行（status = 'active'）、软删除（deleted_at IS NULL）、租户范围，以及“最近”窗口（例如最近 90 天）。

关键在于让索引与 UI 保持一致。如果界面总是隐藏已删除行，那么查询也应该总带上 deleted_at IS NULL，部分索引也应使用相同条件。细微的不一致，例如一个地方用 is_deleted = false 而另一个地方用 deleted_at IS NULL，都可能让 planner 无法使用索引。

部分索引也可以和全文搜索、trigram 索引并存。例如仅为未删除行建立文本搜索索引能控制索引大小。

权衡：部分索引对少见查询帮助有限。如果有人偶尔要跨已删除记录或跨所有工作区搜索，PostgreSQL 可能会回退到较慢的执行计划。为此可以做一个仅限管理员的路径，或者仅在罕见查询变成常见后再增加第二个索引。

在不把搜索变成谜题的情况下混合方法

大多数团队最终会混合多种技术，因为一个搜索框要处理不同意图。目标是让执行顺序清晰，这样结果会显得可预测。

一个简单的优先级顺序有帮助，无论你是用多次查询实现，还是用带明确 CASE 逻辑的单次查询实现。

一条可预测的优先阶梯

先严格匹配，再逐步放宽：

• 先精确匹配（ID、邮箱、工单号、SKU），使用 B-tree 索引
• 然后做前缀匹配（在适合的情况下）
• 接着用 trigram 匹配姓名和标题的拼写错误与片段
• 最后用全文搜索处理长文本、描述和自由文本

遵循同一阶梯，用户就会学会搜索框“代表什么”。他们不会因为 “12345” 立刻找到工单而感到系统坏掉，同时 “refund policy” 则去搜索长文本而花更久时间。

先筛选，再模糊

当模糊搜索必须在全表上考虑时，成本会非常高。用人们实际使用的筛选条件（状态、指派团队、日期范围、账户）先缩小候选集，然后再对剩余数据运行 trigram 或全文搜索。即便是快速的 trigram 索引，如果要对数百万行评分，也会感觉慢。

也值得写一段非技术人员能懂的规则，比如：“我们先精确匹配工单号，然后对客户姓名做拼写容错匹配，最后搜索备注。” 这个共享定义能避免以后围绕为何某条记录出现的争论。

逐步实施：选择方案并安全落地

一个快速的“到处搜索”框是一系列小决策。先把这些决策写下来，数据库工作会更简单。

1. 定义输入。是只有一个搜索框，还是一个框加筛选（状态、负责人、日期范围）？
2. 为每个字段选择匹配类型。ID 和代码要精确匹配。姓名和邮箱常常需要前缀或模糊匹配。长备注和描述更适合自然语言搜索。
3. 添加合适的索引并确认它们被使用。创建索引后，用 EXPLAIN (ANALYZE, BUFFERS) 检查真实查询。
4. 添加与意图匹配的排序或排名。如果用户输入 “invoice 1042”，精确匹配应该优先；如果输入的是拼写错误的姓名，则应该以相似度排名。
5. 用真实查询测试。试试拼写错误、非常短的术语（如 “al”）、长文本粘贴、空输入，以及“仅筛选”模式。

为安全发布，一次只改动一项并确保回滚容易。对大表新增索引时优先使用 CREATE INDEX CONCURRENTLY，以免阻塞写操作。如果可以，把改动放在功能开关后面，并对比改动前后的延迟。

一个实用模式是：先做精确匹配（快速且精确），对“有人类输入”的字段用 trigram 匹配（容错），对长文本用全文搜索（按相关性排序）。

现实示例：支持管理面板的一个搜索框

想象一个支持管理面板，团队只有一个搜索框，但期望能找到客户、工单甚至备注。这就是“一个输入，多种含义”的经典问题。

第一步是让意图可见而不增加摩擦。如果查询看起来像邮箱或电话号码，就把它当作客户查找；如果像工单 ID（例如 "TKT-10482"），就直接路由到工单。其他情况回退到对工单主题和备注的文本搜索。

对客户查找，trigram 索引通常感觉最好。姓名和公司字符串千奇百怪，人们会输入片段。trigram 索引可以让像 “jon smi” 或 “acm” 这样的搜索既快又宽容。

对工单备注，使用全文搜索。备注是完整句子，通常需要按相关性排序，而不是单纯的子串匹配。当很多工单都提到相同关键词时，排序能把最相关的放到前面。

筛选比大多数团队预期的重要。如果坐席常驻于“未结工单”，为只覆盖未结行建立部分索引。对“活跃客户”同理。这样能保持索引小且常用路径快。

非常短的查询需要规则，否则数据库会为噪声做昂贵的工作：

• 1–2 个字符：显示最近的打开工单和最近更新的客户
• 3 个字符及以上：对客户字段运行 trigram，对工单文本运行全文搜索
• 无明确意图：显示混合列表，但对每类结果做上限（例如每类 10 条）

常见会让搜索变慢或令人困惑的错误

大多数“为什么搜索慢？”的问题都是自找的。目标不是把所有东西都索引，而是索引用户真正会用到的。

常见陷阱是“以防万一”为许多列添加索引。读取可能会提升，但每次插入和更新都有额外工作。在记录频繁变更的内部工具（工单、订单、用户）中，写入速度同样重要。

另一个错误是把全文搜索用在其实需要对姓名或邮箱进行拼写容错查找的场景。全文搜索适合文档与描述，但并不能魔法般地解决 “Jon vs John” 或 “gmail.con vs gmail.com” 这类问题——那通常是 trigram 的职责。

筛选也会悄悄破坏你的执行计划。如果大多数搜索都带有固定筛选（像 status = 'open' 或 org_id = 42），最佳索引可能是一个匹配该条件的部分索引。忘记这一点会让 PostgreSQL 扫描比预期更多的行。

一些重复出现的错误包括：

• 在没有衡量写入成本的情况下添加许多索引
• 期待全文搜索表现得像拼写容错的自动完成
• 忽视常用筛选如何改变可用索引
• 在小且干净的数据上测试而忽略真实术语频率（常见词 vs 罕见 ID）
• 对没有支持索引的列排序，迫使执行慢排序

例子：一个支持界面按主题、客户名和工单号搜索，然后按最近活动排序。如果 latest_activity_at 在过滤后的集合上没有索引，这个排序可能抹掉你从搜索索引里获得的速度提升。

发布前的快速检查

在把“到处搜索”功能标为完成前，把你承诺的行为具体化。

• 用户是在通过精确标识符（工单号、邮箱）找记录吗？
• 他们期望拼写容错吗？
• 他们需要从长文本和描述中得到排序结果吗？

如果混合了模式，就决定冲突时哪种规则优先。

然后识别驱动大部分搜索的 2–3 个字段。如果 80% 的搜索是按邮箱、姓名和工单 ID 发起，先优化这些，再把其它当作次要。

一个简单的发版前检查表：

• 确认每个字段的主要匹配模式（精确、模糊或按相关性排序）
• 列出用户每天常用的筛选，并确保索引匹配这些组合
• 决定如何处理非常短和空输入（例如要求 2–3 个字符才做模糊搜索；空输入展示“最近”）
• 让排序可解释：按最近、按最匹配文本，或使用简单的合并规则

最后，用真实的数据量和延迟来测试而不仅仅验证正确性。对 1,000 行感觉瞬间的查询，在 1,000,000 行时可能会拖慢。

后续步骤：把计划变成快速的内部搜索界面

当团队就搜索应如何工作达成共识时，搜索框才能长期保持快速。用通俗语言写下规则：何谓“匹配”（精确、前缀、拼写容错）、哪些字段会被搜索，以及筛选如何改变结果集。

保留一小组真实搜索用例作为回归套件。10–20 个查询通常够用：几个常见姓名、几个片段邮箱、一次拼写错误、一次长文本片段和一个“无结果”案例。在改动前后运行它们，确保性能改进不会悄悄破坏相关性。

如果你用 AppMaster (appmaster.io) 构建内部工具，把这些搜索规则与数据模型和业务逻辑一起定义，这样在需求变化时 UI 行为和数据库选择不会偏离。