AI 助理
备案控制台
开发者社区 数据库 关系型数据库 文章 正文

PostgreSQL 11 preview - Parallel Append (多表并行计算) sharding架构并行计算核心功能之一

2018-02-28 2620
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS SQL Server Serverless，2-4RCU 50GB 3个月
推荐场景：
RDS PostgreSQL Serverless，0.5-4RCU 50GB 3个月
推荐场景：
对影评进行热评分析
云原生数据库 PolarDB 分布式版，标准版 2核8GB
推荐场景：
使用PolarDB-X与Flink搭建实时数据大屏
简介：

标签

PostgreSQL , 多表并行 , parallel append , sharding , 外部表 , 继承 , 分区表 , union , pg_pathman , inherit

背景

append是数据库执行计划中很场景的一个NODE，数据来自扫描多个对象的集合时，都需要APPEND。比如：

1、扫描分区表

2、扫描主表（包含若干继承表时）

3、UNION ALL语句。(union 暂时不支持)

pic

使用parallel append的功能，可以设计出非常灵活的架构，例如sharding可以在数据库内核层面并行，不需要依赖中间件例如plproxy了。(暂时还不支持直接用foreign table+inherit的模式，不过可以用pg_pathman)

pic

parallel append 参数开关

enable_parallel_append (boolean)  
  
Enables or disables the query planner's use of parallel-aware append plan types.   
  
The default is on.

测试

# 全局可使用WORKER进程数    
max_worker_processes = 128    
    
# 全局可开并行计算的WORKER进程数    
max_parallel_workers = 128     
     
# 并行计算COST计算依赖的几个参数    
set parallel_tuple_cost =0;    
set parallel_setup_cost =0;    
    
# 最小多大的表才会使用并行计算    
set min_parallel_index_scan_size =0;    
set min_parallel_table_scan_size =0;    
  
# 每个gather可以创建多个worker process  
set max_parallel_workers_per_gather =32;

生成测试数据

1、创建本地分区表

create unlogged table p(id int8, info text) partition by hash(id);     
    
CREATE unlogged TABLE p0 PARTITION OF p FOR VALUES WITH (MODULUS 4, REMAINDER 0);      
CREATE unlogged TABLE p1 PARTITION OF p FOR VALUES WITH (MODULUS 4, REMAINDER 1);      
CREATE unlogged TABLE p2 PARTITION OF p FOR VALUES WITH (MODULUS 4, REMAINDER 2);      
CREATE unlogged TABLE p3 PARTITION OF p FOR VALUES WITH (MODULUS 4, REMAINDER 3);

2、写入1亿测试数据

insert into p select generate_series(1,100000000), 'test';

3、设置分区并行度为0，防止单个分区并行扫描太快，看不出性能差异。

alter table p0 set ( parallel_workers=0 );    
alter table p1 set ( parallel_workers=0 );    
alter table p2 set ( parallel_workers=0 );    
alter table p3 set ( parallel_workers=0 );

当每个分区都返回大量数据时

这里测试了两个CASE，一个含并行聚合，一个不含并行计算（全量返回）。实际上parallel append适合一路并行，而不适合上层没什么计算，串行接收大量APPEND数据的场景。

1、含并行聚合（上层直接对接partial agg worker，所以流式处理掉了），并行append

postgres=# set enable_parallel_append =on;  
SET  
  
postgres=# explain (analyze,verbose) select count(*) from p;  
                                                                   QUERY PLAN                                                                      
-------------------------------------------------------------------------------------------------------------------------------------------------  
 Finalize Aggregate  (cost=850840.80..850840.81 rows=1 width=8) (actual time=6400.860..6400.861 rows=1 loops=1)  
   Output: count(*)  
   ->  Gather  (cost=850840.78..850840.79 rows=3 width=8) (actual time=5630.195..6400.849 rows=4 loops=1)  
         Output: (PARTIAL count(*))  
         Workers Planned: 3  
         Workers Launched: 3  
         ->  Partial Aggregate  (cost=850840.78..850840.79 rows=1 width=8) (actual time=6133.146..6133.147 rows=1 loops=4)  
               Output: PARTIAL count(*)  
               Worker 0: actual time=6253.609..6253.609 rows=1 loops=1  
               Worker 1: actual time=6395.587..6395.588 rows=1 loops=1  
               Worker 2: actual time=6253.407..6253.407 rows=1 loops=1  
               ->  Parallel Append  (cost=0.00..770195.40 rows=32258152 width=0) (actual time=0.027..4772.225 rows=25000000 loops=4)  
                     Worker 0: actual time=0.030..4882.573 rows=24999575 loops=1  
                     Worker 1: actual time=0.030..5025.288 rows=25002155 loops=1  
                     Worker 2: actual time=0.035..4906.483 rows=25002850 loops=1  
                     ->  Seq Scan on public.p3  (cost=0.00..385180.36 rows=25002936 width=0) (actual time=0.033..3137.362 rows=25002850 loops=1)  
                           Worker 2: actual time=0.033..3137.362 rows=25002850 loops=1  
                     ->  Seq Scan on public.p1  (cost=0.00..385168.96 rows=25002196 width=0) (actual time=0.030..3253.775 rows=25002155 loops=1)  
                           Worker 1: actual time=0.030..3253.775 rows=25002155 loops=1  
                     ->  Seq Scan on public.p0  (cost=0.00..385129.04 rows=24999604 width=0) (actual time=0.029..3110.662 rows=24999575 loops=1)  
                           Worker 0: actual time=0.029..3110.662 rows=24999575 loops=1  
                     ->  Seq Scan on public.p2  (cost=0.00..385066.36 rows=24995536 width=0) (actual time=0.011..2512.500 rows=24995420 loops=1)  
 Planning time: 0.261 ms  
 Execution time: 6463.125 ms  
