Use Ganos to create spatial indexes in parallel - PolarDB

PolarDB for PostgreSQL(Compatible with Oracle) allows you to use Ganos to create spatial indexes in parallel. Ganos uses the spatial sorting (GiST Sort) method to create indexes in parallel and reduce the number of read and write operations on disks. This way, the indexing efficiency is improved. This topic provides an example to show how you can accelerate the indexing process for millions of spatio-temporal data records.

Background information

PostgreSQL databases are suitable for storing and managing spatial data. An increase in data volume can increase performance issues. It is time-consuming to create spatial indexes for tens of millions of data records.

Prerequisites

An Alibaba Cloud account is created.
A cluster of PolarDB for PostgreSQL(Compatible with Oracle) is created. In the example described in this topic, a cluster of PolarDB for PostgreSQL(Compatible with Oracle) that has 4 cores and 16 GB memory is used.

Additional considerations

The GiST Sort method is suitable only for point data. If you use this method for other types of spatial data, the query performance of indexes can be compromised.

Procedure

Create a spatio-temporal extension and generate test data.

Create a database in the PolarDB for PostgreSQL(Compatible with Oracle) cluster.
Use pgAdmin to connect to the database.
Execute the following statement to create a Ganos spatio-temporal extension:
```
create extension ganos_geometry cascade;
```

Execute the following statements 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;

Use the traditional method to create a spatial index.
Execute the following statements to create a spatial index:
```
select now();
CREATE index ON test using GiST(geom);
select now();
```
The following number shows the amount of time used to create the spatial index:
```
330.10 S
```
Use Ganos to create a spatial index in parallel.
Execute the following statements to create a spatial index in parallel by using Ganos:
```
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();
```
The following number shows the amount of time used to create the spatial index:
```
33.54 S
```

Conclusion

Compared with the traditional method, the GiST Sort method improves the indexing efficiency for spatial data by approximately nine times.