Use GiST Sort to accelerate parallel creation of spatial indexes for points - PolarDB

Building a GiST index on a large table with point data can take a long time because the standard build inserts each row directly into the index tree, causing random disk I/O at scale. PolarDB for PostgreSQL addresses this with the GiST Sort method: it sorts point data before insertion, which reduces random I/O and lets multiple parallel workers contribute to the build simultaneously.

How GiST Sort works

Standard GiST index builds insert each tuple directly into the tree. On large, unsorted datasets, this causes frequent random reads and writes. The GiST Sort method sorts point data first, then inserts it in order. This significantly reduces random I/O and enables parallel workers to build the index concurrently.

Limitations

GiST Sort only applies to point data. Using it on other spatial data types degrades index query performance.

Prerequisites

Before you begin, make sure you have:

A table with point geometry data
At least 1 GB of memory available for maintenance_work_mem
Enough CPU cores to support the number of parallel workers you plan to use

Create a parallel GiST index

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

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