PostgreSQL 全文索引

本文仅讨论 contrib/fulltextindex 模块的全文索引。contrib/tsearch 模块提供了更高级的索引功能，本文不涉及。

contrib/fulltextindex 模块是在 PostgreSQL 中实现基本文本索引的快速简便方法。该模块在 7.1 及以下版本中可用，并且能够索引每个表的一个列，而在 7.2 版本中能够索引每个表的任意数量的列。

这种形式的全文索引通过记录所有可能的词语后缀来工作。例如，单词 'sydney' 将存储字符串 Sydney、'ydney'、'dney'、'ney' 和 'ey'。这意味着无论用户输入 'Sydney' 的哪个部分，都会在索引中找到它。

无论如何 - 这些都是技术性内容！只需按照以下步骤操作，你甚至不需要了解其工作原理！

安装

配置和安装 PostgreSQL 后，你需要切换到源代码包中的 contrib/fulltextindex 目录。输入 'gmake all' 将编译模块，'gmake install' 将安装它。安装将至少设置一个 fti.so 共享库和一个 fti.pl 索引生成便利脚本。我建议查看 README.fti 文件。

要在数据库中启用全文索引，请执行以下 SQL 代码

CREATE FUNCTION fti() RETURNS opaque AS '/path/to/fti.so' LANGUAGE 'C';

如果希望在创建任何新数据库时自动启用该功能，只需将其添加到 'template1' 数据库中。

在空表上创建索引

要索引一个尚未填充数据的表，你需要执行以下步骤

1. 创建一个新表来存放索引。我建议使用与要索引的表相同的名称，并在后面附加 '_fti'。

例如。

      CREATE TABLE articles_fti (
      	string type,
      	id oid
      );

其中 'type' 应与要索引的字段类型匹配，或者至少与要索引的字段大小相同。请注意，这假设存在 'articles' 表。

2. 正确索引表。这包括索引要索引的表和全文索引表。

例如。

      CREATE INDEX "articles_fti_string_idx" ON articles_fti(string);
      CREATE INDEX "articles_fti_id_idx" ON articles_fti(id); 
      CREATE INDEX "articles_oid_idx" ON articles(oid);

这种索引方案使查找、插入和删除变得快速。

3. 创建在对要索引的表进行更改时触发的触发器，以更新全文索引表。

例如。

      CREATE TRIGGER "articles_fti_trigger" AFTER UPDATE OR INSERT OR DELETE
      ON articles FOR EACH ROW 
      EXECUTE PROCEDURE fti(articles_fti, title, body, keywords);

在本例中，'articles_fti' 是存放 'articles' 索引的表，'title'、'body' 和 'keywords' 是 'articles' 表中要索引的字段。

注意：在 PostgreSQL 7.1 及以下版本中，你只能索引一列，不能索引多列。

4. 现在，你可以在 'articles' 表中随意插入、更新和删除数据，全文索引将保持更新。

索引现有表

在某些情况下，你可能需要索引现有表。你可能需要索引一个已经存在的表，然后使用触发器来保持其更新，或者你可以安排一个 cron 作业来每天重新索引表（或者类似的操作），而不是使用触发器。

无论如何，你都可以使用 fti.pl。

假设上面的 'articles' 表已经包含数据。执行以下步骤来索引它。

1. 执行上面的步骤一，创建全文索引表。

2. 运行 fti.pl：/path/to/fti.pl -d mydb -t articles -c title,body,keywords -f /dev/stdout | sort -u > fti.sql

请注意，在 PostgreSQL 7.1 及以下版本中，'-c' 选项只能指定一个列。

3. 使用 psql 登录到数据库。使用此命令来填充全文索引表。（确保你已经清空了它！）

      \copy articles_fti from fti.sql

4. 现在，执行上面的步骤二、三和四来完成整个过程。

如果你需要从头开始重新生成索引，请记住首先删除索引并重新创建它们 - 这将大大加快速度。

使用索引

现在我们已经创建了索引，是时候使用它进行快速查询了！最好的说明方法是举个例子

假设我们在 articles 表中搜索 'perth' 和 'stralia' 两个词。（用户在 'australia' 的前面省略了 'au'！）

查询的构造方式如下

	SELECT 
		DISTINCT(a.*) 
	FROM 
		articles a, 
		articles_fti f1, 
		articles_fti f2 
	WHERE 
		f1.string ~ '^perth 
		AND f2.string ~ '^stralia' 
		AND f1.id=f2.id 
		AND a.oid=f1.id;

看起来很复杂？嗯，你需要注意一些事项

1. 对于每个使用的关键字，你需要为 'articles_fti' 表添加一个表别名。

2. '~' 操作符表示正则表达式。'^' 表示“匹配单词开头”。这些操作符的巧妙使用确保 PostgreSQL 的 btree 索引能够正常工作。

3. 你必须将所有关键字转换为小写，因为全文索引只存储小写版本。

4. 默认情况下，将索引长度为两个字符或以上的单词。

优化查询

如果你按照上面的方法进行了索引，那么查询应该已经是最优的了。不过，你可以通过在 SELECT 查询前面加上 'EXPLAIN' 来验证这一点。

需要优化的操作有三个：连接全文表和已索引表（基于 id 和 oid）；根据 id 从全文表中删除数据；以及在字符串列中简单地查找匹配的文本。

如果你不喜欢使用触发器来保持索引的更新，你可以完全放弃它，并且只在需要时手动重新生成索引。当然，如果你的数据从未更改过 - 你根本不需要触发器。