(24 rows)

2、串行APPEND

postgres=# set enable_parallel_append =off;  
SET  
  
postgres=# explain (analyze,verbose) select count(*) from p;  
                                                             QUERY PLAN                                                                
-------------------------------------------------------------------------------------------------------------------------------------  
 Aggregate  (cost=1790545.40..1790545.41 rows=1 width=8) (actual time=21705.971..21705.972 rows=1 loops=1)  
   Output: count(*)  
   ->  Append  (cost=0.00..1540544.72 rows=100000272 width=0) (actual time=0.010..16055.808 rows=100000000 loops=1)  
         ->  Seq Scan on public.p0  (cost=0.00..385129.04 rows=24999604 width=0) (actual time=0.010..2214.981 rows=24999575 loops=1)  
         ->  Seq Scan on public.p1  (cost=0.00..385168.96 rows=25002196 width=0) (actual time=0.011..2225.458 rows=25002155 loops=1)  
         ->  Seq Scan on public.p2  (cost=0.00..385066.36 rows=24995536 width=0) (actual time=0.013..2264.015 rows=24995420 loops=1)  
         ->  Seq Scan on public.p3  (cost=0.00..385180.36 rows=25002936 width=0) (actual time=0.013..2214.180 rows=25002850 loops=1)  
 Planning time: 0.111 ms  
 Execution time: 21706.010 ms  
(9 rows)

3、不含并行聚合（上层返回所有数据，性能反而下降），并行append

postgres=# explain (analyze,verbose)   
select * from p;  
                                                              QUERY PLAN                                                                
--------------------------------------------------------------------------------------------------------------------------------------  
 Gather  (cost=0.00..770195.40 rows=100000272 width=13) (actual time=0.238..72791.861 rows=100000000 loops=1)  
   Output: p3.id, p3.info  
   Workers Planned: 3  
   Workers Launched: 3  
   ->  Parallel Append  (cost=0.00..770195.40 rows=32258152 width=13) (actual time=0.019..4450.007 rows=25000000 loops=4)  
         Worker 0: actual time=0.021..4713.479 rows=24999575 loops=1  
         Worker 1: actual time=0.021..4705.110 rows=25002155 loops=1  
         Worker 2: actual time=0.023..4710.256 rows=25002850 loops=1  
         ->  Seq Scan on public.p3  (cost=0.00..385180.36 rows=25002936 width=13) (actual time=0.022..2955.118 rows=25002850 loops=1)  
               Output: p3.id, p3.info  
               Worker 2: actual time=0.022..2955.118 rows=25002850 loops=1  
         ->  Seq Scan on public.p1  (cost=0.00..385168.96 rows=25002196 width=13) (actual time=0.020..2949.203 rows=25002155 loops=1)  
               Output: p1.id, p1.info  
               Worker 1: actual time=0.020..2949.203 rows=25002155 loops=1  
         ->  Seq Scan on public.p0  (cost=0.00..385129.04 rows=24999604 width=13) (actual time=0.021..2957.799 rows=24999575 loops=1)  
               Output: p0.id, p0.info  
               Worker 0: actual time=0.021..2957.799 rows=24999575 loops=1  
         ->  Seq Scan on public.p2  (cost=0.00..385066.36 rows=24995536 width=13) (actual time=0.009..1919.412 rows=24995420 loops=1)  
               Output: p2.id, p2.info  
 Planning time: 0.156 ms  
 Execution time: 76464.568 ms  
(21 rows)

4、串行APPEND

postgres=# set enable_parallel_append =off;  
SET  
postgres=# explain (analyze,verbose)         
select * from p;  
                                                           QUERY PLAN                                                             
--------------------------------------------------------------------------------------------------------------------------------  
 Append  (cost=0.00..1540544.72 rows=100000272 width=13) (actual time=0.009..14691.301 rows=100000000 loops=1)  
   ->  Seq Scan on public.p0  (cost=0.00..385129.04 rows=24999604 width=13) (actual time=0.008..1930.118 rows=24999575 loops=1)  
         Output: p0.id, p0.info  
   ->  Seq Scan on public.p1  (cost=0.00..385168.96 rows=25002196 width=13) (actual time=0.012..1946.220 rows=25002155 loops=1)  
         Output: p1.id, p1.info  
   ->  Seq Scan on public.p2  (cost=0.00..385066.36 rows=24995536 width=13) (actual time=0.011..1911.555 rows=24995420 loops=1)  
         Output: p2.id, p2.info  
   ->  Seq Scan on public.p3  (cost=0.00..385180.36 rows=25002936 width=13) (actual time=0.013..1933.505 rows=25002850 loops=1)  
         Output: p3.id, p3.info  
 Planning time: 0.111 ms  
 Execution time: 18336.654 ms  
(11 rows)

当每个分区仅返回少量数据时

1、并行append

postgres=# set enable_parallel_append =on;  
SET  
  
postgres=# explain (analyze,verbose) select count(*) from p where id=1;  
                                                           QUERY PLAN                                                             
