What is the GanosBase feature for creating spatial indexes in parallel-PolarDB(PolarDB)-阿里云帮助中心

PolarDB for PostgreSQL (Compatible with Oracle) provides the Ganos parallel spatial index creation feature. This feature uses the spatial sorting (GiST Sort) method to parallelize the index building process, greatly reduces disk read and write operations, and accelerates index building. You can follow this topic to experience how spatial index creation on tens of millions of spatio-temporal data records is accelerated.

Background information

PostgreSQL databases are well suited for storing and managing spatial data. However, as the data volume grows, performance issues become increasingly apparent. When the data volume reaches tens of millions of records, creating a spatial index can take a long time.

Prerequisites

You have an Alibaba Cloud account.
A PolarDB for PostgreSQL (Compatible with Oracle) cluster is created. This topic uses a 4-core, 16 GB PolarDB for PostgreSQL (Compatible with Oracle) cluster as an example.

Notes

The spatial sorting method applies only to point data (Point). Using this method for other spatial data types degrades index query performance.

Procedure

Create the spatio-temporal extension and generate test data.

Create a database in the PolarDB for PostgreSQL (Compatible with Oracle) cluster.
Connect to the database by using pgAdmin.
Run the following command to create the Ganos spatio-temporal extension.
```
create extension ganos_geometry cascade;
```

Run the following command to generate test data.

CREATE TABLE test (id int, geom Geometry(Point, 4326), name text, code int);
INSERT INTO test SELECT i,
                ST_SetSRID(ST_MakePoint(random() * 180.0, random() * 90.0), 4326),
                'text', 1
                FROM generate_series(1,1000 * 10000) AS i;

Create a spatial index by using the traditional method.
Run the following command to create a spatial index.
```
select now();
CREATE index ON test using GiST(geom);
select now();
```
Time elapsed:
```
330.10 S
```

Use Ganos to create a spatial index in parallel.

Run the following command to use Ganos to create a spatial index in parallel.

set max_parallel_maintenance_workers=4;
set maintenance_work_mem='1GB';
set polar_enable_gist_sort= on;

select now();
CREATE index on test using GiST(geom);
select now();

Time elapsed:

33.54 S

Conclusion

Using Ganos to create a spatial index in parallel is about 10 times faster than using the traditional method.