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

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

2020-02-25 472
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据库 PolarDB PostgreSQL 版，标准版 2核4GB 50GB
云原生数据库 PolarDB MySQL 版，通用型 2核8GB 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数据库、数据库生态工具、云原生智能化数据库管控平台，为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案，提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
德哥
目录
相关文章
bjcuug
|
消息中间件 存储 关系型数据库
PostgreSQL技术大讲堂 - 第33讲：并行查询管理
PostgreSQL从小白到专家，技术大讲堂 - 第33讲：并行查询管理
bjcuug
688 1
vohelon
|
关系型数据库 Linux PostgreSQL
这个错误是因为Flink CDC在尝试访问PostgreSQL的"decoderbufs"文件时，发现该文件不存在
【1月更文挑战第23天】【1月更文挑战第111篇】这个错误是因为Flink CDC在尝试访问PostgreSQL的"decoderbufs"文件时，发现该文件不存在
vohelon
519 11
游客uqxendrmbghb6
|
SQL 关系型数据库 MySQL
远程访问GitLab内置的PostgreSQL数据库
业务系统需要接入GitLab，业务系统以及GitLab都有一套各自的用户系统，需要实现同一套账户密码的话需要将数据同步给GitLab（主要是密码），然而由于GitLab安全策略，通过api进行同步GitLab用户数据并不满足需求，所以需要能直接访问GitLab数据库进行数据修改。
游客uqxendrmbghb6
5156 0
远程访问GitLab内置的PostgreSQL数据库
w风雨无阻w
|
关系型数据库 Linux PostgreSQL
Linux centos8 docker中安装postgresql12.4及远程访问设置
Linux centos8 docker中安装postgresql12.4及远程访问设置
w风雨无阻w
1211 0
赵渝强老师
|
7月前
|
Oracle 关系型数据库 数据库
【赵渝强老师】在PostgreSQL中访问Oracle
本文介绍了如何在PostgreSQL中使用oracle_fdw扩展访问Oracle数据库数据。首先需从Oracle官网下载三个Instance Client安装包并解压，设置Oracle环境变量。接着从GitHub下载oracle_fdw扩展，配置pg_config环境变量后编译安装。之后启动PostgreSQL服务器，在数据库中创建oracle_fdw扩展及外部数据库服务，建立用户映射。最后通过创建外部表实现对Oracle数据的访问。文末附有具体操作步骤与示例代码。
赵渝强老师
237 6
【赵渝强老师】在PostgreSQL中访问Oracle
赵渝强老师
|
7月前
|
关系型数据库 数据库 PostgreSQL
【赵渝强老师】在PostgreSQL中使用file_fdw访问外部文件系统
本文介绍了PostgreSQL的file_fdw扩展，它支持直接从数据库访问服务器文件系统中的文件，文件格式需为text、csv或binary。内容涵盖从编译安装扩展、配置postgresql.conf参数、重启数据库实例，到创建扩展、外部文件服务及外部表的完整流程，并通过具体示例展示如何查询外部表数据，同时附有视频讲解以帮助理解操作步骤。
赵渝强老师
222 22
赵渝强老师
|
7月前
|
关系型数据库 数据库 PostgreSQL
【赵渝强老师】使用postgre_fdw访问外部PostgreSQL
本文介绍了如何使用postgres_fdw扩展让PostgreSQL访问外部远端数据库数据。通过编译安装扩展、修改配置文件、重启数据库、创建扩展及外部服务器对象等步骤，最终实现本地数据库通过外部表访问远程数据。附带视频讲解，详细演示操作流程，并提醒需调整远端PostgreSQL配置以支持远程登录。
赵渝强老师
171 7
yzshover
|
关系型数据库 大数据 PostgreSQL
PostgreSQL16-新特性-并行聚合
PostgreSQL16-新特性-并行聚合
yzshover
361 0
weixin_836869520
|
关系型数据库 Unix 数据库
PostgreSQL开启远程访问
PostgreSQL开启远程访问
weixin_836869520
870 2
yuanzhengme
|
关系型数据库 Linux 数据安全/隐私保护
PostgreSQL【部署 02】在线安装PostgreSQL（Some psql features might not work 问题处理+角色密码设置+配置远程访问）
PostgreSQL【部署 02】在线安装PostgreSQL（Some psql features might not work 问题处理+角色密码设置+配置远程访问）
yuanzhengme
201 0
PostgreSQL【部署 02】在线安装PostgreSQL（Some psql features might not work 问题处理+角色密码设置+配置远程访问）

热门文章

最新文章

  • 1
    【紧急救援】MySQL CPU 100%！一套组合拳教你快速定位并解决！
  • 2
    MySQL中日期和时间函数的使用指南
  • 3
    5分钟，使用yum方式完成mysql安装
  • 4
    Docker部署WordPress LNMP(Nginx PHP MySQL)环境实践
  • 5
    MySQL - 左连接、右连接、内连接、完全外连接、交叉连接 & 一对多、多对一、多对多 & 联合连接
  • 6
    mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file
  • 7
    mysql多实例配置使用方法　
  • 8
    Mysql与StarRocks语法上的不同
  • 9
    mysql-mmm配置文档--下
  • 10
    MySQL查询重复出现次数最多的记录
  • 1
    CTE vs 子查询：深入拆解PostgreSQL复杂SQL的隐藏性能差异
    582
  • 2
    拯救海量数据：PostgreSQL分区表性能优化实战手册（附压测对比）
    727
  • 3
    B-tree不是万能药：PostgreSQL索引失效的7种高频场景与破解方案
    384
  • 4
    PostgreSQL窗口函数避坑指南：如何让复杂分析查询提速300%？
    314
  • 5
    从Explain到执行：手把手优化PostgreSQL慢查询的5个关键步骤
    857
  • 6
    【赵渝强老师】在PostgreSQL中访问Oracle
    237
  • 7
    PolarDB PostgreSQL企业版固定规格集群的Serverless功能正式上线
    417
  • 8
    【赵渝强老师】使用postgre_fdw访问外部PostgreSQL
    171
  • 9
    【赵渝强老师】在PostgreSQL中使用file_fdw访问外部文件系统
    222
  • 10
    【赵渝强老师】创建PostgreSQL的数据库
    222

    • 相关课程

    更多
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 阿里云数据库产品家族及特性
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布
  • 阿里云MySQL云数据库产品体系介绍
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL

    • 相关实验场景

    更多
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践

    • 推荐镜像

    更多
  • mysql
  • postgresql
    • 下一篇
    阿里云负载均衡收费标准：ALB、NLB和CLB价格，包括LCU费用、实例费和公网带宽价格