--------------------------------------------------------------------------------------------------------------------------------  
 Aggregate  (cost=895183.26..895183.27 rows=1 width=8) (actual time=2315.544..2315.545 rows=1 loops=1)  
   Output: count(*)  
   ->  Gather  (cost=0.00..895183.25 rows=4 width=0) (actual time=1769.974..2315.536 rows=1 loops=1)  
         Workers Planned: 3  
         Workers Launched: 3  
         ->  Parallel Append  (cost=0.00..895183.25 rows=1 width=0) (actual time=1591.915..2169.437 rows=0 loops=4)  
               Worker 0: actual time=0.025..2310.110 rows=1 loops=1  
               Worker 1: actual time=2286.699..2286.699 rows=0 loops=1  
               Worker 2: actual time=2311.206..2311.206 rows=0 loops=1  
               ->  Seq Scan on public.p3  (cost=0.00..447687.70 rows=1 width=0) (actual time=2311.205..2311.205 rows=0 loops=1)  
                     Filter: (p3.id = 1)  
                     Rows Removed by Filter: 25002850  
                     Worker 2: actual time=2311.205..2311.205 rows=0 loops=1  
               ->  Seq Scan on public.p1  (cost=0.00..447674.45 rows=1 width=0) (actual time=2286.697..2286.697 rows=0 loops=1)  
                     Filter: (p1.id = 1)  
                     Rows Removed by Filter: 25002155  
                     Worker 1: actual time=2286.697..2286.697 rows=0 loops=1  
               ->  Seq Scan on public.p0  (cost=0.00..447628.05 rows=1 width=0) (actual time=0.024..2310.109 rows=1 loops=1)  
                     Filter: (p0.id = 1)  
                     Rows Removed by Filter: 24999574  
                     Worker 0: actual time=0.024..2310.109 rows=1 loops=1  
               ->  Seq Scan on public.p2  (cost=0.00..447555.20 rows=1 width=0) (actual time=1769.730..1769.730 rows=0 loops=1)  
                     Filter: (p2.id = 1)  
                     Rows Removed by Filter: 24995420  
 Planning time: 0.138 ms  
 Execution time: 2365.247 ms  
(26 rows)

2、串行APPEND

postgres=# set enable_parallel_append =off;  
SET  
  
postgres=# explain (analyze,verbose) select count(*) from p where id=1;  
                                                        QUERY PLAN                                                          
--------------------------------------------------------------------------------------------------------------------------  
 Aggregate  (cost=1790545.41..1790545.42 rows=1 width=8) (actual time=6989.018..6989.018 rows=1 loops=1)  
   Output: count(*)  
   ->  Append  (cost=0.00..1790545.40 rows=4 width=0) (actual time=0.011..6989.011 rows=1 loops=1)  
         ->  Seq Scan on public.p0  (cost=0.00..447628.05 rows=1 width=0) (actual time=0.011..1788.032 rows=1 loops=1)  
               Filter: (p0.id = 1)  
               Rows Removed by Filter: 24999574  
         ->  Seq Scan on public.p1  (cost=0.00..447674.45 rows=1 width=0) (actual time=1732.249..1732.249 rows=0 loops=1)  
               Filter: (p1.id = 1)  
               Rows Removed by Filter: 25002155  
         ->  Seq Scan on public.p2  (cost=0.00..447555.20 rows=1 width=0) (actual time=1731.916..1731.916 rows=0 loops=1)  
               Filter: (p2.id = 1)  
               Rows Removed by Filter: 24995420  
         ->  Seq Scan on public.p3  (cost=0.00..447687.70 rows=1 width=0) (actual time=1736.809..1736.809 rows=0 loops=1)  
               Filter: (p3.id = 1)  
               Rows Removed by Filter: 25002850  
 Planning time: 0.259 ms  
 Execution time: 6989.060 ms  
(17 rows)

测试union all的parallel append

postgres=# set enable_parallel_append =on;  
SET  
  
postgres=#   
explain (analyze,verbose)   
select count(*) from p0 where id=1   
union all   
select count(*) from p1 where id=1   
union all   
select count(*) from p2 where id=1   
union all   
select count(*) from p3 where id=1;  
                                                           QUERY PLAN                                                             
--------------------------------------------------------------------------------------------------------------------------------  
 Gather  (cost=447627.70..895181.50 rows=4 width=8) (actual time=1855.298..2363.268 rows=4 loops=1)  
   Output: (count(*))  
   Workers Planned: 3  
   Workers Launched: 3  
   ->  Parallel Append  (cost=447627.70..895181.50 rows=1 width=8) (actual time=2215.816..2215.817 rows=1 loops=4)  
         Worker 0: actual time=2356.711..2356.712 rows=1 loops=1  
         Worker 1: actual time=2336.656..2336.657 rows=1 loops=1  
         Worker 2: actual time=2314.918..2314.919 rows=1 loops=1  
         ->  Aggregate  (cost=447686.63..447686.64 rows=1 width=8) (actual time=2314.917..2314.918 rows=1 loops=1)  
               Output: count(*)  
               Worker 2: actual time=2314.917..2314.918 rows=1 loops=1  
               ->  Seq Scan on public.p3  (cost=0.00..447686.62 rows=1 width=0) (actual time=2314.906..2314.906 rows=0 loops=1)  
                     Output: p3.id, p3.info  
                     Filter: (p3.id = 1)  
                     Rows Removed by Filter: 25002850  
                     Worker 2: actual time=2314.906..2314.906 rows=0 loops=1  
         ->  Aggregate  (cost=447673.95..447673.96 rows=1 width=8) (actual time=2336.655..2336.655 rows=1 loops=1)  
               Output: count(*)  
               Worker 1: actual time=2336.655..2336.655 rows=1 loops=1  
               ->  Seq Scan on public.p1  (cost=0.00..447673.95 rows=1 width=0) (actual time=2336.646..2336.646 rows=0 loops=1)  
                     Output: p1.id, p1.info  
                     Filter: (p1.id = 1)  
                     Rows Removed by Filter: 25002155  
                     Worker 1: actual time=2336.646..2336.646 rows=0 loops=1  
         ->  Aggregate  (cost=447627.70..447627.71 rows=1 width=8) (actual time=2356.710..2356.710 rows=1 loops=1)  
               Output: count(*)  
               Worker 0: actual time=2356.710..2356.710 rows=1 loops=1  
               ->  Seq Scan on public.p0  (cost=0.00..447627.70 rows=1 width=0) (actual time=0.027..2356.702 rows=1 loops=1)  
                     Output: p0.id, p0.info  
                     Filter: (p0.id = 1)  
                     Rows Removed by Filter: 24999574  
                     Worker 0: actual time=0.027..2356.702 rows=1 loops=1  
         ->  Aggregate  (cost=447553.75..447553.76 rows=1 width=8) (actual time=1854.978..1854.979 rows=1 loops=1)  
               Output: count(*)  
               ->  Seq Scan on public.p2  (cost=0.00..447553.75 rows=1 width=0) (actual time=1854.973..1854.973 rows=0 loops=1)  
                     Output: p2.id, p2.info  
                     Filter: (p2.id = 1)  
                     Rows Removed by Filter: 24995420  
 Planning time: 0.160 ms  
 Execution time: 2413.145 ms  
