PgSQL · 應用案例 · 相似人群圈選，人群擴選，向量相似 使用實踐

背景

PostgreSQL 相似插件非常多，插件的功能以及用法如下：

《PostgreSQL 相似搜尋插件介紹大彙總 (cube,rum,pg_trgm,smlar,imgsmlr,pg_similarity) (rum,gin,gist)》

相似人群分析在精準營銷，推薦系統中的需求很多。

人的屬性可以使用向量來表達，每個值代表一個屬性的權重值，通過向量相似，可以得到一群相似的人群。

例如

create table tt (  
  uid int8 primary key,  
  att1 float4,  -- 屬性1 的權重值   
  att2 float4,  -- 屬性2 的權重值  
  att3 float4,  -- 屬性3 的權重值  
  ...  
  attn float4   -- 屬性n 的權重值  
);  
           

使用cube表示屬性

create table tt (  
  uid int8 primary key,  
  att cube  -- 屬性  
);  
           

使用cube或imgsmlr可以達到類似的目的。

a <-> b float8  Euclidean distance between a and b.  
a <#> b float8  Taxicab (L-1 metric) distance between a and b.  
a <=> b float8  Chebyshev (L-inf metric) distance between a and b.  
           

但是如果向量很大(比如屬性很多)，建議使用一些方法抽象出典型的特征值，壓縮向量。 類似圖層，圖檔壓縮。實際上imgsmlr就是這麼做的：

例如256256的像素，壓縮成44的像素，存儲為特征值。

例子

1、建立插件

create extension cube;  
           

2、建立測試表

create table tt (id int , c1 cube);  
           

3、建立GIST索引

create index idx_tt_1 on tt using gist(c1);  
           

4、建立生成随機CUBE的函數

create or replace function gen_rand_cube(int,int) returns cube as $$  
  select ('('||string_agg((random()*$2)::text, ',')||')')::cube from generate_series(1,$1);  
$$ language sql strict;  
           

5、CUBE最多存100個次元

postgres=# \set VERBOSITY verbose  
  
postgres=# select gen_rand_cube(1000,10);  
  
ERROR:  22P02: invalid input syntax for cube  
DETAIL:  A cube cannot have more than 100 dimensions.  
CONTEXT:  SQL function "gen_rand_cube" statement 1  
LOCATION:  cube_yyparse, cubeparse.y:111  
           

6、寫入測試資料

insert into tt select id, gen_rand_cube(16, 10) from generate_series(1,10000) t(id);  
           

7、通過單個特征值CUBE查詢相似人群，以點搜群

select * from tt order by c1 <-> '(1,2,3,4,5,6,7)' limit x;  -- 個體搜群體  
           

8、通過多個特征值CUBE查詢相似人群，以群搜群

select * from tt order by c1 <-> '[(1,2,3,4,5,6,7),(1,3,4,5,6,71,3), ...]' limit x; -- 群體搜群體  
           

postgres=# explain select * from tt order by c1 <-> '[(1,2,3),(2,3,4)]' limit 1;  
                                QUERY PLAN                                  
--------------------------------------------------------------------------  
 Limit  (cost=0.11..0.14 rows=1 width=44)  
   ->  Index Scan using idx_tt_1 on tt  (cost=0.11..0.16 rows=2 width=44)  
         Order By: (c1 <-> '(1, 2, 3),(2, 3, 4)'::cube)  
(3 rows)  
           

9、如果需要再計算壓縮前的特征值的相似性，可以使用原始值再計算一遍。

《PostgreSQL 遺傳學應用 - 矩陣相似距離計算 (歐式距離,…XX距離)》

select *,   
  c1 <-> ?1,   -- c1表示壓縮後的特征值浮點數向量，比如（4*4）  
  distance_udf(detail_c1,?2)   -- deatil_c1 表示原始特征值浮點數向量(比如128*128)    
from tt order by c1 <-> ?1 limit xx;  
           

參考

https://www.postgresql.org/docs/devel/static/cube.html https://github.com/postgrespro/imgsmlr https://github.com/eulerto/pg_similarity 《PostgreSQL 11 相似圖像搜尋插件 imgsmlr 性能測試與優化 3 - citus 8機128shard (4億圖像)》 《PostgreSQL 11 相似圖像搜尋插件 imgsmlr 性能測試與優化 2 - 單機分區表 (dblink 異步調用并行) (4億圖像)》 《PostgreSQL 11 相似圖像搜尋插件 imgsmlr 性能測試與優化 1 - 單機單表 (4億圖像)》