字典单词排序列表的最佳索引

Question

我有一个包含两个列词的表：ID 序列和德语单词列表"word"(按sort命令排序，然后是 COPY'ied(：

Aachen
Aachener
Aachenerin
Aachenerinnen
Aachenern
Aacheners
Aachens

目标是列出包含特定字符串的所有单词，f.i.：

SELECT word FROM german_words WHERE word ~ 'chen';

对于"word"列，我使用了正常的唯一索引，此类查询需要 1 到 2 秒(1.9M 行(。PostgreSQL中是否有一种索引策略可以使其更快？

Answer 1

对于此类查询，以下索引将是最佳选择：

CREATE INDEX ON words USING gin (word gin_trgm_ops);

这需要您安装pg_trgm扩展。

从文档中：

F.31.4. 索引支持

pg_trgm模块提供 GiST 和 GIN 索引运算符类，允许您在文本列上创建索引，以便进行非常快速的相似性搜索。这些索引类型支持上述相似性运算符，并且还支持基于 trigram 的索引搜索 LIKE、ILIKE、~ 和 ~* 查询。

---

您可能会得到的执行计划如下：

+----+---------------------------------------------------------------------------------------------------------------------------+ |   |查询计划 | | 1 |字字上的位图堆扫描(成本=20.47..70.73 行=60 宽度=36((实际时间=0.147..0.432 行=182 循环=1(| | 2 |  复查 Cond： (单词 ~ 'chen'：：文本( | | 3 |  通过索引重新检查删除的行：2 | | 4 |  堆块：精确 = 42 | | 4 ]  ->位图索引扫描words_word_idx(成本=0.00..20.45行=60宽度=0((实际时间=0.129..0.129行=184循环=1(| | 6 |        索引 Cond： (单词 ~ 'chen'：：文本( | | 7 |规划时间： 0.133 ms | | 8 |执行时间：0.476 ms | +----+---------------------------------------------------------------------------------------------------------------------------+

您可以在Rextester上查看

这里