|The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 478 pages
RRP £14.95 ($19.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
5.14.6 System Dependencies on Operator Classes
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 user-defined data type,
PostgreSQL looks for the default B-tree 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
Comparison of arrays of user-defined types also relies on the semantics defined by the default B-tree operator class.
If there is no default B-tree 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
>. The new behavior of relying on default operator classes avoids having to make any assumption about the behavior of operators with particular names.
Another important point is that an operator that appears in a hash operator family is a candidate for hash joins, hash aggregation, and related optimizations. The hash operator family is essential here since it identifies the hash function(s) to use.
|ISBN 9781906966065||The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide||See the print edition|