Indexing of Time Types

GiST and SP-GiST indexes can be created for table columns of the timestampset, period, and periodset types. An example of creation of a GiST index in a column During of type period in a table Reservation is as follows:

CREATE TABLE Reservation (ReservationID integer PRIMARY KEY, RoomID integer,
  During period);
CREATE INDEX Reservation_During_Idx ON Reservation USING GIST(During);
				

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 time type. For these index types, basically the only useful operation is equality. There is a B-tree sort ordering defined for values of time 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.