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

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

2020-02-25 339
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据库 PolarDB MySQL 版，通用型 2核4GB 50GB
云原生数据库 PolarDB PostgreSQL 版，标准版 2核4GB 50GB
简介： 标签 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
算法
关键词：
PostgreSQL并行
PostgreSQL访问
云数据库 RDS MySQL 版外部表
PostgreSQL外部表
云数据库 RDS MySQL 版解说
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系，提升产品性能，打磨产品功能，从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手，打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系， 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台，为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案，提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
德哥
目录
相关文章
三分钟热度的鱼
|
6月前
|
SQL 分布式计算 大数据
MaxCompute产品使用合集之自建的mysql是否支持外部表
MaxCompute作为一款全面的大数据处理平台，广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践，可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集，涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
三分钟热度的鱼
79 0
德哥
|
SQL 存储 Oracle
PostgreSQL 分页, offset, 返回顺序, 扫描方法原理(seqscan, index scan, index only scan, bitmap scan, parallel xx scan)，游标
PostgreSQL 分页, offset, 返回顺序, 扫描方法原理(seqscan, index scan, index only scan, bitmap scan, parallel xx scan)，游标
德哥
3830 0
德哥
|
SQL 存储 弹性计算
PostgreSQL 分页, offset, 返回顺序, 扫描方法原理(seqscan, index scan, index only scan, bitmap scan, parallel xx scan)，游标
标签 PostgreSQL , 数据离散性 , 扫描性能 , 重复扫 , bitmap index scan , 排序扫描 , 扫描方法 , 顺序 背景 一个这样的问题： 为什么select x from tbl offset x limit x; 两次查询连续的OFFSET，会有重复数据呢？ select ctid,* from tbl where ... offset 0 li
德哥
2072 0
传说中的德哥
|
SQL NoSQL 关系型数据库
【重新发现PostgreSQL之美】- 37 三焦者,决渎之官,水道出焉. FDW
大家好，这里是重新发现PostgreSQL之美 - 37 三焦者,决渎之官,水道出焉. FDW
传说中的德哥
204 0
德哥
|
弹性计算 安全 关系型数据库
阿里云RDS PG 10 HA版 - 使用postgres_fdw外部表插件 - 实例内跨库访问其他库的表
标签 PostgreSQL , postgres_fdw , 阿里云 , 内核安全限制 背景 阿里云rds pg内核安全上做了限制，只能访问当前实例的其他库，所以使用dblink, postgres_fdw时，虽然PG功能上是可以访问其他远程实例的，但是阿里云RDS PG限制了只能访问当前实例。 另一方面，当前实例是HA版本，并且是云化版本，所以IP，PORT都可能在发生迁移、切换后发
德哥
642 0
德哥
|
SQL 分布式计算 并行计算
PostgreSQL 并行计算解说 之13 - parallel OLAP : 中间结果 parallel with unlogged table
标签 PostgreSQL , cpu 并行 , smp 并行 , 并行计算 , gpu 并行 , 并行过程支持 背景 PostgreSQL 11 优化器已经支持了非常多场合的并行。简单估计，已支持27余种场景的并行计算。 parallel seq scan parallel
德哥
638 0
德哥
|
SQL 分布式计算 并行计算
PostgreSQL 并行计算解说 之14 - parallel index scan
标签 PostgreSQL , cpu 并行 , smp 并行 , 并行计算 , gpu 并行 , 并行过程支持 背景 PostgreSQL 11 优化器已经支持了非常多场合的并行。简单估计，已支持27余种场景的并行计算。 parallel seq scan paral
德哥
1162 0
德哥
|
存储 关系型数据库 Go
PostgreSQL 11 内核优化 - 降低vacuum cleanup阶段index scan概率 ( vacuum_cleanup_index_scale_factor , skip index vacuum cleanup stage)
PostgreSQL 11 内核优化 - 降低vacuum cleanup阶段index scan概率 ( vacuum_cleanup_index_scale_factor , skip index vacuum cleanup stage)
德哥
1263 0
开发工程师
|
关系型数据库 分布式数据库 PolarDB
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
开发工程师
358 0
-开发达人-
|
存储 缓存 关系型数据库
PolarDB for PostgreSQL 开源必读手册-开源PolarDB for PostgreSQL架构介绍（上）
PolarDB for PostgreSQL 开源必读手册
-开发达人-
481 0

热门文章

最新文章

  • 1
    RDS for SQL server 空间问题排查汇总
  • 2
    【MySQL】explicit_defaults_for_timestamp 参数详解
  • 3
    mysql 优化语句
  • 4
    mysql----其他小技巧
  • 5
    MySQL修改表结构
  • 6
    数据的正确性和完整性：解析MySQL中的一致性
  • 7
    MySQL5.7.17的简单配置文件
  • 8
    MySQL学习笔记之命令总结
  • 9
    Mysql高级之存储过程
  • 10
    如何修改WAMP中mysql默认空密码
  • 1
    玩转阿里云RDS PostgreSQL数据库通过pg_jieba插件进行分词
    647
  • 2
    使用ECS和RDS搭建个人博客
    1325
  • 3
    PolarDB | PostgreSQL 高并发队列处理业务的数据库性能优化实践
    825
  • 4
    【ECS生长万物之开源】如何一键本地部署PolarDB for PostgreSQL
    1122
  • 5
    NineData已支持「最受欢迎数据库」PostgreSQL
    347
  • 6
    postgresql snapshot 快照源码解读
    640
  • 7
    如何使用AnalyticDB PostgreSQL 版实现“一站式全文检索”业务
    32305
  • 8
    将 PostgreSQL 迁移到 MySQL 数据库
    1706
  • 9
    flink postgresql cdc实时同步（含pg安装配置等）
    3040
  • 10
    PostgreSQL内存上下文[翻译]
    282

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL
  • One Box：解读事务与分析一体化数据库HybridDB for MySQL
  • 如何支撑HTAP场景-HybridDB for MySQL系统架构和技术演进

    • 相关实验场景

    更多
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • PolarDB for PostgreSQL 闪回特性体验
  • 5分钟上手Flink MySQL连接器
  • 云端MySQL的安装及应用
  • 手动部署MySQL数据库（CentOS 7）

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mariadb
    • 下一篇
    阿里云无影云电脑免费试用，最长可试用3个月