备案控制台
探索云世界
开发者社区 数据库 文章 正文

PostgreSQL 并行计算解说 之25 - parallel FDW scan (并行访问多个外部表) with parallel append (FDW must with IsForeignScanParallelSafe)

2020-02-25 287
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据库 PolarDB MySQL 版，Serverless 5000PCU 100GB
推荐场景：
PolarDB for MySQL 多主集群体验
简介： 标签 PostgreSQL , cpu 并行 , smp 并行 , 并行计算 , gpu 并行 , 并行过程支持 背景 PostgreSQL 11 优化器已经支持了非常多场合的并行。简单估计，已支持27余种场景的并行计算。 parallel seq scan

标签

PostgreSQL , cpu 并行 , smp 并行 , 并行计算 , gpu 并行 , 并行过程支持

背景

PostgreSQL 11 优化器已经支持了非常多场合的并行。简单估计，已支持27余种场景的并行计算。

parallel seq scan                                                  
                                                  
parallel index scan                                                  
                                                  
parallel index only scan                                                  
                                                  
parallel bitmap scan                                                  
                                                  
parallel filter                                                  
                                              
parallel hash agg                                              
                                              
parallel group agg                                              
                                                  
parallel cte                                                  
                                                  
parallel subquery                                                  
                                                  
parallel create table                                                  
                                                  
parallel create index                                                  
                                                  
parallel select into                                                  
                                                  
parallel CREATE MATERIALIZED VIEW                                                  
                                                  
parallel 排序 : gather merge                                                   
                                                  
parallel nestloop join                                                  
                                                  
parallel hash join                                                  
                                                  
parallel merge join                                                  
                                                  
parallel 自定义并行聚合                                                  
                                                  
parallel 自定义并行UDF                                                  
                                                  
parallel append                 
        
parallel append merge             
                                                  
parallel union all          
                                                  
parallel fdw table scan                                                  
                                                  
parallel partition join                                                  
                                                  
parallel partition agg                                                  
                                                  
parallel gather                                          
                                  
parallel gather merge                                  
                                                  
parallel rc 并行                                                  
                                                  
parallel rr 并行                                                  
                                                  
parallel GPU 并行                                                  
                                                  
parallel unlogged table                
              
lead parallel

接下来进行一一介绍。

关键知识请先自行了解：

1、优化器自动并行度算法 CBO

《PostgreSQL 9.6 并行计算 优化器算法浅析》

《PostgreSQL 11 并行计算算法，参数，强制并行度设置》

parallel FDW scan (并行访问多个外部表) with parallel append (FDW must with IsForeignScanParallelSafe)

FDW是PG的外部数据访问接口，支持超级多的外部数据源，PG优化器已经支持了FDW的并行访问接口，当你需要访问多个FDW外部表时，只要这个外部表FDW是parallel safe的，就可以结合parallel append实现多个外部表的并行扫描。

https://wiki.postgresql.org/wiki/Fdw

通过union all 或者 继承表访问，都能够实现fdw tables的并行扫描。 (前提是FDW handler是parallel safe的)，从目前来看内置的file_fdw是safe的，而postgres_fdw不是。

Scans of foreign tables, unless the foreign data wrapper has an IsForeignScanParallelSafe API which indicates otherwise.

https://www.postgresql.org/docs/current/parallel-safety.html

https://stackoverflow.com/questions/52357024/parallel-append-on-foreign-tables-in-postgresql-11/52451104#52451104

https://stackoverflow.com/questions/52501783/need-parallel-append-on-foreign-tables-in-postgresql

https://www.postgresql.org/docs/11/file-fdw.html

数据量：10亿

db5=# copy (select generate_series(1,1000000000/24)) to '/data01/digoal/pg_root8001/file1' ;  
COPY 41666666  
  
create extension file_fdw;  
CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;  
  
do language plpgsql $$  
declare  
begin  
  for i in 0..23 loop  
    execute format('CREATE FOREIGN TABLE ft%s (i int) server pglog options (filename ''/data01/digoal/pg_root8001/file1'')', i);  
  end loop;  
end;  
$$;  
  
create table ft(like ft0);  
  
do language plpgsql $$  
declare  
begin  
  for i in 0..23 loop  
    execute format('alter table ft%s inherit ft', i);  
  end loop;  
end;  
$$;  
  
analyze ft;  
  
db5=# explain verbose select * from ft0 limit 10;  
                                   QUERY PLAN                                      
