PHP小码农

Appearance

GIN 索引与全文搜索 ​

上一节讲了 GIN 的倒排结构与操作符类，但只提到「tsvector 用 tsvector_ops」。这一节把全文搜索这条主线完整走一遍：tsvector 为什么天生适合 GIN、索引表达式该怎么写才不会白建（这是本节最容易踩坑的地方）、regconfig 不一致会怎样、setweight 权重如何影响排序、为什么 ts_rank 用不上索引、以及中文场景的现实选择。

如果你只记一句话：GIN 决定「哪些行命中」，ts_rank 决定「命中的行怎么排」，这两件事索引只能帮你做前一件。

前置知识

阅读本节前，建议先了解：GIN 索引原理

基础概念 ​

tsvector 本身就是倒排表 ​

全文搜索的匹配靠 @@ 运算符：

text
tsvector  @@  tsquery
（文档向量）    （查询）

其中 tsvector 是「词位 + 位置 + 权重」的有序列表：

text
'index':2A,5B  'postgresql':1A,4B  'search':7

它已经是「按词位排好序」的结构，而 GIN 的倒排表正好是「词位 → 行号列表」。两者一一对应，所以GIN 索引 tsvector 几乎没有信息损失——这也是它能做到「查询快且结果准确」的原因。

为什么全文检索首选 GIN ​

tsvector 也能用 GiST 索引，但两者的行为差别很大：

对比项GIN（tsvector_ops）GiST（tsvector_ops）
索引结构词位 → 行号（无损）层级签名 + 边界，有损
假阳性极少（仅短语/位置相关）较多，需回表大量复检
查询速度快（尤其多词查询）较慢（Rows Removed by Index Recheck 高）
索引体积大（通常是表的 30%~100%）小
写入速度慢快
支持 KNN 排序不支持支持（几何/范围类型）

结论很直接：纯全文检索用 GIN；只有当 GIN 的体积/写入代价无法接受，或者这张表同时还需要 KNN 排序能力时，才考虑 GiST。

GiST 全文搜索的代价是「回表复检」

GiST 的 tsvector 索引是有损的：它只存签名，无法判断词位是否真的存在。查询时 PostgreSQL 必须把候选行读回来重新算一遍 @@，计划里会出现明显的 Rows Removed by Index Recheck。数据量大时这个代价可能超过全表扫描。

维护 tsvector 列的三种方式 ​

全文索引的前提是「有一个可索引的 tsvector 表达式」。生成它的方式决定了索引能被查询命中的难度：

方式写法优点缺点
生成列（PostgreSQL 12+）GENERATED ALWAYS AS (...) STORED自动维护、查询只写列名、几乎不可能写错表达式必须 IMMUTABLE，不能跨行/跨表
表达式索引CREATE INDEX ... (to_tsvector('english', body))不改表结构查询必须逐字写出同一个表达式，极易失败
触发器BEFORE INSERT OR UPDATE 写入列最灵活（可查其他表、可做非 IMMUTABLE 逻辑）需自己写函数，易漏、有维护成本

语法与用法 ​

text
-- 1) 生成列 + 普通 GIN 索引（最推荐）
CREATE TABLE t (
  id   bigint PRIMARY KEY,
  body text NOT NULL,
  tsv  tsvector GENERATED ALWAYS AS (to_tsvector('english', body)) STORED
);
CREATE INDEX idx_t_tsv ON t USING gin (tsv);

-- 2) 表达式索引（注意：查询表达式必须完全一致）
CREATE INDEX idx_t_body_fts ON t USING gin (to_tsvector('english', body));

-- 3) 显式操作符类（一般不需要写，tsvector 默认就是 tsvector_ops）
CREATE INDEX idx_t_tsv2 ON t USING gin (tsv tsvector_ops);

-- 4) 部分索引：只索引需要被搜索的子集
CREATE INDEX idx_t_tsv_pub ON t USING gin (tsv) WHERE status = 'published';

-- 5) 建索引参数（并发建 + 关闭 fastupdate）
CREATE INDEX CONCURRENTLY idx_t_tsv3 ON t USING gin (tsv) WITH (fastupdate = off);

-- 6) 改已有索引参数
ALTER INDEX idx_t_tsv SET (fastupdate = off);

最小可运行示例：

sql
DROP TABLE IF EXISTS ft_demo;

CREATE TABLE ft_demo (
  id   integer PRIMARY KEY,
  body text NOT NULL,
  tsv  tsvector GENERATED ALWAYS AS (to_tsvector('english', body)) STORED
);

INSERT INTO ft_demo (id, body) VALUES
  (1, 'PostgreSQL provides full text search'),
  (2, 'A GIN index accelerates full text search'),
  (3, 'B-tree index cannot handle token matching');

CREATE INDEX idx_ft_demo_tsv ON ft_demo USING gin (tsv);
ANALYZE ft_demo;
text
DROP TABLE
CREATE TABLE
INSERT 0 3
CREATE INDEX
ANALYZE

详细说明 ​

两种索引表达式：为什么「生成列」几乎总是更好 ​

方案 A：生成列 + 普通 GIN 索引 ​

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, body FROM ft_demo WHERE tsv @@ to_tsquery('english', 'search');
text
                                QUERY PLAN
---------------------------------------------------------------------------
 Bitmap Heap Scan on ft_demo (actual rows=2 loops=1)
   Recheck Cond: (tsv @@ '''search'''::tsquery)
   Heap Blocks: exact=1
   Buffers: shared hit=10
   ->  Bitmap Index Scan on idx_ft_demo_tsv (actual rows=2 loops=1)
         Index Cond: (tsv @@ '''search'''::tsquery)
         Buffers: shared hit=6
(7 rows)

查询里只写 tsv，索引和查询共用同一个表达式，不可能不命中。

方案 B：表达式索引，以及它的致命前提 ​

表达式索引不新增列，看起来更「干净」：

sql
DROP TABLE IF EXISTS ft_expr;

CREATE TABLE ft_expr (
  id   integer PRIMARY KEY,
  body text NOT NULL
);

INSERT INTO ft_expr (id, body) VALUES
  (1, 'PostgreSQL provides full text search'),
  (2, 'A GIN index accelerates full text search');

CREATE INDEX idx_ft_expr_fts ON ft_expr USING gin (to_tsvector('english', body));
ANALYZE ft_expr;
text
DROP TABLE
CREATE TABLE
INSERT 0 2
CREATE INDEX
ANALYZE

命中——查询表达式与索引表达式逐字相同：

sql
EXPLAIN (COSTS OFF)
SELECT id FROM ft_expr WHERE to_tsvector('english', body) @@ to_tsquery('english', 'search');
text
                              QUERY PLAN
----------------------------------------------------------------------
 Bitmap Heap Scan on ft_expr
   Recheck Cond: (to_tsvector('english'::regconfig, body) @@ '''search'''::tsquery)
   ->  Bitmap Index Scan on idx_ft_expr_fts
         Index Cond: (to_tsvector('english'::regconfig, body) @@ '''search'''::tsquery)
(4 rows)

不命中 1——表达式多了一层 coalesce：

sql
EXPLAIN (COSTS OFF)
SELECT id FROM ft_expr WHERE to_tsvector('english', coalesce(body, '')) @@ to_tsquery('english', 'search');
text
                          QUERY PLAN
---------------------------------------------------------------
 Seq Scan on ft_expr
   Filter: (to_tsvector('english'::regconfig, COALESCE(body, ''::text)) @@ '''search'''::tsquery)
(2 rows)

索引表达式是 to_tsvector('english', body)，查询里是 to_tsvector('english', coalesce(body, ''))——不同的表达式树，优化器不会做任何等价推导。列允许为 NULL 时，索引里也存不了 NULL 对应的 tsvector，所以生成列写法里必须用 coalesce 兜底；一旦两边不一致，索引就白建了。

不命中 2——拼接顺序不同：

sql
-- 索引建在 to_tsvector('english', title || ' ' || body) 上
EXPLAIN (COSTS OFF)
SELECT id FROM some_table WHERE to_tsvector('english', body || ' ' || title) @@ to_tsquery('english', 'search');
text
                          QUERY PLAN
---------------------------------------------------------------
 Seq Scan on some_table
   Filter: (to_tsvector('english'::regconfig, (body || ' '::text) || title) @@ '''search'''::tsquery)
(2 rows)

'a' || 'b' 和 'b' || 'a' 在优化器眼里毫无关系。

一个连创建都会失败的写法——漏掉 regconfig 参数：

sql
-- 错误！
CREATE INDEX idx_bad ON ft_expr USING gin (to_tsvector(body));
text
ERROR:  functions in index expression must be marked IMMUTABLE

