diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/catalogs.sgml | 69 | ||||
| -rw-r--r-- | doc/src/sgml/ref/alter_opfamily.sgml | 18 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_opclass.sgml | 18 | ||||
| -rw-r--r-- | doc/src/sgml/xindex.sgml | 65 |
4 files changed, 162 insertions, 8 deletions
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 9a8729b8b31..54a6dcc102c 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -423,7 +423,16 @@ <entry><structfield>amcanorder</structfield></entry> <entry><type>bool</type></entry> <entry></entry> - <entry>Does the access method support ordered scans?</entry> + <entry>Does the access method support ordered scans sorted by the + indexed column's value?</entry> + </row> + + <row> + <entry><structfield>amcanorderbyop</structfield></entry> + <entry><type>bool</type></entry> + <entry></entry> + <entry>Does the access method support ordered scans sorted by the result + of an operator on the indexed column?</entry> </row> <row> @@ -598,9 +607,13 @@ <para> The catalog <structname>pg_amop</structname> stores information about operators associated with access method operator families. There is one - row for each operator that is a member of an operator family. An operator + row for each operator that is a member of an operator family. A family + member can be either a <firstterm>search</> operator or an + <firstterm>ordering</> operator. An operator can appear in more than one family, but cannot appear in more than one - position within a family. + search position nor more than one ordering position within a family. + (It is allowed, though unlikely, for an operator to be used for both + search and ordering purposes.) </para> <table> @@ -646,6 +659,14 @@ </row> <row> + <entry><structfield>amoppurpose</structfield></entry> + <entry><type>char</type></entry> + <entry></entry> + <entry>Operator purpose, either <literal>s</> for search or + <literal>o</> for ordering</entry> + </row> + + <row> <entry><structfield>amopopr</structfield></entry> <entry><type>oid</type></entry> <entry><literal><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.oid</literal></entry> @@ -659,11 +680,53 @@ <entry>Index access method operator family is for</entry> </row> + <row> + <entry><structfield>amopsortfamily</structfield></entry> + <entry><type>oid</type></entry> + <entry><literal><link linkend="catalog-pg-opfamily"><structname>pg_opfamily</structname></link>.oid</literal></entry> + <entry>The btree operator family this entry sorts according to, if an + ordering operator; zero if a search operator</entry> + </row> + </tbody> </tgroup> </table> <para> + A <quote>search</> operator entry indicates that an index of this operator + family can be searched to find all rows satisfying + <literal>WHERE</> + <replaceable>indexed_column</> + <replaceable>operator</> + <replaceable>constant</>. + Obviously, such an operator must return boolean, and its left-hand input + type must match the index's column data type. + </para> + + <para> + An <quote>ordering</> operator entry indicates that an index of this + operator family can be scanned to return rows in the order represented by + <literal>ORDER BY</> + <replaceable>indexed_column</> + <replaceable>operator</> + <replaceable>constant</>. + Such an operator could return any sortable data type, though again + its left-hand input type must match the index's column data type. + The exact semantics of the <literal>ORDER BY</> are specified by the + <structfield>amopsortfamily</structfield> column, which must reference + a btree operator family for the operator's result type. + </para> + + <note> + <para> + At present, it's assumed that the sort order for an ordering operator + is the default for the referenced opfamily, i.e., <literal>ASC NULLS + LAST</>. This might someday be relaxed by adding additional columns + to specify sort options explicitly. + </para> + </note> + + <para> An entry's <structfield>amopmethod</> must match the <structname>opfmethod</> of its containing operator family (including <structfield>amopmethod</> here is an intentional denormalization of the diff --git a/doc/src/sgml/ref/alter_opfamily.sgml b/doc/src/sgml/ref/alter_opfamily.sgml index 1018af8412d..3c8ca21f61e 100644 --- a/doc/src/sgml/ref/alter_opfamily.sgml +++ b/doc/src/sgml/ref/alter_opfamily.sgml @@ -22,7 +22,7 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> ALTER OPERATOR FAMILY <replaceable>name</replaceable> USING <replaceable class="parameter">index_method</replaceable> ADD - { OPERATOR <replaceable class="parameter">strategy_number</replaceable> <replaceable class="parameter">operator_name</replaceable> ( <replaceable class="parameter">op_type</replaceable>, <replaceable class="parameter">op_type</replaceable> ) + { OPERATOR <replaceable class="parameter">strategy_number</replaceable> <replaceable class="parameter">operator_name</replaceable> ( <replaceable class="parameter">op_type</replaceable>, <replaceable class="parameter">op_type</replaceable> ) [ FOR SEARCH | FOR ORDER BY <replaceable class="parameter">sort_family_name</replaceable> ] | FUNCTION <replaceable class="parameter">support_number</replaceable> [ ( <replaceable class="parameter">op_type</replaceable> [ , <replaceable class="parameter">op_type</replaceable> ] ) ] <replaceable class="parameter">function_name</replaceable> ( <replaceable class="parameter">argument_type</replaceable> [, ...] ) } [, ... ] ALTER OPERATOR FAMILY <replaceable>name</replaceable> USING <replaceable class="parameter">index_method</replaceable> DROP @@ -155,6 +155,22 @@ ALTER OPERATOR FAMILY <replaceable>name</replaceable> USING <replaceable class=" </varlistentry> <varlistentry> + <term><replaceable class="parameter">sort_family_name</replaceable></term> + <listitem> + <para> + The name (optionally schema-qualified) of an existing btree operator + family that describes the sort ordering associated with an ordering + operator. + </para> + + <para> + If neither <literal>FOR SEARCH</> nor <literal>FOR ORDER BY</> is + specified, <literal>FOR SEARCH</> is the default. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><replaceable class="parameter">support_number</replaceable></term> <listitem> <para> diff --git a/doc/src/sgml/ref/create_opclass.sgml b/doc/src/sgml/ref/create_opclass.sgml index f12f13dc08e..eff585405cd 100644 --- a/doc/src/sgml/ref/create_opclass.sgml +++ b/doc/src/sgml/ref/create_opclass.sgml @@ -23,7 +23,7 @@ PostgreSQL documentation <synopsis> CREATE OPERATOR CLASS <replaceable class="parameter">name</replaceable> [ DEFAULT ] FOR TYPE <replaceable class="parameter">data_type</replaceable> USING <replaceable class="parameter">index_method</replaceable> [ FAMILY <replaceable class="parameter">family_name</replaceable> ] AS - { OPERATOR <replaceable class="parameter">strategy_number</replaceable> <replaceable class="parameter">operator_name</replaceable> [ ( <replaceable class="parameter">op_type</replaceable>, <replaceable class="parameter">op_type</replaceable> ) ] + { OPERATOR <replaceable class="parameter">strategy_number</replaceable> <replaceable class="parameter">operator_name</replaceable> [ ( <replaceable class="parameter">op_type</replaceable>, <replaceable class="parameter">op_type</replaceable> ) ] [ FOR SEARCH | FOR ORDER BY <replaceable class="parameter">sort_family_name</replaceable> ] | FUNCTION <replaceable class="parameter">support_number</replaceable> [ ( <replaceable class="parameter">op_type</replaceable> [ , <replaceable class="parameter">op_type</replaceable> ] ) ] <replaceable class="parameter">function_name</replaceable> ( <replaceable class="parameter">argument_type</replaceable> [, ...] ) | STORAGE <replaceable class="parameter">storage_type</replaceable> } [, ... ] @@ -181,6 +181,22 @@ CREATE OPERATOR CLASS <replaceable class="parameter">name</replaceable> [ DEFAUL </varlistentry> <varlistentry> + <term><replaceable class="parameter">sort_family_name</replaceable></term> + <listitem> + <para> + The name (optionally schema-qualified) of an existing btree operator + family that describes the sort ordering associated with an ordering + operator. + </para> + + <para> + If neither <literal>FOR SEARCH</> nor <literal>FOR ORDER BY</> is + specified, <literal>FOR SEARCH</> is the default. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><replaceable class="parameter">support_number</replaceable></term> <listitem> <para> diff --git a/doc/src/sgml/xindex.sgml b/doc/src/sgml/xindex.sgml index 8f9fd21f386..6d059bda706 100644 --- a/doc/src/sgml/xindex.sgml +++ b/doc/src/sgml/xindex.sgml @@ -276,10 +276,13 @@ </table> <para> - Notice that all strategy operators return Boolean values. In - practice, all operators defined as index method strategies must + Notice that all the operators listed above return Boolean values. In + practice, all operators defined as index method search operators must return type <type>boolean</type>, since they must appear at the top level of a <literal>WHERE</> clause to be used with an index. + (Some index access methods also support <firstterm>ordering operators</>, + which typically don't return Boolean values; that feature is discussed + in <xref linkend="xindex-ordering-ops">.) </para> </sect2> @@ -464,7 +467,7 @@ </table> <para> - Unlike strategy operators, support functions return whichever data + Unlike search 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 @@ -921,6 +924,62 @@ ALTER OPERATOR FAMILY integer_ops USING btree ADD </para> </sect2> + <sect2 id="xindex-ordering-ops"> + <title>Ordering Operators</title> + + <para> + Some index access methods (currently, only GiST) support the concept of + <firstterm>ordering operators</>. What we have been discussing so far + are <firstterm>search operators</>. A search operator is one for which + the index can be searched to find all rows satisfying + <literal>WHERE</> + <replaceable>indexed_column</> + <replaceable>operator</> + <replaceable>constant</>. + Note that nothing is promised about the order in which the matching rows + will be returned. In contrast, an ordering operator does not restrict the + set of rows that can be returned, but instead determines their order. + An ordering operator is one for which the index can be scanned to return + rows in the order represented by + <literal>ORDER BY</> + <replaceable>indexed_column</> + <replaceable>operator</> + <replaceable>constant</>. + The reason for defining ordering operators that way is that it supports + nearest-neighbor searches, if 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><-></> 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><-></> + operator returns <type>float8</>, 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</> is the built-in operator family that includes + operations on <type>float8</>. This declaration states that the index + is able to return rows in order of increasing values of the + <literal><-></> operator. + </para> + </sect2> + <sect2 id="xindex-opclass-features"> <title>Special Features of Operator Classes</title> |