并行查询sql优化

max_worker_processes：设置整个数据库实例层面允许支持的最大后台工作进程数，默认值为8。调整此参数需要重启数据库。数据库的一些系统进程如SysLogger、Bgwriter、WaLWriter、Pgarch、AutoVacuum、PgStat并不包含在这个参数控制的进程数内，即这些系统进程不是后台工作进程。而一些第三方插件产生的后台进程和并行的工作进程都算是后台工作进程，受这个参数的限制。

max_parallel_workers：设置整个数据库实例层面允许用做并行的后台工作进程数。修改无须重启机器。默认值为8。若此参数设置为比max_worker_processes高则无效。

max_parallel_workers_per_gather：设置某个并行操作允许并行度。修改无须重启机器。默认值为2。如果设置为0表示关闭并行查询。此参数比max_parallel_workers高则无效。一般设置为max_worker_processes>=max_parallel_workers>=max_parallel_workers_per_gather。

当设置max_parallel_workers_per_gather=2时，实际执行 597556.456 ms

postgres=# EXPLAIN ANALYZE SELECT id, name FROM people_warm ORDER BY id;
                                                                    QUERY PLAN                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------
 Gather Merge  (cost=5019047.44..9880499.52 rows=41666680 width=37) (actual time=567717.158..594007.752 rows=50000000 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Sort  (cost=5018047.42..5070130.77 rows=20833340 width=37) (actual time=566715.013..570783.692 rows=16666667 loops=3)
         Sort Key: id
         Sort Method: external merge  Disk: 767080kB
         Worker 0:  Sort Method: external merge  Disk: 765288kB
         Worker 1:  Sort Method: external merge  Disk: 767304kB
         ->  Parallel Seq Scan on people_warm  (cost=0.00..776515.40 rows=20833340 width=37) (actual time=0.055..383585.941 rows=16666667 loops=3)
 Planning Time: 0.047 ms
 Execution Time: 597556.456 ms

当设置max_parallel_workers_per_gather=5时，实际执行60948.152 ms，提高了将近10倍。

postgres=# set max_parallel_workers_per_gather=5;
SET
postgres=# EXPLAIN ANALYZE SELECT id, name FROM people_warm ORDER BY id;
                                                                   QUERY PLAN                                                               
     
--------------------------------------------------------------------------------------------------------------------------------------------
-----
 Gather Merge  (cost=2378731.91..8424972.54 rows=50000000 width=37) (actual time=33153.631..57510.906 rows=50000000 loops=1)
   Workers Planned: 5
   Workers Launched: 5
   ->  Sort  (cost=2377731.83..2402731.83 rows=10000000 width=37) (actual time=32014.067..34372.589 rows=8333333 loops=6)
         Sort Key: id
         Sort Method: external merge  Disk: 382696kB
         Worker 0:  Sort Method: external merge  Disk: 377376kB
         Worker 1:  Sort Method: external merge  Disk: 363088kB
         Worker 2:  Sort Method: external merge  Disk: 396384kB
         Worker 3:  Sort Method: external merge  Disk: 390440kB
         Worker 4:  Sort Method: external merge  Disk: 389736kB
         ->  Parallel Seq Scan on people_warm  (cost=0.00..668182.00 rows=10000000 width=37) (actual time=0.138..18433.465 rows=8333333 loop
s=6)
 Planning Time: 0.059 ms
 Execution Time: 60948.152 ms

加完索引之后查询

postgres=# EXPLAIN ANALYZE SELECT id, name FROM people_warm ORDER BY id;
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using id_idx on people_warm  (cost=0.56..1895871.62 rows=50000000 width=37) (actual time=0.082..9285.344 rows=50000000 loops=1)
 Planning Time: 0.086 ms
 Execution Time: 10571.772 ms