PostgreSQL 9.6: Powerful Parallel Query Processing – Full Performance Test

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.

postgresqlparallelqueryprocessing

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:


Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Leave a Reply

Be the First to Comment!

Notify of
avatar

wpDiscuz