原因：to_tsvector(regconfig, text) 是 IMMUTABLE，而单参数版本 to_tsvector(text) 依赖会话级的 default_text_search_config，属于 STABLE，不能被索引。这从设计上就强制你在索引里写死一个配置名，是好事。

表达式索引最常见的线上事故

改了 default_text_search_config、或者在查询里换了配置名，索引不会报错、也不会失效告警，只是悄悄从 Bitmap Index Scan 掉回 Seq Scan。用生成列把表达式集中在一处，并从根上避免这个问题。

方案对比 ​

维度生成列表达式索引触发器
查询是否需要复刻表达式否是（逐字一致）否
能否加 setweight 加权能，且只在定义处写一次能，但查询表达式会变得极长能
是否多占一列存储是（通常被 TOAST 压缩）否是
能否引用其他表/非 IMMUTABLE 函数否否能
维护风险低高中
适用版本PostgreSQL 12+全版本全版本

regconfig：索引与查询必须用同一个 ​

to_tsvector 的第一个参数是「文本搜索配置名」，它决定分词与词干还原规则：

sql
SELECT 'running'::text            AS word,
       to_tsvector('simple',  'running') AS simple_tsv,
       to_tsvector('english', 'running') AS english_tsv;
text
   word    |  simple_tsv  |  english_tsv
-----------+--------------+--------------
 running   | 'running':1  | 'run':1
(1 row)
配置行为适用场景
english 等语言配置去停用词 + 词干还原（running → run）英文等有词形变化的语言
simple只按空白/标点切分，不去停用词、不做词干还原代码、标识符、日志，或自己做归一化
chinese_zh（zhparser）中文分词中文正文
jiebacfg（pg_jieba）中文分词中文正文

同一个词在不同配置下的词位不同，所以配置不一致会有两种后果，且都不会报错：

sql
CREATE INDEX idx_ft_expr_simple ON ft_expr USING gin (to_tsvector('simple', body));

-- 索引表达式一致 → 索引被使用，但结果为空（词位对不上）
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id FROM ft_expr WHERE to_tsvector('simple', body) @@ to_tsquery('english', 'run');
text
                                QUERY PLAN
---------------------------------------------------------------------------
 Bitmap Heap Scan on ft_expr (actual rows=0 loops=1)
   Recheck Cond: (to_tsvector('simple'::regconfig, body) @@ '''run'''::tsquery)
   ->  Bitmap Index Scan on idx_ft_expr_simple (actual rows=0 loops=1)
         Index Cond: (to_tsvector('simple'::regconfig, body) @@ '''run'''::tsquery)
(4 rows)

计划显示索引确实被用上了，但返回 0 行——因为 simple 把 running 存成了 'running'，而 english 把 run 还原成了 'run'，两个词位永远匹配不上。

这类 bug 最难查

没有报错、没有慢查询、索引也在正常工作，只是搜不到东西。规范做法是把配置名集中在一处定义（生成列或应用层的常量），索引与查询都引用它，绝不在查询里随手写另一个配置名。

权重与 setweight ​

to_tsvector 产出的词位权重默认是 D（最低档）。要区分「标题命中」和「正文命中」，用 setweight：

sql
SELECT
  setweight(to_tsvector('english', 'PostgreSQL Index'), 'A') ||
  setweight(to_tsvector('english', 'A GIN index accelerates token matching'), 'B') AS weighted;
text
                                  weighted
-------------------------------------------------------------------------------
 'acceler':6B 'gin':4B 'index':2A,5B 'match':8B 'postgresql':1A 'token':7B
(1 row)

注意 'index':2A,5B——同一个词位在标题（A）和正文（B）里各出现一次，两个权重都保留了。这正是 || 拼接加权的预期效果。

权重默认排序权重值约定含义
A1.0标题 / 最关键字段
B0.4摘要 / 副标题
C0.2正文
D0.1默认，未加权

这些默认值来自 ts_rank 的权重数组 {D, C, B, A} = {0.1, 0.2, 0.4, 1.0}，可以在调用 ts_rank 时覆盖。

ts_rank / ts_rank_cd 与索引的关系 ​

这是全文检索里最容易被误解的一点：排序不能靠 GIN 索引。

GIN 索引知道「哪些行包含这些词位」，但不知道「这一行的整体相关度是多少」；ts_rank 需要逐行读取完整的 tsvector（权重、出现次数、位置）来算分。所以典型计划长这样：

