PostgreSQL 9.6: Powerful Parallel Query Processing – Full Performance Test

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

A most awaited feature, The Powerful Parallel Sequential Query Scan feature introduced in PostgreSQL 9.6.

You can easily set the Parallel Sequential parameter’s value and can execute your queries 10 times faster.
When we are talking about Parallel Sequential Scanning, in background multiple workers or CPU threads are responsible for executing one single query.

In the PostgreSQL 9.6, You can change the Process Workers parameter value which is default 8. (Using max_worker_processes parameter)

Important Note:
If you want to increase Parallel Process Worker parameter value, You should make sure about your total CPU usage because More Parallel Process Worker required More CPU.
Once you change the parameter value, You must restart the PostgreSQL service.

Database Theory: What is Parallel Query Processing (Parallel Database System)?

The details about new Parallel Sequential Parameters:

max_worker_processes: Total number of background workers limited by this parameter. (Default: 8)
max_parallel_workers_per_gather: Total number of workers that can assist a sequential scan.
parallel_setup_cost: used to estimate the cost of instantiate a worker.
min_parallel_relation_size: You can define the minimum size of the relation and only those relations will consider for additional workers.
parallel_tuple_cost: used to estimate the cost of transferring a tuple from one worker to another.

Below is a full demonstration of this:

Prepared different result set by changing the max_parallel_workers_per_gather parameter value and once you change the value of this parameter, please do not forget to restart PostgreSQL Service. (You can find this parameter in postgresql.conf)

For each execution result, You should compare the value of “Execution time”.’

First create one sample table:

Insert Millions of data to test the performance of BRIN Index:
Check the total size of table:
Check the execution plan of below query, where max_parallel_workers_per_gather = 0:
Result:
Check the execution plan of below query, where max_parallel_workers_per_gather = 2:
Result:
Check the execution plan of below query, where max_parallel_workers_per_gather = 4:
Result:
Check the execution plan of below query, where max_parallel_workers_per_gather = 6:
Result: