PostgreSQL 教程

Appearance

12.2. 表和索引# ​

12.2.1. 搜索表
12.2.2. 创建索引

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

12.2.1. 搜索表# ​

可以在没有索引的情况下执行全文搜索。要打印包含单词friend的title的每一行的简单查询，在body字段中为

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. 创建索引# ​

我们可以创建一个GIN索引（第 12.9 节）以加快文本搜索

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

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

由于索引中使用了to_tsvector的两个参数版本，因此仅使用具有相同配置名称的to_tsvector的 2 个参数版本的查询引用将使用该索引。即，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表示形式不会显式存储。