沉浸式学习PostgreSQL|PolarDB 3: 营销场景, 根据用户画像的相似度进行目标人群圈选, 实现精准营销

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介: 业务场景1 介绍: 营销场景, 根据用户画像的相似度进行目标人群圈选, 实现精准营销在营销场景中, 通常会对用户的属性、行为等数据进行统计分析, 生成用户的标签, 也就是常说的用户画像.标签举例: 男性、女性、年轻人、大学生、90后、司机、白领、健身达人、博士、技术达人、科技产品爱好者、2胎妈妈、老师、浙江省、15天内逛过手机电商店铺、... ...有了用户画像, 在营销场景中一个重要的营销手段是根据条件选中目标人群, 进行精准营销.例如圈选出包含这些标签的人群: 白领、科技产品爱好者、浙江省、技术达人、15天内逛过手机电商店铺 .

作者

digoal

日期

2023-08-24

标签

PostgreSQL , PolarDB , 数据库 , 教学


背景

非常欢迎数据库用户提出场景给我, 在此issue回复即可, 一起来建设沉浸式数据库学习教学素材库, 帮助开发者用好数据库, 提升开发者职业竞争力, 同时为企业降本提效.

本文的实验可以使用永久免费的阿里云云起实验室来完成.

如果你本地有docker环境也可以把镜像拉到本地来做实验:

x86_64机器使用以下docker image:

Apple Chip机器使用以下docker image:

业务场景1 介绍: 营销场景, 根据用户画像的相似度进行目标人群圈选, 实现精准营销

在营销场景中, 通常会对用户的属性、行为等数据进行统计分析, 生成用户的标签, 也就是常说的用户画像.

标签举例: 男性、女性、年轻人、大学生、90后、司机、白领、健身达人、博士、技术达人、科技产品爱好者、2胎妈妈、老师、浙江省、15天内逛过手机电商店铺、... ...

有了用户画像, 在营销场景中一个重要的营销手段是根据条件选中目标人群, 进行精准营销.

例如圈选出包含这些标签的人群: 白领、科技产品爱好者、浙江省、技术达人、15天内逛过手机电商店铺 .

这个实验的目的是在有画像的基础上, 如何快速根据标签组合进行人群圈选 .

实现和对照

设计1张标签元数据表, 后面的用户画像表从这张标签表随机抽取标签. 业务查询时也从这里搜索存在的标签并进行圈选条件的组合, 得到对应的标签ID组合.

drop table if exists tbl_tag;  
create table tbl_tag (  
  tid int primary key,  -- 标签id  
  tag text,  -- 标签名  
  info text  -- 标签描述  
);

假设有1万个标签, 写入标签元数据表.

insert into tbl_tag select id, md5(id::text), md5(random()::text) from generate_series(1, 10000) id;

创建2个函数, 产生若干的标签. 用来模拟产生每个用户对应的标签数据. 分别返回字符串和数组类型.

第一个函数, 随机提取若干个标签, 始终包含1-100的热门标签8个, 返回用户标签字符串:

create or replace function get_tags_text(int) returns text as $$  
  with a as (select string_agg(tid::text, ',') s from tbl_tag where tid = any (array(select ceil(random()*100)::int from generate_series(1,8) group by 1)))  
  , b as (select string_agg(tid::text, ',') s from tbl_tag where tid = any (array(select ceil(100+random()*9900)::int from generate_series(1,$1) group by 1)))  
  select ','||a.s||','||b.s||',' from a,b;  
$$ language sql strict;

得到类似这样的结果:

postgres=# select get_tags_text(10);  
                            get_tags_text  
----------------------------------------------------------------------  
 ,11,12,39,44,45,59,272,1001,1322,1402,2514,6888,7404,8922,9200,9409,  
(1 row)  
postgres=# select get_tags_text(10);  
                             get_tags_text  
------------------------------------------------------------------------  
 ,12,34,52,55,71,79,88,302,582,1847,3056,5156,8231,8542,8572,8747,9727,  
(1 row)

第二个函数, 随机提取若干个标签, 始终包含1-100的热门标签8个, 返回用户标签数组:

create or replace function get_tags_arr(int) returns int[] as $$  
  with a as (select array_agg(tid) s from tbl_tag where tid = any (array(select ceil(random()*100)::int from generate_series(1,8) group by 1)))  
  , b as (select array_agg(tid) s from tbl_tag where tid = any (array(select ceil(100+random()*9900)::int from generate_series(1,$1) group by 1)))  
  select a.s||b.s from a,b;  
$$ language sql strict;

得到类似这样的结果:

postgres=# select * from get_tags_arr(10);  
                                get_tags_arr  
----------------------------------------------------------------------------  
 {13,35,42,61,67,69,76,78,396,2696,3906,4356,5064,5711,7363,9417,9444,9892}  
(1 row)  
postgres=# select * from get_tags_arr(10);  
                              get_tags_arr  
-------------------------------------------------------------------------  
 {2,10,20,80,84,85,89,3410,3515,4159,4182,5217,6549,6775,7289,9141,9431}  
(1 row)

传统方法 设计和实验

传统数据库没有数组类型, 所以需要用字符串存储标签.

创建用户画像表

drop table if exists tbl_users;  
create unlogged table tbl_users (  -- 为便于加速生成测试数据, 使用unlogged table  
  uid int primary key,   -- 用户id  
  tags text   -- 该用户拥有的标签 , 使用字符串类型  
);

创建100万个用户, 用户被贴的标签数从32到256个, 随机产生, 其中8个为热门标签(例如性别、年龄段等都属于热门标签).

insert into tbl_users select id, get_tags_text(ceil(24+random()*224)::int) from generate_series(1,1000000) id;

测试如下, 分别搜索包含如下标签组合的用户:

  • 2
  • 2,8
  • 2,2696
  • 2,4356,5064,5711,7363,9417,9444
  • 4356,5064,5711,7363,9417,9444

使用如下SQL:

select uid from tbl_users where tags like '%,2,%';  
select uid from tbl_users where tags like '%,2,%' or tags like '%,8,%';  
select uid from tbl_users where tags like '%,2,%' or tags like '%,2696,%';  
select uid from tbl_users where tags like '%,2,%' or tags like '%,4356,%' or tags like '%,5064,%' or tags like '%,5711,%' or tags like '%,7363,%' or tags like '%,9417,%' or tags like '%,9444,%' ;  
select uid from tbl_users where tags like '%,4356,%' or tags like '%,5064,%' or tags like '%,5711,%' or tags like '%,7363,%' or tags like '%,9417,%' or tags like '%,9444,%' ;

查看以上SQL运行的执行计划和耗时如下:

postgres=# explain analyze select uid from tbl_users where tags like '%,2,%';  
                                                    QUERY PLAN  
-------------------------------------------------------------------------------------------------------------------  
 Seq Scan on tbl_users  (cost=0.00..103268.00 rows=80808 width=4) (actual time=0.018..1108.805 rows=77454 loops=1)  
   Filter: (tags ~~ '%,2,%'::text)  
   Rows Removed by Filter: 922546  
 Planning Time: 1.095 ms  
 Execution Time: 1110.267 ms  
(5 rows)  
postgres=# explain analyze select uid from tbl_users where tags like '%,2,%' or tags like '%,8,%';  
                                                     QUERY PLAN  
---------------------------------------------------------------------------------------------------------------------  
 Seq Scan on tbl_users  (cost=0.00..105768.00 rows=127232 width=4) (actual time=0.029..2001.379 rows=149132 loops=1)  
   Filter: ((tags ~~ '%,2,%'::text) OR (tags ~~ '%,8,%'::text))  
   Rows Removed by Filter: 850868  
 Planning Time: 1.209 ms  
 Execution Time: 2004.062 ms  
(5 rows)  
postgres=# explain analyze select uid from tbl_users where tags like '%,2,%' or tags like '%,2696,%';  
                                                    QUERY PLAN  
