这篇文章主要讲解了“相似图像搜索插件imgsmlr性能测试与优化方法是什么”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“相似图像搜索插件imgsmlr性能测试与优化方法是什么”吧！

1、安装imgsmlr插件软件 (所有节点)

2、create extension imgsmlr(所有节点)

3、生成随机img sig的函数 (cn, 因为只需要用于插入, 不需要下推)

CREATEORREPLACEFUNCTIONpublic.gen_rand_img_sig(integer)RETURNSsignatureLANGUAGEsqlSTRICTAS$function$select('('||rtrim(ltrim(array(select(random()*$1)::float4fromgenerate_series(1,16))::text,'{'),'}')||')')::signature;$function$;

4、创建测试表 (cn)

createtablet_img(idintprimarykey,sigsignature);

5、创建索引 (cn)

createindexidx_t_img_1ont_imgusinggist(sig);

6、创建分片表(128 shard) (cn)

setcitus.shard_count=128;selectcreate_distributed_table('t_img','id');

7、写入4.5亿随机图像特征值

vitest.sql\setidrandom(1,2000000000)insertintot_imgvalues(:id,gen_rand_img_sig(10))onconflict(id)donothing;

pgbench-Mprepared-n-r-P1-f./test.sql-c128-j128-t10000000

写入约4.5亿随机图像特征值

postgres=#selectcount(*)fromt_img;count-----------446953185(1row)

postgres=#select*fromt_imglimit10;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)(10rows)查询性能

1、由于imgsmlr的一些类型没有写对应的send, recv函数接口，所以需要使用TEXT交互。CN设置参数如下

setcitus.binary_master_copy_format=off;

未设置时报错

WARNING:42883:nobinaryoutputfunctionavailablefortypesignatureLOCATION:ReportResultError,remote_commands.c:302

2、创建生成随机图像特征值stable函数，便于测试。（所有节点）

createorreplacefunctionget_rand_img_sig(int)returnssignatureas$$select('('||rtrim(ltrim(array(select(random()*$1)::float4fromgenerate_series(1,16))::text,'{'),'}')||')')::signature;$$languagesqlstrictstable;

3、性能

postgres=#select*fromt_imgorderbysig<->get_rand_img_sig(10)limit1;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)(1row)Time:612.839ms

4、执行计划

postgres=#explain(analyze,verbose,timing,costs,buffers)select*fromt_imgorderbysig<->get_rand_img_sig(10)limit1;QUERYPLAN---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Limit(cost=0.00..0.00rows=0width=0)(actualtime=823.235..823.237rows=1loops=1)Output:remote_scan.id,remote_scan.sig,remote_scan.worker_column_3->Sort(cost=0.00..0.00rows=0width=0)(actualtime=823.233..823.233rows=1loops=1)Output:remote_scan.id,remote_scan.sig,remote_scan.worker_column_3SortKey:remote_scan.worker_column_3SortMethod:top-NheapsortMemory:25kB->CustomScan(CitusReal-Time)(cost=0.00..0.00rows=0width=0)(actualtime=823.185..823.200rows=128loops=1)Output:remote_scan.id,remote_scan.sig,remote_scan.worker_column_3TaskCount:128TasksShown:Oneof128->TaskNode:host=172.24.211.224port=1921dbname=postgres->Limit(cost=0.67..0.97rows=1width=72)(actualtime=151.011..151.012rows=1loops=1)Output:id,sig,((sig<->get_rand_img_sig(10)))Buffers:sharedhit=5769->IndexScanusingidx_t_img_1_106940onpublic.t_img_106940t_img(cost=0.67..1052191.36rows=3488100width=72)(actualtime=151.008..151.009rows=1loops=1)Output:id,sig,(sig<->get_rand_img_sig(10))OrderBy:(t_img.sig<->get_rand_img_sig(10))Buffers:sharedhit=5769Planningtime:1.021msExecutiontime:156.785msPlanningtime:2.364msExecutiontime:823.577ms(23rows)

postgres=#select*fromt_imgorderbysig<->get_rand_img_sig(10)limit1;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)(1row)Time:612.783mspostgres=#select*fromt_imgorderbysig<->get_rand_img_sig(10)limit1;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)(1row)Time:610.960ms

感谢各位的阅读，以上就是“相似图像搜索插件imgsmlr性能测试与优化方法是什么”的内容了，经过本文的学习后，相信大家对相似图像搜索插件imgsmlr性能测试与优化方法是什么这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！