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

标签

PostgreSQL , 全文检索 , ranking

背景

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

《排序算法》这个章节实际上介绍了PostgreSQL的ranking算法。

tsvector将文档分为4层结构：标题、作者、摘要、内容。对这四个层级，用户可以设定对应的weight，用于ranking的计算。同时用户可以设定ranking的修正掩码。

但是只有四个层级，远远不能满足业务需求，那么PostgreSQL实现更多层级，更精细的ranking算法呢？

如何自定义ranking呢？

例如在电商行业中，我们可能存储了每个店铺的标签，每个标签旁边可能是一个系数，这个系数可能是动态调整的。在搜索的时候，商店的某些标签被命中了，可能搜索到了几百万个店铺，但是最后要根据权重算出应该如何排序，并得到其中的1万个店铺。

这种情况，如何精细化排序就体现出来了。

例子1 - tsvector向量

1、店铺标签表：

create table tbl (
  shop_id int8 primary key,   -- 店铺 ID
  tags text                   -- 多值类型，标签1:评分1,标签2:评分2,.....
);

对tags字段，使用UDF索引，存储标签的数组或tsvector索引。

如果使用tsvector，主要是便于使用PostgreSQL的全文检索的语法，包含、不包含、距离等。

tags例如

国民_足浴:0.99,国民_餐饮:0.1,娱乐_KTV:0.45

2、标签权值表：

create table tbl_weight (
  tagid int primary key,   -- 标签ID
  tagname name,            -- 标签名
  desc text,               -- 标签描述
  weight float8            -- 标签权值
);    

create index idx_tbl_weight_1 on tbl_weight (tagname);

3、文本转标签数组、tsvector的UDF

create or replace function text_to_tsvector(text) returns tsvector as $$
  select array_to_tsvector(array_agg(substring(id,'(.+):'))) from unnest(regexp_split_to_array($1, ',')) as t(id);
$$ language sql strict immutable;    

postgres=# select text_to_tsvector('abc:1.1,bc:100,c:293');
 text_to_tsvector
------------------
 'abc' 'bc' 'c'
(1 row)

创建TSVECTOR表达式索引

create index idx_tbl_1 on tbl using gin (text_to_tsvector(tags));

4、取出命中标签权值的UDF

postgres=# select substring('bc:1.1,abc:100,c:293','[^,]?abc:([\d\.]+)') ;
 substring
-----------
 100
(1 row)    

postgres=# select substring('abc:1.1,bc:100,c:293','[^,]?abc:([\d\.]+)') ;
 substring
-----------
 1.1
(1 row)

未命中则返回NULL

postgres=# select substring('abc:1.1,bc:100,c:293','[^,]?adbc:([\d\.]+)') ;
 substring
-----------    

(1 row)    

postgres=# select substring('abc:1.1,bc:100,c:293','[^,]?adbc:([\d\.]+)') is null;
 ?column?
----------
 t
(1 row)

5、全文检索

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

select

6、精排

计算ranking可能是结合 “命中的标签、评分、标签本身的权值” 根据算法得到一个ranking值。

根据4得到命中标签的评分，根据命中标签从tbl_weight得到对应的权值。

将算法封装到UDF，最后得到RANKING。

ranking算法的UDF函数内容略，请根据业务的需要编写对应算法，伪代码如下。

create or replace function cat_ranking(tsquery) returns float8 as $$
declare  

begin
  for each x in array (contains_element) loop
    search hit element's score.
    search hit element's weight.
    cat ranking and increment
  end loop;
  return res;
end;
$$ language plpgsql strict;

7、

7.1 删除标签与对应的评分：

regexp_replace 函数。

7.2 追加标签与对应的评分：

concat函数。

7.3 修改元素评分：

regexp_replace 函数。

以上都可以使用正则表达式来操作。

例子2 - 多维数组

使用数组来存储标签和权值，实际上在编程上会比使用tsvector更简单。

首先需要介绍一些用到的数组函数

根据元素求位置，根据标签，求它的位置，根据这个位置从score[]得到它的SCORE。  

postgres=# select array_position(array[1,2,null,null,2,2,3,1],null);
 array_position
----------------
              3
(1 row)  

postgres=# select array_positions(array[1,2,null,null,2,2,3,1],null);
 array_positions
-----------------
 {3,4}
(1 row)  

postgres=# select array_positions(array[1,2,null,null,2,2,3,1],2);
 array_positions
-----------------
 {2,5,6}
(1 row)  

求某个位置的元素  

array[i]  

postgres=# select (array[1,2,null,null,2,2,3,1])[1];
 array
-------
     1
(1 row)  

postgres=# select (array[1,2,null,null,2,2,3,1])[3];
 array
-------  

(1 row)  

postgres=# select (array[1,2,null,null,2,2,3,1])[5];
 array
-------
     2
(1 row)  

追加元素  

array_append  

替换元素  

array_replace  

删除某个元素  

array_remove，注意如果有一样的元素，都会被删掉（如果有一样的score，就的注意，需要用删除位置来删除元素）  

postgres=# select array_remove(array[1,2,null,null,2,2,3,1],2);
   array_remove
-------------------
 {1,NULL,NULL,3,1}
(1 row)  

删除某个位置的元素，  

postgres=# create or replace function array_remove(anyarray,int[]) returns anyarray as $$
  select array(select $1[i] from (select id from generate_series(1,array_length($1,1)) t(id) where id <> all( $2) ) t(i))
$$ language sql strict;
CREATE FUNCTION
postgres=# select array_remove(array[1,2,null,null,2,2,3,1],array[1,2]);
    array_remove
---------------------
 {NULL,NULL,2,2,3,1}
(1 row)  

postgres=# select array_remove(array[1,2,null,null,2,2,3,1],array[3,5]);
   array_remove
------------------
 {1,2,NULL,2,3,1}
(1 row)

1、店铺标签表：

create table tbl (
  shop_id int8 primary key,   -- 店铺 ID
  tags text[],                -- 数组，标签1,标签2,.....
  scores float8[]             -- 数组，评分1,评分2,.....
);     

create index idx_tbl_1 on tbl using gin(tags);

国民_足浴,国民_餐饮,娱乐_KTV  

0.99,0.1,0.45

2、标签权值表：

create table tbl_weight (
  tagid int primary key,   -- 标签ID
  tagname name,            -- 标签名
  desc text,               -- 标签描述
  weight float8            -- 标签权值
);    

create index idx_tbl_weight_1 on tbl_weight (tagname);

3、包含、不包含、相交的数组查询。

https://www.postgresql.org/docs/10/static/functions-array.html

4、精排算法，与例子1类似。自定义UDF即可。

使用array简化了开发工作量，不需要使用正则表达式，效率也会提高。

参考

https://www.postgresql.org/docs/10/static/functions-matching.html