Create GiST indexing to accelerate queries on trajectory data - ApsaraDB RDS

GiST (Generalized Search Tree) indexes accelerate spatial queries on trajectory data. You can create a GiST index on a column that stores trajectory data.

Syntax

CREATE INDEX [index_name] ON table_name USING GIST(traj_col [operator_family]);

Parameter	Required	Description
`index_name`	No	Name of the index.
`table_name`	Yes	Name of the table containing the trajectory column.
`traj_col`	Yes	Name of the trajectory column to index.
`operator_family`	No	Operator family that determines which axes and query types the index supports. Default: `trajgist_ops_multi`. To customize this, use the ganos.trajectory.index_split_config parameter.

Note

The GiST index accelerates queries that use the following functions: ST_ndIntersect, ST_ndDWithin, ST_ndContains, and ST_ndWithin.

Choose an operator family

Each operator family indexes a specific combination of axes (x, y, z, t) and supports the corresponding query types. Choose the operator family that matches the axes your queries filter on.

Operator family	Indexed axes	Supported query types
`trajgist_ops_z`	z	z-axis queries
`trajgist_ops_t`	t	t-axis queries
`trajgist_ops_2d`	x, y	x/y-axis queries
`trajgist_ops_2dt`	x, y, t	x/y-only, t-only, and x/y/t queries
`trajgist_ops_3d`	x, y, z	x/y-only, z-only, and x/y/z queries
`trajgist_ops_3dt`	x, y, z, t	All query types supported by the five families above
`trajgist_ops_multi`	Multiple bounding boxes	Accelerates queries; requires more time and storage to create than the other families

Choosing an operator family:

Query only the t axis: trajgist_ops_t
Query x/y and t together: trajgist_ops_2dt (one index instead of two separate indexes)
Query all axes: trajgist_ops_3dt
Need maximum query coverage and can accommodate higher storage and index creation time: trajgist_ops_multi

Examples

Create an index on the t axis:

CREATE INDEX ON table_name USING GIST(traj_col trajgist_ops_t);

Create an index on the x and y axes:

CREATE INDEX ON table_name USING GIST(traj_col trajgist_ops_2d);

Create an index on the x, y, and t axes:

CREATE INDEX ON table_name USING GIST(traj_col trajgist_ops_2dt);

What's next

ganos.trajectory.index_split_config — Configure the rule used to split a trajectory when GiST indexing is used.