Follow these practices to optimize query performance, reduce storage usage, and improve write throughput for trajectory data in ApsaraDB RDS for PostgreSQL with Ganos.
Choose the right index type
Ganos supports three index types for trajectory data. Select the type that matches the dimensions your queries filter on:
| Index type | Use when |
|---|---|
| Spatial index | Queries filter on spatial range only |
| Temporal index | Queries filter on time range only |
| Spatio-temporal composite index | Queries filter on both spatial range and time range |
Spatial index
Use a spatial index when your queries filter only on spatial range—for example, finding all trajectories that pass through a geographic area.
-- Create a function-based spatial index to accelerate the filtering of spatial data.
create index tr_spatial_geometry_index on trajtab using gist (st_trajectoryspatial(traj));Temporal index
Use a temporal index when your queries filter only on time range. Use a GiST-based index for time-span queries, or B-tree indexes on start and end times for point-in-time lookups:
-- Create a function-based temporal index to accelerate the filtering of time.
create index tr_timespan_time_index on trajtab using gist (st_timespan(traj));
-- Create function-based indexes on the start time and end time of a trajectory object.
create index tr_starttime_index on trajtab using btree (st_starttime(traj));
create index tr_endtime_index on trajtab using btree (st_endtime(traj));Spatio-temporal composite index
Use a spatio-temporal composite index when queries filter on both spatial range and time range. This index type requires the btree_gist extension, which allows mixing B-tree and GiST index columns.
-- Create a btree_gist extension.
create extension btree_gist;
-- Use btree_gist to create a spatio-temporal composite index on the start time, end time, and spatial data of a trajectory object.
create index tr_traj_test_stm_etm_sp_index on traj_test using gist (st_starttime(traj),st_endtime(traj),st_trajectoryspatial(traj));Use partitioned tables
The amount of trajectory data in a database keeps increasing along with the continuous use of the database. As a result, a larger number of database indexes are created and data queries slow down. In this case, you can use partitioned tables to decrease the data size of a single table.
For partitioning syntax and strategies, see Table partitioning in the PostgreSQL documentation.
Reduce string-type attribute fields
A large number of string-type attribute fields in trajectory data wastes storage and degrades performance.
Fixed values: Convert them to integers in application code instead of storing strings.
Required string fields: Set a default maximum length to cap storage use.
To set the default string attribute length to 32 characters:
-- Set the default length of string-type attribute fields to 32.
Set ganos.trajectory.attr_string_length = 32;Batch trajectory points into objects
Build trajectory objects from multiple points in a single operation. Avoid appending trajectory points one by one.
Enable LZ4 compression
LZ4 is an advanced compression algorithm with a higher compression ratio and execution speed. Enabling it reduces storage usage.
Session-level — applies to the current session only:
-- Enable LZ4 compression.
Set toast_compression_use_lz4 = true;
-- Disable LZ4 compression and use the default PostgreSQL compression algorithm.
Set toast_compression_use_lz4 = false;Database-level — applies to all future sessions in the database:
-- Enable LZ4 compression for the database.
Alter database dbname Set toast_compression_use_lz4 = true;
-- Disable LZ4 compression and use the default compression algorithm for the database.
Alter database dbname Set toast_compression_use_lz4 = false;Replace dbname with your database name.