PostgreSQL 相似搜索插件介绍大汇总 (cube,rum,pg_trgm,smlar,imgsmlr,pg_similarity) (rum,gin,gist)

日期

标签

背景

类别1 : 元素重叠度相似

1、rum

2、pg_trgm

3、smlar

4、smlar+海明码(向量相似)

5、pg_similarity

类别2 : 向量相似(类似knn距离)

1、imgsmlr(图片向量相似)

postgres=# \d t_img
 Table "public.t_img"
 Column | Type | Collation | Nullable | Default
--------+-----------+-----------+----------+---------
 id | integer | | not null |
 sig | signature | | |
Indexes:
 "t_img_pkey" PRIMARY KEY, btree (id)
 "idx_t_img_1" gist (sig)

postgres=# select count(*) from t_img;
 count
-----------
 319964709
(1 row)

Time: 698.075 ms

postgres=# select * from t_img order by sig <-> '(3.539080, 0.243861, 1.509150, 1.781380, 8.677560, 4.232060, 8.979810, 1.665030, 1.294100, 4.449800, 9.200450, 1.859860, 5.440250, 7.788580, 0.514258, 8.424920)' limit 1;
 id | sig
-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------
 148738668 | (2.554440, 0.310499, 2.322520, 0.478624, 7.816080, 4.360440, 8.287050, 1.011060, 2.114320, 3.541110, 9.166300, 1.922250, 4.488640, 7.897890, 1.600290, 7.462080)
(1 row)

Time: 337.301 ms

2 CUBE

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.

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t_img0 order by sig::Text::cube <-> '(0.435404, 6.602870, 9.050220, 9.379750, 2.483920, 1.534660, 0.363753, 4.079670, 0.124681, 3.611220, 7.127460, 7.880070, 2.574830, 6.778820, 5.156320, 8.329430)' limit 1;
 QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (cost=0.36..0.37 rows=1 width=76) (actual time=147.432..147.434 rows=1 loops=1)
 Output: id, sig, ((((sig)::text)::cube <-> '(0.435404, 6.60287, 9.05022, 9.37975, 2.48392, 1.53466, 0.363753, 4.07967, 0.124681, 3.61122, 7.12746, 7.88007, 2.57483, 6.77882, 5.15632, 8.32943)'::cube))
 Buffers: shared hit=16032
 -> Index Scan using idx_t_img0_1 on public.t_img0 (cost=0.36..13824.28 rows=754085 width=76) (actual time=147.430..147.430 rows=1 loops=1)
 Output: id, sig, (((sig)::text)::cube <-> '(0.435404, 6.60287, 9.05022, 9.37975, 2.48392, 1.53466, 0.363753, 4.07967, 0.124681, 3.61122, 7.12746, 7.88007, 2.57483, 6.77882, 5.15632, 8.32943)'::cube)
 Order By: (((t_img0.sig)::text)::cube <-> '(0.435404, 6.60287, 9.05022, 9.37975, 2.48392, 1.53466, 0.363753, 4.07967, 0.124681, 3.61122, 7.12746, 7.88007, 2.57483, 6.77882, 5.15632, 8.32943)'::cube)
 Buffers: shared hit=16032
 Planning Time: 0.096 ms
 Execution Time: 148.905 ms
(9 rows)

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t_img0 order by sig <-> '(0.435404, 6.602870, 9.050220, 9.379750, 2.483920, 1.534660, 0.363753, 4.079670, 0.124681, 3.611220, 7.127460, 7.880070, 2.574830, 6.778820, 5.156320, 8.329430)' limit 2;
 QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (cost=0.36..0.37 rows=2 width=72) (actual time=40.284..48.183 rows=2 loops=1)
 Output: id, sig, ((sig <-> '(0.435404, 6.602870, 9.050220, 9.379750, 2.483920, 1.534660, 0.363753, 4.079670, 0.124681, 3.611220, 7.127460, 7.880070, 2.574830, 6.778820, 5.156320, 8.329430)'::signature))
 Buffers: shared hit=2914
 -> Index Scan using t_img0_sig_idx on public.t_img0 (cost=0.36..7032.36 rows=754085 width=72) (actual time=40.282..48.179 rows=2 loops=1)
 Output: id, sig, (sig <-> '(0.435404, 6.602870, 9.050220, 9.379750, 2.483920, 1.534660, 0.363753, 4.079670, 0.124681, 3.611220, 7.127460, 7.880070, 2.574830, 6.778820, 5.156320, 8.329430)'::signature)
 Order By: (t_img0.sig <-> '(0.435404, 6.602870, 9.050220, 9.379750, 2.483920, 1.534660, 0.363753, 4.079670, 0.124681, 3.611220, 7.127460, 7.880070, 2.574830, 6.778820, 5.156320, 8.329430)'::signature)
 Buffers: shared hit=2914
 Planning Time: 0.091 ms
 Execution Time: 48.210 ms
(9 rows)