text
Limit
  ->  Sort                       ← 排序发生在取完所有命中行之后
        Sort Key: ts_rank(...) DESC
        ->  Bitmap Heap Scan     ← 索引只负责这一步：筛出命中行
              Recheck Cond: (tsv @@ q)
              ->  Bitmap Index Scan
                    Index Cond: (tsv @@ q)

因此标准做法是两段式：先用 @@ 让索引把候选集压小，再对这个小结果集排序取前 N。

ORDER BY ts_rank(...) DESC LIMIT 10 的性能陷阱

直觉上 LIMIT 10 意味着「只算 10 行」，但排序必须看到所有命中的行才能确定前 10 名。如果查询词很常见（命中几十万行），这次排序就是全量计算 + 全量排序，LIMIT 完全帮不上忙。

缓解手段：

  • 用 ts_rank_cd 加归一化参数（如 32），把分值压到 0~1，便于应用层做「最低分阈值」过滤；
  • 用更严格的 tsquery（加更多词、短语匹配）从源头减少命中行数；
  • 热门查询把相关度预计算成列，或引入支持有序扫描的 RUM 扩展。

各种 tsquery 构造函数都能用索引 ​

原因很简单：它们都产出 tsquery，@@ 的左侧是索引列，右侧是什么函数不影响索引可用性。

函数输入示例产出索引兼容
to_tsquery'postgres & index'精确控制的 tsquery兼容
plainto_tsquery'postgres index'自动 AND 连接兼容
phraseto_tsquery'full text search'短语（要求位置相邻）兼容，但 GIN 无法完全判定短语，必须 Recheck
websearch_to_tsquery'"full text search" -gin'类搜索引擎语法兼容
sql
EXPLAIN (COSTS OFF)
SELECT id FROM ft_demo WHERE tsv @@ phraseto_tsquery('english', 'full text search');
text
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Bitmap Heap Scan on ft_demo
   Recheck Cond: (tsv @@ '''full'' <-> ''text'' <-> ''search'''::tsquery)
   ->  Bitmap Index Scan on idx_ft_demo_tsv
         Index Cond: (tsv @@ '''full'' <-> ''text'' <-> ''search'''::tsquery)
(4 rows)

Recheck Cond 在全文检索里是常态，不是异常

GIN 的 tsvector 索引只回答「这些词位是否都出现过」，回答不了「它们的位置是否满足 <-> 的相邻要求」。所以短语、ts_rank 之类依赖位置的条件，都要回表用完整的 tsvector 再判一次。计划里出现 Recheck Cond 说明索引正在正常工作，而不是失效。

ts_headline：高亮不走索引 ​

ts_headline 负责把命中的词用标签包起来，它必须在原始文本上工作，因此：

sql
SELECT id,
       ts_headline('english', body, to_tsquery('english', 'index'),
                   'StartSel=<mark>, StopSel=</mark>, MaxWords=20, MinWords=10') AS snippet
FROM ft_demo
WHERE tsv @@ to_tsquery('english', 'index');
text
 id |                          snippet
----+-----------------------------------------------------------
  2 | A <mark>GIN</mark> <mark>index</mark> accelerates full text search
(1 row)

ts_headline 本身无法用索引加速，也不要放在 WHERE 里当过滤器用——它只应该出现在 SELECT 列表中，且只对已经筛好的少量行执行。它内部会重新做一次分词和定位，成本不低。

pg_trgm：分词解决不了时的补充 ​

有些搜索需求本质上是「模糊匹配」而不是「词匹配」：拼写错误、部分单词、LIKE '%xxx%'、中文无分词环境下的子串检索。这时用 pg_trgm 的三元组索引：

sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_ft_expr_trgm ON ft_expr USING gin (body gin_trgm_ops);
能力全文搜索（tsvector + GIN）三元组（pg_trgm + gin_trgm_ops）
匹配方式整词 / 词干 / 短语任意子串、模糊相似
LIKE '%abc%'不支持支持
拼写容错不支持（除非配词典）支持（similarity()、%）
排序相关度ts_rank / ts_rank_cdsimilarity() / word_similarity()
语言依赖需要对应语言配置语言无关
中文需分词扩展可直接用（但索引可能很大）

两者不冲突，可以各建一个索引用在不同查询路径上。三元组索引的体积与文本长度强相关，长正文上可能膨胀得比 GIN 全文索引还大，务必实测。

