PolarDB:Enable the spatio-temporal parallel query feature

Parallel query is performed at the table level. The following figure shows the parallel query process.

• If a large number of workers are used for the parallel query, the CPU load is high. If the CPU load is high, we recommend that you set the max_parallel_workers_per_gather parameter to 2 to use 2 workers.

• When you enable the parallel query feature for highly concurrent access requests on a server with limited memory, you must set the work_mem parameter to an appropriate value. The minimum value of this parameter is 64 KB. You must make sure that the number of concurrent access requests multiplied by the number of parallel workers multiplied by the value of the work_mem parameter does not exceed 60% of the server memory.

To enable the parallel query feature, perform the following operations:

1. Configure parallel query parameters in the postgresql.conf profile.

   • Configure the max_parallel_workers parameter to specify the total number of parallel workers that can be enabled. The value of the parameter ranges from 8 to 32 and must be smaller than the value of the max_worker_processes parameter.

   • Configure the max_parallel_workers_per_gather parameter to specify the maximum concurrency for the Gather node of a query. The value of the parameter ranges from 2 to 4 and must be smaller than the value of the max_parallel_workers parameter.

   • To forcefully enable the parallel query feature, set the force_parallel_mode parameter to on.

   • To set the number of parallel workers for a table, execute the alter table table_name set (parallel_workers=n) statement.

     Note

     In the preceding SQL statement, n indicates the number of parallel workers. For more information about how to set an appropriate value for n, see the description of the max_parallel_workers_per_gather parameter.

2. Increase the cost of GanosBase functions.

   After a GanosBase module extension is created, functions have a default cost. If the data size of the table for the module extension is small, parallel query is disabled by default. If a function is computing-intensive and parallel query is suitable for the function, you must increase the cost of the function before you can enable parallel query.