这篇文章主要讲解了“相似图像搜索插件imgsmlr性能测试与优化方法是什么”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“相似图像搜索插件imgsmlr性能测试与优化方法是什么”吧！
1、安装imgsmlr插件软件 (所有节点)
2、create extension imgsmlr (所有节点)
3、生成随机img sig的函数 (cn, 因为只需要用于插入, 不需要下推)
CREATE OR REPLACE FUNCTION public.gen_rand_img_sig(integer)
RETURNS signature
LANGUAGE sql
STRICT
AS $function$
select ('('||rtrim(ltrim(array(select (random()*$1)::float4 from generate_series(1,16))::text,'{'),'}')||')')::signature;
$function$;4、创建测试表 (cn)
create table t_img (id int primary key, sig signature);
5、创建索引 (cn)
create index idx_t_img_1 on t_img using gist (sig);
6、创建分片表(128 shard) (cn)
set citus.shard_count = 128;
select create_distributed_table('t_img','id');7、写入4.5亿随机图像特征值
vi test.sql \set id random(1,2000000000) insert into t_img values (:id, gen_rand_img_sig(10)) on conflict(id) do nothing;
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 128 -j 128 -t 10000000
写入约4.5亿随机图像特征值
postgres=# select count(*) from t_img; count ----------- 446953185 (1 row)
postgres=# select * from t_img limit 10; id | sig -----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------ 47902935 | (5.861920, 1.062770, 8.318020, 2.205840, 0.202951, 6.956610, 1.413190, 2.898480, 8.961630, 6.377800, 1.110450, 6.684520, 2.286290, 7.850760, 1.832650, 0.074348) 174656795 | (2.165030, 0.183753, 9.913950, 9.208260, 5.165660, 6.603510, 2.008380, 8.117910, 2.358590, 5.466330, 9.139280, 8.893700, 4.664190, 9.361670, 9.016990, 2.271000) 96186891 | (9.605980, 4.395920, 4.336720, 3.174360, 8.706960, 0.155107, 9.408940, 4.531100, 2.783530, 5.681780, 9.792380, 6.428320, 2.983760, 9.733290, 7.635160, 7.035780) 55061667 | (7.567960, 5.874530, 5.222040, 5.638520, 3.488960, 8.770750, 7.054610, 7.239630, 9.202280, 9.465020, 4.079080, 5.729770, 0.475227, 8.434800, 6.873730, 5.140080) 64659434 | (4.860650, 3.984440, 3.009900, 5.116680, 6.489150, 4.224800, 0.609752, 8.731120, 6.577390, 8.542540, 9.096120, 8.976700, 8.936000, 2.836270, 7.186250, 6.264300) 87143098 | (4.801570, 7.870150, 0.939599, 3.666670, 1.102340, 5.819580, 6.511330, 6.430760, 0.584531, 3.024190, 6.255460, 8.823820, 5.076960, 0.181344, 8.137380, 1.230360) 109245945 | (7.541850, 7.201460, 6.858400, 2.605210, 1.283090, 7.525200, 4.213240, 8.413760, 9.707390, 1.916970, 1.719320, 1.255280, 9.006780, 4.851420, 2.168250, 5.997360) 4979218 | (8.463000, 4.051410, 9.057320, 1.367980, 3.344340, 7.032640, 8.583770, 1.873090, 5.524810, 0.187254, 5.783270, 6.141040, 2.479410, 6.406450, 9.371700, 0.050690) 72846137 | (7.018560, 4.039150, 9.114800, 2.911170, 5.531180, 8.557330, 6.739050, 0.103649, 3.691390, 7.584640, 8.184180, 0.599390, 9.037130, 4.090610, 4.369770, 6.480000) 36813995 | (4.643480, 8.704640, 1.073880, 2.665530, 3.298300, 9.244280, 5.768050, 0.887555, 5.990350, 2.991390, 6.186550, 6.464940, 6.187140, 0.150242, 2.123070, 2.932270) (10 rows)
1、由于imgsmlr的一些类型没有写对应的send, recv函数接口，所以需要使用TEXT交互。CN设置参数如下
set citus.binary_master_copy_format =off;
未设置时报错
WARNING: 42883: no binary output function available for type signature LOCATION: ReportResultError, remote_commands.c:302
2、创建生成随机图像特征值stable函数，便于测试。（所有节点）
create or replace function get_rand_img_sig(int) returns signature as $$
select ('('||rtrim(ltrim(array(select (random()*$1)::float4 from generate_series(1,16))::text,'{'),'}')||')')::signature;
$$ language sql strict stable;3、性能
postgres=# select * from t_img order by sig <-> get_rand_img_sig(10) limit 1; id | sig -----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------ 565459043 | (1.790420, 9.463960, 7.089370, 5.888980, 0.974693, 2.148580, 6.153310, 9.098670, 2.815750, 7.625620, 7.598990, 7.141670, 7.189410, 4.630740, 3.673030, 7.820140) (1 row) Time: 612.839 ms
4、执行计划
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t_img order by sig <-> get_rand_img_sig(10) limit 1; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..0.00 rows=0 width=0) (actual time=823.235..823.237 rows=1 loops=1) Output: remote_scan.id, remote_scan.sig, remote_scan.worker_column_3 -> Sort (cost=0.00..0.00 rows=0 width=0) (actual time=823.233..823.233 rows=1 loops=1) Output: remote_scan.id, remote_scan.sig, remote_scan.worker_column_3 Sort Key: remote_scan.worker_column_3 Sort Method: top-N heapsort Memory: 25kB -> Custom Scan (Citus Real-Time) (cost=0.00..0.00 rows=0 width=0) (actual time=823.185..823.200 rows=128 loops=1) Output: remote_scan.id, remote_scan.sig, remote_scan.worker_column_3 Task Count: 128 Tasks Shown: One of 128 -> Task Node: host=172.24.211.224 port=1921 dbname=postgres -> Limit (cost=0.67..0.97 rows=1 width=72) (actual time=151.011..151.012 rows=1 loops=1) Output: id, sig, ((sig <-> get_rand_img_sig(10))) Buffers: shared hit=5769 -> Index Scan using idx_t_img_1_106940 on public.t_img_106940 t_img (cost=0.67..1052191.36 rows=3488100 width=72) (actual time=151.008..151.009 rows=1 loops=1) Output: id, sig, (sig <-> get_rand_img_sig(10)) Order By: (t_img.sig <-> get_rand_img_sig(10)) Buffers: shared hit=5769 Planning time: 1.021 ms Execution time: 156.785 ms Planning time: 2.364 ms Execution time: 823.577 ms (23 rows)
postgres=# select * from t_img order by sig <-> get_rand_img_sig(10) limit 1; id | sig ----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------ 30290963 | (4.656000, 7.143380, 7.738080, 1.971150, 4.294430, 4.397560, 7.121350, 8.629690, 2.768710, 2.715320, 0.358493, 0.486682, 5.985860, 8.319860, 2.560290, 3.384480) (1 row) Time: 612.783 ms postgres=# select * from t_img order by sig <-> get_rand_img_sig(10) limit 1; id | sig ------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------ 1632633492 | (6.969460, 5.835990, 0.629481, 7.621580, 0.171138, 2.586950, 1.483150, 5.526530, 3.835270, 2.275350, 3.470760, 4.934100, 0.442193, 1.843810, 0.561291, 0.647721) (1 row) Time: 610.960 ms
感谢各位的阅读，以上就是“相似图像搜索插件imgsmlr性能测试与优化方法是什么”的内容了，经过本文的学习后，相信大家对相似图像搜索插件imgsmlr性能测试与优化方法是什么这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是创新互联，小编将为大家推送更多相关知识点的文章，欢迎关注！