

PostgreSQL 8.1.4 Documentation  

Prev  Fast Backward  Chapter 32. Extending SQL  Fast Forward  Next 
The procedures described thus far let you define new types, new functions, and new operators. However, we cannot yet define an index on a column of a new data type. To do this, we must define an operator class for the new data type. Later in this section, we will illustrate this concept in an example: a new operator class for the Btree index method that stores and sorts complex numbers in ascending absolute value order.
Note: Prior to PostgreSQL release 7.3, it was necessary to make manual additions to the system catalogs
pg_amop
,pg_amproc
, andpg_opclass
in order to create a userdefined operator class. That approach is now deprecated in favor of using CREATE OPERATOR CLASS, which is a much simpler and less errorprone way of creating the necessary catalog entries.
The pg_am
table contains one row for every
index method (internally known as access method). Support for
regular access to tables is built into
PostgreSQL, but all index methods are
described in pg_am
. It is possible to add a
new index method by defining the required interface routines and
then creating a row in pg_am
— but that is
beyond the scope of this chapter (see Chapter 48).
The routines for an index method do not directly know anything about the data types that the index method will operate on. Instead, an operator class identifies the set of operations that the index method needs to use to work with a particular data type. Operator classes are so called because one thing they specify is the set of WHEREclause operators that can be used with an index (i.e., can be converted into an indexscan qualification). An operator class may also specify some support procedures that are needed by the internal operations of the index method, but do not directly correspond to any WHEREclause operator that can be used with the index.
It is possible to define multiple operator classes for the same data type and index method. By doing this, multiple sets of indexing semantics can be defined for a single data type. For example, a Btree index requires a sort ordering to be defined for each data type it works on. It might be useful for a complexnumber data type to have one Btree 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 method.
The same operator class name can be used for several different index methods (for example, both Btree and hash index methods have operator classes named int4_ops), but each such class is an independent entity and must be defined separately.
The operators associated with an operator class are identified by "strategy numbers", which serve to identify the semantics of each operator within the context of its operator class. For example, Btrees impose a strict ordering on keys, lesser to greater, and so operators like "less than" and "greater than or equal to" are interesting with respect to a Btree. Because PostgreSQL allows the user to define operators, PostgreSQL cannot look at the name of an operator (e.g., < or >=) and tell what kind of comparison it is. Instead, the index method defines a set of "strategies", which can be thought of as generalized operators. Each operator class specifies which actual operator corresponds to each strategy for a particular data type and interpretation of the index semantics.
The Btree index method defines five strategies, shown in Table 322.
Table 322. Btree Strategies
Operation  Strategy Number 

less than  1 
less than or equal  2 
equal  3 
greater than or equal  4 
greater than  5 
Hash indexes express only bitwise equality, and so they use only one strategy, shown in Table 323.
Rtree indexes express relationships in twodimensional space. They use twelve strategies, shown in Table 324. Four of these are true twodimensional tests (overlaps, same, contains, contained by); four of them consider only the X direction; and the other four provide the same tests in the Y direction.
Table 324. Rtree Strategies
Operation  Strategy Number 

strictly left of  1 
does not extend to right of  2 
overlaps  3 
does not extend to left of  4 
strictly right of  5 
same  6 
contains  7 
contained by  8 
does not extend above  9 
strictly below  10 
strictly above  11 
does not extend below  12 
GiST indexes are even more flexible: they do not have a fixed set of strategies at all. Instead, the "consistency" support routine of each particular GiST operator class interprets the strategy numbers however it likes.
Note that all strategy operators return Boolean values. In practice, all operators defined as index method strategies must return type boolean, since they must appear at the top level of a WHERE clause to be used with an index.
By the way, the amorderstrategy column
in pg_am
tells whether
the index method supports ordered scans. Zero means it doesn't; if it
does, amorderstrategy is the strategy
number that corresponds to the ordering operator. For example, Btree
has amorderstrategy = 1, which is its
"less than" strategy number.
Strategies aren't usually enough information for the system to figure out how to use an index. In practice, the index methods require additional support routines in order to work. For example, the Btree index method must be able to compare two keys and determine whether one is greater than, equal to, or less than the other. Similarly, the Rtree index method must be able to compute intersections, unions, and sizes of rectangles. These operations do not correspond to operators used in qualifications in SQL commands; they are administrative routines used by the index methods, internally.
Just as with strategies, the operator class identifies which specific functions should play each of these roles for a given data type and semantic interpretation. The index method defines the set of functions it needs, and the operator class identifies the correct functions to use by assigning them to the "support function numbers".
Btrees require a single support function, shown in Table 325.
Table 325. Btree Support Functions
Function  Support Number 

