Optimize the performance of spatio-temporal queries - Lindorm

To improve the performance of spatio-temporal queries, make sure that the query conditions match the specified spatio-temporal indexes so that Lindorm does not need to scan all data in the table. This topic describes how to optimize the performance of spatio-temporal queries.

Optimize queries that contains only spatio-temporal conditions

If the conditions of a query contain only spatial ranges or spatial and time ranges, you can use the Z-ORDER indexing function to generate spatio-temporal codes for the spatial columns or time columns in one of the following formats: Z-ORDER(geometry) or Z-ORDER(geometry,time). Take note of the following items when you use the Z-ORDER function in queries:

The input parameters that you specify for the Z-ORDER function affect the performance of the query. You can use spatio-temporal indexes only when the query conditions contain all columns that you specify as the input parameters of the Z-ORDER function.
If you use the Z-ORDER function to generate spatio-temporal codes for both spatial and time columns, you can use spatio-temporal indexes only when the upper and lower limits of the time ranges are both specified in the query conditions.

The following table describes how query performance is affected when you specify different input parameters for the Z-ORDER indexing function. In the sample statements listed in the table, the g column is a spatial column and the t column is a time column.


Condition	Statement	`Z-ORDER(g)`	`Z-ORDER(g,t)`
Only spatial ranges are specified.	`SELECT id FROM point_table WHERE ST_Contains(ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'),g);`	The `Z-ORDER(g)` indexing function can be used to accelerate the query.	The `Z-ORDER(g,t)` indexing function cannot be used to accelerate the query. To use `Z-ORDER(g,t)` to create a spatio-temporal index for query acceleration, the query condition must contain both spatial ranges and time ranges.
Spatial and time ranges are both specified.	`SELECT id FROM point_table WHERE ST_Contains(ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'),g) AND t>'2021-01-01 08:21:00' AND t<'2021-01-01 08:23:00';`	The `Z-ORDER(g,t)` indexing function can be used to accelerate the query.	The `Z-ORDER(g,t)` indexing function can be used to accelerate the query.
Spatial and time ranges are both specified.	`SELECT id FROM point_table WHERE ST_Contains(ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'),g) AND t > '2021-01-01 08:21:00';`	The `Z-ORDER(g,t)` indexing function can be used to accelerate the query.	The `Z-ORDER(g,t)` indexing function cannot be used to accelerate the query because the lower limit of the time range is not specified.

Optimize queries that contains spatio-temporal and other conditions

If the query conditions contain spatio-temporal and other conditions, we recommend that you create a composite index for query acceleration. A composite index is a primary key index or secondary index in which the Z-ORDER function and other columns are included, such as PRIMARY KEY(Z-ORDER(g,t), id). The Z-ORDER function is included in the composite index as a column because the Z-ORDER function returns a column of spatio-temporal codes corresponding to the values in specific columns. For more information about composite indexes in LindormTable, see Query optimization.

Optimize parallel queries

If you enable parallel queries, you can use sharding to increase the parallelism of queries to improve query performance.

Sharding is a method that is used to split data into shards. You can use sharding when you create spatio-temporal indexes to distribute data into more shards. You can also use sharding to write spatially or temporally adjacent data to different regions in the index table. In this case, data is stored in different shards in the database storage.

You can use the Z-ORDER indexing function in the following format to create spatio-temporal indexes for columns that store points and specify the number of shards: Z-ORDER(Point, numShards). The numShards parameter specifies the number of data rows that are written to different regions in the index table. A series of prefixes that range from 0 to numShards-1 are separately added to these data rows in sequence before they are written.

The following example compares the query performance before and after you use sharding when you create spatio-temporal indexes.

Do not use sharding when you create spatio-temporal indexes

Create a spatio-temporal table named table_noshard for a sample table that stores about 150 million rows of point data and do not specify numShards.
```
CREATE TABLE table_noshard(id INT, g GEOMETRY(POINT), name VARCHAR, PRIMARY KEY(Z-ORDER(g)));
```
Enable parallel queries for table_noshard.
```
SELECT /*+_l_enable_parallel_(8)*/ id FROM table_noshard WHERE ST_Contains(ST_GeomFromText('POLYGON((...))'), g);
```
According to the results, 2,058,894 rows of data is returned within 10986 ms.

Use sharding when you create spatio-temporal indexes

Create a spatio-temporal table named table_shard8 for the same sample table and set numShards to 8.
```
CREATE TABLE table_shard8(id INT, g GEOMETRY(POINT), name VARCHAR, PRIMARY KEY(Z-ORDER(g, 8)));
```
Enable parallel queries for table_shard8.
```
SELECT /*+_l_enable_parallel_(8)*/ id FROM table_shard8 WHERE ST_Contains(ST_GeomFromText('POLYGON((...))'), g);
```
According to the results, 2,058,894 rows of data is returned within 2501 ms.

According to the results, the performance of parallel queries is significantly improved after you configure numShards.

Optimize queries in which irregular ranges are specified

After you create a spatio-temporal index, if the shape of the specified query range is significantly different from that of the rectangular bounding box specified by the filtering range of the index, the index can barely improve the query performance. The following figure shows an example. fanwei

When you perform a query on large amounts of data, you can specify the /*+_l_enable_enhanced_filter_*/ hint to enable enhanced index filtering.

For example, you can execute the following statement to enable enhanced index filtering for a table named gtest. In this example, a spatio-temporal index is created for the g column and POLYGON((...)) specifies a range with an irregular shape.

SELECT /*+_l_enable_enhanced_filter_*/ id FROM gtest WHERE ST_Contains(ST_GeomFromText('POLYGON((...))'), g);

Note You do not need to use enhanced index filtering when you query small amounts of data. Therefore, enhanced index filtering is disabled by default.