Enable GanosBase spatio-temporal parallel query - PolarDB

GanosBase uses PostgreSQL's parallel query capability to accelerate complex spatio-temporal queries on large datasets.

How it works

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

Usage notes

High CPU load: If many workers are running simultaneously and CPU usage is high, set max_parallel_workers_per_gather to 2.
Memory under concurrent load: When enabling parallel query on a server handling many concurrent connections with limited memory, set work_mem to a value that satisfies this constraint:
```
concurrent_requests × parallel_workers × work_mem ≤ 60% of server memory
```
The minimum value of work_mem is 64 KB.

Enable parallel query for GanosBase

Step 1: Configure parallel query parameters

Set the following parameters in postgresql.conf:

Parameter	Description	Range / value
`max_parallel_workers`	Total number of parallel workers that can run at the same time. Must be less than `max_worker_processes`.	8–32
`max_parallel_workers_per_gather`	Maximum number of workers per Gather node in a query plan. Must be less than `max_parallel_workers`.	2–4
`force_parallel_mode`	Forces the planner to use a parallel plan, regardless of cost estimates.	`on` \| `off`

To set the number of parallel workers for a specific table, run:

ALTER TABLE <table_name> SET (parallel_workers = <n>);

Replace <n> with the number of workers. For guidance on choosing a value, refer to the max_parallel_workers_per_gather range in the table above.

Step 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:

ALTER FUNCTION <function_name>(<argument_types>) COST <value>;