Compare two keys and return an integer less than zero, zero, or greater than zero, indicating whether the first key is less than, equal to, or greater than the second.  1 
Hash indexes likewise require one support function, shown in Table 326.
Rtree indexes require three support functions, shown in Table 327.
GiST indexes require seven support functions, shown in Table 328.
Table 328. GiST Support Functions
Function  Support Number 

consistent  1 
union  2 
compress  3 
decompress  4 
penalty  5 
picksplit  6 
equal  7 
Unlike strategy operators, support functions return whichever data type the particular index method expects; for example in the case of the comparison function for Btrees, a signed integer.
Now that we have seen the ideas, here is the promised example of creating a new operator class. (You can find a working copy of this example in src/tutorial/complex.c and src/tutorial/complex.sql in the source distribution.) The operator class encapsulates operators that sort complex numbers in absolute value order, so we choose the name complex_abs_ops. First, we need a set of operators. The procedure for defining operators was discussed in Section 32.12. For an operator class on Btrees, the operators we require are:
The least errorprone way to define a related set of comparison operators is to write the Btree comparison support function first, and then write the other functions as oneline wrappers around the support function. This reduces the odds of getting inconsistent results for corner cases. Following this approach, we first write
#define Mag(c) ((c)>x*(c)>x + (c)>y*(c)>y) static int complex_abs_cmp_internal(Complex *a, Complex *b) { double amag = Mag(a), bmag = Mag(b); if (amag < bmag) return 1; if (amag > bmag) return 1; return 0; }
Now the lessthan function looks like
PG_FUNCTION_INFO_V1(complex_abs_lt); Datum complex_abs_lt(PG_FUNCTION_ARGS) { Complex *a = (Complex *) PG_GETARG_POINTER(0); Complex *b = (Complex *) PG_GETARG_POINTER(1); PG_RETURN_BOOL(complex_abs_cmp_internal(a, b) < 0); }
The other four functions differ only in how they compare the internal function's result to zero.
Next we declare the functions and the operators based on the functions to SQL:
CREATE FUNCTION complex_abs_lt(complex, complex) RETURNS bool AS 'filename', 'complex_abs_lt' LANGUAGE C IMMUTABLE STRICT; CREATE OPERATOR < ( leftarg = complex, rightarg = complex, procedure = complex_abs_lt, commutator = > , negator = >= , restrict = scalarltsel, join = scalarltjoinsel );
It is important to specify the correct commutator and negator operators, as well as suitable restriction and join selectivity functions, otherwise the optimizer will be unable to make effective use of the index. Note that the lessthan, equal, and greaterthan cases should use different selectivity functions.
Other things worth noting are happening here:
There can only be one operator named, say, =
and taking type complex for both operands. In this
case we don't have any other operator = for
complex, but if we were building a practical data
type we'd probably want = to be the ordinary
equality operation for complex numbers (and not the equality of
the absolute values). In that case, we'd need to use some other
operator name for complex_abs_eq
.
Although PostgreSQL can cope with functions having the same SQL name as long as they have different argument data types, C can only cope with one global function having a given name. So we shouldn't name the C function something simple like abs_eq. Usually it's a good practice to include the data type name in the C function name, so as not to conflict with functions for other data types.
We could have made the SQL name of the function abs_eq, relying on PostgreSQL to distinguish it by argument data types from any other SQL function of the same name. To keep the example simple, we make the function have the same names at the C level and SQL level.
The next step is the registration of the support routine required by Btrees. The example C code that implements this is in the same file that contains the operator functions. This is how we declare the function:
CREATE FUNCTION complex_abs_cmp(complex, complex) RETURNS integer AS 'filename' LANGUAGE C IMMUTABLE STRICT;
Now that we have the required operators and support routine, we can finally create the operator class:
CREATE OPERATOR CLASS complex_abs_ops DEFAULT FOR TYPE complex USING btree AS OPERATOR 1 < , OPERATOR 2 <= , OPERATOR 3 = , OPERATOR 4 >= , OPERATOR 5 > , FUNCTION 1 complex_abs_cmp(complex, complex);
And we're done! It should now be possible to create and use Btree indexes on complex columns.
We could have written the operator entries more verbosely, as in
OPERATOR 1 < (complex, complex) ,
but there is no need to do so when the operators take the same data type we are defining the operator class for.
The above example assumes that you want to make this new operator class the default Btree operator class for the complex data type. If you don't, just leave out the word DEFAULT.
So far we have implicitly assumed that an operator class deals with only one data type. While there certainly can be only one data type in a particular index column, it is often useful to index operations that compare an indexed column to a value of a different data type. This is presently supported by the Btree and GiST index methods.
Btrees require the lefthand operand of each operator to be the indexed data type, but the righthand operand can be of a different type. There must be a support function having a matching signature. For example, the builtin operator class for type bigint (int8) allows crosstype comparisons to int4 and int2. It could be duplicated by this definition:
CREATE OPERATOR CLASS int8_ops DEFAULT FOR TYPE int8 USING btree AS  standard int8 comparisons OPERATOR 1 < , OPERATOR 2 <= , OPERATOR 3 = , OPERATOR 4 >= , OPERATOR 5 > , FUNCTION 1 btint8cmp(int8, int8) ,  crosstype comparisons to int2 (smallint) OPERATOR 1 < (int8, int2) , OPERATOR 2 <= (int8, int2) , OPERATOR 3 = (int8, int2) , OPERATOR 4 >= (int8, int2) , OPERATOR 5 > (int8, int2) , FUNCTION 1 btint82cmp(int8, int2) ,  crosstype comparisons to int4 (integer) OPERATOR 1 < (int8, int4) , OPERATOR 2 <= (int8, int4) , OPERATOR 3 = (int8, int4) , OPERATOR 4 >= (int8, int4) , OPERATOR 5 > (int8, int4) , FUNCTION 1 btint84cmp(int8, int4) ;
Notice that this definition "overloads" the operator strategy and support function numbers. This is allowed (for Btree operator classes only) so long as each instance of a particular number has a different righthand data type. The instances that are not crosstype are the default or primary operators of the operator class.
GiST indexes do not allow overloading of strategy or support function numbers, but it is still possible to get the effect of supporting multiple righthand data types, by assigning a distinct strategy number to each operator that needs to be supported. The consistent support function must determine what it needs to do based on the strategy number, and must be prepared to accept comparison values of the appropriate data types.
PostgreSQL uses operator classes to infer the properties of operators in more ways than just whether they can be used with indexes. Therefore, you might want to create operator classes even if you have no intention of indexing any columns of your data type.
In particular, there are SQL features such as ORDER BY and DISTINCT that require comparison and sorting of values. To implement these features on a userdefined data type, PostgreSQL looks for the default Btree operator class for the data type. The "equals" member of this operator class defines the system's notion of equality of values for GROUP BY and DISTINCT, and the sort ordering imposed by the operator class defines the default ORDER BY ordering.
Comparison of arrays of userdefined types also relies on the semantics defined by the default Btree operator class.
If there is no default Btree operator class for a data type, the system will look for a default hash operator class. But since that kind of operator class only provides equality, in practice it is only enough to support array equality.
When there is no default operator class for a data type, you will get errors like "could not identify an ordering operator" if you try to use these SQL features with the data type.
Note: In PostgreSQL versions before 7.4, sorting and grouping operations would implicitly use operators named =, <, and >. The new behavior of relying on default operator classes avoids having to make any assumption about the behavior of operators with particular names.
There are two special features of operator classes that we have not discussed yet, mainly because they are not useful with the most commonly used index methods.
Normally, declaring an operator as a member of an operator class means that the index method can retrieve exactly the set of rows that satisfy a WHERE condition using the operator. For example,
SELECT * FROM table WHERE integer_column < 4;
can be satisfied exactly by a Btree index on the integer column. But there are cases where an index is useful as an inexact guide to the matching rows. For example, if an Rtree index stores only bounding boxes for objects, then it cannot exactly satisfy a WHERE condition that tests overlap between nonrectangular objects such as polygons. Yet we could use the index to find objects whose bounding box overlaps the bounding box of the target object, and then do the exact overlap test only on the objects found by the index. If this scenario applies, the index is said to be "lossy" for the operator, and we add RECHECK to the OPERATOR clause in the CREATE OPERATOR CLASS command. RECHECK is valid if the index is guaranteed to return all the required rows, plus perhaps some additional rows, which can be eliminated by performing the original operator invocation.
Consider again the situation where we are storing in the index only the bounding box of a complex object such as a polygon. In this case there's not much value in storing the whole polygon in the index entry — we may as well store just a simpler object of type box. This situation is expressed by the STORAGE option in CREATE OPERATOR CLASS: we'd write something like
CREATE OPERATOR CLASS polygon_ops DEFAULT FOR TYPE polygon USING gist AS ... STORAGE box;
At present, only the GiST index method supports a STORAGE type that's different from the column data type. The GiST compress and decompress support routines must deal with datatype conversion when STORAGE is used.