首页 文章

为什么PostgreSQL Text-Search GiST索引比GIN索引慢得多？

提问于
浏览 次
16

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

使用 LIKE 谓词或POSIX正则表达式匹配来搜索120万行的天真方法需要 90-105 seconds （在我的Macbook上）才能对关键字进行全表扫描搜索 .

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

未编制索引的临时文本搜索查询大约需要 8 minutes ：

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

创建GIN索引大约需要 40 minutes ：

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 milliseconds ：

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

但是，当我创建GiST索引时，结果完全不同 . 创建索引需要少于 2 minutes ：

CREATE INDEX PostText_GIN ON Posts USING GIST(PostText);

之后，使用 @@ 文本搜索运算符的查询需要 90-100 seconds . 因此，GiST索引确实将未编制索引的TS查询从8分钟改进为1.5分钟 . 但是，使用 LIKE 进行全表扫描并没有什么改进 . 它在Web编程环境中毫无用处 .

我错过了使用GiST索引至关重要的东西吗？索引是否需要预先缓存在内存中？我正在使用MacPorts的普通PostgreSQL安装，没有调整 .

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

PS：我确实知道像Sphinx Search和Lucene这样的替代品 . 我只是想了解PostgreSQL本身提供的功能 .

performance postgresql full-text-search

3 回答

  • 6

    尝试

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

    这会创建一个适合前缀查询的索引 . 请参阅Operator Classes and Operator Families上的PostgreSQL文档 . @@运算符仅对术语向量有意义;使用LIKE，GiST索引（使用varchar_pattern_ops）将获得出色的结果 .

    回复于
  • 6

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

    回复于
  • 2

    顺便说一句：如果你还没有得到满意的回答，你所做的部分

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

    本来应该

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

    回复于

相关问题