使用pg_trgm在3亿个地址中进行搜索

Question

我有一个PostgreSQL 9.3数据库，其中包含3亿个地址。我想使用pg_trgm模糊搜索这些行，最终目的是实现类似Google地图搜索的搜索功能。

当我使用pg_trgm搜索这些地址时，需要大约30秒才能获取结果。虽然有许多行与默认相似度阈值条件0.3匹配，但我只需要大约5到10个结果。因此我创建了一个三元组GiST索引：

CREATE INDEX addresses_trgm_index ON addresses USING gist (address gist_trgm_ops);

这是我的查询：

SELECT address, similarity(address, '981 maun st') AS sml 
FROM addresses 
WHERE address % '981 maun st' 
ORDER BY sml DESC 
LIMIT 10;

生产环境上的测试表已被删除。我展示了我的测试环境中的EXPLAIN输出。大约有700万行数据，需要约1.6秒才能获取结果。当数据达到3亿时，需要超过30秒。

ebdb=> explain analyse select address, similarity(address, '781 maun st') as sml from addresses where address % '781 maun st' order by sml desc limit 10;
                                    QUERY PLAN                                                                            
————————————————————————————————————————————————————————————————————————————————    
 Limit  (cost=7615.83..7615.86 rows=10 width=16) (actual time=1661.004..1661.010 rows=10 loops=1)
 ->  Sort  (cost=7615.83..7634.00 rows=7268 width=16) (actual time=1661.003..1661.005 rows=10 loops=1)
     Sort Key: (similarity((address)::text, '781 maun st'::text))
     Sort Method: top-N heapsort  Memory: 25kB
     ->  Index Scan using addresses_trgm_index on addresses  (cost=0.41..7458.78 rows=7268 width=16) (actual time=0.659..1656.386 rows=5241 loops=1)
           Index Cond: ((address)::text % '781 maun st'::text)
 Total runtime: 1661.066 ms
(7 rows)

有没有一种好的方法来提高性能，或者进行表分区是一个好计划吗？

- Gary Tao

---

“我只需要大约5到10个结果”…你是否在查询中设置了适当的LIMIT限制？ - David Aldridge

在Postgres 9.3中，分区功能是可用的，但是它是使用表继承来实现的。在postgres 10中，分区功能则明确地提供了支持。 - VynlJunkie

@DavidAldridge，是的，我也会考虑升级数据库并使用并行方式。 - Gary Tao

请**[编辑]您的问题，并添加使用explain (analyze, verbose)**生成的执行计划。请使用格式化文本，不要使用屏幕截图。 - user330315

@a_horse_with_no_name 已更新问题。非常感谢听取任何建议。 - Gary Tao

Answer 1

PostgreSQL 9.3 ... 有什么好的方法可以提高性能吗？或者做表分区是否是一个好的计划？

表分区不会对性能有任何帮助。但是，有一个好的方法： 升级 到当前版本的Postgres。GiST索引进行了许多改进，尤其是pg_trgm模块和大数据方面。使用Postgres 10应该会更快。

你的“最近邻居”搜索看起来很正确，但如果使用小的LIMIT，请改用此等效查询：

SELECT address, similarity(address, '981 maun st') AS sml 
FROM   addresses 
WHERE  address % '981 maun st' 
ORDER  BY <b>address <-> '981 maun st'</b>
LIMIT  10;

引用手册：

当只需要少量最接近的匹配项时，通常会优于第一个公式。

- Erwin Brandstetter