GiST and SP-GiST indexes can be created for table columns of temporal types. The GiST index implements an R-tree for temporal alphanumeric types and for temporal point types. The SP-GiST index implements a Quad-tree for temporal alphanumeric types and an Oct-tree for temporal point types. Examples of index creation are as follows:

CREATE INDEX Department_NoEmps_Gist_Idx ON Department USING Gist(NoEmps);
CREATE INDEX Trips_Trip_SPGist_Idx ON Trips USING SPGist(Trip);

The GiST and SP-GiST indexes store the bounding box for the temporal types. As explained in Chapter 3, these are

A GiST or SP-GiST index can accelerate queries involving the following operators (see Section 5.7 for more information):

In addition, a GiST index can accelerate nearest neighbor queries involving the |=| operator.

For example, given the index defined above on the Department table and a query that involves a condition with the && (overlaps) operator, if the right argument is a temporal float then both the value and the time dimensions are considered for filtering the tuples of the relation, while if the right argument is a float value, a float range, or a time type, then either the value or the time dimension will be used for filtering the tuples of the relation. Furthermore, a bounding box can be constructed from a value/range and/or a timestamp/period, which can be used for filtering the tuples of the relation. Examples of queries using the index on the Department table defined above are given next.

SELECT * FROM Department WHERE NoEmps && 5;
SELECT * FROM Department WHERE NoEmps && intrange '[1, 5)';
SELECT * FROM Department WHERE NoEmps && timestamptz '2012-04-01';
SELECT * FROM Department WHERE NoEmps && period '[2012-04-01, 2012-05-01)';
SELECT * FROM Department WHERE NoEmps &&
tbox(intrange '[1, 5)', period '[2012-04-01, 2012-05-01)');
SELECT * FROM Department WHERE NoEmps &&
tfloat '{[1@2012-01-01, 1@2012-02-01), [5@2012-04-01, 5@2012-05-01)}';

Similarly, examples of queries using the index on the Trips table defined above are given next.

SELECT * FROM Trips WHERE Trip && geometry 'Polygon((0 0,0 1,1 1,1 0,0 0))';
SELECT * FROM Trips WHERE Trip && timestamptz '2001-01-01';
SELECT * FROM Trips WHERE Trip && period '[2001-01-01, 2001-01-05)';
SELECT * FROM Trips WHERE Trip &&
stbox(geometry 'Polygon((0 0,0 1,1 1,1 0,0 0))', period '[2001-01-01, 2001-01-05]');
SELECT * FROM Trips WHERE Trip &&
tgeompoint '{[Point(0 0)@2001-01-01, Point(1 1)@2001-01-02, Point(1 1)@2001-01-05)}';

Finally, B-tree indexes can be created for table columns of all temporal types. For this index type, the only useful operation is equality. There is a B-tree sort ordering defined for values of temporal types, with corresponding <, <=, >, >= and operators, but the ordering is rather arbitrary and not usually useful in the real world. B-tree support for temporal types is primarily meant to allow sorting internally in queries, rather than creation of actual indexes.

In order to speed up several of the functions in Chapter 5, we can add in the WHERE clause of queries a bounding box comparison that make uses of the available indexes. For example, this would be typically the case for the functions that project the temporal types to the value/spatial and/or time dimensions. This will filter out the tuples with an index as shown in the following query.

SELECT atPeriod(T.Trip, period(2001-01-01, 2001-01-02))
FROM Trips T
-- Bouding box index filtering
WHERE T.Trip && period(2001-01-01, 2001-01-02)

In the case of temporal points, all spatial relationships with the possible semantics (see Section 5.12.6) automatically include a bounding box comparison that will make use of any indexes that are available on the temporal points. For this reason, the first version of the relationships is typically used for filtering the tuples with the help of an index when computing the temporal relationships as shown in the following query.

SELECT tintersects(T.Trip, R.Geom)
FROM Trips T, Regions R
-- Bouding box index filtering
WHERE intersects(T.Trip, R.Geom);