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

digoal’s 大量PostgreSQL文章入口