PostgreSQL 8.3文档-SQL语言-全文检索-8.3 12.2 表和索引

From PgsqlWiki

Jump to: ,

 表和索引

前面一节的例子演示了使用简单的常量字串进行全文匹配。本节显示如何搜索表数据，还有是如何使用索引。

 搜索一个表

我们可以不用索引来做全文检索。一个简单的，打印 body 字段里包含有单词 friend 的所有行的标题的查询看上去像这样：

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

这样还会找到相关的单词，比如 friends 和 friendly，因为所有这些词都归纳为同一个规范化的语意（lexeme）。

上面的查询声明了使用 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 类型的数据行的时候。

尽管这些查询不用索引也可以使用，但是大多数应用会发现这个方法太慢了，除非是偶尔的特定搜索。使用全文索引的实际情况通常要求创建索引。

创建索引

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

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

请注意我们使用了 2 参数的 to_tsvector 版本。只有声明了配置名称的搜索函数可以在表达式索引（）里面使用。这事因为索引内容不能被 default_text_search_config 影响。如果它们被影响，那么索引内容可能就不一致，因为不同的条目可能包含使用不同的文本搜索配置生成的 tsvector，而这样就没有办法猜测是什么了。这样的索引是无法正确转储和恢复的。

因为在上面的索引里面使用了 2 参数的 to_tsvector 版本，所以，只有使用了同样的配置参数的 2 参数版本的 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;

在使用独立字段存储 tsvector 形式的时候，我们需要创建一些触发器以保持 tsvector 字段对 title 和 body 修改的同步。解释了如何实现这个。

分离字段方式比表达式索引方式的一个优点是它不需要在查询里明确声明文本搜索的配置就可以使用索引。如上面例子所示，查询可以依赖于default_text_search_config。另外一个优点是搜索会更快，因为它不需要重新调用 to_tsvector 函数来判断是否和索引匹配。（这点在使用 GiST 索引而不是 GIN 索引的时候更重要；参阅。）不过，表达式索引的方法的优点是设置简单，而且它需要的磁盘空间更烧，因为 tsvector 表现形式不用明确存储。

取自""