在数据库表中搜索文本
- 搜索表
- 创建索引

在数据库表中搜索文本

本主题说明如何使用文本搜索运算符搜索数据库表以及如何创建索引以加快文本搜索。

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

本节包含以下子主题：

搜索表
创建索引

搜索表

可以在没有索引的情况下进行全文搜索。在其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;

为清楚起见，我们省略了在两个字段之一中查找包含NULL的行所需的coalesce函数调用。

虽然这些查询在没有索引的情况下可以正常工作，但大多数应用程序会发现这种方法太慢，除非是偶尔的临时搜索。文本搜索的实际使用通常需要创建索引。

创建索引

我们可以创建一个GIN索引（文本搜索的GiST和GIN索引）来加速文本搜索：

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

请注意，使用了to_tsvector的双参数版本。只能在表达式索引中使用指定配置名称的文本搜索功能。这是因为索引内容必须不受default_text_search_config的影响。如果它们受到影响，索引内容可能会不一致，因为不同的条目可能包含使用不同文本搜索配置创建的tsvector，并且无法猜测哪个是哪个。正确地导出和恢复这样的索引是不可能的。

因为在上面的索引中使用了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;
UPDATE pgweb SET textsearchable_index_col =
     to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''));

然后我们创建一个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索引时，这一点更为重要;请参阅文本搜索的GiST和GIN索引。）然而，表达式索引方法设置起来更简单，并且由于tsvector不是显式存储，因此需要更少的磁盘空间。

Parent topic: 使用全文搜索