AI 助理
备案控制台
开发者社区 PolarDB开源 PolarDB PostgreSQL 版 文章 正文

使用 PolarDB 开源版 smlar 插件进行高效率相似文本搜索、自助选药、相似人群圈选等业务

2022-12-29 381
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据库 PolarDB PostgreSQL 版，标准版 2核4GB 50GB
云原生数据库 PolarDB MySQL 版，通用型 2核4GB 50GB
简介： PolarDB 的云原生存算分离架构, 具备低廉的数据存储、高效扩展弹性、高速多机并行计算能力、高速数据搜索和处理; PolarDB与计算算法结合, 将实现双剑合璧, 推动业务数据的价值产出, 将数据变成生产力. 本文将介绍使用 PolarDB 开源版 smlar 插件进行高效率相似文本搜索、自助选药、相似人群圈选等业务

背景

PolarDB 的云原生存算分离架构, 具备低廉的数据存储、高效扩展弹性、高速多机并行计算能力、高速数据搜索和处理; PolarDB与计算算法结合, 将实现双剑合璧, 推动业务数据的价值产出, 将数据变成生产力.

本文将介绍使用 PolarDB 开源版 smlar 插件进行高效率相似文本搜索、自助选药、相似人群圈选等业务

测试环境为macOS+docker, PolarDB部署请参考下文:

场景

1、自助匹配药品, 例如用户根据病情描述, 自动匹配相关的药品. 这个属于文本相似范畴. 文本相似性:

注意有语义的情况:

  • 感冒,不发烧,咳嗽,无痰,流清涕,肌肉酸痛
  • 感冒,发烧,咳嗽,有痰,无鼻涕

将药品主治症状的文本向量化, 存储为文本数组.

根据病人描述, 将文本向量化, 在药品库中进行文本向量的相似匹配, 快速找到最匹配的药品.

2、根据特征进行人群扩选, 例如在数据库中存储了每个用户的特征(使用数组表示)

根据输入的数组(画像)搜索相似人群, 即人群扩选, 业务上进行精准推送.

3、文章相似性搜索, 因为文章关键字很多, 每个关键字的权重也不一样, 不能只按命中多少关键字来决定相似性. 可以借助tfidf, 结合总文本数, 关键字在所有文本中出现的次数, 命中关键字等进行计算.

在所有文本中出现次数越多的关键字, 根据算法其权重可能越低. 具体算法可参考:

设计与算法

以上需求实际上都是多值列的相似计算, 使用smlar插件即可实现.

数据存储: 多值列(例如数组)