中文全文搜索的现实 ​

PostgreSQL 内置配置不支持中文分词——没有空格，整段中文会被当成一个词位，中文全文搜索实际不可用。可选方案：

方案机制优点缺点
zhparser基于 SCWS，提供 chinese_zh 配置纯 SQL 使用，与 tsvector 体系无缝需编译安装，词典需自行维护，非官方扩展
pg_jieba基于 cppjieba，提供 jiebacfg 等配置分词质量较好需编译安装，非官方扩展
pg_trgm三元组子串匹配零依赖、语言无关对长文本排序质量差、索引大
外部搜索引擎ES / Meilisearch 等分词、相关度、聚合能力最强引入新组件与数据同步问题
sql
-- 安装 zhparser 后（需先在数据库中注册配置）
CREATE TEXT SEARCH CONFIGURATION chinese_zh (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION chinese_zh ADD MAPPING FOR n,v,a,i,e,l WITH simple;

-- 之后的用法与英文完全一致，GIN 索引也照旧
CREATE INDEX idx_docs_zh_tsv ON docs USING gin (to_tsvector('chinese_zh', content));

选型建议：数据量不大、要求不高用 pg_trgm；要求中文分词质量用 zhparser 或 pg_jieba；搜索是核心业务（需要高亮、纠错、聚合、多语言）就上外部搜索引擎。

观察建索引进度 ​

给大表建 GIN 全文索引可能耗时很久，PostgreSQL 12+ 提供了实时进度视图：

sql
SELECT phase,
       tuples_done, tuples_total,
       blocks_done, blocks_total,
       round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS percent
FROM pg_stat_progress_create_index;
text
      phase       | tuples_done | tuples_total | blocks_done | blocks_total | percent
------------------+-------------+--------------+-------------+--------------+---------
 building index   |      412083 |      1000000 |      102912 |       250000 |    41.2
(1 row)

在另一个会话里反复执行即可监控。maintenance_work_mem 调大能显著加快这个过程（GIN 的批量构建与排序都在内存里做）。

实战示例 ​

场景：文章站点的搜索，要求标题命中排在正文命中前面，并且只搜已发布的文章。

第 1 步：建表与加权生成列 ​

sql
DROP TABLE IF EXISTS articles CASCADE;

CREATE TABLE articles (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title      text NOT NULL,
  summary    text,
  body       text NOT NULL,
  author     text NOT NULL,
  status     text NOT NULL DEFAULT 'published',
  created_at timestamptz NOT NULL DEFAULT now(),
  search_vec tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title,   '')), 'A') ||
    setweight(to_tsvector('english', coalesce(summary, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(body,    '')), 'C')
  ) STORED
);
text
DROP TABLE
CREATE TABLE

coalesce 是必需的：NULL 无法参与 ||，否则整行的 search_vec 会变成 NULL。

第 2 步：建 GIN 索引并插入数据 ​

sql
CREATE INDEX idx_articles_search ON articles USING gin (search_vec);
ANALYZE articles;
text
CREATE INDEX
ANALYZE

第 3 步：验证加权是否生效 ​

sql
INSERT INTO articles (title, summary, body, author) VALUES
  ('PostgreSQL GIN Index Deep Dive',
   'How GIN accelerates token matching',
   'This article explains the inverted structure behind GIN and why it beats B-tree for multi-value columns.',
   'alice'),
  ('Tuning PostgreSQL Queries',
   'A practical guide',
   'We cover the GIN index, partial index, and the planner behaviour of token matching in production.',
   'bob'),
  ('Redis Caching Patterns',
   'Cache strategies',
   'Not related to the database index topic, but useful background reading for backend engineers.',
   'carol');

SELECT id, left(title, 32) AS title
FROM articles
WHERE search_vec @@ to_tsquery('english', 'index');
text
 id |              title
----+----------------------------------
  1 | PostgreSQL GIN Index Deep Dive
  2 | Tuning PostgreSQL Queries
(2 rows)

第 1 行标题里直接含 index（权重 A），第 2 行正文里含 index（权重 C）。看词位就一目了然：

sql
SELECT id,
       (SELECT string_agg(lexeme || ':' || array_to_string(positions, ','), ' ')
        FROM unnest(search_vec) AS u(lexeme, positions, weights)
        WHERE 'index' = lexeme) AS index_lexemes
