Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to achieve database partition table + dblink asynchronous call parallelism". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Partition table + dblink asynchronous invocation parallelism

1. Create a partition table

Create table t_img (id int primary key, sig signature) partition by hash (id)

2. Create 64 partitions

Do language plpgsql $$declare i int; begin for i in 0.. 63 loop execute format ('create table t_img%s partition of t_img for values WITH (MODULUS 64, REMAINDER% s)', I, I); end loop; end; $$

3. Create an image eigenvalue field index.

Create index idx_t_img_1 on t_img using gist (sig)

4. Write 400 million random image eigenvalues

Vi test.sql\ set id random (12000000000) 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 64-j 64-t 10000000dblink Asynchronous call Encapsulation

1. Create a dblink plug-in

Create extension if not exists dblink

2. Create a connection function that does not report errors

Create or replace function conn (name,-- dblink name text-connection string, URL) returns void as $declare begin perform dblink_connect ($1, $2); return; exception when others then return; end; $$language plpgsql strict

3. Write a function whose input parameters are the number of zones and image eigenvalues. Turn on 64 parallelism and search each partition at the same time, returning the most similar image record.

Create or replace function parallel_img_search (v_mod int,-- number of partitions v_sig signature,-- Image eigenvalues conn text default format ('hostaddr=%s port=%s user=%s dbname=%s application_name=',' 127.0.0.1, current_setting ('port'), current_user, current_database ())-- dblink connection) returns setof record as $declare app_prefix text: =' abc'; sql text Ts1 timest begin for i in 0..v_mod loop perform conn (app_prefix | | I, conn | | app_prefix | | I); perform id,sig from dblink_get_result (app_prefix | | I, false) as t (id int, sig signature); sql: = format ('select * from t_img%s order by sig% L limit 1), I, v_sig); perform dblink_send_query (app_prefix | | I, sql); end loop; ts1: = clock_timestamp () For i in 0..v_mod loop return query select id,sig from dblink_get_result (app_prefix | | I, false) as t (id int, sig signature); end loop; raise notice'%', clock_timestamp ()-ts1; return; end; $$language plpgsql strict

4. Create a stable function to generate random image eigenvalues.

Create or replace function get_rand_img_sig (int) returns signature as $$select ('('| | rtrim (array (select (random () * $1):: float4 from generate_series (1je 16)):: text,' {'),'}'):: signature; $language sql strict stable

Examples

Postgres=# select get_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) (1 row) Time: 0.345 ms

5. Write about 298 million image eigenvalues.

Postgres=# select count (*) from tsp; count-297915819 (1 row) uses dblink asynchronous calls to query 64 partitions in parallel

Use the dblink asynchronous call API to query all partitions. It takes 394ms.

Postgres=# select * from parallel_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) as t (id int, sig signature) order by 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 limit 1 NOTICE: 00.394257 id for 00VERV 00VERV | 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) (1 row) Time: 741.161 ms

Time to query a single partition directly: 238 milliseconds

Postgres=# explain (analyze,verbose,timing,costs,buffers) select sig from t_img48 order by 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) 'limit 1 QUERY PLAN- - -Limit (cost=0.36..0.37 rows=1 width=72) (actual time=231.287..231.288 rows=1 loops=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: shared hit=11881-> Index Scan using t_img48_sig_idx on public.t_img48 (cost=0.36..41619.32 rows=4466603 width=72) (actual time=231.285..231.285 rows=1 loops=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) Order By: (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: shared hit=11881 Planning Time: 0.060 ms Execution Time: 237.818 ms (9 rows) Time: 238.242 ms "how to implement database partition tables + dblink asynchronous invocation parallelism" ends here Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.