多值列的相似性算法: cosine, overlap, tfidf.

  switch(getSmlType())  
  {  
    case ST_TFIDF:  
      PG_RETURN_FLOAT4( TFIDFSml(sa, sb) );  
      break;  
    case ST_COSINE:  
      {  
        int       cnt;  
        double      power;  
  
        power = ((double)(sa->nelems)) * ((double)(sb->nelems));  
        cnt = numOfIntersect(sa, sb);  
  
        PG_RETURN_FLOAT4(  ((double)cnt) / sqrt( power ) );  
      }  
      break;  
    case ST_OVERLAP:  
      {  
        float4 res = (float4)numOfIntersect(sa, sb);  
  
        PG_RETURN_FLOAT4(res);  
      }  
      break;

元素去重后计算.

postgres=# set smlar.type='cosine';   
SET  
postgres=# SELECT smlar('{1,4,6}'::int[], '{5,4,6}' );    
  smlar     
----------  
 0.666667  
(1 row)  
postgres=# SELECT smlar('{1,4,6}'::int[], '{5,4,4,6}' );    
  smlar     
----------  
 0.666667  
(1 row)  
-- 2/sqrt(3*3)   
  
postgres=# set smlar.type='overlap';   
SET  
postgres=# SELECT smlar('{1,4,6}'::int[], '{5,4,4,6}' );    
 smlar   
-------  
     2  
(1 row)  
-- 2  
  
postgres=# set smlar.type='tfidf';   
SET  
  
-- 设置tfidf表, 这个表可以用采样文档统计得到, 也可以自由定义其内容  
set smlar.stattable = 'documents_body_stats';   
  
create table documents_body_stats (  -- tfidf权重表.   
  value text unique,  -- value表示的关键字出现在多少篇文档中; value is null的行表示总文档篇数;  
  ndoc int not null    
);   
  
insert into documents_body_stats values ('0', 1); -- 0 出现在了1篇文章中.   
insert into documents_body_stats values ('1', 100); -- 1 出现在了100篇文章中.  
insert into documents_body_stats values ('4', 101), ('6', 201);   
insert into documents_body_stats values ('5', 1001);   
insert into documents_body_stats values (null, 10000);   -- value is null的行表示总文档篇数;  
  
postgres=# SELECT smlar('{1,4,6}'::text[], '{5,4,4,6}' );    
  smlar     
----------  
 0.742594  
(1 row)  
  
postgres=# SELECT smlar('{1,4,6}'::text[], '{5,5,5,6,6}' );    
 smlar    
--------  
 0.4436  
(1 row)  
  
postgres=# SELECT smlar('{0,1,4,5,6}'::text[], '{0,1,5}' );    
  smlar     
----------  
 0.868165  
(1 row)  
  
postgres=# SELECT smlar('{0,1,4,5,6}'::text[], '{1,5,6}' );    
  smlar     
----------  
 0.531762  
(1 row)

加速原理

smlar 对数组支持gin和gist两个索引接口, 以gin为例, 如何快速筛选相似的记录?

例如, 输入条件的数组长度为6, 使用overlap算法, 要求相似度为4, 那么必须要有4个或4个以上元素命中的记录才符合要求.

  • 在gin索引中搜索元素1, 提取到ctid里的blockid, 每个blockid +1.
  • 在gin索引中搜索元素2, 提取到ctid里的blockid, 每个blockid +1.
  • 在gin索引中搜索元素3, 提取到ctid里的blockid, 每个blockid +1.
  • 在gin索引中搜索元素4, 提取到ctid里的blockid, 每个blockid +1.
  • 在gin索引中搜索元素5, 提取到ctid里的blockid, 每个blockid +1.
  • 在gin索引中搜索元素6, 提取到ctid里的blockid, 每个blockid +1.

在以上blockid中, 数据库只需要回表搜索大于等于4的blockid, recheck是否满足相似条件.

gin,gist支持的operator calss?

GiST/GIN support for % and && operations for:

Array Type GIN operator class GiST operator class
bit[] _bit_sml_ops
bytea[] _bytea_sml_ops _bytea_sml_ops
char[] _char_sml_ops _char_sml_ops
cidr[] _cidr_sml_ops _cidr_sml_ops
date[] _date_sml_ops _date_sml_ops
float4[] _float4_sml_ops _float4_sml_ops
float8[] _float8_sml_ops _float8_sml_ops
inet[] _inet_sml_ops _inet_sml_ops
int2[] _int2_sml_ops _int2_sml_ops
int4[] _int4_sml_ops _int4_sml_ops
int8[] _int8_sml_ops _int8_sml_ops
interval[] _interval_sml_ops _interval_sml_ops
macaddr[] _macaddr_sml_ops _macaddr_sml_ops
money[] _money_sml_ops
numeric[] _numeric_sml_ops _numeric_sml_ops
oid[] _oid_sml_ops _oid_sml_ops
text[] _text_sml_ops _text_sml_ops
time[] _time_sml_ops _time_sml_ops
timestamp[] _timestamp_sml_ops _timestamp_sml_ops
timestamptz[] _timestamptz_sml_ops _timestamptz_sml_ops
timetz[] _timetz_sml_ops _timetz_sml_ops
varbit[] _varbit_sml_ops
varchar[] _varchar_sml_ops _varchar_sml_ops

例子

1、部署smlar on PolarDB

git clone --depth 1  git://sigaev.ru/smlar.git  
  
  
cd smlar/  
  
USE_PGXS=1 make  
USE_PGXS=1 make install  
  
  
[postgres@aa25c5be9681 smlar]$ USE_PGXS=1 make installcheck  
/home/postgres/tmp_basedir_polardb_pg_1100_bld/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/home/postgres/tmp_basedir_polardb_pg_1100_bld/bin'      --dbname=contrib_regression smlar int2 int4 int8 float4 float8 money oid timestamp timestamptz time timetz date interval macaddr inet cidr text varchar char bytea bit varbit numeric int4g int8g intervalg textg int4i int8i intervali texti composite_int4 composite_text  
(using postmaster on 127.0.0.1, default port)  
============== dropping database "contrib_regression" ==============  
DROP DATABASE  
============== creating database "contrib_regression" ==============  
CREATE DATABASE  
ALTER DATABASE  
============== running regression test queries        ==============  
test smlar                        ... ok  
test int2                         ... ok  
test int4                         ... ok  
test int8                         ... ok  
test float4                       ... ok  
test float8                       ... ok  
test money                        ... ok  
test oid                          ... ok  
test timestamp                    ... ok  
test timestamptz                  ... ok  
test time                         ... ok  
test timetz                       ... ok  
test date                         ... ok  
test interval                     ... ok  
test macaddr                      ... ok  
test inet                         ... ok  
test cidr                         ... ok  
test text                         ... ok  
test varchar                      ... ok  
test char                         ... ok  
test bytea                        ... ok  
test bit                          ... ok  
test varbit                       ... ok  
test numeric                      ... ok  
test int4g                        ... ok  
test int8g                        ... ok  
test intervalg                    ... ok  
test textg                        ... ok  
test int4i                        ... ok  
test int8i                        ... ok  
test intervali                    ... ok  
test texti                        ... ok  
test composite_int4               ... ok  
test composite_text               ... ok  
  
  
===========================================================  
 All 34 tests passed.   
  
 POLARDB:  
 All 34 tests, 0 tests in ignore, 0 tests in polar ignore.   
===========================================================

2、安装插件

postgres=# create extension smlar ;  
CREATE EXTENSION

3、创建测试表, 写入测试数据

create table tbl (id int, propt int[]);  
  
create or replace function gen_arr(normal int, hot int) returns int[] as $$  
  select array(select (100000*random())::int+500 from generate_series(1,$1)) || array(select (500*random())::int from generate_series(1,$2));  
$$ language sql strict;  
  
insert into tbl select id, gen_arr(22, 10) from generate_series(1,2000000) id;  
  
postgres=# select * from tbl limit 5;  
 id |                                                                                     propt                                                                                       
----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
  1 | {1386,57573,55117,44934,83223,3444,77658,49523,85849,62549,99593,40714,53146,32510,68449,33662,45912,70227,64560,78831,86052,56387,157,490,51,484,53,176,273,240,300,277}  
  2 | {15075,100383,88390,18019,77540,37413,3368,39590,36506,43582,92236,68516,11532,25398,13927,81259,89457,92259,66811,45344,23676,64902,275,100,375,451,373,116,251,150,141,324}  
  3 | {16664,82803,7375,53577,85671,46465,89583,28753,38201,57599,39785,63099,71026,20543,52056,62785,86854,96900,85960,51256,51917,5901,129,208,400,244,459,49,386,283,198,467}  
  4 | {47066,46889,24635,93031,35972,52888,30732,93071,92172,93330,63597,12216,44887,25882,98570,41287,11343,49327,92704,16743,75095,34373,481,117,129,30,3,412,228,470,107,461}  
  5 | {46010,85290,76290,98398,15522,68861,90070,8352,31959,1786,52739,57341,99856,93526,68184,48683,85730,84427,23278,19603,80575,46747,224,430,234,136,159,204,243,120,406,471}  
(5 rows)

4、创建索引

create index on tbl using gin (propt _int4_sml_ops);

5、相似度搜索

overlap

postgres=# set smlar.type ='overlap';  
SET  
postgres=# set smlar.threshold=10;  
SET  
  
postgres=# explain analyze select * from tbl where propt % '{157,490,51,484,53,176,273,240,300,277}'::int[];  
                                                         QUERY PLAN                                                            
-----------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl  (cost=219.50..6871.30 rows=2000 width=36) (actual time=37.548..37.549 rows=1 loops=1)  
   Recheck Cond: (propt % '{157,490,51,484,53,176,273,240,300,277}'::integer[])  
   Heap Blocks: exact=1  
   ->  Bitmap Index Scan on tbl_propt_idx  (cost=0.00..219.00 rows=2000 width=0) (actual time=37.514..37.515 rows=1 loops=1)  
         Index Cond: (propt % '{157,490,51,484,53,176,273,240,300,277}'::integer[])  
 Planning Time: 0.161 ms  
 Execution Time: 37.593 ms  
(7 rows)  
  
Time: 38.683 ms  
postgres=# select * from tbl where propt % '{157,490,51,484,53,176,273,240,300,277}'::int[];  
 id |                                                                                   propt                                                                                     
----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
  1 | {1386,57573,55117,44934,83223,3444,77658,49523,85849,62549,99593,40714,53146,32510,68449,33662,45912,70227,64560,78831,86052,56387,157,490,51,484,53,176,273,240,300,277}  
(1 row)  
  
Time: 38.794 ms  
  
关闭索引, 性能直线下降:  
postgres=# set enable_bitmapscan =off;  
SET  
Time: 0.510 ms  
postgres=# select * from tbl where propt % '{157,490,51,484,53,176,273,240,300,277}'::int[];  
 id |                                                                                   propt                                                                                     
----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
  1 | {1386,57573,55117,44934,83223,3444,77658,49523,85849,62549,99593,40714,53146,32510,68449,33662,45912,70227,64560,78831,86052,56387,157,490,51,484,53,176,273,240,300,277}  
(1 row)  
  
Time: 12553.942 ms (00:12.554)

采用smlar提速100倍以上.

cosine

postgres=# set smlar.type ='cosine';  
SET  
  
postgres=# set smlar.threshold=0.55;  
SET  
Time: 1.107 ms  
postgres=# select * from tbl where propt % '{157,490,51,484,53,176,273,240,300,277}'::int[];  
 id |                                                                                   propt                                                                                     
----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
  1 | {1386,57573,55117,44934,83223,3444,77658,49523,85849,62549,99593,40714,53146,32510,68449,33662,45912,70227,64560,78831,86052,56387,157,490,51,484,53,176,273,240,300,277}  
(1 row)  
  
Time: 42.701 ms

tfidf

  • 例如将所有的药品说明书进行文本向量处理, 提取关键字, 生成tfidf表.
  • 请自行测试

参考

https://github.com/jirutka/smlar

文章标签：
云原生数据库 PolarDB
图像搜索
性能测试
测试技术
搜索推荐
关系型数据库
索引
PostgreSQL
PolarDB
算法
数据库
存储
分布式数据库
关键词：
云原生数据库 PolarDB搜索
云原生数据库 PolarDB开源
云原生数据库 PolarDB插件
云原生数据库 PolarDB文本搜索人群
云原生数据库 PolarDB开源插件
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系，提升产品性能，打磨产品功能，从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手，打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系， 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台，为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案，提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
德哥
目录
相关文章
体验瑶池数据库
|
1月前
|
关系型数据库 分布式数据库 PolarDB
参与有礼｜开源PolarDB文档捉虫
2024年9月，开源PolarDB-PG发布兼容PostgreSQL 15版本，为提升用户体验，特举办“开源文档捉虫”活动，邀请您反馈文档问题和优化建议。活动时间为2024年11月1日至2025年2月28日。参与即有机会赢取PolarDB开源社区T恤、新春茶碗及福字版印礼盒等丰富奖品。更多详情及反馈入口请点击链接。
体验瑶池数据库
44 0
参与有礼｜开源PolarDB文档捉虫
体验瑶池数据库
|
1月前
|
数据库
使用rpm安装开源PolarDB-PG没找到 postgresql.conf
开源PolarDB-PG 数据库初始化
体验瑶池数据库
67 1
lsug6eziqmdfk1111
|
2月前
|
存储 关系型数据库 分布式数据库
使用开源PolarDB和imgsmlr进行高效的图片存储和相似度搜索
使用开源PolarDB和imgsmlr进行高效的图片存储和相似度搜索
lsug6eziqmdfk1111
47 5
sunrr
|
2月前
|
SQL JSON 关系型数据库
MySQL是一个广泛使用的开源关系型数据库管理系统，它有许多不同的版本
【10月更文挑战第3天】MySQL是一个广泛使用的开源关系型数据库管理系统，它有许多不同的版本
sunrr
187 5
#FFF
|
2月前
|
关系型数据库 分布式数据库 数据库
PolarDB 开源：推动数据库技术新变革
在数字化时代，数据成为核心资产，数据库的性能和可靠性至关重要。阿里云的PolarDB作为新一代云原生数据库，凭借卓越性能和创新技术脱颖而出。其开源不仅让开发者深入了解内部架构，还促进了数据库生态共建，提升了稳定性与可靠性。PolarDB采用云原生架构，支持快速弹性扩展和高并发访问，具备强大的事务处理能力及数据一致性保证，并且与多种应用无缝兼容。开源PolarDB为国内数据库产业注入新活力，打破国外垄断，推动国产数据库崛起，降低企业成本与风险。未来，PolarDB将在生态建设中持续壮大，助力企业数字化转型。
#FFF
108 2
龙大吉
|
3月前
惊世骇俗！开源 PolarDB-X 部署安装大冒险，全程心跳与惊喜不断！
【9月更文挑战第8天】作为技术爱好者的我，近期成功完成了开源 PolarDB-X 的部署安装。尽管过程中遇到不少挑战，但通过精心准备环境、下载安装包、配置参数及启动服务等步骤，最终顺利实现部署。本文将详细介绍部署全过程及可能遇到的问题，为您的 PolarDB-X 探索之旅提供参考与启发，希望能让大家在技术海洋里畅游得更加顺利！
龙大吉
178 2
众所周知
|
3月前
|
关系型数据库 MySQL 分布式数据库
PolarDB开源社区动态：最新版本功能亮点与更新解读
【9月更文挑战第6天】随着云计算技术的发展，分布式数据库系统成为企业数据处理的核心。阿里云的云原生数据库PolarDB自开源以来备受关注，近日发布的最新版本在内核稳定性、性能、分布式CDC架构及基于时间点的恢复等方面均有显著提升，并新增了MySQL一键导入功能。本文将解读这些新特性并提供示例代码，帮助企业更好地利用PolarDB处理实时数据同步和离线分析任务，提升数据安全性。未来，PolarDB将继续创新，为企业提供更高效的数据处理服务。
众所周知
219 3
DatabaseEvangelist
|
2月前
|
关系型数据库 MySQL 分布式数据库
零基础教你用云数据库PolarDB搭建企业网站，完成就送桌面收纳桶！
零基础教你用云数据库PolarDB搭建企业网站，完成就送桌面收纳桶，邀请好友完成更有机会获得​小米Watch S3、小米体重称​等诸多好礼！
DatabaseEvangelist
66 0
零基础教你用云数据库PolarDB搭建企业网站，完成就送桌面收纳桶！
爱的不是纯牛奶-47754
|
3月前
|
关系型数据库 MySQL Serverless
探索PolarDB MySQL版：Serverless数据库的灵活性与性能
本文介绍了个人开发者对阿里云PolarDB MySQL版，特别是其Serverless特性的详细评测体验。评测涵盖了产品初体验、性能观测、Serverless特性深度评测及成本效益分析等方面。尽管试用过程中遇到一些小问题，但总体而言，PolarDB MySQL版表现出色，提供了高性能、高可用性和灵活的资源管理，是个人开发者和企业用户的优秀选择。
爱的不是纯牛奶-47754
134 14
DatabaseEvangelist
|
14天前
|
Cloud Native 关系型数据库 分布式数据库
PolarDB 分布式版 V2.0，安全可靠的集中分布式一体化数据库管理软件
阿里云PolarDB数据库管理软件（分布式版）V2.0 ，安全可靠的集中分布式一体化数据库管理软件。
DatabaseEvangelist
86 7
PolarDB开源

PolarDB PostgreSQL 版

热门文章

最新文章

  • 1
    2024年全国大学生计算机系统能力大赛PolarDB数据库创新设计赛（天池杯）等你来战！
  • 2
    PolarDB 开源版 使用TimescaleDB 实现时序数据高速写入、压缩、实时聚合计算、自动老化等
  • 3
    如何一键本地部署PolarDB for PostgreSQL
  • 4
    PolarDB-PG | PostgreSQL + 阿里云OSS 实现高效低价的海量数据冷热存储分离
  • 5
    PolarDB-X 动手实践系列第一讲：如何一键部署开源 PolarDB-X
  • 6
    PG内核解读-第2节PostgreSQL体系结构
  • 7
    阿里云数据库开源重磅发布：PolarDB HTAP的功能特性和关键技术
  • 8
    PolarDB for PostgreSQL 三节点开源版 3主机集群部署实践
  • 9
    PolarDB | PostgreSQL 高并发队列处理业务的数据库性能优化实践
  • 10
    沉浸式学习PostgreSQL|PolarDB 12: 如何快速构建 海量 逼真 测试数据
  • 1
    使用rpm安装开源PolarDB-PG没找到 postgresql.conf
    67
  • 2
    2024年全国大学生计算机系统能力大赛PolarDB数据库创新设计赛（天池杯）等你来战！
    367
  • 3
    PolarDB 体验报告
    391
  • 4
    开源之夏2024学生报名启动！阿里云PolarDB社区项目期待你的参与！
    2379
  • 5
    智能辅助驾驶业务遭遇大表瓶颈，小鹏汽车如何破局？
    80495
  • 6
    5分钟免费体验PolarDB PG版Serverless的极致弹性！
    643
  • 7
    【公测】PolarDB PostgreSQL版Serverless功能免费使用​！
    924
  • 8
    一文熟悉PolarDB-PG 分区表核心特性
    82823
  • 9
    成都晨云信息技术完成阿里云PolarDB数据库产品生态集成认证
    898
  • 10
    PolarDB PostgreSQL版：Oracle兼容的高性能数据库
    519

    • 相关产品

  • 云原生数据库 PolarDB
    文档详情 产品详情

    • 相关课程

    更多
  • PolarDB for PostgreSQL 开源人才初级认证培训课程
  • PolarDB for PostgreSQL 内核解读系列课程
  • 7天突破PolarDB for PostgreSQL 2022版
  • 关系型数据库ACP认证课程
  • 关系型数据库线上预习视频
  • PolarDB for PostgreSQL 入门

    • 相关电子书

    更多
  • PolarDB+AnalyticDB助力交通物流行业系统升级
  • PolarDB NL2SQL: 帮助您写出准确、优化的SQL
  • 云栖大会：开源 PolarDB 架构演进、关键技术与社区建设

    • 相关实验场景

    更多
  • 如何快速体验知识检索增强应用
  • 以电商场景为例搭建AI语义搜索应用
  • PolarDB NL2SQL：自然语言驱动的数据智能
  • 阿里云百炼xAnalyticDB PostgreSQL构建AIGC应用
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 使用CloudLens采集PolarDB日志并进行审计分析
    • 下一篇
    【案例实战】SpringBoot整合阿里云文件上传OSS