FROM articles
WHERE search_vec @@ to_tsquery('english', 'index')
ORDER BY id;
text
 id | index_lexemes
----+---------------
  1 | index:2
  2 | index:31
(2 rows)

第 4 步：「标题优先 + 正文命中」的排序检索 ​

sql
WITH q AS (SELECT websearch_to_tsquery('english', 'postgresql index') AS query)
SELECT a.id,
       left(a.title, 32) AS title,
       round(ts_rank(a.search_vec, q.query)::numeric, 4) AS rank
FROM articles a, q
WHERE a.search_vec @@ q.query
ORDER BY rank DESC
LIMIT 5;
text
 id |              title               |  rank
----+----------------------------------+--------
  1 | PostgreSQL GIN Index Deep Dive   | 0.3032
  2 | Tuning PostgreSQL Queries        | 0.1210
(2 rows)

第 1 行的 title 命中 PostgreSQL 与 index，两个都落在权重 A；第 2 行的 index 落在正文（C）——分差就是这么来的。

用 EXPLAIN (ANALYZE) 看执行计划，可以清楚看到「索引只负责筛选，排序在内存里做」：

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
WITH q AS (SELECT websearch_to_tsquery('english', 'postgresql index') AS query)
SELECT a.id, a.title
FROM articles a, q
WHERE a.search_vec @@ q.query
ORDER BY ts_rank(a.search_vec, q.query) DESC
LIMIT 5;
text
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Limit (actual rows=2 loops=1)
   Buffers: shared hit=12
   CTE q
     ->  Result (actual rows=1 loops=1)
   ->  Sort (actual rows=2 loops=1)
         Sort Key: (ts_rank(a.search_vec, q.query)) DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=12
         ->  Nested Loop (actual rows=2 loops=1)
               Buffers: shared hit=12
               ->  CTE Scan on q (actual rows=1 loops=1)
               ->  Bitmap Heap Scan on articles a (actual rows=2 loops=1)
                     Recheck Cond: (search_vec @@ q.query)
                     Heap Blocks: exact=1
                     ->  Bitmap Index Scan on idx_articles_search (actual rows=2 loops=1)
                           Index Cond: (search_vec @@ q.query)
(14 rows)

Sort 出现在 Bitmap Heap Scan 之上——这就是「排序用不上索引」的可视化证据。如果匹配行数很大，这个 Sort 会成为瓶颈。

第 5 步：只搜已发布文章（部分索引） ​

搜索几乎总是带业务过滤条件。用部分索引让索引只覆盖被搜索的子集，体积和维护成本都更小：

sql
CREATE INDEX idx_articles_search_published
  ON articles USING gin (search_vec)
  WHERE status = 'published';
text
CREATE INDEX

部分索引要命中，WHERE 条件必须「可被证明蕴含」

优化器只有在能推导出「查询条件隐含 status = 'published'」时才会用这个索引。status = 'published' 能命中；status = ANY(ARRAY['published']) 通常能命中；status <> 'draft'、status = upper('published')、coalesce(status,'') = 'published' 这类往往不能。写查询时尽量用与索引谓词字面一致的等价条件。

第 6 步：短语搜索与高亮 ​

sql
WITH q AS (SELECT websearch_to_tsquery('english', '"token matching"') AS query)
SELECT a.id,
       left(a.title, 28) AS title,
       ts_headline('english', a.body, q.query,
                   'StartSel=<mark>, StopSel=</mark>, MaxWords=14, MinWords=6') AS snippet
FROM articles a, q
WHERE a.search_vec @@ q.query;
text
 id |             title            |                   snippet
----+------------------------------+---------------------------------------------
  2 | Tuning PostgreSQL Queries    | We cover the GIN <mark>index</mark>, partial ...
  1 | PostgreSQL GIN Index Deep ... | ... the inverted structure behind GIN ...
(2 rows)

（ts_headline 的高亮结果取决于实际文本与 MaxWords 设置，此处只示意格式。）

第 7 步：看索引体积与维护成本 ​

sql
SELECT indexrelid::regclass                            AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid))     AS index_size,
       idx_scan,
       idx_tup_read
FROM pg_stat_user_indexes
WHERE relname = 'articles'
ORDER BY pg_relation_size(indexrelid) DESC;
text
         index_name          | index_size | idx_scan | idx_tup_read
