Postgresql 中文操作指南

12.2. Tables and Indexes #

上一节中的示例说明如何使用简单的常量字符串进行全文匹配。本节介绍了如何搜索表格数据，可选择使用索引。

可以执行不需要索引的全文搜索。打印包含 title 的每个行的 friend 域的简单查询是：

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 设置的配置。

更复杂的示例是选择包含 create 和 table 中的 title 或 body 的最近十份文档：

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. Creating Indexes #

我们可以创建一个 GIN 索引 ( Section 12.9) 以加快文本搜索速度：

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

请注意使用了 to_tsvector 的 2 个参数版本。只有指定配置名称的文本搜索函数才能在表达式索引 ( Section 11.7) 中使用。这是因为索引内容不能受 default_text_search_config 影响。如果受到影响，索引内容可能会不一致，因为不同的条目可能包含使用不同的文本搜索配置创建的 _tsvector_s，并且无法猜测哪一个是什么。将此类索引正确转储和恢复是不可能的。

由于上述索引中使用了_to_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 索引更重要；参见 Section 12.9。）但是，表达式索引方法设置起来更简单，并且需要的磁盘空间更少，因为未明确存储 tsvector 表示形式。