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

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

Precautions

  • 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.

Usage

To enable Ganos parallel query, perform the following operations:
  1. 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.
  2. 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.