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

digoal 2018-01-23

296

作者

digoal

日期

2018-01-23

背景

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 ```