Accelerate spatial index creation for points using parallel GiST Sort - PolarDB

Building a GiST index by sequential insertion is slow for large datasets because index tuples scattered across disk cause high random I/O. The GiST (Generalized Search Tree) Sort method avoids this by sorting index entries before building, which reduces random disk I/O and speeds up index creation.

Limitations

The GiST Sort method supports point geometry only. Applying it to lines, polygons, or mixed geometry types degrades index query performance.

Create a parallel spatial index

Prerequisites

Before you begin, ensure that you have:

A table with a point geometry column
Enough CPU cores and memory for the parallel workers you plan to use

Parameters

Parameter	Description	Recommended value
`polar_enable_gist_sort`	Enables the GiST Sort method for parallel index creation	`on`
`max_parallel_maintenance_workers`	Number of parallel workers for the index build	Do not exceed the number of physical CPU cores
`maintenance_work_mem`	Total memory usage of parallel workers	At least 1 GB

The parameter values in the following steps are examples. Adjust them based on your hardware and workload.

Steps

Enable the GiST Sort method.
```
SET polar_enable_gist_sort = on;
```
Set the number of parallel workers. Each additional worker increases CPU load during the build. Keep this value at or below the number of physical CPU cores.
```
SET max_parallel_maintenance_workers = 4;
```
Set the memory budget for parallel workers. Set it to at least 1 GB.
```
SET maintenance_work_mem = '1GB';
```
Create the GiST index.
```
CREATE INDEX ON t USING gist(geom);
```
Disable the GiST Sort method.
```
SET polar_enable_gist_sort = off;
```

Performance considerations

Parallel workers and CPU cores: Setting max_parallel_maintenance_workers above the physical CPU core count causes CPU contention and may slow the build rather than speed it up.

I/O-bound workloads: Adding more workers does not improve build speed when the bottleneck is disk I/O rather than CPU. In this case, increasing maintenance_work_mem to reduce random I/O has more impact than increasing worker count.