This text was automatically converted from troff me macros to HTML. Information may have been lost, added, or changed in the process. Lars Aronsson and Lysator do not guarantee the correctness of this HTML document.
define index *- construct a secondary index
define [archive] index index-name on classname using am-name ( attname-1 type_class-1 { , attname-i type_class-i } )
This command constructs an index called index-name. If the archive keyword is absent, the classname class is indexed. When archive is present, an index is created on the archive class associated with the classname class.
Am-name is the name of the access method which is used for the index.
The key fields for the index are specified as a collection of attribute names and associated "operator classes" . An operator class is used to specify the operators to be used for a particular index. For example, a btree index on four-byte integers would use the int4_ops class; this operator class includes comparison functions for four-byte integers.
POSTGRES Version 4.0 provides btree and rtree access methods for secondary indices. The operator classes defined on btrees are
The int24_ops operator class is useful for constructing indices on int2 data, and doing comparisons against int4 data in query qualifications. Similarly, int42_ops support indices on int4 data that is to be compared against int2 data in queries.int2_ops char_ops int4_ops char16_ops int24_ops oid_ops int42_ops text_ops floag4_ops abstime_ops float8_ops
The POSTGRES query optimizer will consider using b-tree indices in a scan whenever an indexed attribute is involved in a comparison using one of
< <= = >= >
The operator classes defined on rtrees are
Both of these support indices on the box datatype in POSTGRES. The difference between them is that bigbox_ops scales box coordinates down, to avoid floating point exceptions from doing multiplication, addition, and subtraction on very large floating-point coordinates. If the field on which your rectangles lie is about 20,000 units square or larger, you should use bigbox_ops . The poly_ops operator class supports rtree indices on polygon data.box_ops poly_ops bigbox_ops
The POSTGRES query optimizer will consider using an r-tree index whenever an indexed attribute is involved in a comparison using one of
<< &< &> >> @ ~= &&
Create a btree index on the emp class using the age attribute.
define index empindex on emp using btree (age int4_ops)
Create a btree index on employee name.
define index empname on emp using btree (name char16_ops)
Create an rtree index on the bounding rectangle of cities.
define index cityrect on city using rtree (boundbox box_ops)
Archive indices are not supported in Version 4.0.
There should be an access method designers guide.
Indices may only be defined on a single key.