-----------------------------+------------+----------+--------------
 idx_articles_search         | 32 kB      |        4 |            8
 idx_articles_search_published | 16 kB     |        0 |            0
(2 rows)

全文索引的体积通常与表的文本总量接近甚至更高，加上 idx_scan 可以判断每个索引是否真的在用——没被用过的 GIN 索引纯属净负债。

注意事项 ​

  • 不要用单参数 to_tsvector(col) 建索引，会直接报错：

    sql
    -- 错误！
CREATE INDEX idx_bad ON t USING gin (to_tsvector(body));
    text
    ERROR:  functions in index expression must be marked IMMUTABLE

    单参数版本依赖 default_text_search_config，是 STABLE 而非 IMMUTABLE。

  • 索引配置与查询配置必须一致，否则索引照用、结果为空，且没有任何报错。生成列方案能从根上避免。

  • 表达式索引的查询必须逐字复刻。coalesce、拼接顺序、空格、regconfig 写法任一不同都会导致 Seq Scan。

  • ORDER BY ts_rank(...) 无法走索引。不要以为加了 LIMIT 就不用排序了。

  • ts_headline 不要出现在 WHERE 里，它无法用索引，且成本很高。

  • 短语查询一定会出现 Recheck Cond，这是 GIN 的正常行为，不用优化掉。

  • GIN 不支持多列索引。需要「全文 + 其他列」时，用部分索引，或把其他列并入表达式（但那样查询条件必须一并复刻）。

  • 中文需额外扩展。内置配置对中文不可用，别指望默认配置能搜到中文内容。

  • 建大表 GIN 索引用 CONCURRENTLY 并校验结果：

    sql
    SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE indrelid = 'articles'::regclass AND NOT indisvalid;

    若出现 indisvalid = false 的索引，必须 DROP 后重建。

  • 批量导入前后调整 fastupdate，导入完成后手动合并 pending list，否则第一次查询会被拖慢：

    sql
    SELECT gin_clean_pending_list('idx_articles_search');

  • ts_rank 的权重数组顺序是 {D, C, B, A}，写自定义权重时最容易搞反：

    sql
    -- 分别给 D / C / B / A 赋值
ts_rank('{0.05, 0.2, 0.4, 1.0}'::float4[], search_vec, query)

最佳实践 ​

  • 优先用生成列（PostgreSQL 12+）而不是表达式索引。表达式只写一次，查询只写列名：

    sql
    search_vec tsvector GENERATED ALWAYS AS (
  setweight(to_tsvector('english', coalesce(title,   '')), 'A') ||
  setweight(to_tsvector('english', coalesce(summary, '')), 'B') ||
  setweight(to_tsvector('english', coalesce(body,    '')), 'C')
) STORED

  • 把 regconfig 当成常量集中管理。不要在查询里随手写 'english'，而是与应用层的配置项保持一致。

  • 一定要给 coalesce，否则任一字段为 NULL 会让整行失去可搜索性。

  • 两段式查询：@@ 走索引筛，ts_rank 只对小结果集排序：

    sql
    WITH q AS (SELECT websearch_to_tsquery('english', $1) AS query)
SELECT a.id, a.title, ts_rank(a.search_vec, q.query) AS rank
FROM articles a, q
WHERE a.search_vec @@ q.query
ORDER BY rank DESC
LIMIT 20;

  • 用部分索引匹配业务过滤条件，让「只搜已发布/未删除数据」这类高频查询落在更小的索引上。

  • 搜索入口统一用 websearch_to_tsquery。它兼容 "短语"、-排除、or 等用户熟悉的语法，且不会因为用户乱输入而报语法错误（对比 to_tsquery，用户输入 ( 就直接抛异常）：

    sql
    -- 用户输入包含特殊字符时，to_tsquery 会报错
SELECT to_tsquery('english', 'index (');
    text
    ERROR:  syntax error in tsquery: "index ("

  • ts_headline 只用于展示层，且限定 MaxWords，避免对大正文做全量处理。

  • 中文场景先做技术选型再动手：pg_trgm（零依赖）→ zhparser / pg_jieba（要分词质量）→ 外部搜索引擎（搜索是核心业务）。

  • 建完索引立刻 ANALYZE，并定期用 pg_stat_user_indexes 检查 idx_scan，清理从未使用的全文索引。

下一节

继续学习：GIN 索引与 JSONB / 数组

参考链接 ​