PostgreSQL 全文检索之 - 位置匹配过滤语法 (如 '速度 <1> 激情')-阿里云开发者社区

PostgreSQL 全文检索之 - 位置匹配过滤语法 (如 '速度 <1> 激情')

2018-01-27 2374

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云原生数据库 PolarDB MySQL 版，Serverless 5000PCU 100GB

云原生数据库 PolarDB 分布式版，标准版 2核8GB

云数据库 RDS MySQL Serverless，0.5-2RCU 50GB

简介：

背景

PostgreSQL 的全文检索功能非常的丰富，除了我们说的开放式的parser，字典。同时还支持ranking算法（内置以及扩展）。

那么实际上在搜索的需求中，还有一类位置过滤的需求，例如“速度与激情”这个词，分词后是有位置信息的，我们期望搜到的是“速度”和“激情”之间间隔一个的情况，提高精准度。

PostgreSQL的搜索距离的语法如下：

select * from tbl where ts @@ '速度 <距离值> 激情'::tsquery;  
  
如  
  
select * from tbl where ts @@ '速度 <1> 激情'::tsquery;

例子

首先需要一个中文分词器，RDS PG自带，无需下载。

https://github.com/jaiminpan/pg_scws

create extension pg_scws;

1、创建测试表

postgres=# create table ts_test (id int, info text, ts tsvector);  
CREATE TABLE

2、写入测试数据

postgres=# insert into ts_test values (1, '激情，创新，坚持，速度-- 北京北方创业出租汽车公司优质服务小花絮', to_tsvector('scwscfg', '激情，创新，坚持，速度-- 北京北方创业出租汽车公司优质服务小花絮'));  
INSERT 0 1  
postgres=# insert into ts_test values (1, '电影速度与激情8的票房破亿', to_tsvector('scwscfg', '电影速度与激情8的票房破亿'));  
INSERT 0 1

3、查看分词结果，其中分词中已经带上了位置信息。

postgres=# select * from ts_test;  
 id |                              info                               |                                                     ts                                                       
----+-----------------------------------------------------------------+------------------------------------------------------------------------------------------------------------  
  1 | 激情，创新，坚持，速度-- 北京北方创业出租汽车公司优质服务小花絮 | '优质服务':9 '公司':8 '出租汽车':7 '创业':6 '创新':2 '北京':5 '坚持':3 '小花':10 '激情':1 '絮':11 '速度':4  
  1 | 电影速度与激情8的票房破亿                                       | '激情':3 '电影':1 '破':5 '票房':4 '速度':2  
(2 rows)

3、写入更多干扰数据

postgres=# insert into ts_test select 2, '激情，创新，坚持，速度-- 北京北方创业出租汽车公司优质服务小花絮', to_tsvector('scwscfg', '激情，创新，坚持，速度-- 北京北方创业出租汽车公司优质服务小花絮') from generate_series(1,1000000);  
INSERT 0 1000000

4、创建一个支持位置搜索的索引插件rum。

https://github.com/postgrespro/rum

postgres=# create extension rum;  
CREATE EXTENSION

5、创建rum分词索引

postgres=# CREATE INDEX rumidx ON ts_test USING rum (ts rum_tsvector_ops);  
CREATE INDEX

6、接下来见证奇迹，第一个查询是不带位置搜索的，我们看到匹配了所有记录。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from ts_test where ts @@ '速度 & 激情'::tsquery;  
                                                             QUERY PLAN                                                               
------------------------------------------------------------------------------------------------------------------------------------  
 Index Scan using rumidx on public.ts_test  (cost=9.60..41.24 rows=25 width=68) (actual time=223.602..456.956 rows=1000002 loops=1)  
   Output: id, info, ts  
   Index Cond: (ts_test.ts @@ '''速度'' & ''激情'''::tsquery)  
   Buffers: shared hit=38132  
 Planning time: 0.226 ms  
 Execution time: 541.545 ms  
(6 rows)

7、第二个查询是带位置了，只匹配了1条记录，也就是我们要的“速度与激情”。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from ts_test where ts @@ '速度 <1> 激情'::tsquery;  
                                                          QUERY PLAN                                                            
------------------------------------------------------------------------------------------------------------------------------  
 Index Scan using rumidx on public.ts_test  (cost=9.60..41.24 rows=25 width=68) (actual time=270.675..270.677 rows=1 loops=1)  
   Output: id, info, ts  
   Index Cond: (ts_test.ts @@ '''速度'' <-> ''激情'''::tsquery)  
   Buffers: shared hit=1095  
 Planning time: 0.111 ms  
 Execution time: 273.840 ms  
(6 rows)

PostgreSQL 分词就是这么好用

带距离  
  
postgres=# select * from ts_test where ts @@ '速度 <1> 激情'::tsquery;  
 id |           info            |                     ts                       
----+---------------------------+--------------------------------------------  
  1 | 电影速度与激情8的票房破亿 | '激情':3 '电影':1 '破':5 '票房':4 '速度':2  
(1 row)  
  
不带距离  
  
postgres=# select * from ts_test where ts @@ '速度 & 激情'::tsquery limit 5;  
 id |                              info                               |                                                     ts                                                       
----+-----------------------------------------------------------------+------------------------------------------------------------------------------------------------------------  
  1 | 激情，创新，坚持，速度-- 北京北方创业出租汽车公司优质服务小花絮 | '优质服务':9 '公司':8 '出租汽车':7 '创业':6 '创新':2 '北京':5 '坚持':3 '小花':10 '激情':1 '絮':11 '速度':4  
  1 | 电影速度与激情8的票房破亿                                       | '激情':3 '电影':1 '破':5 '票房':4 '速度':2  
  2 | 激情，创新，坚持，速度-- 北京北方创业出租汽车公司优质服务小花絮 | '优质服务':9 '公司':8 '出租汽车':7 '创业':6 '创新':2 '北京':5 '坚持':3 '小花':10 '激情':1 '絮':11 '速度':4  
  2 | 激情，创新，坚持，速度-- 北京北方创业出租汽车公司优质服务小花絮 | '优质服务':9 '公司':8 '出租汽车':7 '创业':6 '创新':2 '北京':5 '坚持':3 '小花':10 '激情':1 '絮':11 '速度':4  
  2 | 激情，创新，坚持，速度-- 北京北方创业出租汽车公司优质服务小花絮 | '优质服务':9 '公司':8 '出租汽车':7 '创业':6 '创新':2 '北京':5 '坚持':3 '小花':10 '激情':1 '絮':11 '速度':4  
(5 rows)