PostgreSQL 全文检索之 - 位置匹配 过滤语法(例如 ‘速度 <1> 激情’)
背景
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)
8、如果距离是一个范围，我们同样可以使用UDF来实现过滤。
-- 创建获取某两个词的距离区间的UDF函数。
create or replace function get_lexeme_pos_range(tsvector, text, text) returns int4range as $$
declare
a_pos int[];
b_pos int[];
m1 int;
m2 int;
begin
-- 第一个词的所有出现位置
select positions into a_pos from (select * from unnest($1)) t where lexeme=$2;
-- 第二个词的所有出现位置
select positions into b_pos from (select * from unnest($1)) t where lexeme=$3;
-- 两个词的出现位置的距离，取区间
-- 返回range类型
select min(abs(t1.pos-t2.pos)), max(abs(t1.pos-t2.pos)) into m1,m2 from
(select unnest(a_pos) pos) t1 cross join
(select unnest(b_pos) pos) t2;
return int4range(m1,m2+1);
end;
$$ language plpgsql strict immutable;
-- 测试，取距离是1到2（不含2）的
postgres=# select get_lexeme_pos_range(ts, '速度', '激情'), * from ts_test where ts @@ tsquery '速度 & 激情' and get_lexeme_pos_range(ts, '速度', '激情') && int4range(1,2) limit 1;
get_lexeme_pos_range | id | info | ts
----------------------+----+---------------------------+--------------------------------------------
[1,2) | 1 | 电影速度与激情8的票房破亿 | '激情':3 '电影':1 '破':5 '票房':4 '速度':2
(1 row)
Time: 0.713 ms
-- 测试，取距离是2到5（不含5）的
postgres=# select get_lexeme_pos_range(ts, '速度', '激情'), * from ts_test where ts @@ tsquery '速度 & 激情' and get_lexeme_pos_range(ts, '速度', '激情') && int4range(2,5) limit 1;
get_lexeme_pos_range | id | info | ts
----------------------+----+-----------------------------------------------------------------+------------------------------------------------------------------------------------------------------------
[3,4) | 1 | 激情，创新，坚持，速度-- 北京北方创业出租汽车公司优质服务小花絮 | '优质服务':9 '公司':8 '出租汽车':7 '创业':6 '创新':2 '北京':5 '坚持':3 '小花':10 '激情':1 '絮':11 '速度':4
(1 row)
Time: 0.682 ms
参考
《多流实时聚合 - 记录级实时快照 - JSON聚合与json全文检索的功能应用》
《PostgreSQL - 全文检索内置及自定义ranking算法介绍 与案例》
《用PostgreSQL 做实时高效 搜索引擎 - 全文检索、模糊查询、正则查询、相似查询、ADHOC查询》
《HTAP数据库 PostgreSQL 场景与性能测试之 14 - (OLTP) 字符串搜索 - 全文检索》
《HTAP数据库 PostgreSQL 场景与性能测试之 7 - (OLTP) 全文检索 - 含索引实时写入》
《全文检索 不包含 优化 - 阿里云RDS PostgreSQL最佳实践》
《PostgreSQL 10.0 preview 功能增强 - JSON 内容全文检索》
《PostgreSQL 全文检索加速 快到没有朋友 - RUM索引接口(潘多拉魔盒)》
《PostgreSQL chinese full text search 中文全文检索》