xindex.sgml

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

      </row>
      <row>
       <entry>penalty</entry>
       <entry>5</entry>
      </row>
      <row>
       <entry>picksplit</entry>
       <entry>6</entry>
      </row>
      <row>
       <entry>equal</entry>
       <entry>7</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

  <para>
   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.
  </para>
 </sect2>

 <sect2 id="xindex-example">
  <title>An Example</title>

  <para>
   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
   <filename>src/tutorial/complex.c</filename> and
   <filename>src/tutorial/complex.sql</filename> in the source
   distribution.)
   The operator class encapsulates
   operators that sort complex numbers in absolute value order, so we
   choose the name <literal>complex_abs_ops</literal>.  First, we need
   a set of operators.  The procedure for defining operators was
   discussed in <xref linkend="xoper">.  For an operator class on
   B-trees, the operators we require are:

   <itemizedlist spacing="compact">
    <listitem><simpara>absolute-value less-than (strategy 1)</></>
    <listitem><simpara>absolute-value less-than-or-equal (strategy 2)</></>
    <listitem><simpara>absolute-value equal (strategy 3)</></>
    <listitem><simpara>absolute-value greater-than-or-equal (strategy 4)</></>
    <listitem><simpara>absolute-value greater-than (strategy 5)</></>
   </itemizedlist>
  </para>

  <para>
   The least error-prone way to define a related set of comparison operators
   is to write the B-tree comparison support function first, and then write the
   other functions as one-line wrappers around the support function.  This
   reduces the odds of getting inconsistent results for corner cases.
   Following this approach, we first write

<programlisting>
#define Mag(c)  ((c)-&gt;x*(c)-&gt;x + (c)-&gt;y*(c)-&gt;y)

static int
complex_abs_cmp_internal(Complex *a, Complex *b)
{
    double      amag = Mag(a),
                bmag = Mag(b);

    if (amag &lt; bmag)
        return -1;
    if (amag &gt; bmag)
        return 1;
    return 0;
}
</programlisting>

   Now the less-than function looks like

<programlisting>
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) &lt; 0);
}
</programlisting>

   The other four functions differ only in how they compare the internal
   function's result to zero.
  </para>

  <para>
   Next we declare the functions and the operators based on the functions
   to SQL:

<programlisting>
CREATE FUNCTION complex_abs_lt(complex, complex) RETURNS bool
    AS '<replaceable>filename</replaceable>', 'complex_abs_lt'
    LANGUAGE C IMMUTABLE STRICT;

CREATE OPERATOR &lt; (
   leftarg = complex, rightarg = complex, procedure = complex_abs_lt,
   commutator = &gt; , negator = &gt;= ,
   restrict = scalarltsel, join = scalarltjoinsel
);
</programlisting>
   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 less-than, equal, and
   greater-than cases should use different selectivity functions.
  </para>

  <para>
   Other things worth noting are happening here:

  <itemizedlist>
   <listitem>
    <para>
     There can only be one operator named, say, <literal>=</literal>
     and taking type <type>complex</type> for both operands.  In this
     case we don't have any other operator <literal>=</literal> for
     <type>complex</type>, but if we were building a practical data
     type we'd probably want <literal>=</literal> 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 <function>complex_abs_eq</>.
    </para>
   </listitem>

   <listitem>
    <para>
     Although <productname>PostgreSQL</productname> 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 <filename>abs_eq</filename>.  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.
    </para>
   </listitem>

   <listitem>
    <para>
     We could have made the SQL name
     of the function <filename>abs_eq</filename>, relying on
     <productname>PostgreSQL</productname> 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.
    </para>
   </listitem>
  </itemizedlist>
  </para>

  <para>
   The next step is the registration of the support routine required
   by B-trees.  The example C code that implements this is in the same
   file that contains the operator functions.  This is how we declare
   the function:

<programlisting>
CREATE FUNCTION complex_abs_cmp(complex, complex)
    RETURNS integer
    AS '<replaceable>filename</replaceable>'
    LANGUAGE C IMMUTABLE STRICT;
</programlisting>
  </para>

  <para>
   Now that we have the required operators and support routine,
   we can finally create the operator class:

<programlisting>
CREATE OPERATOR CLASS complex_abs_ops
    DEFAULT FOR TYPE complex USING btree AS
        OPERATOR        1       &lt; ,
        OPERATOR        2       &lt;= ,
        OPERATOR        3       = ,
        OPERATOR        4       &gt;= ,
        OPERATOR        5       &gt; ,
        FUNCTION        1       complex_abs_cmp(complex, complex);
</programlisting>
  </para>

  <para>
   And we're done!  It should now be possible to create
   and use B-tree indexes on <type>complex</type> columns.
  </para>

  <para>
   We could have written the operator entries more verbosely, as in
<programlisting>
        OPERATOR        1       &lt; (complex, complex) ,
