PDF page 38
#### 3.5.1 Overview {#pg9-6-3-5-1-overview} In the conventional PostgreSQL, all of the SQL statements ware executed only by the back-end process that accepts the connection. In PostgreSQL 9.6 now it is possible to perform parallel processing by multiple worker processes. **Figure 1.** Parallel Seq Scan \/ Parallel Aggregate PDF page 38
Parallel processing can be executed only for Seq Scan, Join and Aggregate. The degree of parallelism depends on the size of the table. Processes executing parallel processing use the mechanism of the Background Worker. The maximum value of the degree of parallelism is determined by the parameter max_parallel_degree or max_worker_processes, whichever is smaller. Parameter max_parallel_degree can be changed by general users by per-session. **Table 22.** Related parameters for parallel processing | Parameter Name | Description (context) | Default value | | --- | --- | --- | | max_parallel_degree | The maximum value of the degree of parallelism (user) | 2 | | parallel_setup_cost | Start cost of parallel processing (user) | 1000 | | parallel_tuple_cost | Tuple cost of parallel processing (user) | 0.1 | | max_worker_processes | The maximum value of the worker process (postmaster) | 8 | | force_parallel_mode | Force parallel processing (user) | off |PDF page 39
##### Parameter force_parallel_mode {#pg9-6-p39-parameter-force-parallel-mode} Parallel processing is executed only when the cost is considered lower than the normal serial processing. By specifying the parameter force_parallel_mode to on, parallel processing is forced ( Also value 'regress' is for the regression test). However, the parallel processing is executed only when the parameter max_parallel_degree is 1 or more. ##### Related table option {#pg9-6-p39-related-table-option} Table option parallel_degree determines the degree of parallelism for each table. When the value is set to 0, parallel processing is prohibited. If not set, the parameters max_parallel_degree of the session will be the upper limit. If parallel_degree is set to greater than the max_parallel_degree, the upper limit of the actual degree of parallelism cannot exceed the max_parallel_degree. **Example 35.** Execution plan of parallel processing\. ```text postgres=> ALTER TABLE data1 SET (parallel_degree = 2) ; ALTER TABLE postgres=> \d+ data1 Table "public.data1" Column | Type | Modifiers | Storage | Stats target | Description --------+-----------------------+-----------+----------+--------------+----------- c1 | numeric | | main | | c2 | character varying(10) | | extended | | Options: parallel_degree=2 ``` #### 3.5.2 Execution plan {#pg9-6-3-5-2-execution-plan} The example below is the execution plan of the parallel processing SELECT statement. COUNT processing of large-scale table is processed in 3 parallelism.PDF page 40
**Example 36.** Execution plan of parallel processing ```text postgres=> SET max_parallel_degree = 10 ; SET postgres=> EXPLAIN (ANALYZE, VERBOSE) SELECT COUNT(*) FROM data1 ; QUERY PLAN ---------------------------------------------------------------------------------- Finalize Aggregate (cost=29314.09..29314.10 rows=1 width=8) (actual time=662.055..662.055 rows=1 loops=1) Output: pg_catalog.count(*) -> Gather (cost=29313.77..29314.08 rows=3 width=8) (actual time=654.818..662.043 rows=4 loops=1) Output: (count(*)) Workers Planned: 3 Workers Launched: 3 -> Partial Aggregate (cost=28313.77..28313.78 rows=1 width=8) (actual time=640.330..640.331 rows=1 loops=4) Output: count(*) Worker 0: actual time=643.386..643.386 rows=1 loops=1 Worker 1: actual time=645.587..645.588 rows=1 loops=1 Worker 2: actual time=618.493..618.494 rows=1 loops=1 -> Parallel Seq Scan on public.data1 (cost=0.00..25894.42 rows=967742 width=0) (actual time=0.033..337.848 rows=750000 loops=4) Output: c1, c2 Worker 0: actual time=0.037..295.732 rows=652865 loops=1 Worker 1: actual time=0.026..415.235 rows=772230 loops=1 Worker 2: actual time=0.042..359.622 rows=620305 loops=1 Planning time: 0.130 ms Execution time: 706.955 ms (18 rows) ``` ```sql Following execution plan component can be shown by the EXPLAIN statement about parallel processing. ```PDF page 41
**Table 23.** The output of the EXPLAIN statement | Plan component | Description | Explain Statement | | --- | --- | --- | | Parallel Seq Scan | Parallel search processing | All | | Partial Aggregate | Aggregation processing performed by the worker process | All | | Gather | Processing to aggregate the worker process | All | | Finalize Aggregate | The final aggregation processing | All | | Workers Planned: | The number of planned worker processes | All | | Workers Launched: | The number of workers that are actually run | ANALYZE | | Worker N (N=0,1,…) | Processing time of each worker, etc | ANALYZE, VERBOSE | | Single Copy | Processing to be executed in a single process | All | #### 3.5.3 Parallel processing and functions {#pg9-6-3-5-3-parallel-processing-and-functions} There are usable functions and unusable functions in parallel processing. When functions which have 'u'(PARALLEL UNSAFE) value for proparallel column in pg_proc catalog are user in SQL statement, parallel processing can not be performed. The following table shows major standard PARALLEL UNSAFE functions. **Table 24.** Major PARALLEL UNSAFE standard functions | Category | Function name example | | --- | --- | | JSON | json_populate_record, json_populate_recordset, jsonb_insert, jsonb_set | | SEQUENCE object | nextval, currval, setval, lastval | | Large Object | lo_*, loread, lowrite | | Replication | pg_create_*_slot, pg_drop_*_slot, pg_logical_*, pg_replication_* | | Other | pg_advisory_*, pg_try_advisory_*, plpgsql_*_handler, pg_extension_config_dump, pg_*_backup, set_config, txid_current, query_to_xml* | In the following example, two SQL statements that differ only conditional part of the WHERE clause are executed. SELECT statement with the literal in the WHERE clause will be performed parallel processing but, SELECT statement with the currval of sequence operation function is executed in serial.PDF page 42
**Example 37.** The difference of the execution plan by the use of PARALLEL UNSAFE function ```text postgres=> EXPLAIN SELECT COUNT(*) FROM data1 WHERE c1=10 ; QUERY PLAN ------------------------------------------------------------------------------ Aggregate (cost=29314.08..29314.09 rows=1 width=8) -> Gather (cost=1000.00..29314.07 rows=3 width=0) Workers Planned: 3 -> Parallel Seq Scan on data1 (cost=0.00..28313.78 rows=1 width=0) Filter: (c1 = '10'::numeric) (5 rows) postgres=> EXPLAIN SELECT COUNT(*) FROM data1 WHERE c1=currval('seq1') ; QUERY PLAN --------------------------------------------------------------- Aggregate (cost=68717.01..68717.02 rows=1 width=8) -> Seq Scan on data1 (cost=0.00..68717.00 rows=3 width=0) Filter: (c1 = (currval('seq1'::regclass))::numeric) (3 rows) ``` ```text In pg_proc in the catalog, functions that are the proparallel column 'r' can only be run on the leader process of parallel processing. ``` **Table 25.** Major RESTRICTED PARALLEL SAFE standard functions | Category | Function name example | | --- | --- | | Date and Age | age, now | | Random number | random, setseed | | Upgrade | binary_upgrade* | | Convert to XML | cursor_to_xml*, database_to_xml*, schema_to_xml*, table_to_xml* | | Other | pg_start_backup, inet_client*, current_query, pg_backend_pid, pg_conf*, pg_cursor, pg_get_viewdef, pg_prepared_statement, etc | ##### User-defined functions and PARALLEL SAFE {#pg9-6-p42-user-defined-functions-and-parallel-safe} To indicate whether it is possible to perform parallel processing for user-defined functions, can be used PARALLEL SAFE clause or PARALLEL UNSAFE clause in the CREATE FUNCTION statement or ALTER FUNCTION statement. The default is PARALLEL UNSAFE.PDF page 43
**Example 38.** User\-defined functions and PARALLEL SAFE ```text postgres=> CREATE FUNCTION add(integer, integer) RETURNS integer postgres-> AS 'select $1 + $2;' postgres-> LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT ; CREATE FUNCTION postgres=> SELECT proname, proparallel FROM pg_proc WHERE proname = 'add' ; proname | proparallel ---------+------------- add | u (1 row) postgres=> ALTER FUNCTION add(integer, integer) PARALLEL SAFE ; ALTER FUNCTION postgres=> SELECT proname, proparallel FROM pg_proc WHERE proname='add' ; proname | proparallel ---------+------------- add | s (1 row) ``` #### 3.5.4 Calculation of the degree of parallelism {#pg9-6-3-5-4-calculation-of-the-degree-of-parallelism} The degree of parallelism is calculated based on the size of the table, it is increased by one as the size of table is 3,000 blocks, 9,000 blocks and 27,000s block. After that, it is increased as the size of table becomes threefold within the range that does not exceed the parameter max_parallel_degree or parameter max_worker_processes. Actual calculation is executed by create_parallel_paths function in the source code src/backend/optimizer/path/allpaths.c.PDF page 44
**Example 39.** The part of create\_parallel\_paths function ```text int parallel_threshold = 1000; /* * If this relation is too small to be worth a parallel scan, just * In that case, we want to generate a parallel path here anyway. It * might not be worthwhile just for this relation, but when combined * with all of its inheritance siblings it may well pay off. */ if (rel->pages < parallel_threshold && rel->reloptkind == RELOPT_BASEREL) return; /* * Limit the degree of parallelism logarithmically based on the size * of the relation. This probably needs to be a good deal more * sophisticated, but we need something here for now. */ while (rel->pages > parallel_threshold * 3 && parallel_degree < max_parallel_degree) { parallel_degree++; parallel_threshold *= 3; if (parallel_threshold >= PG_INT32_MAX / 3) break; } ```