Access Methods and Operator Classes

14.2. Access Methods and Operator Classes

The pg_am table contains one row for every index access method. Support for access to regular tables is built into PostgreSQL, but all index access methods are described in pg_am. It is possible to add a new index access method by defining the required interface routines and then creating a row in pg_am --- but that is far beyond the scope of this chapter.

The routines for an index access method do not directly know anything about the data types the access method will operate on. Instead, an operator class identifies the set of operations that the access method needs to be able to use to work with a particular data type. Operator classes are so called because one thing they specify is the set of WHERE-clause operators that can be used with an index (ie, can be converted into an index scan qualification). An operator class may also specify some support procedures that are needed by the internal operations of the index access method, but do not directly correspond to any WHERE-clause operator that can be used with the index.

It is possible to define multiple operator classes for the same input data type and index access method. By doing this, multiple sets of indexing semantics can be defined for a single data type. For example, a B-tree index requires a sort ordering to be defined for each data type it works on. It might be useful for a complex-number data type to have one B-tree operator class that sorts the data by complex absolute value, another that sorts by real part, and so on. Typically one of the operator classes will be deemed most commonly useful and will be marked as the default operator class for that data type and index access method.

The same operator class name can be used for several different access methods (for example, both B-tree and hash access methods have operator classes named oid_ops), but each such class is an independent entity and must be defined separately.

© Copyright 2003-2023 The ultimate PHP Editor and PHP IDE site.