How to run Parallel Query in PostgreSQL 9.6 ?

Parallel queries as the name suggest will provide parallel execution of sequential scans queries including joins, and aggregates.

So lets try to understand this feature introduced in PostgreSQL 9.6 in action.

Initial Data Setup

As you can see above we have not created index. Lets try to analyze an aggregate statement on the order_data table.

As you can see it is trying to do a sequential scan above. In PostgreSQL 9.6 Parallel Queries are not enabled by default.
There are two ways to enable Parallel Queries in PostgreSQL 9.6.

  1. Changing postgresql.conf , this will make it available for all the sequential queries.
  2. Changing only for the session where you are running queries.

Let use the second approach and see what is the result of our EXPLAIN again.
You can use the max_parallel_workers_per_gather parameter to change the number of parallel workers which can made available to execute the query in parallel.

Quick Tip

There is no advantage of giving the number of workers more than the number of CPU’s as it won’t be able to use parallelism after that.

Set the max_parallel_workers_per_gather parameter.

You can use the following command to see if that is done.

Now if you run the analyze command again you will see PostgreSQL is trying to use parallelism and results will be faster.

So if you notice above the execution time is almost reduced by a factor of 4.
Enjoy parallelism from PostgreSQL 9.6 !

0 comments… add one

Leave a Comment