-------------------------------------------------------------------------------------------------------------------  
 Seq Scan on tbl_users  (cost=0.00..105768.00 rows=90093 width=4) (actual time=0.035..2058.797 rows=90084 loops=1)  
   Filter: ((tags ~~ '%,2,%'::text) OR (tags ~~ '%,2696,%'::text))  
   Rows Removed by Filter: 909916  
 Planning Time: 1.190 ms  
 Execution Time: 2060.434 ms  
(5 rows)  
postgres=# explain analyze select uid from tbl_users where tags like '%,2,%' or tags like '%,4356,%' or tags like '%,5064,%' or tags like '%,5711,%' or tags like '%,7363,%' or tags like '%,9417,%' or tags like '%,9444,%' ;  
                                                                                                       QUERY PLAN  
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Seq Scan on tbl_users  (cost=0.00..118268.00 rows=135482 width=4) (actual time=0.024..6765.315 rows=150218 loops=1)  
   Filter: ((tags ~~ '%,2,%'::text) OR (tags ~~ '%,4356,%'::text) OR (tags ~~ '%,5064,%'::text) OR (tags ~~ '%,5711,%'::text) OR (tags ~~ '%,7363,%'::text) OR (tags ~~ '%,9417,%'::text) OR (tags ~~ '%,9444,%'::text))  
   Rows Removed by Filter: 849782  
 Planning Time: 4.344 ms  
 Execution Time: 6767.990 ms  
(5 rows)  
postgres=#  explain analyze select uid from tbl_users where tags like '%,4356,%' or tags like '%,5064,%' or tags like '%,5711,%' or tags like '%,7363,%' or tags like '%,9417,%' or tags like '%,9444,%' ;  
                                                                                          QUERY PLAN  
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Seq Scan on tbl_users  (cost=0.00..115768.00 rows=59480 width=4) (actual time=0.112..6206.775 rows=78827 loops=1)  
   Filter: ((tags ~~ '%,4356,%'::text) OR (tags ~~ '%,5064,%'::text) OR (tags ~~ '%,5711,%'::text) OR (tags ~~ '%,7363,%'::text) OR (tags ~~ '%,9417,%'::text) OR (tags ~~ '%,9444,%'::text))  
   Rows Removed by Filter: 921173  
 Planning Time: 4.223 ms  
 Execution Time: 6208.191 ms  
(5 rows)

PolarDB|PG新方法1 设计和实验

第一种方法没有用到任何的索引, 每次请求都要扫描用户画像表的所有记录, 计算每一个LIKE的算子, 性能比较差.

为了提升查询性能, 我们可以使用gin索引和pg_trgm插件, 支持字符串内的模糊查询索引加速.

复用方法1的数据, 创建gin索引, 支持索引加速模糊查询.

create extension pg_trgm;  
create index on tbl_users using gin (tags gin_trgm_ops);

使用索引后, 查看执行计划和耗时如下:

postgres=# explain analyze select uid from tbl_users where tags like '%,2,%';  
                                                              QUERY PLAN  
---------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl_users  (cost=555.93..58686.88 rows=80808 width=4) (actual time=30.315..76.314 rows=77454 loops=1)  
   Recheck Cond: (tags ~~ '%,2,%'::text)  
   Heap Blocks: exact=53210  
   ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..535.73 rows=80808 width=0) (actual time=22.967..22.967 rows=77454 loops=1)  
         Index Cond: (tags ~~ '%,2,%'::text)  
 Planning Time: 0.991 ms  
 Execution Time: 78.163 ms  
(7 rows)  
postgres=# explain analyze select uid from tbl_users where tags like '%,2,%' or tags like '%,8,%';  
                                                                 QUERY PLAN  
---------------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl_users  (cost=983.56..87215.27 rows=127232 width=4) (actual time=48.651..811.842 rows=149132 loops=1)  
   Recheck Cond: ((tags ~~ '%,2,%'::text) OR (tags ~~ '%,8,%'::text))  
   Rows Removed by Index Recheck: 299658  
   Heap Blocks: exact=41915 lossy=33158  
   ->  BitmapOr  (cost=983.56..983.56 rows=131313 width=0) (actual time=43.554..43.554 rows=0 loops=1)  
         ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..535.73 rows=80808 width=0) (actual time=24.923..24.923 rows=77454 loops=1)  
               Index Cond: (tags ~~ '%,2,%'::text)  
         ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..384.22 rows=50505 width=0) (actual time=18.629..18.629 rows=77054 loops=1)  
               Index Cond: (tags ~~ '%,8,%'::text)  
 Planning Time: 1.496 ms  
 Execution Time: 814.748 ms  
(11 rows)  
postgres=# explain analyze select uid from tbl_users where tags like '%,2,%' or tags like '%,2696,%';  
                                                                 QUERY PLAN  
---------------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl_users  (cost=958.67..64006.30 rows=90093 width=4) (actual time=75.859..900.779 rows=90084 loops=1)  
   Recheck Cond: ((tags ~~ '%,2,%'::text) OR (tags ~~ '%,2696,%'::text))  
   Rows Removed by Index Recheck: 348263  
   Heap Blocks: exact=39411 lossy=33155  
   ->  BitmapOr  (cost=958.67..958.67 rows=90909 width=0) (actual time=71.980..71.981 rows=0 loops=1)  
         ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..535.73 rows=80808 width=0) (actual time=26.486..26.487 rows=77454 loops=1)  
               Index Cond: (tags ~~ '%,2,%'::text)  
         ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..377.89 rows=10101 width=0) (actual time=45.492..45.492 rows=62326 loops=1)  
               Index Cond: (tags ~~ '%,2696,%'::text)  
 Planning Time: 1.479 ms  
 Execution Time: 902.637 ms  
(11 rows)  
postgres=# explain analyze select uid from tbl_users where tags like '%,2,%' or tags like '%,4356,%' or tags like '%,5064,%' or tags like '%,5711,%' or tags like '%,7363,%' or tags like '%,9417,%' or tags like '%,9444,%' ;  
                                                                                                          QUERY PLAN  
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl_users  (cost=3041.18..100880.75 rows=135482 width=4) (actual time=210.772..4047.148 rows=150218 loops=1)  
   Recheck Cond: ((tags ~~ '%,2,%'::text) OR (tags ~~ '%,4356,%'::text) OR (tags ~~ '%,5064,%'::text) OR (tags ~~ '%,5711,%'::text) OR (tags ~~ '%,7363,%'::text) OR (tags ~~ '%,9417,%'::text) OR (tags ~~ '%,9444,%'::text))  
   Rows Removed by Index Recheck: 422706  
   Heap Blocks: exact=56868 lossy=33226  
   ->  BitmapOr  (cost=3041.18..3041.18 rows=141614 width=0) (actual time=205.898..205.899 rows=0 loops=1)  
         ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..535.73 rows=80808 width=0) (actual time=24.656..24.656 rows=77454 loops=1)  
               Index Cond: (tags ~~ '%,2,%'::text)  
         ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..428.40 rows=20202 width=0) (actual time=45.014..45.014 rows=62615 loops=1)  
               Index Cond: (tags ~~ '%,4356,%'::text)  
         ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..377.89 rows=10101 width=0) (actual time=22.680..22.680 rows=39025 loops=1)  
               Index Cond: (tags ~~ '%,5064,%'::text)  
         ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..428.40 rows=20202 width=0) (actual time=28.809..28.809 rows=62697 loops=1)  
               Index Cond: (tags ~~ '%,5711,%'::text)  
         ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..377.89 rows=10101 width=0) (actual time=28.646..28.646 rows=62647 loops=1)  
               Index Cond: (tags ~~ '%,7363,%'::text)  
         ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..327.89 rows=100 width=0) (actual time=28.361..28.361 rows=62172 loops=1)  
               Index Cond: (tags ~~ '%,9417,%'::text)  
         ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..327.89 rows=100 width=0) (actual time=27.729..27.730 rows=62821 loops=1)  
               Index Cond: (tags ~~ '%,9444,%'::text)  
 Planning Time: 4.517 ms  
 Execution Time: 4050.040 ms  
(21 rows)  
postgres=#  explain analyze select uid from tbl_users where tags like '%,4356,%' or tags like '%,5064,%' or tags like '%,5711,%' or tags like '%,7363,%' or tags like '%,9417,%' or tags like '%,9444,%' ;  
                                                                                             QUERY PLAN  
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl_users  (cost=2357.58..50755.97 rows=59480 width=4) (actual time=209.115..3689.534 rows=78827 loops=1)  
   Recheck Cond: ((tags ~~ '%,4356,%'::text) OR (tags ~~ '%,5064,%'::text) OR (tags ~~ '%,5711,%'::text) OR (tags ~~ '%,7363,%'::text) OR (tags ~~ '%,9417,%'::text) OR (tags ~~ '%,9444,%'::text))  
   Rows Removed by Index Recheck: 455241  
   Heap Blocks: exact=55903 lossy=33218  
   ->  BitmapOr  (cost=2357.58..2357.58 rows=60806 width=0) (actual time=204.235..204.236 rows=0 loops=1)  
         ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..428.40 rows=20202 width=0) (actual time=57.485..57.485 rows=62615 loops=1)  
               Index Cond: (tags ~~ '%,4356,%'::text)  
         ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..377.89 rows=10101 width=0) (actual time=26.156..26.157 rows=39025 loops=1)  
               Index Cond: (tags ~~ '%,5064,%'::text)  
         ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..428.40 rows=20202 width=0) (actual time=33.539..33.539 rows=62697 loops=1)  
               Index Cond: (tags ~~ '%,5711,%'::text)  
         ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..377.89 rows=10101 width=0) (actual time=30.136..30.136 rows=62647 loops=1)  
               Index Cond: (tags ~~ '%,7363,%'::text)  
         ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..327.89 rows=100 width=0) (actual time=28.794..28.794 rows=62172 loops=1)  
               Index Cond: (tags ~~ '%,9417,%'::text)  
         ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..327.89 rows=100 width=0) (actual time=28.122..28.122 rows=62821 loops=1)  
               Index Cond: (tags ~~ '%,9444,%'::text)  
 Planning Time: 3.860 ms  
 Execution Time: 3691.329 ms  
(19 rows)

PolarDB|PG新方法2 设计和实验

很显然你不能满足于前面的模糊查询索引带来的性能提升, 特别是当and条件非常多时, 模糊查询的索引也要被多次扫描并使用bitmap进行合并, 性能不好. (以上方法对于一个模糊查询条件性能提升是非常明显的.)

PolarDB和PostgreSQL都支持数组类型, 用数组存储标签, 支持gin索引可以加速数组的包含查询.

创建用户画像表, 使用数组存储标签字段.

drop table if exists tbl_users;  
create unlogged table tbl_users (  -- 为便于加速生成测试数据, 使用unlogged table  
  uid int primary key,   -- 用户id  
  tags int[]   -- 该用户拥有的标签 , 使用数组类型  
);

创建100万个用户, 用户被贴的标签数从32到256个, 随机产生, 其中8个为热门标签(例如性别、年龄段等都属于热门标签).

insert into tbl_users select id, get_tags_arr(ceil(24+random()*224)::int) from generate_series(1,1000000) id;  
create index on tbl_users using gin (tags);

搜索包含如下标签组合的用户:

  • 2
  • 2,8
  • 2,2696
  • 2,4356,5064,5711,7363,9417,9444
  • 4356,5064,5711,7363,9417,9444

数组匹配的 SQL 语句如下:

select uid from tbl_users where tags @> array[2];  
select uid from tbl_users where tags @> array[2,8];  
select uid from tbl_users where tags @> array[2,2696];  
select uid from tbl_users where tags @> array[2,4356,5064,5711,7363,9417,9444];  
select uid from tbl_users where tags @> array[4356,5064,5711,7363,9417,9444];

使用数组类型和gin索引后, 查看执行计划和耗时如下:

postgres=# explain analyze select uid from tbl_users where tags @> array[2];  
                                                              QUERY PLAN  
---------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl_users  (cost=437.95..53717.07 rows=76333 width=4) (actual time=24.031..69.706 rows=77641 loops=1)  
   Recheck Cond: (tags @> '{2}'::integer[])  
   Heap Blocks: exact=50231  
   ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..418.86 rows=76333 width=0) (actual time=15.026..15.026 rows=77641 loops=1)  
         Index Cond: (tags @> '{2}'::integer[])  
 Planning Time: 1.137 ms  
 Execution Time: 74.015 ms  
(7 rows)  
postgres=# explain analyze select uid from tbl_users where tags @> array[2,8];  
                                                             QUERY PLAN  
------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl_users  (cost=49.97..6172.63 rows=5847 width=4) (actual time=10.745..18.272 rows=5303 loops=1)  
   Recheck Cond: (tags @> '{2,8}'::integer[])  
   Heap Blocks: exact=5133  
   ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..48.51 rows=5847 width=0) (actual time=10.081..10.081 rows=5303 loops=1)  
         Index Cond: (tags @> '{2,8}'::integer[])  
 Planning Time: 0.256 ms  
 Execution Time: 18.561 ms  
(7 rows)  
postgres=# explain analyze select uid from tbl_users where tags @> array[2,2696];  
                                                           QUERY PLAN  
---------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl_users  (cost=21.27..443.58 rows=382 width=4) (actual time=2.872..4.662 rows=1003 loops=1)  
   Recheck Cond: (tags @> '{2,2696}'::integer[])  
   Heap Blocks: exact=999  
   ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..21.18 rows=382 width=0) (actual time=2.729..2.729 rows=1003 loops=1)  
         Index Cond: (tags @> '{2,2696}'::integer[])  
 Planning Time: 0.246 ms  
 Execution Time: 4.750 ms  
(7 rows)  
postgres=# explain analyze select uid from tbl_users where tags @> array[2,4356,5064,5711,7363,9417,9444];  
                                                         QUERY PLAN  
----------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl_users  (cost=64.38..65.50 rows=1 width=4) (actual time=5.476..5.478 rows=0 loops=1)  
   Recheck Cond: (tags @> '{2,4356,5064,5711,7363,9417,9444}'::integer[])  
   ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..64.38 rows=1 width=0) (actual time=5.471..5.472 rows=0 loops=1)  
         Index Cond: (tags @> '{2,4356,5064,5711,7363,9417,9444}'::integer[])  
 Planning Time: 0.223 ms  
 Execution Time: 5.523 ms  
(6 rows)  
postgres=# explain analyze select uid from tbl_users where tags @> array[4356,5064,5711,7363,9417,9444];  
                                                         QUERY PLAN  
----------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl_users  (cost=55.36..56.47 rows=1 width=4) (actual time=4.476..4.477 rows=0 loops=1)  
   Recheck Cond: (tags @> '{4356,5064,5711,7363,9417,9444}'::integer[])  
   ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..55.36 rows=1 width=0) (actual time=4.471..4.472 rows=0 loops=1)  
         Index Cond: (tags @> '{4356,5064,5711,7363,9417,9444}'::integer[])  
 Planning Time: 0.275 ms  
 Execution Time: 4.528 ms  
(6 rows)

PolarDB|PG新方法3 设计和实验

当我们输入一组标签, 如果想放宽圈选条件, 而不仅仅是以上精确包含, 怎么实现? 例如:

  • 包含多少个以上的标签
  • 有百分之多少以上的标签重合

复用上面的数据, 换上smlar插件和索引来实现以上功能.

创建smlar插件

postgres=# create extension smlar ;  
CREATE EXTENSION

换上smlar索引

drop index tbl_users_tags_idx;  
create index on tbl_users using gin (tags _int4_sml_ops);

smlar插件的%操作符用来表达数组近似度过滤.

postgres=# explain analyze select count(*) from tbl_users where tags % array[1,2,3];  
                                                                QUERY PLAN  
-------------------------------------------------------------------------------------------------------------------------------------------  
 Aggregate  (cost=1132.46..1132.47 rows=1 width=8) (actual time=75.613..75.614 rows=1 loops=1)  
   ->  Bitmap Heap Scan on tbl_users  (cost=35.25..1129.96 rows=1000 width=0) (actual time=75.609..75.610 rows=0 loops=1)  
         Recheck Cond: (tags % '{1,2,3}'::integer[])  
         Rows Removed by Index Recheck: 15059  
         Heap Blocks: exact=13734  
         ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..35.00 rows=1000 width=0) (actual time=31.466..31.466 rows=15059 loops=1)  
               Index Cond: (tags % '{1,2,3}'::integer[])  
 Planning Time: 0.408 ms  
 Execution Time: 75.687 ms  
