PostgreSQL的GIN索引比pg_trgm的GIST慢吗？

Question

尽管所有文档都说了什么，但我发现pg_trgm相关搜索的GIN索引要比GIST索引慢得多。该表位于2500万行的表中，该表的文本字段相对较短（平均长度为21个字符）。文本的大多数行都是“
123 Main st，City”格式的地址。

GIST索引大约需要4秒钟的搜索时间，例如

select suggestion from search_suggestions where suggestion % 'seattle';

但是，使用以下命令运行时，GIN需要90秒，并且得到以下结果EXPLAIN ANALYZE：

Bitmap Heap Scan on search_suggestions  (cost=330.09..73514.15 rows=25043 width=22) (actual time=671.606..86318.553 rows=40482 loops=1)
  Recheck Cond: ((suggestion)::text % 'seattle'::text)
  Rows Removed by Index Recheck: 23214341
  Heap Blocks: exact=7625 lossy=223807
  ->  Bitmap Index Scan on tri_suggestions_idx  (cost=0.00..323.83 rows=25043 width=0) (actual time=669.841..669.841 rows=1358175 loops=1)
        Index Cond: ((suggestion)::text % 'seattle'::text)
Planning time: 1.420 ms
Execution time: 86327.246 ms

请注意，即使实际上只有40k行匹配，索引也选择了超过一百万行。有什么想法为什么表现这么差吗？这是在PostgreSQL 9.4上。

Answer 1

一些问题很突出：

首先，考虑升级到 Postgres 的 当前版本 。在撰写本文时为pg 9.6或pg 10（当前为beta）。自Pg
9.4起，对GIN索引，附加模块pg_trgm和大数据进行了多次改进。

接下来，您需要更多的 RAM ，尤其是更高的 work_mem 设置。从EXPLAIN输出中的这一行可以看出：

Heap Blocks: exact=7625 lossy=223807

___位图堆扫描_ （带有您的特定编号）的详细信息中的 “有损”
表示严重短缺work_mem。Postgres仅在位图索引扫描中收集块地址，而不是在行指针中收集，因为在work_mem设置较低的情况下，这样做预计会更快（无法在RAM中保存确切的地址）。这样，必须在下面的
位图堆扫描中 过滤更多的不合格行。这个相关的答案有详细信息：

• 使用位图索引扫描在查询计划中的“凌晨检查条件：”行

但是，在不考虑整体情况的情况下，不要设置work_mem 得 太高：

• 使用ORDER BY日期和文本优化简单查询

可能还有其他问题，例如索引或表膨胀或更多配置瓶颈。但是，如果你只是解决这两个项目，查询应该是 多 快了。

另外，您是否真的需要检索示例中的所有40k行？你可能要一个小添加 LIMIT 到查询，并使其成为“近邻”搜索-
在这种情况下，其主旨在于指数毕竟是更好的选择，因为 这 应该是与要旨的索引快。例子：

• 相似度函数的最佳索引