PostgreSQL 10 索引 - 通过模糊搜索获得更好的答案

pg_trgm

想在PostgreSQL中使用模糊搜索，应该增加pg_trgm扩展：

postgres=# CREATE EXTENSION pg_trgm;
CREATE EXTENSION

pg_trgm扩展是很强大，为了展示能力，我准备了479785条北京的地点数据，数据保存在：

postgres=# CREATE TABLE t_location (name text);
CREATE TABLE

查看一下数据

postgres=# SELECT * FROM t_location LIMIT 4;
                               name                                
-------------------------------------------------------------------
 郎府社区卫生服务中心老庄户社区卫生站
 府学小学
 春晨宾馆
 十方诸佛宝塔
(4 行记录)

时间：0.367 ms
postgres=# SELECT count(*) FROM t_location;
 count  
--------
 479785
(1 行记录)

pg_trgm提供了不同的运算符，用来计算两个字符串之间的距离：

postgres=# SELECT 'abcde' <-> 'abdeacb';
 ?column? 
----------
 0.833333

可以在查询中使用距离运算找出最匹配的项：

postgres=# SELECT * FROM t_location ORDER BY name <-> '四环' LIMIT 3;
   name   
----------
 四环宾馆
 四环超市
 四环旅馆
(3 行记录)

Time: 1258.368 ms (00:01.258)

对于文本，可以使用GiST索引：

postgres=# CREATE INDEX idx_trgm ON t_location USING GiST(name GiST_trgm_ops);
CREATE INDEX
Time: 6957.277 ms (00:06.957)

pg_trgm为我们提供了GiST_trgm_ops运算符类别，用于相似性搜索。

postgres=# explain SELECT * FROM t_location ORDER BY name <-> '四环' LIMIT 5;
                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Limit  (cost=0.29..0.70 rows=5 width=29)
   ->  Index Scan using idx_trgm on t_location  (cost=0.29..39787.99 rows=479785 width=29)
         Order By: (name <-> '四环'::text)
(3 行记录)

加速LIKE查询

LIKE查询导致了严重的性能问题。在大多数数据库系统里，LIKE使用顺序扫描，所以很慢。
幸运的是，PostgreSQL提供了解决问题的方法：

postgres=# SELECT * FROM t_location WHERE name LIKE '%四环%' LIMIT 5;
           name           
--------------------------
 士毛汽车工厂西四环南路店
 四环陈胖子干鲜果品店
 西四环南路３０号院东门
 四环花木中心Ｄ厅
 四环花木中心Ｇ厅
(5 行记录)

时间：12.306 ms

postgres=# explain SELECT * FROM t_location WHERE name LIKE '%四环%';
                               QUERY PLAN                               
------------------------------------------------------------------------
 Bitmap Heap Scan on t_location  (cost=4.62..162.54 rows=43 width=25)
   Recheck Cond: (name ~~ '%四环%'::text)
   ->  Bitmap Index Scan on idx_trgm  (cost=0.00..4.61 rows=43 width=0)
         Index Cond: (name ~~ '%四环%'::text)
(4 行记录)

前面部署的trigram索引也能为LIKE加速。%可用于搜索字符串的任何位置。

处理正则表达式

Trigram索引还能加速简单的正则表达式。

postgres=# SELECT * FROM t_location WHERE name ~ '.*四环.*０.*' LIMIT 5;
                 name                 
--------------------------------------
 西四环南路３０号院东门
 西四环南路３０号院
 西四环南路３０号院停车场
 西四环南路３０号院西门
 中汽雷日４Ｓ店南四环中路２０号充电站
(5 行记录)

时间：144.825 ms

postgres=# explain SELECT * FROM t_location WHERE name ~ '.*四环.*０.*' LIMIT 5;
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Limit  (cost=0.29..20.84 rows=5 width=25)
   ->  Index Scan using idx_trgm on t_location  (cost=0.29..177.04 rows=43 width=25)
         Index Cond: (name ~ '.*四环.*０.*'::text)
(3 行记录)