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

标签

PostgreSQL , 全文检索 , 位置过滤

---

背景

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)  

参考

《多流实时聚合 - 记录级实时快照 - JSON聚合与json全文检索的功能应用》

《PostgreSQL - 全文检索内置及自定义ranking算法介绍 与案例》

《用PostgreSQL 做实时高效 搜索引擎 - 全文检索、模糊查询、正则查询、相似查询、ADHOC查询》

《HTAP数据库 PostgreSQL 场景与性能测试之 14 - (OLTP) 字符串搜索 - 全文检索》

《HTAP数据库 PostgreSQL 场景与性能测试之 7 - (OLTP) 全文检索 - 含索引实时写入》

《多国语言字符串的加密、全文检索、模糊查询的支持》

《全文检索 不包含 优化 - 阿里云RDS PostgreSQL最佳实践》

《PostgreSQL 10.0 preview 功能增强 - JSON 内容全文检索》

《PostgreSQL 全文检索加速 快到没有朋友 - RUM索引接口(潘多拉魔盒)》

《PostgreSQL 行级 全文检索》

《PostgreSQL chinese full text search 中文全文检索》