(9 rows)

smlar插件支持的参数配置如下, 通过配置这些参数, 我们可以控制按什么算法来计算相似度, 相似度的过滤阈值是多少?

postgres=# select name,setting,enumvals,extra_desc from pg_settings where name ~ 'smlar';  
          name          | setting |        enumvals        |                                 extra_desc  
------------------------+---------+------------------------+-----------------------------------------------------------------------------  
 smlar.idf_plus_one     | off     |                        | Calculate idf by log(1+d/df)  
 smlar.persistent_cache | off     |                        | Cache of global stat is stored in transaction-independent memory  
 smlar.stattable        |         |                        | Named table stores global frequencies of array's elements  
 smlar.tf_method        | n       | {n,log,const}          | TF method: n => number of entries, log => 1+log(n), const => constant value  
 smlar.threshold        | 0.6     |                        | Array's with similarity lower than threshold are not similar by % operation  
 smlar.type             | cosine  | {cosine,tfidf,overlap} | Type of similarity formula: cosine(default), tfidf, overlap  
(6 rows)

接下来我们来实现上述两种近似搜索:

  • 包含多少个以上的标签
  • 有百分之多少以上的标签重合

包含多少个以上的标签, smlar.type = overlap  ,  smlar.threshold = INT

set smlar.type = overlap;  
set smlar.threshold = 1;  -- 精确匹配  
select uid from tbl_users where tags % array[2];  
set smlar.type = overlap;  
set smlar.threshold = 1;  -- 匹配到1个以上标签  
select uid from tbl_users where tags % array[2,8];  
set smlar.type = overlap;  
set smlar.threshold = 2;  -- 精确匹配  
select uid from tbl_users where tags % array[2,2696];  
set smlar.type = overlap;  
set smlar.threshold = 5;  -- 匹配到5个以上标签  
select uid from tbl_users where tags % array[2,4356,5064,5711,7363,9417,9444];  
set smlar.type = overlap;  
set smlar.threshold = 6;  -- 精确匹配  
select uid from tbl_users where tags % array[4356,5064,5711,7363,9417,9444];

使用smlar插件, 数组类型和gin索引后, 查看执行计划和耗时如下:

postgres=# explain analyze select uid from tbl_users where tags % array[2];  
                                                             QUERY PLAN  
-------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl_users  (cost=17.65..1112.36 rows=1000 width=4) (actual time=38.272..306.985 rows=77129 loops=1)  
   Recheck Cond: (tags % '{2}'::integer[])  
   Heap Blocks: exact=50082  
   ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..17.40 rows=1000 width=0) (actual time=26.498..26.498 rows=77129 loops=1)  
         Index Cond: (tags % '{2}'::integer[])  
 Planning Time: 0.414 ms  
 Execution Time: 309.182 ms  
(7 rows)  
postgres=# explain analyze select uid from tbl_users where tags % array[2,8];  
                                                              QUERY PLAN  
--------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl_users  (cost=26.45..1121.16 rows=1000 width=4) (actual time=33.378..790.183 rows=149118 loops=1)  
   Recheck Cond: (tags % '{2,8}'::integer[])  
   Rows Removed by Index Recheck: 351146  
   Heap Blocks: exact=35117 lossy=33064  
   ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..26.20 rows=1000 width=0) (actual time=29.934..29.934 rows=149118 loops=1)  
         Index Cond: (tags % '{2,8}'::integer[])  
 Planning Time: 0.924 ms  
 Execution Time: 794.029 ms  
(8 rows)  
postgres=# explain analyze select uid from tbl_users where tags % array[2,2696];  
                                                            QUERY PLAN  
----------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl_users  (cost=26.45..1121.16 rows=1000 width=4) (actual time=6.287..26.042 rows=1028 loops=1)  
   Recheck Cond: (tags % '{2,2696}'::integer[])  
   Heap Blocks: exact=1019  
   ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..26.20 rows=1000 width=0) (actual time=5.956..5.956 rows=1028 loops=1)  
         Index Cond: (tags % '{2,2696}'::integer[])  
 Planning Time: 0.439 ms  
 Execution Time: 26.218 ms  
(7 rows)  
postgres=# explain analyze select uid from tbl_users where tags % array[2,4356,5064,5711,7363,9417,9444];  
                                                           QUERY PLAN  
---------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl_users  (cost=70.45..1165.16 rows=1000 width=4) (actual time=13.211..13.212 rows=0 loops=1)  
   Recheck Cond: (tags % '{2,4356,5064,5711,7363,9417,9444}'::integer[])  
   ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..70.20 rows=1000 width=0) (actual time=13.204..13.205 rows=0 loops=1)  
         Index Cond: (tags % '{2,4356,5064,5711,7363,9417,9444}'::integer[])  
 Planning Time: 0.204 ms  
 Execution Time: 13.264 ms  
(6 rows)  
postgres=# explain analyze select uid from tbl_users where tags % array[4356,5064,5711,7363,9417,9444];  
                                                           QUERY PLAN  
---------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl_users  (cost=61.65..1156.36 rows=1000 width=4) (actual time=11.364..11.366 rows=0 loops=1)  
   Recheck Cond: (tags % '{4356,5064,5711,7363,9417,9444}'::integer[])  
   ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..61.40 rows=1000 width=0) (actual time=11.357..11.358 rows=0 loops=1)  
         Index Cond: (tags % '{4356,5064,5711,7363,9417,9444}'::integer[])  
 Planning Time: 0.264 ms  
 Execution Time: 11.447 ms  
(6 rows)

有百分之多少以上的标签重合, smlar.type = cosine    ,  smlar.threshold = FLOAT

set smlar.type = cosine;  
set smlar.threshold = 1;  -- 精确匹配, 目标也必须只包含2, 相当于相等  
select uid from tbl_users where tags % array[2];  
set smlar.type = cosine;  
set smlar.threshold = 0.5;  -- 两组标签的交集(重叠标签)占两组标签叠加(并集)后的50%以上  
select uid from tbl_users where tags % array[2,8];  
set smlar.type = cosine;  
set smlar.threshold = 1;  -- 精确匹配, 两组标签的交集(重叠标签)占两组标签叠加(并集)后的100%以上  
select uid from tbl_users where tags % array[2,2696];  
set smlar.type = cosine;  
set smlar.threshold = 0.7;  -- 两组标签的交集(重叠标签)占两组标签叠加(并集)后的70%以上  
select uid from tbl_users where tags % array[2,4356,5064,5711,7363,9417,9444];  
set smlar.type = cosine;  
set smlar.threshold = 0.9;  -- 两组标签的交集(重叠标签)占两组标签叠加(并集)后的90%以上  
select uid from tbl_users where tags % array[4356,5064,5711,7363,9417,9444];

使用smlar插件, 数组类型和gin索引后, 查看执行计划和耗时如下:

postgres=# explain analyze select uid from tbl_users where tags % array[2];  
                                                             QUERY PLAN  
-------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl_users  (cost=17.65..1112.36 rows=1000 width=4) (actual time=301.094..301.094 rows=0 loops=1)  
   Recheck Cond: (tags % '{2}'::integer[])  
   Rows Removed by Index Recheck: 77129  
   Heap Blocks: exact=50082  
   ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..17.40 rows=1000 width=0) (actual time=25.659..25.659 rows=77129 loops=1)  
         Index Cond: (tags % '{2}'::integer[])  
 Planning Time: 0.252 ms  
 Execution Time: 301.135 ms  
(8 rows)  
postgres=# explain analyze select uid from tbl_users where tags % array[2,8];  
                                                              QUERY PLAN  
