sql - 对于 pg_trgm，PostgreSQL GIN 索引比 GIST 慢？

Question

尽管所有文档都这么说，但我发现 GIN 索引在 pg_trgm 相关搜索中比 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(目前是测试版)。自 Pg 9.4 以来，对 GIN 索引、附加模块 pg_trgm 和大数据进行了多项改进。

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

Heap Blocks: exact=7625 lossy=223807

“有损” 位图堆扫描(带有您的特定数字)的详细信息表明 work_mem 严重不足。 Postgres 只收集位图索引扫描中的 block 地址，而不是行指针，因为使用较低的 work_mem 设置(无法在 RAM 中保存确切地址)预计会更快。在接下来的 Bitmap Heap Scan 中，必须过滤掉更多不符合条件的行。这个相关的答案有详细信息:

• “Recheck Cond:” line in query plans with a bitmap index scan

但不要在不考虑整体情况的情况下将 work_mem 设置得太高:

• Optimize simple query using ORDER BY date and text

可能还有其他问题，如索引或表膨胀或更多配置瓶颈。但是，如果您只修复这两项，查询应该已经多快了。

此外，您真的需要检索示例中的所有 40k 行吗？您可能想在查询中添加一个小的 LIMIT 并使其成为“最近邻”搜索 - 在这种情况下，GiST 索引毕竟是更好的选择，因为that 使用 GiST 索引应该会更快。示例:

• Best index for similarity function