performance - 为什么 PostgreSQL Text-Search 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 进行全表扫描相比没有任何改进。它在网络编程环境中毫无用处。

我是否遗漏了使用 GiST 索引的关键内容？索引是否需要预先缓存在内存中？我正在使用 MacPorts 的普通 PostgreSQL 安装，没有进行任何调整。

使用 GiST 索引的推荐方法是什么？还是每个使用 PostgreSQL 做 TS 的人都跳过 GiST 索引而只使用 GIN 索引？

PS:我确实知道 Sphinx Search 和 Lucene 等替代品。我只是想了解 PostgreSQL 本身提供的功能。

Answer 1

如果您有兴趣，文档很好地概述了 GiST 和 GIN 索引之间的性能差异:GiST and GIN Index Types .