PostgreSQL 15 preview - postgres_fdw 支持更多异步, 增强基于FDW的sharding能力.例如 DML异步写入、分区表、union all、union

作者

digoal

日期

2022-04-08

标签

PostgreSQL , 异步 , fdw , postgres_fdw , sharding

postgres_fdw 支持更多异步, 增强基于FDW的sharding能力. 例如 DML异步写入、分区表、union all、union

《PostgreSQL 14 preview - FDW 支持异步执行接口, postgres_fdw 支持异步append - sharding 性能增强 - 未来将支持更多异步操作》

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c2bb02bc2e858ba345b8b33f1f3a54628f719d93

Allow asynchronous execution in more cases.  
author  Etsuro Fujita <efujita@postgresql.org>    
Wed, 6 Apr 2022 06:45:00 +0000 (15:45 +0900)  
committer   Etsuro Fujita <efujita@postgresql.org>    
Wed, 6 Apr 2022 06:45:00 +0000 (15:45 +0900)  
commit  c2bb02bc2e858ba345b8b33f1f3a54628f719d93  
tree    fe742ab96982b0a74ed615d0c1e4c0982715a6e6    tree  
parent  376dc437de40bd17e99a37f72f88627a16d7f200    commit | diff  
Allow asynchronous execution in more cases.  
In commit 27e1f1456, create_append_plan() only allowed the subplan  
created from a given subpath to be executed asynchronously when it was  
an async-capable ForeignPath.  To extend coverage, this patch handles  
cases when the given subpath includes some other Path types as well that  
can be omitted in the plan processing, such as a ProjectionPath directly  
atop an async-capable ForeignPath, allowing asynchronous execution in  
partitioned-scan/partitioned-join queries with non-Var tlist expressions  
and more UNION queries.  
Andrey Lepikhov and Etsuro Fujita, reviewed by Alexander Pyhalov and  
Zhihong Yu.  
Discussion: https://postgr.es/m/659c37a8-3e71-0ff2-394c-f04428c76f08%40postgrespro.ru  
复制

例子

+EXPLAIN (VERBOSE, COSTS OFF)  
+INSERT INTO join_tbl SELECT t1.a, t1.b, 'AAA' || t1.c, t2.a, t2.b, 'AAA' || t2.c FROM async_pt t1, async_pt t2 WHERE t1.a = t2.a AND t1.b = t2.b AND t1.b % 100 = 0;  
+                                                                                           QUERY PLAN                                                                                              
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
+ Insert on public.join_tbl  
+   ->  Append  
+         ->  Async Foreign Scan  
+               Output: t1_1.a, t1_1.b, ('AAA'::text || t1_1.c), t2_1.a, t2_1.b, ('AAA'::text || t2_1.c)  
+               Relations: (public.async_p1 t1_1) INNER JOIN (public.async_p1 t2_1)  
+               Remote SQL: SELECT r5.a, r5.b, r5.c, r8.a, r8.b, r8.c FROM (public.base_tbl1 r5 INNER JOIN public.base_tbl1 r8 ON (((r5.a = r8.a)) AND ((r5.b = r8.b)) AND (((r5.b % 100) = 0))))  
+         ->  Async Foreign Scan  
+               Output: t1_2.a, t1_2.b, ('AAA'::text || t1_2.c), t2_2.a, t2_2.b, ('AAA'::text || t2_2.c)  
+               Relations: (public.async_p2 t1_2) INNER JOIN (public.async_p2 t2_2)  
+               Remote SQL: SELECT r6.a, r6.b, r6.c, r9.a, r9.b, r9.c FROM (public.base_tbl2 r6 INNER JOIN public.base_tbl2 r9 ON (((r6.a = r9.a)) AND ((r6.b = r9.b)) AND (((r6.b % 100) = 0))))  
+         ->  Hash Join  
+               Output: t1_3.a, t1_3.b, ('AAA'::text || t1_3.c), t2_3.a, t2_3.b, ('AAA'::text || t2_3.c)  
+               Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.b = t1_3.b))  
+               ->  Seq Scan on public.async_p3 t2_3  
+                     Output: t2_3.a, t2_3.b, t2_3.c  
+               ->  Hash  
+                     Output: t1_3.a, t1_3.b, t1_3.c  
+                     ->  Seq Scan on public.async_p3 t1_3  
+                           Output: t1_3.a, t1_3.b, t1_3.c  
+                           Filter: ((t1_3.b % 100) = 0)  
+(20 rows)  
+INSERT INTO result_tbl  
+(SELECT a, b, 'AAA' || c FROM async_p1 ORDER BY a LIMIT 10)  
+UNION  
+(SELECT a, b, 'AAA' || c FROM async_p2 WHERE b < 10);  
+                                                   QUERY PLAN                                                      
+-----------------------------------------------------------------------------------------------------------------  
+ Insert on public.result_tbl  
+   ->  HashAggregate  
+         Output: async_p1.a, async_p1.b, (('AAA'::text || async_p1.c))  
+         Group Key: async_p1.a, async_p1.b, (('AAA'::text || async_p1.c))  
+         ->  Append  
+               ->  Async Foreign Scan on public.async_p1  
+                     Output: async_p1.a, async_p1.b, ('AAA'::text || async_p1.c)  
+                     Remote SQL: SELECT a, b, c FROM public.base_tbl1 ORDER BY a ASC NULLS LAST LIMIT 10::bigint  
+               ->  Async Foreign Scan on public.async_p2  
+                     Output: async_p2.a, async_p2.b, ('AAA'::text || async_p2.c)  
+                     Remote SQL: SELECT a, b, c FROM public.base_tbl2 WHERE ((b < 10))  
+(11 rows)  
+EXPLAIN (VERBOSE, COSTS OFF)  
+INSERT INTO result_tbl  
+(SELECT a, b, 'AAA' || c FROM async_p1 ORDER BY a LIMIT 10)  
+UNION ALL  
+(SELECT a, b, 'AAA' || c FROM async_p2 WHERE b < 10);  
+                                                QUERY PLAN                                                   
+-----------------------------------------------------------------------------------------------------------  
+ Insert on public.result_tbl  
+   ->  Append  
+         ->  Async Foreign Scan on public.async_p1  
+               Output: async_p1.a, async_p1.b, ('AAA'::text || async_p1.c)  
+               Remote SQL: SELECT a, b, c FROM public.base_tbl1 ORDER BY a ASC NULLS LAST LIMIT 10::bigint  
+         ->  Async Foreign Scan on public.async_p2  
+               Output: async_p2.a, async_p2.b, ('AAA'::text || async_p2.c)  
+               Remote SQL: SELECT a, b, c FROM public.base_tbl2 WHERE ((b < 10))  
+(8 rows)  
复制

期望 PostgreSQL 增加什么功能?

PolarDB for PostgreSQL云原生分布式开源数据库

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.