</programlisting>
   but there is no need to do so when the operators take the same data type
   we are defining the operator class for.
  </para>

  <para>
   The above example assumes that you want to make this new operator class the
   default B-tree operator class for the <type>complex</type> data type.
   If you don't, just leave out the word <literal>DEFAULT</>.
  </para>
 </sect2>

 <sect2 id="xindex-opclass-crosstype">
  <title>Cross-Data-Type Operator Classes</title>

  <para>
   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 B-tree and GiST index methods.
  </para>

  <para>
   B-trees require the left-hand operand of each operator to be the indexed
   data type, but the right-hand operand can be of a different type.  There
   must be a support function having a matching signature.  For example,
   the built-in operator class for type <type>bigint</> (<type>int8</>)
   allows cross-type comparisons to <type>int4</> and <type>int2</>.  It
   could be duplicated by this definition:

<programlisting>
CREATE OPERATOR CLASS int8_ops
DEFAULT FOR TYPE int8 USING btree AS
  -- standard int8 comparisons
  OPERATOR 1 &lt; ,
  OPERATOR 2 &lt;= ,
  OPERATOR 3 = ,
  OPERATOR 4 &gt;= ,
  OPERATOR 5 &gt; ,
  FUNCTION 1 btint8cmp(int8, int8) ,

  -- cross-type comparisons to int2 (smallint)
  OPERATOR 1 &lt; (int8, int2) ,
  OPERATOR 2 &lt;= (int8, int2) ,
  OPERATOR 3 = (int8, int2) ,
  OPERATOR 4 &gt;= (int8, int2) ,
  OPERATOR 5 &gt; (int8, int2) ,
  FUNCTION 1 btint82cmp(int8, int2) ,

  -- cross-type comparisons to int4 (integer)
  OPERATOR 1 &lt; (int8, int4) ,
  OPERATOR 2 &lt;= (int8, int4) ,
  OPERATOR 3 = (int8, int4) ,
  OPERATOR 4 &gt;= (int8, int4) ,
  OPERATOR 5 &gt; (int8, int4) ,
  FUNCTION 1 btint84cmp(int8, int4) ;
</programlisting>

   Notice that this definition <quote>overloads</> the operator strategy and
   support function numbers.  This is allowed (for B-tree operator classes
   only) so long as each instance of a particular number has a different
   right-hand data type.  The instances that are not cross-type are the
   default or primary operators of the operator class.
  </para>

  <para>
   GiST indexes do not allow overloading of strategy or support function
   numbers, but it is still possible to get the effect of supporting
   multiple right-hand data types, by assigning a distinct strategy number
   to each operator that needs to be supported.  The <literal>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.
  </para>
 </sect2>

 <sect2 id="xindex-opclass-dependencies">
  <title>System Dependencies on Operator Classes</title>

   <indexterm>
    <primary>ordering operator</primary>
   </indexterm>

  <para>
   <productname>PostgreSQL</productname> 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.
  </para>

  <para>
   In particular, there are SQL features such as <literal>ORDER BY</> and
   <literal>DISTINCT</> that require comparison and sorting of values.
   To implement these features on a user-defined data type,
   <productname>PostgreSQL</productname> looks for the default B-tree operator
   class for the data type.  The <quote>equals</> member of this operator
   class defines the system's notion of equality of values for
   <literal>GROUP BY</> and <literal>DISTINCT</>, and the sort ordering
   imposed by the operator class defines the default <literal>ORDER BY</>
   ordering.
  </para>

  <para>
   Comparison of arrays of user-defined types also relies on the semantics
   defined by the default B-tree operator class.
  </para>

  <para>
   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.
  </para>

  <para>
   When there is no default operator class for a data type, you will get
   errors like <quote>could not identify an ordering operator</> if you
   try to use these SQL features with the data type.
  </para>

   <note>
    <para>
     In <productname>PostgreSQL</productname> versions before 7.4,
     sorting and grouping operations would implicitly use operators named
     <literal>=</>, <literal>&lt;</>, and <literal>&gt;</>.  The new
     behavior of relying on default operator classes avoids having to make
     any assumption about the behavior of operators with particular names.
    </para>
   </note>
 </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 means
   that the index method can retrieve exactly the set of rows
   that satisfy a <literal>WHERE</> condition using the operator.  For example,
<programlisting>
SELECT * FROM table WHERE integer_column &lt; 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 an R-tree index stores only
   bounding boxes for objects, then it cannot exactly satisfy a <literal>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 <quote>lossy</> for the
   operator, and we add <literal>RECHECK</> to the <literal>OPERATOR</> clause
   in the <command>CREATE OPERATOR CLASS</> command.
   <literal>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.
  </para>

  <para>
   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 &mdash; we may as well store just a simpler object of type
   <type>box</>.  This situation is expressed by the <literal>STORAGE</>
   option in <command>CREATE OPERATOR CLASS</>: we'd write something like

<programlisting>
CREATE OPERATOR CLASS polygon_ops
    DEFAULT FOR TYPE polygon USING gist AS
        ...
        STORAGE box;
</programlisting>

   At present, only the GiST index method supports a
   <literal>STORAGE</> type that's different from the column data type.
   The GiST <literal>compress</> and <literal>decompress</> support
   routines must deal with data-type conversion when <literal>STORAGE</>
   is used.
  </para>
 </sect2>

</sect1>

<!-- Keep this comment at the end of the file
Local variables:
mode:sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:("/usr/lib/sgml/catalog")
sgml-local-ecat-files:nil
End:
-->