对于pg_trgm，PostgreSQL GIN索引比GIST慢？-Java 学习之路

尽管所有文档都说明了，但我发现GIN索引明显慢于pg_trgm相关搜索的GIST索引 . 这是一个包含2500万行的表，文本字段相对较短（平均长度为21个字符） . 大多数文本行都是“123 Main st，City”形式的地址 .

像搜索一样，GIST索引大约需要4秒

select suggestion from search_suggestions where suggestion % 'seattle';

但是当使用 EXPLAIN ANALYZE 运行时，GIN需要90秒并且结果如下：

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上 .

1 回答

1
一些问题突出：

首先，考虑升级到 current version of Postgres . 在撰写本文的时候是pg 9.6或pg 10（目前为beta） . 自Pg 9.4以来，GIN索引有了多项改进，一般来说附加模块为pg_trgm和大数据 .

接下来，您需要更多 RAM ，特别是更高的 work_mem 设置 . 我可以从 EXPLAIN 输出中的这一行告诉：
```
Heap Blocks: exact=7625 lossy=223807
```
"lossy"在位图堆扫描的详细信息中（带有您的特定数字）表示 work_mem 严重不足 . Postgres只收集位图索引扫描中的块地址而不是行指针，因为对于低 work_mem 设置（不能在RAM中保存确切的地址），预计会更快 . 必须以这种方式在以下位图堆扫描中过滤更多非限定行 . 这个相关答案有详细说明：
- “Recheck Cond:” line in query plans with a bitmap index scan
但是如果不考虑整个情况，不要设置得太高：
- Optimize simple query using ORDER BY date and text
可能还有其他问题，例如索引或表膨胀或更多配置瓶颈 . 但是如果你只修复这两个项目，那么查询应该会快得多 .

另外，您真的需要检索示例中的所有40k行吗？您可能希望在查询中添加一个小 LIMIT 并使其成为"nearest-neighbor"搜索 - 在这种情况下，GiST索引毕竟是更好的选择，因为GiST索引应该更快 . 例：
- Best index for similarity function
回复于 2024-05-05T09:16:04+08:00

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

1 回答

相关问题