为什么PostgreSQL的文本搜索GiST索引比GIN索引慢这么多？

Question

我正在测试PostgreSQL的文本搜索功能，使用StackOverflow的9月数据转储作为样本数据。 :-)

使用LIKE谓词或POSIX正则表达式匹配进行搜索120万行的朴素方法需要大约90-105秒（在我的Macbook上）来进行全表扫描以搜索关键字。

SELECT * FROM Posts WHERE body LIKE '%postgresql%';
SELECT * FROM Posts WHERE body ~ 'postgresql';

一个未索引的、即兴输入的文本搜索查询需要大约8分钟：

SELECT * FROM Posts WHERE to_tsvector(body) @@ to_tsquery('postgresql'); 

创建一个 GIN 索引大约需要40分钟的时间：

ALTER TABLE Posts ADD COLUMN PostText TSVECTOR;
UPDATE Posts SET PostText = to_tsvector(body);
CREATE INDEX PostText_GIN ON Posts USING GIN(PostText);

(我意识到我也可以通过将其定义为表达式索引来一步完成此操作。)

之后，借助GIN索引辅助的查询运行速度要快得多--这需要约40毫秒：

SELECT * FROM Posts WHERE PostText @@ 'postgresql'; 

但是，当我创建一个GiST索引时，结果大不相同。创建该索引只需不到2分钟：

CREATE INDEX PostText_GIN ON Posts USING GIST(PostText);

使用 @@ 文本搜索运算符的查询需要 90-100 秒。因此，GiST 索引将未索引的 TS 查询时间从 8 分钟缩短至 1.5 分钟。但这和使用 LIKE 进行完全表扫描相比没有任何改进，对于 Web 编程环境来说毫无用处。

我是否遗漏了使用 GiST 索引的重要信息？索引是否需要预先缓存在内存中或其他什么操作？我使用的是 MacPorts 上纯 PostgreSQL 安装，没有进行任何调整。

推荐使用 GiST 索引的方式是什么？还是每个使用 PostgreSQL 的 TS 用户都跳过 GiST 索引只使用 GIN 索引？

PS：我知道 Sphinx Search 和 Lucene 等替代方案。我只是想学习 PostgreSQL 自身提供的功能。

- Bill Karwin

Answer 1

如果您感兴趣，文档中对GiST和GIN索引之间的性能差异进行了很好的概述：GiST和GIN索引类型。

- user236454

Answer 2

尝试

CREATE INDEX PostText_GIST ON Posts USING GIST(PostText varchar_pattern_ops);

这将创建适用于前缀查询的索引。请参阅PostgreSQL文档中关于操作类和操作族的说明。@@运算符仅在术语向量上有意义；使用varchar_pattern_ops的GiST索引将在LIKE查询时提供出色的结果。

- Jonathan Feinberg

---

生成那个索引肯定花了不少时间。 :) - Jonathan Feinberg

这不可能起作用，因为 varchar_pattern_ops 是针对类型为 varchar 的数据而设计的，而 PostText 属于类型为 tsvector 的数据。此外，varchar_pattern_ops 只适用于 btree 和 hash 索引，而不适用于 gist 索引。 - Peter Eisentraut

Answer 3

顺便说一下：如果这个问题还没有得到您满意的答复，那么您所写的部分：

选择所有文章中文本包含 'postgresql' 的行：

应该修改为：

选择所有文章中文本符合 to_tsquery('postgresql') 的行：

- John

---

谢谢你的建议，下次我测试PostgreSQL时会尝试一下。我已经使用MySQL几年了。 - Bill Karwin