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

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

2020-02-25 360
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据库 PolarDB MySQL 版，通用型 2核8GB 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数据库、数据库生态工具、云原生智能化数据库管控平台，为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案，提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
德哥
目录
相关文章
bjcuug
|
消息中间件 存储 关系型数据库
PostgreSQL技术大讲堂 - 第33讲：并行查询管理
PostgreSQL从小白到专家，技术大讲堂 - 第33讲：并行查询管理
bjcuug
486 1
yzshover
|
关系型数据库 大数据 PostgreSQL
PostgreSQL16-新特性-并行聚合
PostgreSQL16-新特性-并行聚合
yzshover
160 0
游客ebdjhbkyk4vb4
|
SQL 存储 算法
PostgreSQL并行HashJoin解读
PostgreSQL并行HashJoin解读
游客ebdjhbkyk4vb4
864 0
PostgreSQL并行HashJoin解读
德哥
|
SQL 分布式计算 并行计算
PostgreSQL 并行计算解说 之13 - parallel OLAP : 中间结果 parallel with unlogged table
标签 PostgreSQL , cpu 并行 , smp 并行 , 并行计算 , gpu 并行 , 并行过程支持 背景 PostgreSQL 11 优化器已经支持了非常多场合的并行。简单估计，已支持27余种场景的并行计算。 parallel seq scan parallel
德哥
654 0
德哥
|
SQL 分布式计算 并行计算
PostgreSQL 并行计算解说 之14 - parallel index scan
标签 PostgreSQL , cpu 并行 , smp 并行 , 并行计算 , gpu 并行 , 并行过程支持 背景 PostgreSQL 11 优化器已经支持了非常多场合的并行。简单估计，已支持27余种场景的并行计算。 parallel seq scan paral
德哥
1212 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)
德哥
1277 0
栈江湖
|
26天前
|
存储 Oracle 关系型数据库
数据库传奇：MySQL创世之父的两千金My、Maria
《数据库传奇：MySQL创世之父的两千金My、Maria》介绍了MySQL的发展历程及其分支MariaDB。MySQL由Michael Widenius等人于1994年创建，现归Oracle所有，广泛应用于阿里巴巴、腾讯等企业。2009年，Widenius因担心Oracle收购影响MySQL的开源性，创建了MariaDB，提供额外功能和改进。维基百科、Google等已逐步替换为MariaDB，以确保更好的性能和社区支持。掌握MariaDB作为备用方案，对未来发展至关重要。
栈江湖
55 3
栈江湖
|
26天前
|
安全 关系型数据库 MySQL
MySQL崩溃保险箱：探秘Redo/Undo日志确保数据库安全无忧！
《MySQL崩溃保险箱：探秘Redo/Undo日志确保数据库安全无忧！》介绍了MySQL中的三种关键日志：二进制日志（Binary Log）、重做日志（Redo Log）和撤销日志（Undo Log）。这些日志确保了数据库的ACID特性，即原子性、一致性、隔离性和持久性。Redo Log记录数据页的物理修改，保证事务持久性；Undo Log记录事务的逆操作，支持回滚和多版本并发控制（MVCC）。文章还详细对比了InnoDB和MyISAM存储引擎在事务支持、锁定机制、并发性等方面的差异，强调了InnoDB在高并发和事务处理中的优势。通过这些机制，MySQL能够在事务执行、崩溃和恢复过程中保持
栈江湖
62 3
栈江湖
|
26天前
|
SQL 关系型数据库 MySQL
数据库灾难应对：MySQL误删除数据的救赎之道，技巧get起来！之binlog
《数据库灾难应对：MySQL误删除数据的救赎之道，技巧get起来！之binlog》介绍了如何利用MySQL的二进制日志（Binlog）恢复误删除的数据。主要内容包括： 1. **启用二进制日志**：在`my.cnf`中配置`log-bin`并重启MySQL服务。 2. **查看二进制日志文件**：使用`SHOW VARIABLES LIKE 'log_%';`和`SHOW MASTER STATUS;`命令获取当前日志文件及位置。 3. **创建数据备份**：确保在恢复前已有备份，以防意外。 4. **导出二进制日志为SQL语句**：使用`mysqlbinlog`
栈江湖
82 2
AIweker
|
1月前
|
关系型数据库 MySQL 数据库
Python处理数据库：MySQL与SQLite详解 | python小知识
本文详细介绍了如何使用Python操作MySQL和SQLite数据库，包括安装必要的库、连接数据库、执行增删改查等基本操作，适合初学者快速上手。
AIweker
260 15

热门文章

最新文章

  • 1
    RDS PostgreSQL版发布 rds_duckdb 插件！
  • 2
    【赵渝强老师】PostgreSQL的参数文件
  • 3
    市场领先者MySQL的挑战者：PostgreSQL的崛起
  • 4
    MySQL的count()方法慢
  • 5
    【MySQL基础篇】事务（事务操作、事务四大特性、并发事务问题、事务隔离级别）
  • 6
    【MySQL进阶篇】存储引擎（MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案）
  • 7
    Linux安装jdk、mysql、redis
  • 8
    深入解析MySQL的EXPLAIN：指标详解与索引优化
  • 9
    10个案例告诉你mysql不使用子查询的原因
  • 10
    Mysql并发控制和日志
  • 1
    Linux下安装MySQL
    70
  • 2
    XtraBackup支持哪些MySQL数据库版本？
    813
  • 3
    如何将数据从MySQL迁移到PolarDB？
    596
  • 4
    Java医院绩效考核系统源码B/S+avue+MySQL助力医院实现精细化管理
    91
  • 5
    Linux对MySQL语法校验
    107
  • 6
    【MySQL系列笔记】分库分表
    265
  • 7
    mysql 设置环境变量与未设置环境变量连接数据库的区别
    183
  • 8
    服务器Linux系统配置mysql数据库主从自动备份
    236
  • 9
    mysqlTools 一分钟部署安装本mysql多个版本，解锁繁琐部署过程
    436
  • 10
    golang使用gorm操作mysql3，数据查询
    111

    • 相关课程

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

    • 相关电子书

    更多
  • PostgreSQL 物联网六脉神剑
  • PostgreSQL在哈啰的实践-周飞
  • PostgreSQL高并发数据库应用数据

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • PolarDB for PostgreSQL 闪回特性体验

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mongodb
    • 下一篇
    DataWorks智能交互式数据开发与分析之旅