PostgreSQL 全文搜索：表和索引-未完纪

上一节使用简单的常量字符串演示全文匹配。本节介绍如何搜索表中的数据，并在需要时使用索引。

12.2.1 搜索表

不使用索引也可以进行全文搜索。下面的简单查询，会输出 body 字段中包含单词 friend 的每一行的 title：


SELECT title
FROM pgweb
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'friend');

它也会找到 friends、friendly 等相关词，因为这些单词都会归约为同一个规范化词素。

上述查询指定使用 english 配置来解析、规范化字符串。也可以省略配置参数：


SELECT title
FROM pgweb
WHERE to_tsvector(body) @@ to_tsquery('friend');

此查询将使用 default_text_search_config 指定的配置。

再看一个更复杂的例子：选择 title 或 body 中同时包含 create 和 table 的最近十篇文档。


SELECT title
FROM pgweb
WHERE to_tsvector(title || ' ' || body) @@ to_tsquery('create & table')
ORDER BY last_mod_date DESC
LIMIT 10;

为了清晰，这里省略了 coalesce 函数调用。如果想找到这两个字段之一包含 NULL 的行，就需要它们。

虽然这些查询无需索引也能工作，但对大多数应用而言，这种方法会太慢，可能只适合偶尔进行的临时搜索。实际使用全文搜索，通常需要创建索引。

12.2.2 创建索引

可以创建 GIN 索引（见第12.9节），加快文本搜索：


CREATE INDEX pgweb_idx ON pgweb USING GIN (to_tsvector('english', body));

注意，这里使用的是两个参数的 to_tsvector。只有明确指定配置名称的文本搜索函数，才能用于表达式索引。这是因为索引内容不能受 default_text_search_config 影响。否则，不同索引项可能包含用不同文本搜索配置生成的 tsvector，导致索引内容不一致，而且无法知道每项使用了哪种配置。这样的索引也无法正确地导出和恢复。

因为上面的索引使用了两个参数的 to_tsvector，查询也只有在使用两个参数形式、并指定同样的配置名称时，才会使用该索引。也就是说，WHERE to_tsvector('english', body) @@ 'a & b' 可以使用索引，而 WHERE to_tsvector(body) @@ 'a & b' 不行。这确保了索引仅被与生成索引项时相同的配置使用。

还可以设置更复杂的表达式索引，由另一列指定配置名称，例如：


CREATE INDEX pgweb_idx ON pgweb USING GIN (to_tsvector(config_name, body));

其中，config_name 是 pgweb 表中的一列。这样，同一索引可以混用多种配置，同时记录每个索引项使用的配置。例如，当文档集合包含不同语言的文档时，这会很有用。同样，希望使用索引的查询，表达式必须与索引匹配，例如 WHERE to_tsvector(config_name, body) @@ 'a & b'。

索引甚至可以连接多个列：


CREATE INDEX pgweb_idx ON pgweb USING GIN (to_tsvector('english', title || ' ' || body));

另一种方法是创建一个独立的 tsvector 列，保存 to_tsvector 的输出。要让该列随源数据自动更新，可以使用存储生成列。下面的例子连接 title 和 body，使用 coalesce，确保其中一个字段为 NULL 时，另一个字段仍会被索引：


ALTER TABLE pgweb
    ADD COLUMN textsearchable_index_col tsvector
               GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;

随后创建 GIN 索引，加快搜索：


CREATE INDEX textsearch_idx ON pgweb USING GIN (textsearchable_index_col);

现在就可以执行快速的全文搜索了：


SELECT title
FROM pgweb
WHERE textsearchable_index_col @@ to_tsquery('create & table')
ORDER BY last_mod_date DESC
LIMIT 10;

与表达式索引相比，独立列方案的一个优点是：为了使用索引，不必在查询中明确指定文本搜索配置。如上例所示，查询可以依赖 default_text_search_config。另一个优点是搜索更快，因为无需再次调用 to_tsvector，来验证索引匹配结果。对 GiST 索引而言，这一点比 GIN 索引更重要，见第12.9节。

不过，表达式索引方案更容易设置，也需要更少磁盘空间，因为它不会显式存储 tsvector 表示。

来源：PostgreSQL 18 — 12.2. Tables and Indexes，The PostgreSQL Global Development Group。本文采用 PostgreSQL 18 文档；current 地址会随最新主版本变化。中文翻译，SQL 保留原文。

PostgreSQL License（以下保留原文许可）：

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN “AS IS” BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

许可来源。

文章版权归作者所有，未经允许请勿转载。

THE END