Parallel Query in PostgreSQL

Introduction

Nowadays, CPUs have a vast amount of cores available. For a long time, applications have been able to send queries in parallel to databases. When it comes to reporting queries that work with a vast number of table rows, the ability of a query to utilize multiple CPUs can significantly enhance the speed of its execution. In essence, parallel query execution is a powerful tool that enables faster processing of large data sets.

Parallel Query

Parallel query is a very useful feature, improving performance especially for queries that involve large data sets or complex calculations, by executing a query in parallel across multiple CPUs or servers to execute a single query, the system resources can be utilized more efficiently. This can lead to better overall system performance and increased throughput.

Example

Supose that we are using a Car Database. Assuming we want to make a query that returns all "Ford" brand cars with a price above 30,000, sorted by year of manufacture. We can use parallelism to speed up the execution of this query as follows:
Table

Query example:
SET max_parallel_workers_per_gather = 4;
SELECT *
FROM cars
WHERE make = 'Ford' AND price > 30000
ORDER BY year;

The function SET max_parallel_workers_per_gather = 4; sets the maximum number of parallel workers that can be used by a single Gather or Gather Merge node in a query plan. By setting this parameter to 4, as in the given function, a single Gather or Gather Merge node can use up to 4 parallel workers, which can increase the overall performance of the query by utilizing more system resources.

Conclusion

I'm on my personal computer, but doing this in a very huge system that contains a big database, you can improve the performance to use queries that needs to running through in a lot of data.

References: PostgreSQL - How Parallel Query Works