---------------------------------------------------------------------------------  
 Limit  (cost=0.00..1.04 rows=10 width=4)  
   Output: i  
   ->  Foreign Scan on public.ft0  (cost=0.00..1181573.80 rows=11371528 width=4)  
         Output: i  
         Foreign File: /data01/digoal/pg_root8001/file1  
         Foreign File Size: 363888891 b  
(6 rows)
场景 数据量 关闭并行 开启并行 并行度 开启并行性能提升倍数
parallel scan mult FDW tables (通过继承表方式) 10亿 180 秒 7.8 秒 24 23.1 倍
parallel scan mult FDW tables (通过union all) 10亿 165.6 秒 27.8 秒 5 6 倍 
postgres=# show max_worker_processes ;            
 max_worker_processes             
----------------------            
 128            
(1 row)            
postgres=# set min_parallel_table_scan_size =0;            
postgres=# set min_parallel_index_scan_size =0;            
postgres=# set parallel_tuple_cost =0;            
postgres=# set parallel_setup_cost =0;            
postgres=# set max_parallel_workers=128;            
postgres=# set max_parallel_workers_per_gather =24;            
postgres=# set enable_parallel_hash =on;            
postgres=# set enable_parallel_append =on;            
postgres=# set enable_partitionwise_aggregate =off;            
postgres=# set work_mem ='128MB';            

postgres=# set client_min_messages =debug;    
postgres=# set pg_hint_plan.debug_print =on;    
postgres=# set pg_hint_plan.enable_hint=on;    
postgres=# set pg_hint_plan.message_level =debug;

1、关闭并行，耗时： 180 秒 , 165.6 秒。

postgres=# set max_parallel_workers_per_gather =0;            
postgres=# set enable_parallel_append =off;      
  
db5=# explain select count(*) from ft;  
                                   QUERY PLAN                                      