--------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl_users  (cost=26.45..1121.16 rows=1000 width=4) (actual time=799.554..799.554 rows=0 loops=1)  
   Recheck Cond: (tags % '{2,8}'::integer[])  
   Rows Removed by Index Recheck: 500264  
   Heap Blocks: exact=35117 lossy=33064  
   ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..26.20 rows=1000 width=0) (actual time=43.356..43.356 rows=149118 loops=1)  
         Index Cond: (tags % '{2,8}'::integer[])  
 Planning Time: 0.379 ms  
 Execution Time: 799.611 ms  
(8 rows)  
postgres=# explain analyze select uid from tbl_users where tags % array[2,2696];  
                                                            QUERY PLAN  
----------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl_users  (cost=26.45..1121.16 rows=1000 width=4) (actual time=26.476..26.478 rows=0 loops=1)  
   Recheck Cond: (tags % '{2,2696}'::integer[])  
   Rows Removed by Index Recheck: 1028  
   Heap Blocks: exact=1019  
   ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..26.20 rows=1000 width=0) (actual time=5.242..5.242 rows=1028 loops=1)  
         Index Cond: (tags % '{2,2696}'::integer[])  
 Planning Time: 0.570 ms  
 Execution Time: 26.570 ms  
(8 rows)  
postgres=# explain analyze select uid from tbl_users where tags % array[2,4356,5064,5711,7363,9417,9444];  
                                                           QUERY PLAN  
---------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl_users  (cost=70.45..1165.16 rows=1000 width=4) (actual time=16.722..16.723 rows=0 loops=1)  
   Recheck Cond: (tags % '{2,4356,5064,5711,7363,9417,9444}'::integer[])  
   Rows Removed by Index Recheck: 8  
   Heap Blocks: exact=8  
   ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..70.20 rows=1000 width=0) (actual time=16.586..16.587 rows=8 loops=1)  
         Index Cond: (tags % '{2,4356,5064,5711,7363,9417,9444}'::integer[])  
 Planning Time: 0.276 ms  
 Execution Time: 16.795 ms  
(8 rows)  
postgres=# explain analyze select uid from tbl_users where tags % array[4356,5064,5711,7363,9417,9444];  
                                                          QUERY PLAN  
-------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl_users  (cost=61.65..1156.36 rows=1000 width=4) (actual time=9.755..9.757 rows=0 loops=1)  
   Recheck Cond: (tags % '{4356,5064,5711,7363,9417,9444}'::integer[])  
   ->  Bitmap Index Scan on tbl_users_tags_idx  (cost=0.00..61.40 rows=1000 width=0) (actual time=9.748..9.749 rows=0 loops=1)  
         Index Cond: (tags % '{4356,5064,5711,7363,9417,9444}'::integer[])  
 Planning Time: 0.294 ms  
 Execution Time: 9.811 ms  
(6 rows)

对照

方法 SQL1 耗时 ms SQL2 耗时 ms SQL3 耗时 ms SQL4 耗时 ms SQL5 耗时 ms
传统字符串 + 全表扫描 1110.267 2004.062 2060.434 6767.990 6208.191
传统字符串 + 模糊搜索 + gin索引加速 78.163 814.748 902.637 4050.040 3691.329
数组 + gin索引加速 74.015 18.561 4.750 5.523 4.528
数组(重叠个数)相似度搜索 + gin索引加速 309.182 794.029 26.218 13.264 11.447
数组(重叠占比)相似度搜索 + gin索引加速 301.135 799.611 26.570 16.795 9.811

知识点

1、数组类型

2、gin索引

3、smlar 插件

更多算法参考: https://github.com/jirutka/smlar

4、pg_trgm 插件

思考

pg_trgm插件对字符串做了什么处理, 可以利用gin索引加速模糊查询加速?

smlar插件是如何通过索引快速判断两个数组的相似性达到阈值的?

为什么多个模糊匹配条件使用and条件后, 性能下降严重?

为什么使用数组类型后, 标签条件越多性能越好?

如果多个模糊匹配条件是or 条件呢? 性能会下降还是提升?

还有什么业务场景会用到数组?

还有哪些业务场景会用到字符串模糊匹配?

还有什么业务场景非常适合使用数组相似的功能?

除了使用标签匹配来圈选相似目标人群, 还可不可以使用其他方式圈选? 例如向量距离?

使用标签匹配时, 如果我们要排除某些标签, 而不是包含某些标签, 应该如何写sql, 性能又会怎么样呢?

为什么使用smlar进行相似度过滤时, 相似度越高性能越好?

SQL圈选性能和返回符合条件的用户记录数有没有关系? 是什么关系?

当使用pg_trgm进行模糊搜索加速时, 如果字符串中包含wchar(例如中文)时性能如果很差要怎么办? 如果需要模糊搜索的字符只有1个或2个字符时性能如果很差要怎么办?

参考

