| PostgreSQL 8.4.9 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 34. 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 B-tree index method that stores and sorts complex numbers in ascending absolute value order.
Operator classes can be grouped into operator families to show the relationships between semantically compatible classes. When only a single data type is involved, an operator class is sufficient, so we'll focus on that case first and then return to operator families.
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 50).
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 WHERE-clause operators that can be used with an index (i.e., can be converted into an index-scan qualification). An operator class can also specify some support procedures that are needed by the internal operations of the index 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 data type and index 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 method.
The same operator class name can be used for several different index methods (for example, both B-tree 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, B-trees 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 B-tree. 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 B-tree index method defines five strategies, shown in Table 34-2.
Table 34-2. B-tree Strategies
| Operation | Strategy Number |
|---|---|
| less than | 1 |
| less than or equal | 2 |
| equal | 3 |
| greater than or equal | 4 |
| greater than | 5 |
Hash indexes support only equality comparisons, and so they use only one strategy, shown in Table 34-3.
GiST indexes are 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. As an example, several of the built-in GiST index operator classes index two-dimensional geometric objects, providing the "R-tree" strategies shown in Table 34-4. Four of these are true two-dimensional 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 34-4. GiST Two-Dimensional "R-tree" 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 |
GIN indexes are similar to GiST indexes in flexibility: they don't have a fixed set of strategies. Instead the support routines of each operator class interpret the strategy numbers according to the operator class's definition. As an example, the strategy numbers used by the built-in operator classes for arrays are shown in Table 34-5.
Notice 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.
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 B-tree 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 hash index method must be able to compute hash codes for key values. 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" specified by the index method.
B-trees require a single support function, shown in Table 34-6.
Table 34-6. B-tree 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 34-7.
GiST indexes require seven support functions, shown in Table 34-8.
Table 34-8. GiST Support Functions
| Function | Support Number |
|---|---|
| consistent - determine whether key satisfies the query qualifier | 1 |
| union - compute union of a set of keys | 2 |
| compress - compute a compressed representation of a key or value to be indexed | 3 |
| decompress - compute a decompressed representation of a compressed key | 4 |
| penalty - compute penalty for inserting new key into subtree with given subtree's key | 5 |
| picksplit - determine which entries of a page are to be moved to the new page and compute the union keys for resulting pages | 6 |
| equal - compare two keys and return true if they are equal | 7 |
GIN indexes require four support functions, shown in Table 34-9.
Table 34-9. GIN Support Functions
| Function | Support Number |
|---|---|
| compare - 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 |
| extractValue - extract keys from a value to be indexed | 2 |
| extractQuery - extract keys from a query condition | 3 |
| consistent - determine whether value matches query condition | 4 |
| comparePartial - (optional method) compare partial key from query and key from index, and return an integer less than zero, zero, or greater than zero, indicating whether GIN should ignore this index entry, treat the entry as a match, or stop the index scan | 5 |
Unlike strategy operators, support functions return whichever data type the particular index method expects; for example in the case of the comparison function for B-trees, a signed integer. The number and types of the arguments to each support function are likewise dependent on the index method. For B-tree and hash the support functions take the same input data types as do the operators included in the operator class, but this is not the case for most GIN and GiST support functions.
GIN indexes are similar to GiST indexes in flexibility: they don't have a fixed set of strategies. Instead the support routines of each operator class interpret the strategy numbers according to the operator class's definition. As an example, the strategy numbers used by the built-in operator classes for arrays are shown in Table 34-5.
Notice 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.
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 B-tree 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 hash index method must be able to compute hash codes for key values. 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" specified by the index method.
B-trees require a single support function, shown in Table 34-6.
Table 34-6. B-tree 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 34-7.
GiST indexes require seven support functions, shown in Table 34-8.
Table 34-8. GiST Support Functions
| Function | Support Number |
|---|---|
| consistent - determine whether key satisfies the query qualifier | 1 |
| union - compute union of a set of keys | 2 |
| compress - compute a compressed representation of a key or value to be indexed | 3 |
| decompress - compute a decompressed representation of a compressed key | 4 |
| penalty - compute penalty for inserting new key into subtree with given subtree's key | 5 |
| picksplit - determine which entries of a page are to be moved to the new page and compute the union keys for resulting pages | 6 |
| equal - compare two keys and return true if they are equal | 7 |
GIN indexes require four support functions, shown in Table 34-9.
Table 34-9. GIN Support Functions
| Function | Support Number |
|---|---|
| compare - 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 |
| extractValue - extract keys from a value to be indexed | 2 |
| extractQuery - extract keys from a query condition | 3 |
| consistent - determine whether value matches query condition | 4 |
| comparePartial - (optional method) compare partial key from query and key from index, and return an integer less than zero, zero, or greater than zero, indicating whether GIN should ignore this index entry, treat the entry as a match, or stop the index scan | 5 |
Unlike strategy operators, support functions return whichever data type the particular index method expects; for example in the case of the comparison function for B-trees, a signed integer. The number and types of the arguments to each support function are likewise dependent on the index method. For B-tree and hash the support functions take the same input data types as do the operators included in the operator class, but this is not the case for most GIN and GiST support functions.
GIN indexes are similar to GiST indexes in flexibility: they don't have a fixed set of strategies. Instead the support routines of each operator class interpret the strategy numbers according to the operator class's definition. As an example, the strategy numbers used by the built-in operator classes for arrays are shown in Table 34-5.
Notice 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.
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 B-tree 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 hash index method must be able to compute hash codes for key values. 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" specified by the index method.
B-trees require a single support function, shown in Table 34-6.
Table 34-6. B-tree 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 34-7.
GiST indexes require seven support functions, shown in Table 34-8.
Table 34-8. GiST Support Functions
| Function | Support Number |
|---|---|
| consistent - determine whether key satisfies the query qualifier | 1 |
| union - compute union of a set of keys | 2 |
| compress - compute a compressed representation of a key or value to be indexed | 3 |
| decompress - compute a decompressed representation of a compressed key | 4 |
| penalty - compute penalty for inserting new key into subtree with given subtree's key | 5 |
| picksplit - determine which entries of a page are to be moved to the new page and compute the union keys for resulting pages | 6 |
| equal - compare two keys and return true if they are equal | 7 |
GIN indexes require four support functions, shown in Table 34-9.
Table 34-9. GIN Support Functions
| Function | Support Number |
|---|---|
| compare - 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 |
| extractValue - extract keys from a value to be indexed | 2 |
| extractQuery - extract keys from a query condition | 3 |
| consistent - determine whether value matches query condition | 4 |
| comparePartial - (optional method) compare partial key from query and key from index, and return an integer less than zero, zero, or greater than zero, indicating whether GIN should ignore this index entry, treat the entry as a match, or stop the index scan | 5 |
Unlike strategy operators, support functions return whichever data type the particular index method expects; for example in the case of the comparison function for B-trees, a signed integer. The number and types of the arguments to each support function are likewise dependent on the index method. For B-tree and hash the support functions take the same input data types as do the operators included in the operator class, but this is not the case for most GIN and GiST support functions.
GIN indexes are similar to GiST indexes in flexibility: they don't have a fixed set of strategies. Instead the support routines of each operator class interpret the strategy numbers according to the operator class's definition. As an example, the strategy numbers used by the built-in operator classes for arrays are shown in Table 34-5.
Notice 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.
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 B-tree 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 hash index method must be able to compute hash codes for key values. 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" specified by the index method.
B-trees require a single support function, shown in Table 34-6.
Table 34-6. B-tree 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 34-7.
GiST indexes require seven support functions, shown in Table 34-8.