Ganos can use the parallel query capability of PostgreSQL to accelerate complex spatio-temporal data queries that involve a large amount of data.
Parallel query principles
- A larger number of workers used for parallel query indicate a higher CPU load. If the CPU load is already high, we recommend that you set the number of workers to 2 by setting the max_parallel_workers_per_gather parameter to 2.
- When enabling parallel query for highly concurrent access requests on a server with limited memory, you must properly set the work_mem parameter (to a minimum of 64 KB). You must ensure 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.
- Set parallel query parameters in the postgresql.conf profile.
- Set the max_parallel_workers parameter to specify the total number of parallel workers that can be enabled, in the range of 8 to 32. The value of this parameter must be smaller than that of the max_worker_processes parameter.
- Set the max_parallel_workers_per_gather parameter to specify the maximum number of parallel workers for a single query gather, in the range of 2 to 4. The value of this parameter must be smaller than that of the max_parallel_workers parameter.
- To forcibly enable parallel query, set the force_parallel_mode parameter to on.
- To set the number of parallel workers for a table, execute the following SQL statement:
alter table table_name set (parallel_workers=n).
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.
- Increase the cost of Ganos functions.
After a Ganos 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.