202308/20230807_01.md 《又一款PostgreSQL 向量索引插件 pgvecto.rs , 支持ivfflat, hnsw. 助力非结构化数据的特征向量相似搜索》
202307/20230726_02.md 《标准知识库 + PostgreSQL或PolarDB + 向量插件 + openai(或其他大模型) 提升通用ai机器人在专业领域的精准度》
202307/20230706_05.md 《PostgreSQL 或PolarDB 使用插件pg_tiktoken - 使用 OpenAI tiktoken库文本向量化(tokenization) - 使用分词算法BPE - NLP 自然语言处理》
202307/20230704_01.md 《hydra, 一款基于PostgreSQL的开源HTAP数据库. 支持列存,向量化,物化,冷热分离存储,cloud 等特性》
202306/20230605_01.md 《PostgreSQL又一个向量插件hnsw, 建议关注》
202304/20230419_02.md 《rust pub use 和 PostgreSQL WITH GRANT OPTION 设计相似之处》
202304/20230410_03.md 《PostgreSQL 16 preview - 数组随机打散和随机提取功能 : array_shuffle() , array_sample()》
202303/20230331_07.md 《ChatGPT背后的数据库技术体验 - 向量近似搜索之 milvus(专业的向量数据库)》
202303/20230330_03.md 《ChatGPT背后的数据库技术体验 - 向量近似搜索之 lance》
202303/20230330_01.md 《ChatGPT背后的数据库技术体验 - 向量近似搜索之 PostgreSQL+pase(hnsw,ivfflat,ivfpq)》
202303/20230321_01.md 《德说-第212期, 如何从“智”人到“慧”人? - 到底怎么和AI合作|驾驭AI? - 关于prompt engineer - 关于怎么提问》
202303/20230319_01.md 《DuckDB 存储生态: lance(向量存储引擎): Modern columnar data format for ML/超越parquet》
202303/20230306_03.md 《使用DuckDB 分解深度嵌套的 JSON,一次一个向量》
202301/20230110_04.md 《PostgreSQL 16 preview - 优化gin索引代价估算算法》
202212/20221223_01.md 《使用 PolarDB 开源版 smlar 插件进行高效率相似文本搜索、自助选药、相似人群圈选等业务》
202212/20221222_04.md 《使用 PolarDB 开源版 和 imgsmlr 存储图像特征值以及快速的进行图像相似搜索》
202212/20221222_03.md 《使用 PolarDB 开源版 采用array数组和gin索引高效率解决用户画像、实时精准营销类业务需求》
202212/20221221_02.md 《PolarDB 开源版通过 pg_trgm GIN 索引实现高效率 like '%xxx%' 模糊查询》
202212/20221217_01.md 《PolarDB 开源版通过 parray_gin 实现高效率 数组、JSON 内元素的模糊搜索》
202212/20221209_01.md 《PolarDB 开源版通过pg_similarity实现17种文本相似搜索 - token归一切分, 根据文本相似度检索相似文本.》
202212/20221201_02.md 《ChatGPT背后的数据库技术体验 - 向量近似搜索之 pgvector : 如何用 PolarDB 在不确定世界寻找确定答案 (例如图像相似) - pgvector|pase》
202208/20220829_03.md 《DuckDB:list,struct,map 类型很强大(支持lambda计算) - PostgreSQL:数组、row|record、json字典》
202208/20220829_02.md 《DuckDB 字符串相似性计算函数》
202208/20220809_01.md 《PostgreSQL 14 函数|存储过程 支持begin atomic 代码块, 提升sql函数|过程安全性》
202203/20220323_02.md 《JSON 局部相似 搜索例子》
202203/20220309_02.md 《PostgreSQL 15 preview - 逻辑复制、逻辑订阅增加错误信息errcontext(包含LSN). 结合pg_replication_origin_advance跳过冲突的wal回放》
202203/20220302_01.md 《PostgreSQL + FDW + vector 插件加速向量检索 - 在不确定世界寻找确定答案 (例如图像相似)》
202110/20211005_01.md 《PostgreSQL 数组或JSON内容的模糊匹配索引插件: parray_gin》
202109/20210915_03.md 《DB吐槽大会,第43期 - PG 倒排索引启动和recheck代价高》
202109/20210906_02.md 《B2B营销的第一性原理》
202108/20210805_05.md 《PostgreSQL 15 preview - 内置逻辑订阅sub/pub支持2PC, 内置pgoutput decoding plugin openapi支持2PC》
202107/20210729_03.md 《一种新的向量检索索引 DiskANN: Fast Accurate Billion-point Nearest Neighbor Search on a Single Node》
202107/20210727_01.md 《为什么数据库细分赛道越来越多, 发展越来越快? - 含dbengine统计》
202107/20210723_01.md 《为什么向量数据要归一化?》
202107/20210709_01.md 《实时营销, 人群圈选推荐业务 性能优化 - memory copy+rb contains计算瓶颈 - rb hash分片》
202106/20210623_01.md 《《开慧社》第二期《我朋友的创业故事》- Zilliz 向量数据库创始人 星爵 访谈》
202105/20210531_02.md 《重新发现PostgreSQL之美 - 9 面向多值列的倒排索引GIN|RUM》
202105/20210514_03.md 《PostgreSQL 开源 高维向量相似搜索插件 vector - 关联阿里云rds pg pase, cube, 人脸识别》
202105/20210510_02.md 《PostgreSQL 应用开发解决方案最佳实践系列课程 - 8. 树状图谱关系系统(营销、分销、流量变现、分佣、引爆流行、裂变式传播、家谱、选课、社交、人才库、刑侦、农产品溯源、药品溯源)》
202105/20210510_01.md 《PostgreSQL 应用开发解决方案最佳实践系列课程 - 7. 标签搜索和圈选、相似搜索和圈选、任意字段组合搜索和圈选系统》
202105/20210506_01.md 《PostgreSQL 应用开发解决方案最佳实践系列课程 - 3. 人脸识别和向量相似搜索》
202104/20210406_05.md 《PostgreSQL 14 preview - pg_logical_emit_message() 往WAL写定制消息 , pgoutput 内置decode plugin可以解析这个消息了!》
202103/20210330_03.md 《PostgreSQL 14 preview - Add a xid argument to the filter_prepare callback for output plugins - logical decoding》
202103/20210312_04.md 《该如何衡量产品营销的成功 How to Measure the Success of Product Marketing (GTM Go-To-Market)》
202103/20210312_03.md 《关于进入市场策略(Go-to-Market Strategy)的一切 - 市场营销, 把握用户痛点、产品规划、产品变现闭环》
202103/20210311_02.md 《使用PostgreSQL plpythonu实现一个推荐引擎 - Building a recommendation engine inside Postgres with Python and Pandas》
202103/20210310_01.md 《马斯洛需求层次理论 - 五层需求 八层需求 - 社区、经济学、心理学、营销、GTM(Go-To-Market)相关》
202101/20210125_01.md 《用dbengine 数据 评估 PostgreSQL 全球占有率》
202101/20210119_03.md 《PostgreSQL SQL 技巧例子 - 网站数据解析和copy LOAD, json处理, 字符串处理, 数组处理, CTE与法, 函数, inline code , 数据ETL (generated from原始数据)》
202101/20210111_01.md 《PostgreSQL 14 preview - 向量化IO功能 (pwritev, preadv) , IO性能增强》
202101/20210108_01.md 《PostgreSQL 14 preview - 增强语法解析器 例如复杂类型数组的底层结构赋值 - Add the ability for the core grammar to have more than one parse target.》
202101/20210105_01.md 《庆祝PostgreSQL 3次夺得DB-engine年度数据库桂冠, 阿里云PG创新营开课》
202101/20210103_01.md 《PostgreSQL 文本相似搜索 - pg_trgm_pro - 包含则1, 不包含则计算token相似百分比》
202012/20201204_02.md 《10亿级云资源TAG管理, 实时写入和搜索数据库设计 - gin+btree_gin 倒排搜索》
202010/20201022_01.md 《PostgreSQL 牛逼的分析型功能 - 列存储、向量计算 FDW - DuckDB_fdw - 无数据库服务式本地lib库+本地存储》
202009/20200930_01.md 《PostgreSQL 在资源搜索中的设计 - pase, smlar, pg_trgm - 标签+权重相似排序 - 标签的命中率排序》
202009/20200912_01.md 《PostgreSQL 模糊查询、相似查询 (like '%xxx%') pg_bigm 比 pg_trgm 优势在哪?》
202009/20200911_04.md 《PostgreSQL 人物采访 - Regina Obe》
202008/20200803_11.md 《PostgreSQL 14 扩展逻辑decoding API 应对长事务逻辑复制优化 - Extend the logical decoding output plugin API with stream methods.》
202004/20200424_01.md 《PostgreSQL 向量相似推荐设计 - pase》
202004/20200421_01.md 《社交、电商、游戏等 推荐系统 (相似推荐) - 阿里云pase smlar索引方案对比》
202004/20200412_01.md 《PostgreSQL Polygon Averaging in PostGIS - 多边形叠加统计》
202004/20200403_02.md 《PostgreSQL 日志 ring buffer 功能 - pg_logging》
202003/20200330_01.md 《PostgreSQL 模糊查询增强插件pgroonga , pgbigm (含单字、双字、多字、多字节字符) - 支持JSON模糊查询等》
202003/20200326_19.md 《PostgreSQL 非易失内存 改进版本 - nvm logging 改进》
202003/20200326_16.md 《PostgreSQL pgloggingfilter csvlog log 日志过滤器》
202003/20200326_08.md 《PostgreSQL ghtree实现的海明距离排序索引, 性能不错(模糊图像) - pg-knn_hamming - bit string 比特字符串 相似度搜索》
202003/20200324_29.md 《PostgreSQL bktree 索引using gist例子 - 海明距离检索 - 短文相似、模糊图像搜索 - bit string 比特字符串 相似度搜索》
202002/20200227_01.md 《阿里云PostgreSQL案例精选2 - 图像识别、人脸识别、相似特征检索、相似人群圈选》
202002/20200220_01.md 《阿里云PostgreSQL案例精选1 - 实时精准营销、人群圈选》
202001/20200116_01.md 《PostgreSQL+MySQL 联合解决方案 - 第12课视频 - 全文检索、中文分词、模糊查询、相似文本查询》
202001/20200115_01.md 《PostgreSQL+MySQL 联合解决方案 - 第11课视频 - 多维向量相似搜索 - 图像识别、相似人群圈选等》
202001/20200114_01.md 《PostgreSQL+MySQL 联合解决方案 - 第10课视频 - 任意字段、维度组合搜索(含GIS、数组、全文检索等属性)》
202001/20200113_01.md 《PostgreSQL+MySQL 联合解决方案 - 第9课视频 - 实时精准营销(精准圈选、相似扩选、用户画像)》
202001/20200101_06.md 《PostgreSQL 13 preview - gin倒排索引性能优化 - 防止gin full scan(逻辑推理)》
201912/20191219_02.md 《PostgreSQL 阿里云rds pg发布高维向量索引,支持图像识别、人脸识别 - pase 插件, 以及ivfflat,hnsw搜索算法说明》
201912/20191210_01.md 《PostgreSQL Tutorial for Oracle, MySQL DBAs and Beginners》
201911/20191128_02.md 《画像系统标准化设计 - PostgreSQL roaringbitmap, varbitx , 正向关系, 反向关系, 圈选, 相似扩选(向量相似扩选)》
201910/20191030_01.md 《PostgreSQL batch insert - jdbc reWriteBatchedInserts 批量写入 (copy,insert,begin commit,group commit)》
201909/20190919_01.md 《PostgreSQL 内置Logical Replication output plugin pgoutput》
201909/20190901_06.md 《PostgreSQL 任意类型、任意数组 - 隐式类型转换 - anyelement anytype anydata anyarray commontype commonarray》
201908/20190815_01.md 《阿里云PostgreSQL 向量搜索、相似搜索、图像搜索 插件 palaemon - ivfflat , hnsw , nsg , ssg》
201907/20190706_01.md 《PostgreSQL rum 索引结构 - 比gin posting list|tree 的ctid(行号)多了addition info》
201903/20190320_01.md 《PostgreSQL 数组里面的元素,模糊搜索,模糊查询,like,前后百分号,正则查询,倒排索引》
201902/20190216_01.md 《PostgreSQL 列存, 混合存储, 列存索引, 向量化存储, 混合索引 - OLTP OLAP OLXP HTAP 混合负载应用》
201901/20190116_01.md 《地理位置画像、连锁店圈人、地理围栏圈人、多地圈选、multi-polygon圈选、多点圈选》
201811/20181129_01.md 《PostgreSQL 多维、图像 欧式距离、向量距离、向量相似 查询优化 - cube,imgsmlr - 压缩、分段、异步并行》
201810/20181011_01.md 《PostgreSQL 相似人群圈选,人群扩选,向量相似 使用实践 - cube》
201810/20181009_01.md 《PostgreSQL 随机记录返回 - 300倍提速实践 (随机数组下标代替order by random())》
201810/20181003_02.md 《PostgreSQL bloom filter index 扩展 for bigint》
201809/20180919_02.md 《PostgreSQL pageinspect 诊断与优化GIN (倒排) 索引合并延迟导致的查询性能下降问题》
201809/20180905_03.md 《PostgreSQL 任意字段数组合 AND\OR 条件,指定返回结果条数,构造测试数据算法举例》
201809/20180904_05.md 《Greenplum 优化CASE - 对齐JOIN字段类型,使用数组代替字符串,降低字符串处理开销,列存降低扫描开销》
201809/20180904_04.md 《PostgreSQL 11 相似图像搜索插件 imgsmlr 性能测试与优化 3 - citus 8机128shard (4亿图像)》
201809/20180904_03.md 《PostgreSQL 11 相似图像搜索插件 imgsmlr 性能测试与优化 2 - 单机分区表 (dblink 异步调用并行) (4亿图像)》
201809/20180904_02.md 《PostgreSQL 11 相似图像搜索插件 imgsmlr 性能测试与优化 1 - 单机单表 (4亿图像)》
201809/20180904_01.md 《PostgreSQL 相似搜索插件介绍大汇总 (cube,rum,pg_trgm,smlar,imgsmlr,pg_similarity) (rum,gin,gist)》
201808/20180803_01.md 《PostgreSQL 设计优化case - 大宽表任意字段组合查询索引如何选择(btree, gin, rum) - (含单个索引列数超过32列的方法)》
201808/20180801_01.md 《PostgreSQL 设计优化case - 多对多 转 一对多(数组)》
201807/20180703_02.md 《PostgreSQL 电商业务(任意维度商品圈选应用) - json包range数组的命中优化 - 展开+索引优化》
201806/20180607_02.md 《Greenplum 轨迹相似(伴随分析)》
201805/20180518_02.md 《PostgreSQL ADHoc(任意字段组合)查询(rums索引加速) - 非字典化,普通、数组等组合字段生成新数组》
201803/20180329_01.md 《PostgreSQL 相似文本检索与去重 - (银屑病怎么治?银屑病怎么治疗?银屑病怎么治疗好?银屑病怎么能治疗好?)》
201803/20180310_03.md 《[未完待续] [翻译] PostgreSQL 插件开发 3 (调试) - Writing Postgres Extensions - Debugging》
201803/20180306_01.md 《PostgreSQL 多值类型(如数组),元素值全局唯一 - 约束如何实现》
201802/20180226_04.md 《PostgreSQL 11 preview - 支持 数组外键约束》
201802/20180210_01.md 《PostgreSQL VOPS 向量计算 + DBLINK异步并行 - 单实例 10亿 聚合计算跑进2秒》
201802/20180208_02.md 《PostgreSQL 多个数组聚合为一维数组加速(array_agg)》
201802/20180208_01.md 《PostgreSQL 店铺运营实践 - JSON[]数组 内部标签数据等值、范围检索100倍+加速示例 (含,单值+多值列合成)》
201802/20180207_02.md 《PostgreSQL UDF实现tsvector(全文检索), array(数组)多值字段与scalar(单值字段)类型的整合索引(类分区索引) - 单值与多值类型复合查询性能提速100倍+ 案例 (含,单值+多值列合成)》
201802/20180205_03.md 《PostgreSQL 相似搜索分布式架构设计与实践 - dblink异步调用与多机并行(远程 游标+记录 UDF实例)》
201802/20180202_01.md 《PostgreSQL 相似搜索设计与性能 - 地址、QA、POI等文本 毫秒级相似搜索实践》
201801/20180127_01.md 《Greenplum roaring bitmap与业务场景 (类阿里云RDS PG varbitx, 应用于海量用户 实时画像和圈选、透视)》
201801/20180124_02.md 《PostgreSQL 多重含义数组检索与条件过滤 (标签1:属性, 标签n:属性) - 包括UPSERT操作如何修改数组、追加数组元素》
201801/20180119_04.md 《PostgreSQL 10 自定义并行计算聚合函数的原理与实践 - (含array_agg合并多个数组为单个一元数组的例子)》
201712/20171227_01.md 《PostgreSQL 遗传学应用 - 矩阵相似距离计算 (欧式距离,...XX距离)》
201712/20171205_02.md 《用PostgreSQL 做实时高效 搜索引擎 - 全文检索、模糊查询、正则查询、相似查询、ADHOC查询》
201711/20171126_01.md 《PostgreSQL手机行业经营分析、决策系统设计 - 实时圈选、透视、估算》
201711/20171121_02.md 《乱序写入导致的索引膨胀(B-tree, GIN, GiST皆如此)》
201711/20171107_20.md 《HTAP数据库 PostgreSQL 场景与性能测试之 19 - (OLAP) 用户画像圈人场景 - 数组相交查询与聚合》
201711/20171107_19.md 《HTAP数据库 PostgreSQL 场景与性能测试之 18 - (OLAP) 用户画像圈人场景 - 数组包含查询与聚合》
201711/20171107_18.md 《HTAP数据库 PostgreSQL 场景与性能测试之 17 - (OLTP) 数组相似查询》
201711/20171107_17.md 《HTAP数据库 PostgreSQL 场景与性能测试之 16 - (OLTP) 文本特征向量 - 相似特征(海明...)查询》
201711/20171107_14.md 《HTAP数据库 PostgreSQL 场景与性能测试之 13 - (OLTP) 字符串搜索 - 相似查询》
201711/20171107_09.md 《HTAP数据库 PostgreSQL 场景与性能测试之 8 - (OLTP) 多值类型(数组)含索引实时写入》
201710/20171001_04.md 《[未完待续] PostgreSQL NVM Logging》
201709/20170905_01.md 《PostgreSQL 黑科技 - 空间聚集存储, 使用gevel插件(增强pageinspect)内窥GIN, GiST, SP-GiST索引》
201708/20170804_01.md 《海量数据,海明(simhash)距离高效检索(smlar) - 阿里云RDS PosgreSQL最佳实践 - bit string 比特字符串 相似度搜索》
201708/20170802_01.md 《plpgsql 编程 - JSON数组循环》
201707/20170704_01.md 《PostgreSQL 10 GIN索引 锁优化》
201706/20170627_01.md 《PostgreSQL 9种索引的原理和应用场景》
201706/20170617_01.md 《自动选择正确索引访问接口(btree,hash,gin,gist,sp-gist,brin,bitmap...)的方法》
201706/20170612_05.md 《索引扫描优化之 - GIN数据重组优化(按元素聚合) 想象在玩多阶魔方》
201706/20170612_04.md 《懒人推动社会进步 - 多列聚合, gin与数据分布(选择性)》
201705/20170524_01.md 《17种文本相似算法与GIN索引 - pg_similarity》
201704/20170410_02.md 《为什么啤酒和纸尿裤最搭 - 用HybridDB/PostgreSQL查询商品营销最佳组合》
201704/20170408_01.md 《Greenplum 自定义复合类型 数组》
201703/20170329_02.md 《PostgreSQL 10.0 preview 性能增强 - GIN索引vacuum锁降低》
201703/20170328_04.md 《视觉挖掘与PostGIS空间数据库的完美邂逅 - 广告营销\圈人》
201703/20170313_06.md 《PostgreSQL 10.0 preview 功能增强 - OLAP增强 向量聚集索引(列存储扩展)》
201702/20170225_01.md 《PostgreSQL 向量化执行插件(瓦片式实现-vops) 10x提速OLAP》
201702/20170221_01.md 《PostgreSQL GIN 单列聚集索引 应用》
201702/20170205_01.md 《宝剑赠英雄 - 任意组合字段等效查询, 探探PostgreSQL多列展开式B树 (GIN)》
201702/20170204_01.md 《PostgreSQL GIN索引实现原理》
201702/20170203_01.md 《PostgreSQL GIN multi-key search 优化》
201701/20170116_04.md 《PostgreSQL结合余弦、线性相关算法 在文本、图片、数组相似 等领域的应用 - 3 rum, smlar应用场景分析》
201701/20170116_03.md 《PostgreSQL结合余弦、线性相关算法 在文本、图片、数组相似 等领域的应用 - 2 smlar插件详解》
201701/20170116_02.md 《PostgreSQL结合余弦、线性相关算法 在文本、图片、数组相似 等领域的应用 - 1 文本(关键词)分析理论基础 - TF(Term Frequency 词频)/IDF(Inverse Document Frequency 逆向文本频率)》
201701/20170112_02.md 《导购系统 - 电商内容去重\内容筛选应用(实时识别转载\盗图\侵权?) - 文本、图片集、商品集、数组相似判定的优化和索引技术》
201612/20161231_01.md 《从难缠的模糊查询聊开 - PostgreSQL独门绝招之一 GIN , GiST , SP-GiST , RUM 索引原理与技术背景》
201612/20161225_01.md 《恭迎万亿级营销(圈人)潇洒的迈入毫秒时代 - 万亿user_tags级实时推荐系统数据库设计》
201612/20161222_02.md 《从相似度算法谈起 - Effective similarity search in PostgreSQL》
201611/20161119_01.md 《PostgreSQL 数组忽略大小写匹配》
201611/20161118_01.md 《聊一聊双十一背后的技术 - 毫秒分词算啥, 试试正则和相似度》
201609/20160910_01.md 《如何从PostgreSQL json中提取数组》
201608/20160817_01.md 《PostgreSQL 文本数据分析实践之 - 相似度分析》
201607/20160728_01.md 《为PostgreSQL讨说法 - 浅析《UBER ENGINEERING SWITCHED FROM POSTGRES TO MYSQL》》
201605/20160507_02.md 《PostgreSQL GIN索引limit慢的原因分析》
201605/20160506_02.md 《中文模糊查询性能优化 by PostgreSQL trgm》
201509/20150915_01.md 《beyond b-tree (gin\gist索引讲解PDF)》
201305/20130516_01.md 《PostgreSQL 9.3 pg_trgm imporve support multi-bytes char and gist,gin index for reg-exp search》
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
8月前
|
SQL 关系型数据库 测试技术
沉浸式学习PostgreSQL|PolarDB 20: 学习成为数据库大师级别的优化技能
在上一个实验《沉浸式学习PostgreSQL|PolarDB 19: 体验最流行的开源企业ERP软件 odoo》 中, 学习了如何部署odoo和polardb|pg. 由于ODOO是非常复杂的ERP软件, 对于关系数据库的挑战也非常大, 所以通过odoo业务可以更快速提升同学的数据库优化能力, 发现业务对数据库的使用问题(如索引、事务对锁的运用逻辑问题), 数据库的代码缺陷, 参数或环境配置问题, 系统瓶颈等.
868 1
|
8月前
|
关系型数据库 分布式数据库 数据库
沉浸式学习PostgreSQL|PolarDB 19: 体验最流行的开源企业ERP软件 odoo
本文主要教大家怎么用好数据库, 而不是怎么运维管理数据库、怎么开发数据库内核.
1022 2
|
9月前
|
人工智能 关系型数据库 分布式数据库
沉浸式学习PostgreSQL|PolarDB 16: 植入通义千问大模型+文本向量化模型, 让数据库具备AI能力
本文将带领大家来体验一下如何将“千问大模型+文本向量化模型”植入到PG|PolarDB中, 让数据库具备AI能力.
24634 19
沉浸式学习PostgreSQL|PolarDB 16: 植入通义千问大模型+文本向量化模型, 让数据库具备AI能力
|
18天前
|
监控 关系型数据库 分布式数据库
【PolarDB开源】PolarDB在电商场景的应用:应对高并发与数据一致性挑战
【5月更文挑战第26天】阿里云PolarDB是为电商解决高并发和数据一致性问题的云原生数据库。它采用读写分离、弹性扩展和分布式缓存策略应对高并发,通过全局时钟、分布式事务和数据复制保证数据一致性。在大型促销活动中,电商平台可提前扩容、启用读写分离、优化索引并设置监控告警来应对挑战。PolarDB助力电商构建高性能、高可用的数据处理系统,赢得市场优势。
119 1
|
29天前
|
存储 Cloud Native 关系型数据库
PolarDB-X 是面向超高并发、海量存储和复杂查询场景设计的云原生分布式数据库系统
【5月更文挑战第14天】PolarDB-X 是面向超高并发、海量存储和复杂查询场景设计的云原生分布式数据库系统
136 2
|
8月前
|
关系型数据库 定位技术 分布式数据库
沉浸式学习PostgreSQL|PolarDB 18: 通过GIS轨迹相似伴随|时态分析|轨迹驻点识别等技术对拐卖、诱骗场景进行侦查
本文主要教大家怎么用好数据库, 而不是怎么运维管理数据库、怎么开发数据库内核.
1204 1
|
9月前
|
人工智能 关系型数据库 分布式数据库
沉浸式学习PostgreSQL|PolarDB 17: 向量数据库, 通义大模型AI的外脑
本文所涉及的实验体验的就是怎么建设AI的外脑?向量数据库的核心价值:AI外脑
1395 4
|
29天前
|
关系型数据库 MySQL 分布式数据库
PolarDB MySQL企业版产品系列:满足不同场景需求的解决方案
PolarDB MySQL企业版产品系列:满足不同场景需求的解决方案 在数字化时代,企业对于数据处理的需求越来越多样化,对于数据库的选择也更为谨慎。PolarDB MySQL版为了满足不同场景的需求,提供了单节点、集群版、高压缩引擎(X-Engine)和多主集群(库表)4种不同的产品系列。下面我们将对这4种产品系列进行简要介绍,以帮助您更好地了解它们的特点和适用场景。
135 1
|
29天前
|
关系型数据库 MySQL 分布式数据库
PolarDB auto_inc场景下的性能优化实践
PolarDB auto_inc场景下的性能优化实践 在数据库的使用场景中,并发插入数据或并发导入数据场景是最常见的。针对这一场景,PolarDB MySQL版进行了深度性能优化,以提高插入性能。本文将详细介绍PolarDB在auto_inc场景下的性能优化相关内容。
67 2
|
29天前
|
分布式计算 搜索推荐 算法
用户画像系列——Lookalike在营销圈选扩量中的应用
用户画像系列——Lookalike在营销圈选扩量中的应用
92 0

热门文章

最新文章

相关产品

  • 云原生数据库 PolarDB