PostgreSQL Ordering Operators and GiST Indexing

the operator is one that measures distance. For example, a query like <programlisting><![CDATA[ SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10; ]]> </programlisting> finds the ten places closest to a given target point. A GiST index on the location column can do this efficiently because <literal><-></literal> is an ordering operator. </para> <para> While search operators have to return Boolean results, ordering operators usually return some other type, such as float or numeric for distances. This type is normally not the same as the data type being indexed. To avoid hard-wiring assumptions about the behavior of different data types, the definition of an ordering operator is required to name a B-tree operator family that specifies the sort ordering of the result data type. As was stated in the previous section, B-tree operator families define <productname>PostgreSQL</productname>'s notion of ordering, so this is a natural representation. Since the point <literal><-></literal> operator returns <type>float8</type>, it could be specified in an operator class creation command like this: <programlisting><![CDATA[ OPERATOR 15 <-> (point, point) FOR ORDER BY float_ops ]]> </programlisting> where <literal>float_ops</literal> is the built-in operator family that includes operations on <type>float8</type>. This declaration states that the index is able to return rows in order of increasing values of the <literal><-></literal> operator. </para> </sect2> <sect2 id="xindex-opclass-features"> <title>Special Features of Operator Classes</title> <para> 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. </para> <para> Normally, declaring an operator as a member of an operator class (or family) means that the index method can retrieve exactly the set of rows that satisfy a <literal>WHERE</literal> condition using the operator. For example: <programlisting> SELECT * FROM table WHERE integer_column < 4; </programlisting> can be satisfied exactly by a B-tree 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 a GiST index stores only bounding boxes for geometric objects, then it cannot exactly satisfy a <literal>WHERE</literal> 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 section explains ordering operators in PostgreSQL, particularly in the context of GiST indexes. It demonstrates how ordering operators, unlike search operators that return Boolean results, can return other data types like float or numeric for measuring distances. The text uses an example query to find the ten closest places to a given point using the '<->' operator with a GiST index. It also details how ordering operators are defined in operator classes, specifying a B-tree operator family to determine the sort order of the result type. The passage concludes by introducing special features of operator classes, including the use of indexes as inexact guides for matching rows, which is particularly useful for geometric queries in GiST indexes.