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 means a higher CPU load. If the CPU load is already high in some scenarios, we recommend that you set the number of workers to 2, that is, set 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, whose minimum value is 64 in units of 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.
- Set parallel query parameters in the PostgreSQL configuration file postgresql.conf.
- 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, run 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.