(40 rows)

测试本地继承表的parallel append

1、准备数据

create table pp (like p);  
create table pp0 (like p) inherits(pp);  
create table pp1 (like p) inherits(pp);  
create table pp2 (like p) inherits(pp);  
create table pp3 (like p) inherits(pp);  
insert into pp0 select * from p0;  
insert into pp1 select * from p1;  
insert into pp2 select * from p2;  
insert into pp3 select * from p3;  
alter table pp set (parallel_workers =0);  
alter table pp0 set (parallel_workers =0);  
alter table pp1 set (parallel_workers =0);  
alter table pp2 set (parallel_workers =0);  
alter table pp3 set (parallel_workers =0);

2、parallel append

postgres=# set enable_parallel_append =on;  
SET  
  
postgres=# explain (analyze,verbose) select count(*) from pp where id=1;  
                                                              QUERY PLAN                                                                 
---------------------------------------------------------------------------------------------------------------------------------------  
 Finalize Aggregate  (cost=896183.57..896183.58 rows=1 width=8) (actual time=2726.483..2726.483 rows=1 loops=1)  
   Output: count(*)  
   ->  Gather  (cost=896183.25..896183.56 rows=3 width=8) (actual time=2644.834..2726.474 rows=4 loops=1)  
         Output: (PARTIAL count(*))  
         Workers Planned: 3  
         Workers Launched: 3  
         ->  Partial Aggregate  (cost=895183.25..895183.26 rows=1 width=8) (actual time=2617.010..2617.010 rows=1 loops=4)  
               Output: PARTIAL count(*)  
               Worker 0: actual time=2469.011..2469.011 rows=1 loops=1  
               Worker 1: actual time=2721.059..2721.059 rows=1 loops=1  
               Worker 2: actual time=2633.534..2633.534 rows=1 loops=1  
               ->  Parallel Append  (cost=0.00..895183.25 rows=1 width=0) (actual time=1999.759..2617.002 rows=0 loops=4)  
                     Worker 0: actual time=0.034..2469.004 rows=1 loops=1  
                     Worker 1: actual time=2721.048..2721.048 rows=0 loops=1  
                     Worker 2: actual time=2633.525..2633.525 rows=0 loops=1  
                     ->  Seq Scan on public.pp3  (cost=0.00..447687.70 rows=1 width=0) (actual time=2633.523..2633.523 rows=0 loops=1)  
                           Filter: (pp3.id = 1)  
                           Rows Removed by Filter: 25002850  
                           Worker 2: actual time=2633.523..2633.523 rows=0 loops=1  
                     ->  Seq Scan on public.pp1  (cost=0.00..447674.45 rows=1 width=0) (actual time=2721.047..2721.047 rows=0 loops=1)  
                           Filter: (pp1.id = 1)  
                           Rows Removed by Filter: 25002155  
                           Worker 1: actual time=2721.047..2721.047 rows=0 loops=1  
                     ->  Seq Scan on public.pp0  (cost=0.00..447628.05 rows=1 width=0) (actual time=0.034..2469.002 rows=1 loops=1)  
                           Filter: (pp0.id = 1)  
                           Rows Removed by Filter: 24999574  
                           Worker 0: actual time=0.034..2469.002 rows=1 loops=1  
                     ->  Seq Scan on public.pp2  (cost=0.00..447555.20 rows=1 width=0) (actual time=2644.426..2644.426 rows=0 loops=1)  
                           Filter: (pp2.id = 1)  
                           Rows Removed by Filter: 24995420  
                     ->  Seq Scan on public.pp  (cost=0.00..0.00 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)  
                           Filter: (pp.id = 1)  
 Planning time: 0.080 ms  
 Execution time: 2777.958 ms  
(34 rows)

3、串行append

postgres=# set enable_parallel_append =off;  
SET  
postgres=# explain (analyze,verbose) select count(*) from pp where id=1;  
                                                        QUERY PLAN                                                           
---------------------------------------------------------------------------------------------------------------------------  
 Aggregate  (cost=1790545.41..1790545.42 rows=1 width=8) (actual time=7619.788..7619.788 rows=1 loops=1)  
   Output: count(*)  
   ->  Append  (cost=0.00..1790545.40 rows=5 width=0) (actual time=0.015..7619.783 rows=1 loops=1)  
         ->  Seq Scan on public.pp  (cost=0.00..0.00 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)  
               Filter: (pp.id = 1)  
         ->  Seq Scan on public.pp0  (cost=0.00..447628.05 rows=1 width=0) (actual time=0.013..1999.372 rows=1 loops=1)  
               Filter: (pp0.id = 1)  
               Rows Removed by Filter: 24999574  
         ->  Seq Scan on public.pp1  (cost=0.00..447674.45 rows=1 width=0) (actual time=1984.480..1984.480 rows=0 loops=1)  
               Filter: (pp1.id = 1)  
               Rows Removed by Filter: 25002155  
         ->  Seq Scan on public.pp2  (cost=0.00..447555.20 rows=1 width=0) (actual time=1713.703..1713.703 rows=0 loops=1)  
               Filter: (pp2.id = 1)  
               Rows Removed by Filter: 24995420  
         ->  Seq Scan on public.pp3  (cost=0.00..447687.70 rows=1 width=0) (actual time=1922.221..1922.221 rows=0 loops=1)  
               Filter: (pp3.id = 1)  
               Rows Removed by Filter: 25002850  
 Planning time: 0.079 ms  
 Execution time: 7619.821 ms  
(19 rows)

外部表并行append？

1、创建postgres_fdw

CREATE EXTENSION postgres_fdw;

2、创建外部数据源

CREATE SERVER foreign_server  
        FOREIGN DATA WRAPPER postgres_fdw  
        OPTIONS (host '127.0.0.1', port '9999', dbname 'postgres');

3、设置外部数据源访问秘钥

CREATE USER MAPPING FOR postgres  
        SERVER foreign_server  
        OPTIONS (user 'postgres', password 'password');

4、创建外部表

CREATE FOREIGN TABLE f0 (  
        id int8,  
        info text  
)  
        SERVER foreign_server  
        OPTIONS (schema_name 'public', table_name 'p0');    
  
CREATE FOREIGN TABLE f1 (  
        id int8,  
        info text  
)  
        SERVER foreign_server  
        OPTIONS (schema_name 'public', table_name 'p1');    
  
CREATE FOREIGN TABLE f2 (  
        id int8,  
        info text  
)  
        SERVER foreign_server  
        OPTIONS (schema_name 'public', table_name 'p2');    
  
CREATE FOREIGN TABLE f3 (  
        id int8,  
        info text  
)  
        SERVER foreign_server  
        OPTIONS (schema_name 'public', table_name 'p3');

5、创建外部表继承关系

create table f(id int8, info text);  
  
alter foreign table f0 inherit f;  
  
alter foreign table f1 inherit f;  
  
alter foreign table f2 inherit f;  
  
alter foreign table f3 inherit f;

64个分区的分区表测试

1、创建本地分区表

create unlogged table ppp(id int8, info text) partition by hash(id);       
      
do language plpgsql $$  
declare  
begin  
  for i in 0..63 loop  
    execute format('CREATE unlogged TABLE ppp%s PARTITION OF ppp FOR VALUES WITH (MODULUS 64, REMAINDER %s)', i, i);      
    execute format('alter table ppp%s set ( parallel_workers=0 )', i);  
  end loop;  
end;  
$$;

2、写入2亿测试数据

insert into ppp select generate_series(1,100000000), 'test';      
insert into ppp select generate_series(1,100000000), 'test';

3、串行append测试

set enable_parallel_append =off;    
  
explain (analyze,verbose) select count(*) from ppp where id=1;    
  
postgres=# explain (analyze,verbose) select count(*) from ppp where id=1;    
                                                        QUERY PLAN                                                          
--------------------------------------------------------------------------------------------------------------------------  
 Aggregate  (cost=3581110.32..3581110.33 rows=1 width=8) (actual time=14179.260..14179.260 rows=1 loops=1)  
   Output: count(*)  
   ->  Append  (cost=0.00..3581110.00 rows=128 width=0) (actual time=12412.108..14179.251 rows=2 loops=1)  
         ->  Seq Scan on public.ppp0  (cost=0.00..55939.20 rows=2 width=0) (actual time=220.643..220.643 rows=0 loops=1)  
               Filter: (ppp0.id = 1)  
               Rows Removed by Filter: 3124096  
         ->  Seq Scan on public.ppp1  (cost=0.00..55926.80 rows=2 width=0) (actual time=218.816..218.816 rows=0 loops=1)  
               Filter: (ppp1.id = 1)  
               Rows Removed by Filter: 3123424  
.......  
         ->  Seq Scan on public.ppp62  (cost=0.00..55997.28 rows=2 width=0) (actual time=218.784..218.784 rows=0 loops=1)  
               Filter: (ppp62.id = 1)  
               Rows Removed by Filter: 3127382  
         ->  Seq Scan on public.ppp63  (cost=0.00..55895.03 rows=2 width=0) (actual time=218.490..218.490 rows=0 loops=1)  
               Filter: (ppp63.id = 1)  
               Rows Removed by Filter: 3121682  
 Planning time: 0.753 ms  
 Execution time: 14179.495 ms  
(197 rows)

4、并行append测试

set enable_parallel_append =on;    
  
alter table ppp0 set (parallel_workers =64);  
  
  
postgres=# explain (analyze,verbose) select count(*) from ppp where id=1;    
                                                                     QUERY PLAN                                                                    
