Shulou(Shulou.com)06/01 Report--

This article mainly explains the "PostgreSQL parallel computing algorithm and parameter forced parallelism how to set", the content of the article is simple and clear, easy to learn and understand, now please follow the editor's train of thought slowly in depth, together to study and learn "PostgreSQL parallel computing algorithm and parameter mandatory parallelism how to set" it!

1. Parallelism calculation method of optimizer parallel computing

1. Total number of worker processes

Postgres=# show; max_worker_processes-128 (1 row)

2. For all sessions, the maximum number of WORKER allowed to be opened in parallel computing for QUERY at the same time.

Max_parallel_workers

3. The maximum number of parallel computing WORKER allowed to be enabled per QUERY.

Postgres=# show max_parallel_workers_per_gather; max_parallel_workers_per_gather-0 (1 row)

4. Parallelism of a single query and node

Min (parallel_workers (table level setting, if there is no setting, calculated according to table size), max_parallel_workers_per_gather)

5. The parallelism parameter at the table level is not set by default and calculated from the table size.

Postgres=# alter table pa set (parallel_workers = 32); ALTER TABLE

6. Real parallelism algorithm

Min (max_worker_processes-workers has been run, max_parallel_workers-parallelism currently truly enabled for other sessions, Min (parallel_workers (table-level settings, no settings, calculated according to table size), max_parallel_workers_per_gather) II. Whether the optimizer chooses parallel computing

Whether the optimizer uses parallel computing or not depends on CBO. Choose the method with the lowest cost and estimate the cost of parallel computing. The cost factor parameters are as follows:

Postgres=# show parallel_tuple_cost; parallel_tuple_cost-0 (1 row) postgres=# show parallel_setup_cost; parallel_setup_cost-0 (1 row)

If the execution plan cost of non-parallel computing is lower than that of parallel computing, parallel computing is not used.

Third, whether the optimizer ignores parallel computing

If the table or index scan's table or index is below the set threshold, parallel computing is not enabled for this table scan or index scan.

Postgres=# show min_parallel_table_scan_size; min_parallel_table_scan_size-- 0 (1 row) postgres=# show min_parallel_index_scan_size Min_parallel_index_scan_size-0 (1 row) 4. The optimizer forces the selection of parallel computing parameters # force_parallel_mode = on 5. Parallel computing related parameters

1. Create the index and the parallelism of CREATE TABLE AS,SELECT INTO

Postgres=# show max_parallel_maintenance_workers; max_parallel_maintenance_workers-- 24 (1 row)

2. Parallel partition table JOIN

# enable_partitionwise_join = on

3. Parallel partition table partition aggregation

# enable_partitionwise_aggregate = on

4. Parallel HASH computing

# enable_parallel_hash = on

5. LEADER actively acquires the returned result of parallel WORKER

Parallel_leader_participation = on

6. Parallel APPEND (partition table), UNION ALL query

# enable_parallel_append = on6. Forced parallelism

Force parallelism 24

1. The total WORKER that can be opened is large enough postgres=# show max_worker_processes; max_worker_processes-128 (1 row) 2, the parallelism of all sessions performing parallel computing at the same time is large enough postgres=# set max_parallel_workers=64; SET 3, the WORKER=24 postgres=# set max_parallel_workers_per_gather of parallel computing NODE opened in a single QUERY is 24 4. All table and index scans allow parallel postgres=# set min_parallel_table_scan_size = 0; postgres=# set min_parallel_index_scan_size = 0; 5, parallel computing optimizer cost is set to 0 postgres=# set parallel_tuple_cost = 0; postgres=# set parallel_setup_cost = 0; 6, table-level parallelism is set to 24 postgres=# alter table pa set (parallel_workers = 24); ALTER TABLE 7, effect, force 24 parallelism. Postgres=# explain (analyze) select count (*) from pa QUERY PLAN -Finalize Aggregate (cost=1615.89..1615.89 rows=1 width=8) (actual time=81.711..81.711 rows=1 loops=1)-> Gather (cost=1615.83. .1615.83 rows=24 width=8) (actual time=81.572..90.278 rows=25 loops=1) Workers Planned: 24 Workers Launched: 24-> Partial Aggregate (cost=1615.83..1615.83 rows=1 width=8) (actual time=58.411..58.411 rows=1 loops=25)-> Parallel Seq Scan on pa (cost=0.00..712.71 rows=416667 width=0) (actual time=0.012..35.428 rows=400000 loops=25) Planning Time: 0.449 ms Execution Time: 90.335 ms (8 rows) 7. Function parallelism

1. Parallel function

Create or replace function ftest (int) returns boolean as $$select $1

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.