---------------------------------------------------------------------------------  
 Aggregate  (cost=43033406.87..43033406.88 rows=1 width=8)  
   ->  Append  (cost=0.00..42057423.08 rows=390393516 width=0)  
         ->  Seq Scan on ft  (cost=0.00..0.00 rows=1 width=0)  
         ->  Foreign Scan on ft0  (cost=0.00..4211087.40 rows=41666664 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
         ->  Foreign Scan on ft1  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
         ->  Foreign Scan on ft2  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
         ->  Foreign Scan on ft3  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
         ->  Foreign Scan on ft4  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
         ->  Foreign Scan on ft5  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
         ->  Foreign Scan on ft6  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
         ->  Foreign Scan on ft7  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
         ->  Foreign Scan on ft8  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
         ->  Foreign Scan on ft9  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
         ->  Foreign Scan on ft10  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
         ->  Foreign Scan on ft11  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
         ->  Foreign Scan on ft12  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
         ->  Foreign Scan on ft13  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
         ->  Foreign Scan on ft14  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
         ->  Foreign Scan on ft15  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
         ->  Foreign Scan on ft16  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
         ->  Foreign Scan on ft17  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
         ->  Foreign Scan on ft18  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
         ->  Foreign Scan on ft19  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
         ->  Foreign Scan on ft20  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
         ->  Foreign Scan on ft21  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
         ->  Foreign Scan on ft22  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
         ->  Foreign Scan on ft23  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
(75 rows)  
  
db5=# select count(*) from ft;  
   count     
-----------  
 999999984  
(1 row)  
  
Time: 180065.413 ms (03:00.065)  
  
  
explain   
select * from (    
select count(*) from ft0 union all     
select count(*) from ft1 union all     
select count(*) from ft2 union all     
select count(*) from ft3 union all     
select count(*) from ft4 union all     
select count(*) from ft5 union all     
select count(*) from ft6 union all     
select count(*) from ft7 union all     
select count(*) from ft8 union all     
select count(*) from ft9 union all     
select count(*) from ft10 union all     
select count(*) from ft11 union all     
select count(*) from ft12 union all     
select count(*) from ft13 union all     
select count(*) from ft14 union all     
select count(*) from ft15 union all     
select count(*) from ft16 union all     
select count(*) from ft17 union all     
select count(*) from ft18 union all     
select count(*) from ft19 union all     
select count(*) from ft20 union all     
select count(*) from ft21 union all     
select count(*) from ft22 union all     
select count(*) from ft23     
) as t;  
                                   QUERY PLAN                                      
---------------------------------------------------------------------------------  
 Append  (cost=4315254.06..41081439.89 rows=24 width=8)  
   ->  Aggregate  (cost=4315254.06..4315254.07 rows=1 width=8)  
         ->  Foreign Scan on ft0  (cost=0.00..4211087.40 rows=41666664 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
   ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
         ->  Foreign Scan on ft1  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
   ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
         ->  Foreign Scan on ft2  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
   ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
         ->  Foreign Scan on ft3  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
   ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
         ->  Foreign Scan on ft4  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
   ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
         ->  Foreign Scan on ft5  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
   ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
         ->  Foreign Scan on ft6  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
   ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
         ->  Foreign Scan on ft7  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
   ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
         ->  Foreign Scan on ft8  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
   ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
         ->  Foreign Scan on ft9  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
   ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
         ->  Foreign Scan on ft10  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
   ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
         ->  Foreign Scan on ft11  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
   ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
         ->  Foreign Scan on ft12  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
   ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
         ->  Foreign Scan on ft13  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
   ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
         ->  Foreign Scan on ft14  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
   ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
         ->  Foreign Scan on ft15  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
   ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
         ->  Foreign Scan on ft16  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
   ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
         ->  Foreign Scan on ft17  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
   ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
         ->  Foreign Scan on ft18  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
   ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
         ->  Foreign Scan on ft19  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
   ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
         ->  Foreign Scan on ft20  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
   ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
         ->  Foreign Scan on ft21  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
   ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
         ->  Foreign Scan on ft22  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
   ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
         ->  Foreign Scan on ft23  (cost=0.00..1560624.70 rows=15162037 width=0)  
               Foreign File: /data01/digoal/pg_root8001/file1  
               Foreign File Size: 363888891 b  
(97 rows)  
  
  
  count     
----------  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
(24 rows)  
  
Time: 165555.591 ms (02:45.556)

2、开启并行，耗时： 7.8 秒 , 27.8 秒。

postgres=# set max_parallel_workers_per_gather =24;                
postgres=# set enable_parallel_append =on;     
  
  
db5=# explain verbose /*+ Parallel(ft 24 hard) */ select count(*) from ft;  
DEBUG:  pg_hint_plan:  
used hint:  
Parallel(ft 24 hard)  
not used hint:  
duplication hint:  
error hint:  
  
                                             QUERY PLAN                                               
----------------------------------------------------------------------------------------------------  
 Finalize Aggregate  (cost=195196.81..195196.82 rows=1 width=8)  
   Output: count(*)  
   ->  Gather  (cost=195196.74..195196.75 rows=24 width=8)  
         Output: (PARTIAL count(*))  
         Workers Planned: 24  
         ->  Partial Aggregate  (cost=195196.74..195196.75 rows=1 width=8)  
               Output: PARTIAL count(*)  
               ->  Parallel Append  (cost=0.00..0.00 rows=78078694 width=0)  
                     ->  Foreign Scan on public.ft0  (cost=0.00..4211087.40 rows=41666664 width=0)  
                           Foreign File: /data01/digoal/pg_root8001/file1  
                           Foreign File Size: 363888891 b  
                     ->  Foreign Scan on public.ft1  (cost=0.00..1560624.70 rows=15162037 width=0)  
                           Foreign File: /data01/digoal/pg_root8001/file1  
                           Foreign File Size: 363888891 b  
                     ->  Foreign Scan on public.ft2  (cost=0.00..1560624.70 rows=15162037 width=0)  
                           Foreign File: /data01/digoal/pg_root8001/file1  
                           Foreign File Size: 363888891 b  
                     ->  Foreign Scan on public.ft3  (cost=0.00..1560624.70 rows=15162037 width=0)  
                           Foreign File: /data01/digoal/pg_root8001/file1  
                           Foreign File Size: 363888891 b  
                     ->  Foreign Scan on public.ft4  (cost=0.00..1560624.70 rows=15162037 width=0)  
                           Foreign File: /data01/digoal/pg_root8001/file1  
                           Foreign File Size: 363888891 b  
                     ->  Foreign Scan on public.ft5  (cost=0.00..1560624.70 rows=15162037 width=0)  
                           Foreign File: /data01/digoal/pg_root8001/file1  
                           Foreign File Size: 363888891 b  
                     ->  Foreign Scan on public.ft6  (cost=0.00..1560624.70 rows=15162037 width=0)  
                           Foreign File: /data01/digoal/pg_root8001/file1  
                           Foreign File Size: 363888891 b  
                     ->  Foreign Scan on public.ft7  (cost=0.00..1560624.70 rows=15162037 width=0)  
                           Foreign File: /data01/digoal/pg_root8001/file1  
                           Foreign File Size: 363888891 b  
                     ->  Foreign Scan on public.ft8  (cost=0.00..1560624.70 rows=15162037 width=0)  
                           Foreign File: /data01/digoal/pg_root8001/file1  
                           Foreign File Size: 363888891 b  
                     ->  Foreign Scan on public.ft9  (cost=0.00..1560624.70 rows=15162037 width=0)  
                           Foreign File: /data01/digoal/pg_root8001/file1  
                           Foreign File Size: 363888891 b  
                     ->  Foreign Scan on public.ft10  (cost=0.00..1560624.70 rows=15162037 width=0)  
                           Foreign File: /data01/digoal/pg_root8001/file1  
                           Foreign File Size: 363888891 b  
                     ->  Foreign Scan on public.ft11  (cost=0.00..1560624.70 rows=15162037 width=0)  
                           Foreign File: /data01/digoal/pg_root8001/file1  
                           Foreign File Size: 363888891 b  
                     ->  Foreign Scan on public.ft12  (cost=0.00..1560624.70 rows=15162037 width=0)  
                           Foreign File: /data01/digoal/pg_root8001/file1  
                           Foreign File Size: 363888891 b  
                     ->  Foreign Scan on public.ft13  (cost=0.00..1560624.70 rows=15162037 width=0)  
                           Foreign File: /data01/digoal/pg_root8001/file1  
                           Foreign File Size: 363888891 b  
                     ->  Foreign Scan on public.ft14  (cost=0.00..1560624.70 rows=15162037 width=0)  
                           Foreign File: /data01/digoal/pg_root8001/file1  
                           Foreign File Size: 363888891 b  
                     ->  Foreign Scan on public.ft15  (cost=0.00..1560624.70 rows=15162037 width=0)  
                           Foreign File: /data01/digoal/pg_root8001/file1  
                           Foreign File Size: 363888891 b  
                     ->  Foreign Scan on public.ft16  (cost=0.00..1560624.70 rows=15162037 width=0)  
                           Foreign File: /data01/digoal/pg_root8001/file1  
                           Foreign File Size: 363888891 b  
                     ->  Foreign Scan on public.ft17  (cost=0.00..1560624.70 rows=15162037 width=0)  
                           Foreign File: /data01/digoal/pg_root8001/file1  
                           Foreign File Size: 363888891 b  
                     ->  Foreign Scan on public.ft18  (cost=0.00..1560624.70 rows=15162037 width=0)  
                           Foreign File: /data01/digoal/pg_root8001/file1  
                           Foreign File Size: 363888891 b  
                     ->  Foreign Scan on public.ft19  (cost=0.00..1560624.70 rows=15162037 width=0)  
                           Foreign File: /data01/digoal/pg_root8001/file1  
                           Foreign File Size: 363888891 b  
                     ->  Foreign Scan on public.ft20  (cost=0.00..1560624.70 rows=15162037 width=0)  
                           Foreign File: /data01/digoal/pg_root8001/file1  
                           Foreign File Size: 363888891 b  
                     ->  Foreign Scan on public.ft21  (cost=0.00..1560624.70 rows=15162037 width=0)  
                           Foreign File: /data01/digoal/pg_root8001/file1  
                           Foreign File Size: 363888891 b  
                     ->  Foreign Scan on public.ft22  (cost=0.00..1560624.70 rows=15162037 width=0)  
                           Foreign File: /data01/digoal/pg_root8001/file1  
                           Foreign File Size: 363888891 b  
                     ->  Foreign Scan on public.ft23  (cost=0.00..1560624.70 rows=15162037 width=0)  
                           Foreign File: /data01/digoal/pg_root8001/file1  
                           Foreign File Size: 363888891 b  
                     ->  Seq Scan on public.ft  (cost=0.00..0.00 rows=1 width=0)  
(81 rows)  
  
  
/*+ Parallel(ft 24 hard) */ select count(*) from ft;  
  
   count     
-----------  
 999999984  
(1 row)  
  
Time: 7779.985 ms (00:07.780)  
  
db5=# explain verbose select sum(count) from (                                   
select count(*) from ft0 union all     
select count(*) from ft1 union all     
select count(*) from ft2 union all     
select count(*) from ft3 union all     
select count(*) from ft4 union all     
select count(*) from ft5 union all     
select count(*) from ft6 union all     
select count(*) from ft7 union all     
select count(*) from ft8 union all     
select count(*) from ft9 union all     
select count(*) from ft10 union all     
select count(*) from ft11 union all     
select count(*) from ft12 union all     
select count(*) from ft13 union all     
select count(*) from ft14 union all     
select count(*) from ft15 union all     
select count(*) from ft16 union all     
select count(*) from ft17 union all     
select count(*) from ft18 union all     
select count(*) from ft19 union all     
select count(*) from ft20 union all     
select count(*) from ft21 union all     
select count(*) from ft22 union all     
select count(*) from ft23               
) as t;  
                                          QUERY PLAN                                            
----------------------------------------------------------------------------------------------  
 Gather  (cost=1598529.79..9110843.52 rows=24 width=8)  
   Output: (count(*))  
   Workers Planned: 5  
   ->  Parallel Append  (cost=1598529.79..9110843.52 rows=1 width=8)  
         ->  Aggregate  (cost=4315254.06..4315254.07 rows=1 width=8)  
               Output: count(*)  
               ->  Foreign Scan on public.ft0  (cost=0.00..4211087.40 rows=41666664 width=0)  
                     Output: ft0.i  
                     Foreign File: /data01/digoal/pg_root8001/file1  
                     Foreign File Size: 363888891 b  
         ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
               Output: count(*)  
               ->  Foreign Scan on public.ft1  (cost=0.00..1560624.70 rows=15162037 width=0)  
                     Output: ft1.i  
                     Foreign File: /data01/digoal/pg_root8001/file1  
                     Foreign File Size: 363888891 b  
         ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
               Output: count(*)  
               ->  Foreign Scan on public.ft2  (cost=0.00..1560624.70 rows=15162037 width=0)  
                     Output: ft2.i  
                     Foreign File: /data01/digoal/pg_root8001/file1  
                     Foreign File Size: 363888891 b  
         ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
               Output: count(*)  
               ->  Foreign Scan on public.ft3  (cost=0.00..1560624.70 rows=15162037 width=0)  
                     Output: ft3.i  
                     Foreign File: /data01/digoal/pg_root8001/file1  
                     Foreign File Size: 363888891 b  
         ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
               Output: count(*)  
               ->  Foreign Scan on public.ft4  (cost=0.00..1560624.70 rows=15162037 width=0)  
                     Output: ft4.i  
                     Foreign File: /data01/digoal/pg_root8001/file1  
                     Foreign File Size: 363888891 b  
         ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
               Output: count(*)  
               ->  Foreign Scan on public.ft5  (cost=0.00..1560624.70 rows=15162037 width=0)  
                     Output: ft5.i  
                     Foreign File: /data01/digoal/pg_root8001/file1  
                     Foreign File Size: 363888891 b  
         ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
               Output: count(*)  
               ->  Foreign Scan on public.ft6  (cost=0.00..1560624.70 rows=15162037 width=0)  
                     Output: ft6.i  
                     Foreign File: /data01/digoal/pg_root8001/file1  
                     Foreign File Size: 363888891 b  
         ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
               Output: count(*)  
               ->  Foreign Scan on public.ft7  (cost=0.00..1560624.70 rows=15162037 width=0)  
                     Output: ft7.i  
                     Foreign File: /data01/digoal/pg_root8001/file1  
                     Foreign File Size: 363888891 b  
         ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
               Output: count(*)  
               ->  Foreign Scan on public.ft8  (cost=0.00..1560624.70 rows=15162037 width=0)  
                     Output: ft8.i  
                     Foreign File: /data01/digoal/pg_root8001/file1  
                     Foreign File Size: 363888891 b  
         ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
               Output: count(*)  
               ->  Foreign Scan on public.ft9  (cost=0.00..1560624.70 rows=15162037 width=0)  
                     Output: ft9.i  
                     Foreign File: /data01/digoal/pg_root8001/file1  
                     Foreign File Size: 363888891 b  
         ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
               Output: count(*)  
               ->  Foreign Scan on public.ft10  (cost=0.00..1560624.70 rows=15162037 width=0)  
                     Output: ft10.i  
                     Foreign File: /data01/digoal/pg_root8001/file1  
                     Foreign File Size: 363888891 b  
         ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
               Output: count(*)  
               ->  Foreign Scan on public.ft11  (cost=0.00..1560624.70 rows=15162037 width=0)  
                     Output: ft11.i  
                     Foreign File: /data01/digoal/pg_root8001/file1  
                     Foreign File Size: 363888891 b  
         ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
               Output: count(*)  
               ->  Foreign Scan on public.ft12  (cost=0.00..1560624.70 rows=15162037 width=0)  
                     Output: ft12.i  
                     Foreign File: /data01/digoal/pg_root8001/file1  
                     Foreign File Size: 363888891 b  
         ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
               Output: count(*)  
               ->  Foreign Scan on public.ft13  (cost=0.00..1560624.70 rows=15162037 width=0)  
                     Output: ft13.i  
                     Foreign File: /data01/digoal/pg_root8001/file1  
                     Foreign File Size: 363888891 b  
         ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
               Output: count(*)  
               ->  Foreign Scan on public.ft14  (cost=0.00..1560624.70 rows=15162037 width=0)  
                     Output: ft14.i  
                     Foreign File: /data01/digoal/pg_root8001/file1  
                     Foreign File Size: 363888891 b  
         ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
               Output: count(*)  
               ->  Foreign Scan on public.ft15  (cost=0.00..1560624.70 rows=15162037 width=0)  
                     Output: ft15.i  
                     Foreign File: /data01/digoal/pg_root8001/file1  
                     Foreign File Size: 363888891 b  
         ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
               Output: count(*)  
               ->  Foreign Scan on public.ft16  (cost=0.00..1560624.70 rows=15162037 width=0)  
                     Output: ft16.i  
                     Foreign File: /data01/digoal/pg_root8001/file1  
                     Foreign File Size: 363888891 b  
         ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
               Output: count(*)  
               ->  Foreign Scan on public.ft17  (cost=0.00..1560624.70 rows=15162037 width=0)  
                     Output: ft17.i  
                     Foreign File: /data01/digoal/pg_root8001/file1  
                     Foreign File Size: 363888891 b  
         ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
               Output: count(*)  
               ->  Foreign Scan on public.ft18  (cost=0.00..1560624.70 rows=15162037 width=0)  
                     Output: ft18.i  
                     Foreign File: /data01/digoal/pg_root8001/file1  
                     Foreign File Size: 363888891 b  
         ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
               Output: count(*)  
               ->  Foreign Scan on public.ft19  (cost=0.00..1560624.70 rows=15162037 width=0)  
                     Output: ft19.i  
                     Foreign File: /data01/digoal/pg_root8001/file1  
                     Foreign File Size: 363888891 b  
         ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
               Output: count(*)  
               ->  Foreign Scan on public.ft20  (cost=0.00..1560624.70 rows=15162037 width=0)  
                     Output: ft20.i  
                     Foreign File: /data01/digoal/pg_root8001/file1  
                     Foreign File Size: 363888891 b  
         ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
               Output: count(*)  
               ->  Foreign Scan on public.ft21  (cost=0.00..1560624.70 rows=15162037 width=0)  
                     Output: ft21.i  
                     Foreign File: /data01/digoal/pg_root8001/file1  
                     Foreign File Size: 363888891 b  
         ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
               Output: count(*)  
               ->  Foreign Scan on public.ft22  (cost=0.00..1560624.70 rows=15162037 width=0)  
                     Output: ft22.i  
                     Foreign File: /data01/digoal/pg_root8001/file1  
                     Foreign File Size: 363888891 b  
         ->  Aggregate  (cost=1598529.79..1598529.80 rows=1 width=8)  
               Output: count(*)  
               ->  Foreign Scan on public.ft23  (cost=0.00..1560624.70 rows=15162037 width=0)  
                     Output: ft23.i  
                     Foreign File: /data01/digoal/pg_root8001/file1  
                     Foreign File Size: 363888891 b  
(148 rows)  
  
  count     
----------  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
 41666666  
(24 rows)  
  
Time: 27778.411 ms (00:27.778)

1、当多个外部表在union all子句中期望被并行扫描时，并行度不太好控制，即使使用HINT。

2、当多个外部表继承在一个普通表上时，并行度更好控制，直接使用外部表的父表（普通表）来控制即可（通过HINT）。

其他知识

1、优化器自动并行度算法 CBO

《PostgreSQL 9.6 并行计算 优化器算法浅析》

《PostgreSQL 11 并行计算算法，参数，强制并行度设置》

2、function, op 识别是否支持parallel

postgres=# select proparallel,proname from pg_proc;                                                  
 proparallel |                   proname                                                                      
-------------+----------------------------------------------                                                  
 s           | boolin                                                  
 s           | boolout                                                  
 s           | byteain                                                  
 s           | byteaout

3、subquery mapreduce unlogged table

对于一些情况，如果期望简化优化器对非常非常复杂的SQL并行优化的负担，可以自己将SQL拆成几段，中间结果使用unlogged table保存，类似mapreduce的思想。unlogged table同样支持parallel 计算。

4、vacuum，垃圾回收并行。

5、dblink 异步调用并行

《PostgreSQL VOPS 向量计算 + DBLINK异步并行 - 单实例 10亿 聚合计算跑进2秒》

《PostgreSQL 相似搜索分布式架构设计与实践 - dblink异步调用与多机并行(远程 游标+记录 UDF实例)》

《PostgreSQL dblink异步调用实现 并行hash分片JOIN - 含数据交、并、差 提速案例 - 含dblink VS pg 11 parallel hash join VS pg 11 智能分区JOIN》

暂时不允许并行的场景(将来PG会继续扩大支持范围)：

1、修改行，锁行，除了create table as , select into, create mview这几个可以使用并行。

2、query 会被中断时，例如cursor , loop in PL/SQL ，因为涉及到中间处理，所以不建议开启并行。

3、paralle unsafe udf ，这种UDF不会并行

4、嵌套并行(udf (内部query并行))，外部调用这个UDF的SQL不会并行。（主要是防止large parallel workers ）

5、SSI 隔离级别

参考

https://www.postgresql.org/docs/11/parallel-plans.html

《PostgreSQL 11 并行计算算法，参数，强制并行度设置》

《PostgreSQL 11 preview - 并行计算 增强 汇总》

《PostgreSQL 10 自定义并行计算聚合函数的原理与实践 - (含array_agg合并多个数组为单个一元数组的例子)》

《PostgreSQL 9.6 并行计算 优化器算法浅析》

 

免费领取阿里云RDS PostgreSQL实例、ECS虚拟机

文章标签：
云原生数据库 PolarDB
Java
C++
异构计算
并行计算
分布式计算
SQL
关系型数据库
PostgreSQL
RDS
算法
关键词：
云数据库 RDS MySQL 版外部表
云原生数据库 PolarDB并行
云原生数据库 PolarDB外部表
云数据库 RDS MySQL 版解说
云数据库 RDS PostgreSQL 版并行
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系，提升产品性能，打磨产品功能，从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手，打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系， 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台，为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案，提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
德哥,digoal
目录
相关文章
bjcuug
|
6月前
|
消息中间件 存储 关系型数据库
PostgreSQL技术大讲堂 - 第33讲：并行查询管理
PostgreSQL从小白到专家，技术大讲堂 - 第33讲：并行查询管理
bjcuug
289 1
算精通
|
5月前
|
关系型数据库 MySQL 分布式数据库
PolarDB MySQL版并行查询技术探索与实践
PolarDB MySQL版并行查询技术探索与实践 PolarDB MySQL版在企业级查询加速特性上进行了深度技术探索，其中并行查询作为其重要组成部分，已经在线稳定运行多年，持续演进。本文将详细介绍并行查询的背景、挑战、方案、特性以及实践。
算精通
109 2
算精通
|
5月前
|
SQL 关系型数据库 分布式数据库
深度解析PolarDB数据库并行查询技术
深度解析PolarDB数据库并行查询技术：加速SQL执行的关键问题和核心技术 随着数据规模的不断扩大，用户SQL的执行时间越来越长，这不仅对数据库的优化能力提出更高的要求，并且对数据库的执行模式也提出了新的挑战。为了解决这个问题，许多数据库系统，包括Oracle、SQL Server等，都开始提供并行查询引擎的支持，以充分利用系统资源，达到加速SQL执行的效果。本文将深入探讨基于代价进行并行优化、并行执行的云数据库的并行查询引擎的关键问题和核心技术。
算精通
124 2
yzshover
|
10月前
|
关系型数据库 大数据 PostgreSQL
PostgreSQL16-新特性-并行聚合
PostgreSQL16-新特性-并行聚合
yzshover
103 0
agathe
|
存储 SQL 人工智能
PolarDB 弹性并行查询（ePQ）功能使用白皮书
1 法律声明阿里云提醒您在阅读或使用本文档之前仔细阅读、充分理解本法律声明各条款的内容。如果您 阅读或使用本文档，您的阅读或使用行为将被视为对本声明全部内容的认可。您应当通过阿里云网站或阿里云提供的其他授权通道下载、获取本文档，且仅能用于自身的 合法合规的业务活动。本文档的内容视为阿里云的保密信息，您应当严格遵守保密义务; 未经 阿里云事先书面同意，您不得向任何第三方披露本手册内容或提供给任何第三
agathe
455 0
PolarDB 弹性并行查询（ePQ）功能使用白皮书
bqospzg5rfs7g
|
SQL 移动开发 关系型数据库
PostgreSQL 执行计划，成本公式解说，代价因子校准，自动跟踪SQL执行计划（三）|学习笔记
快速学习PostgreSQL 执行计划，成本公式解说，代价因子校准，自动跟踪SQL执行计划（三）
bqospzg5rfs7g
553 0
PostgreSQL 执行计划，成本公式解说，代价因子校准，自动跟踪SQL执行计划（三）|学习笔记
baphsqca3imha
|
存储 SQL Cloud Native
基于 PolarDB for MySQL 实现并行创建索引赛题解析 | 学习笔记
快速学习基于 PolarDB for MySQL 实现并行创建索引赛题解析
baphsqca3imha
180 0
基于 PolarDB for MySQL 实现并行创建索引赛题解析 | 学习笔记
遥凌
|
存储 Cloud Native 关系型数据库
PolarDB MySQL 弹性多机并行深度剖析
背景并行查询（Parallel Query）是自PolarDB MySQL诞生伊始就致力于研发的企业级查询加速功能，这与PolarDB的产品定位密切相关，基于云原生的计算存储分离使底层数据量远突破单机容量的限制，而针对更海量数据的复杂分析、报表类业务也成为用户自然而然的需求，同时由于PolarDB是服务于在线业务（OLTP）的关系数据库系统，用户会希望分析业务能具有"在线"的能
遥凌
823 0
PolarDB MySQL 弹性多机并行深度剖析
遥凌
|
存储 SQL Cloud Native
PolarDB 并行查询的前世今生
本文会深入介绍PolarDB MySQL在并行查询这一企业级查询加速特性上做的技术探索、形态演进和相关组件的实现原理，所涉及功能随PolarDB MySQL 8.0.2版本上线。
遥凌
971 2
PolarDB 并行查询的前世今生
开发者小助手_LS
|
存储 SQL Cloud Native
PolarDB 并行查询的前世今生
本文会深入介绍PolarDB MySQL在并行查询这一企业级查询加速特性上做的技术探索、形态演进和相关组件的实现原理，所涉及功能随PolarDB MySQL 8.0.2版本上线。
开发者小助手_LS
510 0
PolarDB 并行查询的前世今生

热门文章

最新文章

  • 1
    MySQL全局库表查询准确定位字段
  • 2
    轻松入门MySQL：深入探究MySQL的ER模型，数据库设计的利器与挑战（22）
  • 3
    基于 java + Springboot + vue +mysql 大学生实习管理系统（含源码）
  • 4
    mysql性能优化-慢查询分析、优化索引和配置
  • 5
    CentOS 7 下使用yum安装MySQL5.7.20 最简单 图文详解
  • 6
    MySQL 8教程：详解如何更改和重置用户密码
  • 7
    centos7安装mysql-带网盘安装包
  • 8
    MySQL学习手册（第一部分）
  • 9
    高性能 MySQL 第四版（GPT 重译）（三）(1)
  • 10
    mysql用户权限设置
  • 1
    玩转阿里云RDS PostgreSQL数据库通过pg_jieba插件进行分词
    444
  • 2
    使用ECS和RDS搭建个人博客
    1151
  • 3
    PolarDB | PostgreSQL 高并发队列处理业务的数据库性能优化实践
    603
  • 4
    【ECS生长万物之开源】如何一键本地部署PolarDB for PostgreSQL
    844
  • 5
    NineData已支持「最受欢迎数据库」PostgreSQL
    311
  • 6
    postgresql snapshot 快照源码解读
    357
  • 7
    如何使用AnalyticDB PostgreSQL 版实现“一站式全文检索”业务
    32161
  • 8
    将 PostgreSQL 迁移到 MySQL 数据库
    1084
  • 9
    flink postgresql cdc实时同步（含pg安装配置等）
    1807
  • 10
    PostgreSQL内存上下文[翻译]
    164

    • 相关课程

    更多
  • 7天突破PolarDB for PostgreSQL 2022版
  • 关系型数据库ACP认证课程
  • 关系型数据库线上预习视频
  • PolarDB for PostgreSQL 入门
  • PostgreSQL云上开发实践
  • PostgreSQL实战进阶

    • 相关电子书

    更多
  • 云栖大会：开源 PolarDB 架构演进、关键技术与社区建设
  • 2023云栖大会：和客户一起玩转PolarDB新特性
  • 2023云栖大会：PolarDB for AI

    • 相关实验场景

    更多
  • PolarDB NL2SQL：自然语言驱动的数据智能
  • 阿里云百炼xAnalyticDB PostgreSQL构建AIGC应用
  • 云原生数据库PolarDB快速入门
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 使用CloudLens采集PolarDB日志并进行审计分析
  • PolarDB for PostgreSQL 闪回特性体验

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • clickhouse
    • 下一篇
    2024年阿里云免费云服务器及学生云服务器申请教程参考