7 Parallel Seq Scan

Source chapter 3.5 for PostgreSQL 9.6.

Beta 1Source pages: PDF 38-44Original PDF on GitHub

PDF page 38

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.

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

Table 22 Related parameters for parallel processing

PDF page 39

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.

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.

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

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

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)

Following execution plan component can be shown by the EXPLAIN statement about parallel
processing.

PDF page 41

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

Table 23 The output of the EXPLAIN statement

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.

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_dropslot, 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*

Table 24 Major PARALLEL UNSAFE standard functions

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

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)

In pg_proc in the catalog, functions that are the proparallel column 'r' can only be run on the leader
process of parallel processing.

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

Table 25 Major RESTRICTED PARALLEL SAFE standard functions

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

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

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

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;
         }