----------------------------------------------------------------------------------------------------------------------------------------------  
 Finalize Aggregate  (cost=73565.00..73565.01 rows=1 width=8) (actual time=685.176..685.176 rows=1 loops=1)  
   Output: count(*)  
   ->  Gather  (cost=73564.83..73564.84 rows=64 width=8) (actual time=293.716..685.121 rows=65 loops=1)  
         Output: (PARTIAL count(*))  
         Workers Planned: 64  
         Workers Launched: 64  
         ->  Partial Aggregate  (cost=73564.83..73564.84 rows=1 width=8) (actual time=523.155..523.155 rows=1 loops=65)  
               Output: PARTIAL count(*)  
               Worker 0: actual time=532.810..532.810 rows=1 loops=1  
               Worker 1: actual time=507.106..507.106 rows=1 loops=1  
               Worker 2: actual time=584.379..584.380 rows=1 loops=1  
               Worker 3: actual time=506.344..506.344 rows=1 loops=1  
.........  
               Worker 61: actual time=539.029..539.029 rows=1 loops=1  
               Worker 62: actual time=536.463..536.463 rows=1 loops=1  
               Worker 63: actual time=533.323..533.323 rows=1 loops=1  
               ->  Parallel Append  (cost=0.00..73564.82 rows=2 width=0) (actual time=514.156..523.146 rows=0 loops=65)  
                     Worker 0: actual time=532.802..532.802 rows=0 loops=1  
                     Worker 1: actual time=507.098..507.098 rows=0 loops=1  
                     Worker 2: actual time=0.025..584.367 rows=2 loops=1  
                     Worker 3: actual time=506.334..506.334 rows=0 loops=1  
...............  
                     Worker 60: actual time=528.767..528.767 rows=0 loops=1  
                     Worker 61: actual time=539.020..539.020 rows=0 loops=1  
                     Worker 62: actual time=536.454..536.454 rows=0 loops=1  
                     Worker 63: actual time=533.313..533.313 rows=0 loops=1  
                     ->  Seq Scan on public.ppp13  (cost=0.00..56066.65 rows=2 width=0) (actual time=533.312..533.312 rows=0 loops=1)  
                           Filter: (ppp13.id = 1)  
                           Rows Removed by Filter: 3131252  
                           Worker 63: actual time=533.312..533.312 rows=0 loops=1  
                     ->  Seq Scan on public.ppp6  (cost=0.00..56056.88 rows=2 width=0) (actual time=536.452..536.452 rows=0 loops=1)  
                           Filter: (ppp6.id = 1)  
                           Rows Removed by Filter: 3130710  
                           Worker 62: actual time=536.452..536.452 rows=0 loops=1  
....................  
                     ->  Seq Scan on public.ppp58  (cost=0.00..55880.68 rows=2 width=0) (actual time=292.269..292.269 rows=0 loops=1)  
                           Filter: (ppp58.id = 1)  
                           Rows Removed by Filter: 3120854  
                           Worker 5: actual time=292.269..292.269 rows=0 loops=1  
                     ->  Seq Scan on public.ppp56  (cost=0.00..55874.90 rows=2 width=0) (actual time=0.016..224.329 rows=2 loops=1)  
                           Filter: (ppp56.id = 1)  
                           Rows Removed by Filter: 3120550  
                     ->  Parallel Seq Scan on public.ppp0  (cost=0.00..18108.35 rows=1 width=0) (actual time=225.194..225.194 rows=0 loops=1)  
                           Filter: (ppp0.id = 1)  
                           Rows Removed by Filter: 3124096  
 Planning time: 0.807 ms  
 Execution time: 655.200 ms  
(329 rows)  
.........

测试外部表是否可以使用parallel append

postgres=# set enable_parallel_append =on;  
SET

1、测试外部表的继承表主表

postgres=#   
explain (analyze,verbose)   
select count(*) from f where id=1;  
                                                          QUERY PLAN                                                            
------------------------------------------------------------------------------------------------------------------------------  
 Aggregate  (cost=612.18..612.19 rows=1 width=8) (actual time=7071.067..7071.068 rows=1 loops=1)  
   Output: count(*)  
   ->  Append  (cost=0.00..612.01 rows=69 width=0) (actual time=1740.313..7071.060 rows=1 loops=1)  
         ->  Seq Scan on public.f  (cost=0.00..0.00 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)  
               Filter: (f.id = 1)  
         ->  Foreign Scan on public.f0  (cost=100.00..153.00 rows=17 width=0) (actual time=1740.310..1740.311 rows=1 loops=1)  
               Remote SQL: SELECT NULL FROM public.p0 WHERE ((id = 1))  
         ->  Foreign Scan on public.f1  (cost=100.00..153.00 rows=17 width=0) (actual time=1860.924..1860.924 rows=0 loops=1)  
               Remote SQL: SELECT NULL FROM public.p1 WHERE ((id = 1))  
         ->  Foreign Scan on public.f2  (cost=100.00..153.00 rows=17 width=0) (actual time=1733.198..1733.198 rows=0 loops=1)  
               Remote SQL: SELECT NULL FROM public.p2 WHERE ((id = 1))  
         ->  Foreign Scan on public.f3  (cost=100.00..153.00 rows=17 width=0) (actual time=1736.619..1736.619 rows=0 loops=1)  
               Remote SQL: SELECT NULL FROM public.p3 WHERE ((id = 1))  
 Planning time: 0.192 ms  
 Execution time: 7071.329 ms  
(15 rows)

2、使用union all查询多个外部表

postgres=#   
explain (analyze,verbose)   
select count(*) from f0 where id=1   
union all   
select count(*) from f1 where id=1   
union all   
select count(*) from f2 where id=1   
union all   
select count(*) from f3 where id=1;  
                                                QUERY PLAN                                                  
