digoal
2023-08-24
PostgreSQL , PolarDB , 数据库 , 教学
非常欢迎数据库用户提出场景给我, 在此issue回复即可, 一起来建设沉浸式数据库学习教学素材库, 帮助开发者用好数据库, 提升开发者职业竞争力, 同时为企业降本提效.
本文的实验可以使用永久免费的阿里云云起实验室来完成.
如果你本地有docker环境也可以把镜像拉到本地来做实验:
x86_64机器使用以下docker image:
Apple Chip机器使用以下docker image:
在营销场景中, 通常会对用户的属性、行为等数据进行统计分析, 生成用户的标签, 也就是常说的用户画像.
标签举例: 男性、女性、年轻人、大学生、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)
第一种方法没有用到任何的索引, 每次请求都要扫描用户画像表的所有记录, 计算每一个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)
很显然你不能满足于前面的模糊查询索引带来的性能提升, 特别是当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)
当我们输入一组标签, 如果想放宽圈选条件, 而不仅仅是以上精确包含, 怎么实现? 例如:
- 包含多少个以上的标签
- 有百分之多少以上的标签重合
复用上面的数据, 换上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》
