GiST and SP-GiST indexes can be created for table columns of the tbox
and stbox
types. The GiST index implements an R-tree and the SP-GiST index implements an n-dimensional quad-tree. An example of creation of a GiST index in a column Box
of type stbox
in a table Trips
is as follows:
CREATE TABLE Trips(TripID integer PRIMARY KEY, Trip tgeompoint, Box stbox); CREATE INDEX Trips_Box_Idx ON Trips USING GIST(bbox);
A GiST or SP-GiST index can accelerate queries involving the following operators: &&
, <@
, @>
, ~=
, -|-
, <<
, >>
, &<
, &>
, <<|
, |>>
, &<|
, |&>
, <</
, />>
, &</
, /&>
, <<#
, #>>
, &<#
, and #&>
.
In addition, B-tree indexes can be created for table columns of a bounding box type. For these index types, basically the only useful operation is equality. There is a B-tree sort ordering defined for values of bounding box types, with corresponding <
and >
operators, but the ordering is rather arbitrary and not usually useful in the real world. The B-tree support is primarily meant to allow sorting internally in queries, rather than creation of actual indexes.