----------------------------------------------------------------------------------------------------------  
 Append  (cost=100.04..610.98 rows=4 width=8) (actual time=1731.125..6926.563 rows=4 loops=1)  
   ->  Foreign Scan  (cost=100.04..152.73 rows=1 width=8) (actual time=1731.125..1731.125 rows=1 loops=1)  
         Output: (count(*))  
         Relations: Aggregate on (public.f0)  
         Remote SQL: SELECT count(*) FROM public.p0 WHERE ((id = 1))  
   ->  Foreign Scan  (cost=100.04..152.73 rows=1 width=8) (actual time=1732.174..1732.175 rows=1 loops=1)  
         Output: (count(*))  
         Relations: Aggregate on (public.f1)  
         Remote SQL: SELECT count(*) FROM public.p1 WHERE ((id = 1))  
   ->  Foreign Scan  (cost=100.04..152.73 rows=1 width=8) (actual time=1731.794..1731.795 rows=1 loops=1)  
         Output: (count(*))  
         Relations: Aggregate on (public.f2)  
         Remote SQL: SELECT count(*) FROM public.p2 WHERE ((id = 1))  
   ->  Foreign Scan  (cost=100.04..152.73 rows=1 width=8) (actual time=1731.465..1731.465 rows=1 loops=1)  
         Output: (count(*))  
         Relations: Aggregate on (public.f3)  
         Remote SQL: SELECT count(*) FROM public.p3 WHERE ((id = 1))  
 Planning time: 0.205 ms  
 Execution time: 6926.909 ms  
(19 rows)

小结

1、parallel append功能，在每个参与append的数据分片计算并返回的记录数比较少时，性能提升几乎是线性的。

2、当每个参与append的数据分片都要返回大量数据时，需要注意是否有用到支持并行的聚合，如果没有，那么上层还需要处理大量数据量PARALLEL APPEND效果就不明显。否则不要使用parallel append。(实际上parallel append适合一路并行，而不适合上层没什么计算，串行接收大量APPEND数据的场景。)

3、parallel append + 外部表 + pushdown，可以实现sharding 架构下的并发计算。（例如求SUM,AVG,COUNG,MIN,MAX等，不过针对外部表的parallel append内核层面还没有支持好，需要CUSTOM SCAN）

4、parallel append VS append 性能

case parallel append耗时 串行 append耗时 parallel append性能提升 点评
1亿，4个并行分片，每个分片返回少量数据 2.37 秒 6.99 秒 2.95 倍 几乎线性提升
1亿，4个并行分片，每个分片返回大量数据（但是下一个NODE包括并行聚合） 6.46 秒 21.7 秒 3.36 倍 几乎线性提升
1亿，4个并行分片，每个分片返回大量数据（下一个NODE需要串行返回大量数据） 76.5 秒 18.3 秒 - 4.18 倍 append的下一个NODE需要返回大量数据时不适合使用并行append
2亿，64个并行分片，每个分片返回少量数据 0.655 秒 14.18 秒 21.65 倍 并行越大，提升越明显，这里还需要考虑内存带宽瓶颈（20多倍时，处理速度为 12.9 GB/s）

参考

https://commitfest.postgresql.org/16/987/

《PostgreSQL 9.6 sharding based on FDW & pg_pathman》

《PostgreSQL 9.5+ 高效分区表实现 - pg_pathman》

