Postgres trigram搜索速度很慢

Question

我有一个大约有300万行的表。我在表的多个列上创建了单个gin索引。

CREATE INDEX search_idx ON customer USING gin (name gin_trgm_ops, id gin_trgm_ops, data gin_trgm_ops)

我正在运行以下查询(简化为在条件中使用单列)，但需要大约4秒：

EXPLAIN ANALYSE
SELECT c.id, similarity(c.name, 'john') sml
FROM customer c WHERE
c.name % 'john'
ORDER BY sml DESC
LIMIT 10

输出查询计划为：

Limit (cost=9255.12..9255.14 rows=10 width=30) (actual time=3771.661..3771.665 rows=10 loops=1)
  -> Sort (cost=9255.12..9260.43 rows=2126 width=30) (actual time=3771.659..3771.661 rows=10 loops=1)
       Sort Key: (similarity((name)::text, 'john'::text)) DESC
       Sort Method: top-N heapsort Memory: 26kB
       -> Bitmap Heap Scan on customer c (cost=1140.48..9209.18 rows=2126 width=30) (actual time=140.665..3770.478 rows=3605 loops=1)
            Recheck Cond: ((name)::text % 'john'::text)
            Rows Removed by Index Recheck: 939598
            Heap Blocks: exact=158055 lossy=132577
            -> Bitmap Index Scan on search_idx (cost=0.00..1139.95 rows=2126 width=0) (actual time=105.609..105.610 rows=458131 loops=1)
                 Index Cond: ((name)::text % 'john'::text)
Planning Time: 0.102 ms

我不能理解为什么在第一步中从search_idx检索行时没有排序并将其LIMITed为10，然后从customer表中只提取了10行(而不是2126行)。

有没有什么想法可以让这个查询更快。我尝试了gist index，但我看不到性能提升。我还尝试将work_mem从4MB增加到32MB，我可以看到性能提高了1秒，但不会更多。我还注意到，即使我删除了SELECT子句中的c.id，postgres也不会执行仅索引扫描，仍然会与主表连接。

谢谢你的帮助。

Update1:在下面的Laurenz Albe建议之后，查询性能提高了，现在大约是600ms。计划现在看起来是这样的：

Subquery Scan on q  (cost=0.41..78.29 rows=1 width=12) (actual time=63.150..574.536 rows=10 loops=1)
  Filter: ((q.name)::text % 'john'::text)
  ->  Limit  (cost=0.41..78.16 rows=10 width=40) (actual time=63.148..574.518 rows=10 loops=1)
        ->  Index Scan using search_name_idx on customer c  (cost=0.41..2232864.76 rows=287182 width=40) (actual time=63.146..574.513 rows=10 loops=1)
              Order By: ((name)::text <-> 'john'::text)
Planning Time: 42.671 ms
Execution Time: 585.554 ms

Answer 1

要获得具有索引支持的10个最接近的匹配项，您应该创建一个GiST索引并像这样查询：

SELECT id, sml
FROM (SELECT c.id,
             c.name,
             similarity(c.name, 'john') sml
      FROM customer c
      ORDER BY c.name <-> 'john'
      LIMIT 10) AS q
WHERE name % 'john';

子查询可以使用GiST索引，而外部查询将消除所有不超过pg_trgm.similarity_threshold的结果。