怎么实现数据库分区表+dblink异步调用并行
本篇内容介绍了“怎么实现数据库分区表+dblink异步调用并行”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!
分区表 + dblink 异步调用 并行1、创建分区表
createtablet_img(idintprimarykey,sigsignature)partitionbyhash(id);
2、创建64个分区
dolanguageplpgsql$$declareiint;beginforiin0..63loopexecuteformat('createtablet_img%spartitionoft_imgforvaluesWITH(MODULUS64,REMAINDER%s)',i,i);endloop;end;$$;
3、创建图像特征值字段索引
createindexidx_t_img_1ont_imgusinggist(sig);
4、写入4亿随机图像特征值
vitest.sql\setidrandom(1,2000000000)insertintot_imgvalues(:id,gen_rand_img_sig(10))onconflict(id)donothing;
pgbench-Mprepared-n-r-P1-f./test.sql-c64-j64-t10000000dblink 异步调用封装
1、创建dblink插件
createextensionifnotexistsdblink;
2、创建一个建立连接函数,不报错
createorreplacefunctionconn(name,--dblink名字text--连接串,URL)returnsvoidas$$declarebeginperformdblink_connect($1,$2);return;exceptionwhenothersthenreturn;end;$$languageplpgsqlstrict;
3、编写一个函数,输入参数为分区数,图像特征值。开启64个并行同时搜索每个分区,返回一条最相似的图像记录。
createorreplacefunctionparallel_img_search(v_modint,--分区数v_sigsignature,--图像特征值conntextdefaultformat('hostaddr=%sport=%suser=%sdbname=%sapplication_name=','127.0.0.1',current_setting('port'),current_user,current_database())--dblink连接)returnssetofrecordas$$declareapp_prefixtext:='abc';sqltext;ts1timestamp;beginforiin0..v_modloopperformconn(app_prefix||i,conn||app_prefix||i);performid,sigfromdblink_get_result(app_prefix||i,false)ast(idint,sigsignature);sql:=format('select*fromt_img%sorderbysig<->%Llimit1',i,v_sig);performdblink_send_query(app_prefix||i,sql);endloop;ts1:=clock_timestamp();foriin0..v_modloopreturnqueryselectid,sigfromdblink_get_result(app_prefix||i,false)ast(idint,sigsignature);endloop;raisenotice'%',clock_timestamp()-ts1;return;end;$$languageplpgsqlstrict;
4、创建一个stable函数,用于生成随机图像特征值。
createorreplacefunctionget_rand_img_sig(int)returnssignatureas$$select('('||rtrim(ltrim(array(select(random()*$1)::float4fromgenerate_series(1,16))::text,'{'),'}')||')')::signature;$$languagesqlstrictstable;
例子
postgres=#selectget_rand_img_sig(10);get_rand_img_sig------------------------------------------------------------------------------------------------------------------------------------------------------------------(3.970030,2.340900,0.946223,5.951010,6.560340,7.922950,6.646290,0.430310,7.690120,5.799870,1.337850,1.319830,3.178170,6.439380,0.925341,2.215810)(1row)Time:0.345ms
5、写入约2.98亿图像特征值。
postgres=#selectcount(*)fromt_img;count-----------297915819(1row)使用dblink异步调用并行查询64个分区
使用dblink异步调用接口,查询所有分区,耗时:394毫秒
postgres=#select*fromparallel_img_search(63,'(3.970030,2.340900,0.946223,5.951010,6.560340,7.922950,6.646290,0.430310,7.690120,5.799870,1.337850,1.319830,3.178170,6.439380,0.925341,2.215810)'::signature)ast(idint,sigsignature)orderbysig<->'(3.970030,2.340900,0.946223,5.951010,6.560340,7.922950,6.646290,0.430310,7.690120,5.799870,1.337850,1.319830,3.178170,6.439380,0.925341,2.215810)'::signaturelimit1;NOTICE:00:00:00.394257id|sig------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------1918283556|(3.122560,2.748080,1.133250,5.426950,6.626340,6.876810,7.959190,0.798523,8.638600,5.075110,1.366100,0.899454,2.980070,4.580630,0.986704,1.582110)(1row)Time:741.161ms
直接查询单个分区耗时:238毫秒
postgres=#explain(analyze,verbose,timing,costs,buffers)selectsigfromt_img48orderbysig<->'(3.970030,2.340900,0.946223,5.951010,6.560340,7.922950,6.646290,0.430310,7.690120,5.799870,1.337850,1.319830,3.178170,6.439380,0.925341,2.215810)'limit1;QUERYPLAN----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Limit(cost=0.36..0.37rows=1width=72)(actualtime=231.287..231.288rows=1loops=1)Output:id,sig,((sig<->'(3.970030,2.340900,0.946223,5.951010,6.560340,7.922950,6.646290,0.430310,7.690120,5.799870,1.337850,1.319830,3.178170,6.439380,0.925341,2.215810)'::signature))Buffers:sharedhit=11881->IndexScanusingt_img48_sig_idxonpublic.t_img48(cost=0.36..41619.32rows=4466603width=72)(actualtime=231.285..231.285rows=1loops=1)Output:id,sig,(sig<->'(3.970030,2.340900,0.946223,5.951010,6.560340,7.922950,6.646290,0.430310,7.690120,5.799870,1.337850,1.319830,3.178170,6.439380,0.925341,2.215810)'::signature)OrderBy:(t_img48.sig<->'(3.970030,2.340900,0.946223,5.951010,6.560340,7.922950,6.646290,0.430310,7.690120,5.799870,1.337850,1.319830,3.178170,6.439380,0.925341,2.215810)'::signature)Buffers:sharedhit=11881PlanningTime:0.060msExecutionTime:237.818ms(9rows)Time:238.242ms
“怎么实现数据库分区表+dblink异步调用并行”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站,小编将为大家输出更多高质量的实用文章!
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。