postgresql并行查询测试，生产环境需慎用

--postgresql.conf配置参数,并重启数据库

max_worker_processes =16
max_parallel_workers_per_gather =4
max_parallel_workers = 8
parallel_tuple_cost = 0.1
parallel_setup_cost = 1000.0
min_parallel_table_scan_size = 8MB
min_parallel_index_scan_size = 512kB
force_parallel_mode = off

并行查询消耗更多的CPU,IO,内存资源参数配置值大小通常关系如下

max_worker_processes > max_parallel_workers > max_parallel_workers_per_gather
创建测试表

postgres=# create table test_big1(id int4,name character varying(32), create_time timestamp without time zone default clock_timestamp());
CREATE TABLE
postgres=# insert into test_big1 (id,name) select n,n||'_test' from generate_series(1,50000000) n;
2021-10-16 04:56:30.576 EDT [1590] WARNING:  archive_mode enabled, yet archive_command is not set

查询

postgres=# explain analyze select * from test_big1 where name='1_test';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..523902.15 rows=1 width=25) (actual time=1.344..10348.014 rows=1 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Parallel Seq Scan on test_big1 (cost=0.00..522902.05 rows=1 width=25) (actual time=8256.839..10326.171 rows=0 loops=5)
Filter: ((name)::text = '1_test'::text)
Rows Removed by Filter: 10000000
Planning time: 6.101 ms
Execution time: 10348.488 ms
(8 rows)

postgres=# show max_parallel_workers_per_gather;
max_parallel_workers_per_gather
---------------------------------
4
(1 row)

postgres=# set max_parallel_workers_per_gather =0;
SET
postgres=# show max_parallel_workers_per_gather;
max_parallel_workers_per_gather
---------------------------------
0
(1 row)

postgres=# explain analyze select * from test_big1 where name='1_test';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on test_big1 (cost=0.00..991616.20 rows=1 width=25) (actual time=0.460..11688.153 rows=1 loops=1)
Filter: ((name)::text = '1_test'::text)
Rows Removed by Filter: 49999999
Planning time: 0.056 ms
Execution time: 11688.183 ms
(5 rows)

并行索引扫描

postgres=# explain analyze select count(name) from test_big1 where id<1000000;
                                                                             QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=29768.46..29768.47 rows=1 width=8) (actual time=415.992..415.992 rows=1 loops=1)
   ->  Gather  (cost=29768.04..29768.45 rows=4 width=8) (actual time=415.376..415.979 rows=5 loops=1)
         Workers Planned: 4
         Workers Launched: 4
         ->  Partial Aggregate  (cost=28768.04..28768.05 rows=1 width=8) (actual time=378.530..378.530 rows=1 loops=5)
               ->  Parallel Index Scan using idx_test_big1 on test_big1  (cost=0.56..28146.60 rows=248576 width=13) (actual time=0.063..300.132 rows=200000 loops=5)
                     Index Cond: (id < 1000000)
 Planning time: 0.604 ms
 Execution time: 419.885 ms
(9 rows)

postgres=# set max_parallel_workers_per_gather =0;
SET
postgres=# explain analyze select count(name) from test_big1 where id<1000000;
                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=38089.65..38089.66 rows=1 width=8) (actual time=389.226..389.226 rows=1 loops=1)
   ->  Index Scan using idx_test_big1 on test_big1  (cost=0.56..35603.89 rows=994304 width=13) (actual time=0.009..252.596 rows=999999 loops=1)
         Index Cond: (id < 1000000)
 Planning time: 0.064 ms
 Execution time: 389.281 ms
(5 rows)