文章标签：
云原生数据库 PolarDB
关系型数据库
并行计算
SQL
PostgreSQL
关键词：
架构功能
PostgreSQL架构
parallel架构
PostgreSQL功能
架构核心
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系，提升产品性能，打磨产品功能，从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手，打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系， 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台，为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案，提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
德哥
目录
相关文章
数据库知识学习者
|
7月前
|
关系型数据库 Serverless 分布式数据库
【公测】PolarDB PostgreSQL版Serverless功能免费使用​！
【公测】PolarDB PostgreSQL版Serverless功能免费使用​，公测于2024年3月28日开始，持续三个月，公测期间可以免费使用！
数据库知识学习者
927 7
数据库知识学习者
|
7月前
|
关系型数据库 Serverless 分布式数据库
PolarDB PostgreSQL版Serverless功能上线公测啦，公测期间免费使用！
Serverless数据库能够使得数据库集群资源随客户业务负载动态弹性扩缩，将客户从复杂的业务资源评估和运维工作中解放出来。PolarDB PostgreSQL版 Serverless提供了CPU、内存、存储、网络资源的实时弹性能力，构建计算与存储分离架构下的 PolarDB PostgreSQL版产品新形态。
数据库知识学习者
821 9
hh真是个慢性子
|
4月前
|
运维 监控 关系型数据库
【一文搞懂PGSQL】7. PostgreSQL + repmgr + witness 高可用架构
该文档介绍了如何构建基于PostgreSQL的高可用架构，利用repmgr进行集群管理和故障转移，并引入witness节点增强网络故障检测能力。repmgr是一款轻量级的开源工具，支持一键部署、自动故障转移及分布式节点管理。文档详细描述了环境搭建步骤，包括配置postgresql参数、安装与配置repmgr、注册集群节点以及配置witness节点等。此外，还提供了故障手动与自动切换的方法及常用命令，确保集群稳定运行。
hh真是个慢性子
483 4
sunrr
|
7月前
|
SQL 关系型数据库 分布式数据库
在PolarDB for PostgreSQL中，你可以使用LIKE运算符来实现类似的查询功能，而不是使用IF函数
在PolarDB for PostgreSQL中，你可以使用LIKE运算符来实现类似的查询功能，而不是使用IF函数
sunrr
97 7
sunrr
|
7月前
|
SQL 关系型数据库 分布式数据库
在PolarDB for PostgreSQL中，你可以使用LIKE运算符来实现类似的查询功能
在PolarDB for PostgreSQL中，你可以使用LIKE运算符来实现类似的查询功能【1月更文挑战第13天】【1月更文挑战第65篇】
sunrr
70 2
游客xbewbwflf4oy6
|
7月前
|
关系型数据库 Linux Shell
Centos系统上安装PostgreSQL和常用PostgreSQL功能
Centos系统上安装PostgreSQL和常用PostgreSQL功能
游客xbewbwflf4oy6
705 0
无糖可乐嘟嘟
|
21天前
|
弹性计算 API 持续交付
后端服务架构的微服务化转型
本文旨在探讨后端服务从单体架构向微服务架构转型的过程，分析微服务架构的优势和面临的挑战。文章首先介绍单体架构的局限性，然后详细阐述微服务架构的核心概念及其在现代软件开发中的应用。通过对比两种架构，指出微服务化转型的必要性和实施策略。最后，讨论了微服务架构实施过程中可能遇到的问题及解决方案。
无糖可乐嘟嘟
49 2
丰宝宝
|
1月前
|
Cloud Native Devops 云计算
云计算的未来：云原生架构与微服务的革命####
【10月更文挑战第21天】 随着企业数字化转型的加速，云原生技术正迅速成为IT行业的新宠。本文深入探讨了云原生架构的核心理念、关键技术如容器化和微服务的优势，以及如何通过这些技术实现高效、灵活且可扩展的现代应用开发。我们将揭示云原生如何重塑软件开发流程，提升业务敏捷性，并探索其对企业IT架构的深远影响。 ####
丰宝宝
43 3
叫个什么名字
|
1月前
|
Cloud Native 安全 数据安全/隐私保护
云原生架构下的微服务治理与挑战####
随着云计算技术的飞速发展，云原生架构以其高效、灵活、可扩展的特性成为现代企业IT架构的首选。本文聚焦于云原生环境下的微服务治理问题，探讨其在促进业务敏捷性的同时所面临的挑战及应对策略。通过分析微服务拆分、服务间通信、故障隔离与恢复等关键环节，本文旨在为读者提供一个关于如何在云原生环境中有效实施微服务治理的全面视角，助力企业在数字化转型的道路上稳健前行。 ####
叫个什么名字
52 1
m1a6
|
20天前
|
Java 开发者 微服务
从单体到微服务：如何借助 Spring Cloud 实现架构转型
**Spring Cloud** 是一套基于 Spring 框架的**微服务架构解决方案**，它提供了一系列的工具和组件，帮助开发者快速构建分布式系统，尤其是微服务架构。
m1a6
141 68
从单体到微服务：如何借助 Spring Cloud 实现架构转型
数据库

关系型数据库

热门文章

最新文章

  • 1
    德哥的PostgreSQL私房菜 - 史上最屌PG资料合集
  • 2
    什么场景应该用 MongoDB ？
  • 3
    PostgreSQL 如何实现批量更新、删除、插入
  • 4
    PostgreSQL upsert功能(insert on conflict do)的用法
  • 5
    Linux 性能诊断 perf使用指南
  • 6
    PostgreSQL 9种索引的原理和应用场景
  • 7
    PostgreSQL 如何潇洒的处理每天上百TB的数据增量
  • 8
    Jedis常见异常汇总
  • 9
    PostgreSQL 9.5+ 高效分区表实现 - pg_pathman
  • 10
    PostgreSQL 百亿数据 秒级响应 正则及模糊查询
  • 1
    【AI应用启航workshop】构建高可用数据库、拥抱AI智能问数
    40
  • 2
    【赵渝强老师】PostgreSQL的参数文件
    57
  • 3
    MySQL进阶突击系列(04)事务隔离级别、AICD、CAP、BASE原则一直搞不懂？ | 看这篇就够了
    51
  • 4
    MySQL进阶突击系列(03) MySQL架构原理solo九魂17环连问 | 给大厂面试官的一封信
    54
  • 5
    卓越效能，极简运维，体验Serverless高可用架构，完成任务可领取转轮日历！
    45
  • 6
    数据库数据恢复—ORACLE常见故障的数据恢复方案
    62
  • 7
    【赵渝强老师】PostgreSQL的控制文件
    70
  • 8
    PolarDB 分布式版 V2.0，安全可靠的集中分布式一体化数据库管理软件
    91
  • 9
    数据库数据恢复—MYSQL数据库文件损坏的数据恢复案例
    66
  • 10
    MySQL常见函数第二期，你都用过哪些呢 ？
    59

    • 相关产品

  • 云原生数据库 PolarDB
    文档详情 产品详情

    • 相关课程

    更多
  • PolarDB for PostgreSQL 开源人才初级认证培训课程
  • PolarDB for PostgreSQL 内核解读系列课程
  • 7天突破PolarDB for PostgreSQL 2022版
  • PolarDB for PostgreSQL 入门
  • PostgreSQL云上开发实践
  • PostgreSQL实战进阶

    • 相关电子书

    更多
  • 金融级 PostgreSQL监控及优化
  • PostgreSQL在哈啰的实践-周飞
  • PostgreSQL高并发数据库应用数据

    • 相关实验场景

    更多
  • 每个IT人都想学的“Web应用上云经典架构”实战
  • PolarDB NL2SQL：自然语言驱动的数据智能
  • MySQL引擎及架构优化
  • PolarDB for PostgreSQL 闪回特性体验
  • 基于数据湖架构的网站访问行为分析
  • 使用SLB+2ECS+NAS，部署电商web网站的高可用架构

    • 推荐镜像

    更多
  • postgresql
  • mysql
  • mongodb
    • 下一篇
    DataWorks售前咨询