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
Top comments (0)