<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Interfacing Extensions To Indexes</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 8.2.14 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Extending SQL"
HREF="extend.html"><LINK
REL="PREVIOUS"
TITLE="Operator Optimization Information"
HREF="xoper-optimization.html"><LINK
REL="NEXT"
TITLE="Triggers"
HREF="triggers.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2009-09-04T05:25:47"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 8.2.14 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="xoper-optimization.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="extend.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 33. Extending <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="extend.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="triggers.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="XINDEX"
>33.14. Interfacing Extensions To Indexes</A
></H1
><A
NAME="AEN37938"
></A
><P
> 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
<I
CLASS="FIRSTTERM"
>operator class</I
> 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.
</P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
> Prior to <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> release 7.3, it was
necessary to make manual additions to the system catalogs
<CODE
CLASS="CLASSNAME"
>pg_amop</CODE
>, <CODE
CLASS="CLASSNAME"
>pg_amproc</CODE
>, and
<CODE
CLASS="CLASSNAME"
>pg_opclass</CODE
> in order to create a user-defined
operator class. That approach is now deprecated in favor of using
<A
HREF="sql-createopclass.html"
><I
>CREATE OPERATOR CLASS</I
></A
>,
which is a much simpler and less error-prone way of creating the
necessary catalog entries.
</P
></BLOCKQUOTE
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="XINDEX-IM"
>33.14.1. Index Methods and Operator Classes</A
></H2
><P
> The <CODE
CLASS="CLASSNAME"
>pg_am</CODE
> table contains one row for every
index method (internally known as access method). Support for
regular access to tables is built into
<SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>, but all index methods are
described in <CODE
CLASS="CLASSNAME"
>pg_am</CODE
>. It is possible to add a
new index method by defining the required interface routines and
then creating a row in <CODE
CLASS="CLASSNAME"
>pg_am</CODE
> — but that is
beyond the scope of this chapter (see <A
HREF="indexam.html"
>Chapter 49</A
>).
</P
><P
> The routines for an index method do not directly know anything
about the data types that the index method will operate on.
Instead, an <I
CLASS="FIRSTTERM"
>operator
class</I
><A
NAME="AEN37960"
></A
>
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
<TT
CLASS="LITERAL"
>WHERE</TT
>-clause operators that can be used with an index
(i.e., can be converted into an index-scan qualification). An
operator class may also specify some <I
CLASS="FIRSTTERM"
>support
procedures</I
> that are needed by the internal operations of the
index method, but do not directly correspond to any
<TT
CLASS="LITERAL"
>WHERE</TT
>-clause operator that can be used with the index.
</P
><P
> 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.
</P
><P
> 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
<TT
CLASS="LITERAL"
>int4_ops</TT
>), but each such class is an independent
entity and must be defined separately.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="XINDEX-STRATEGIES"
>33.14.2. Index Method Strategies</A
></H2
><P
> The operators associated with an operator class are identified by
<SPAN
CLASS="QUOTE"
>"strategy numbers"</SPAN
>, 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 <SPAN
CLASS="QUOTE"
>"less than"</SPAN
> and <SPAN
CLASS="QUOTE"
>"greater than or equal
to"</SPAN
> are interesting with respect to a B-tree.
Because
<SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> allows the user to define operators,
<SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> cannot look at the name of an operator
(e.g., <TT
CLASS="LITERAL"
><</TT
> or <TT
CLASS="LITERAL"
>>=</TT
>) and tell what kind of
comparison it is. Instead, the index method defines a set of
<SPAN
CLASS="QUOTE"
>"strategies"</SPAN
>, 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.
</P
><P
> The B-tree index method defines five strategies, shown in <A
HREF="xindex.html#XINDEX-BTREE-STRAT-TABLE"
>Table 33-2</A
>.
</P
><DIV
CLASS="TABLE"
><A
NAME="XINDEX-BTREE-STRAT-TABLE"
></A
><P
><B
>Table 33-2. B-tree Strategies</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><THEAD
><TR
><TH
>Operation</TH
><TH
>Strategy Number</TH
></TR
></THEAD
><TBODY
><TR
><TD
>less than</TD
><TD
>1</TD
></TR
><TR
><TD
>less than or equal</TD
><TD
>2</TD
></TR
><TR
><TD
>equal</TD
><TD
>3</TD
></TR
><TR
><TD
>greater than or equal</TD
><TD
>4</TD
></TR
><TR
><TD
>greater than</TD
><TD
>5</TD
></TR
></TBODY
></TABLE
></DIV
><P
> Hash indexes express only bitwise equality, and so they use only one
strategy, shown in <A
HREF="xindex.html#XINDEX-HASH-STRAT-TABLE"
>Table 33-3</A
>.
</P
><DIV
CLASS="TABLE"
><A
NAME="XINDEX-HASH-STRAT-TABLE"
></A
><P
><B
>Table 33-3. Hash Strategies</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><THEAD
><TR
><TH
>Operation</TH
><TH
>Strategy Number</TH
></TR
></THEAD
><TBODY
><TR
><TD
>equal</TD
><TD
>1</TD
></TR
></TBODY
></TABLE
></DIV
><P
> GiST indexes are even more flexible: they do not have a fixed set of
strategies at all. Instead, the <SPAN
CLASS="QUOTE"
>"consistency"</SPAN
> 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 <SPAN
CLASS="QUOTE"
>"R-tree"</SPAN
> strategies shown in
<A
HREF="xindex.html#XINDEX-RTREE-STRAT-TABLE"
>Table 33-4</A
>. 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.
</P
><DIV
CLASS="TABLE"
><A
NAME="XINDEX-RTREE-STRAT-TABLE"
></A
><P
><B
>Table 33-4. GiST Two-Dimensional <SPAN
CLASS="QUOTE"
>"R-tree"</SPAN
> Strategies</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><THEAD
><TR
><TH
>Operation</TH
><TH
>Strategy Number</TH
></TR
></THEAD
><TBODY
><TR
><TD
>strictly left of</TD
><TD
>1</TD
></TR
><TR
><TD
>does not extend to right of</TD
><TD
>2</TD
></TR
><TR
><TD
>overlaps</TD
><TD
>3</TD
></TR
><TR
><TD
>does not extend to left of</TD
><TD
>4</TD
></TR
><TR
><TD
>strictly right of</TD
><TD
>5</TD
></TR
><TR
><TD
>same</TD
><TD
>6</TD
></TR
><TR
><TD
>contains</TD
><TD
>7</TD
></TR
><TR
><TD
>contained by</TD
><TD
>8</TD
></TR
><TR
><TD
>does not extend above</TD
><TD
>9</TD
></TR
><TR
><TD
>strictly below</TD
><TD
>10</TD
></TR
><TR
><TD
>strictly above</TD
><TD
>11</TD
></TR
><TR
><TD
>does not extend below</TD
><TD
>12</TD
></TR
></TBODY
></TABLE
></DIV
><P
> 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 <A
HREF="xindex.html#XINDEX-GIN-ARRAY-STRAT-TABLE"
>Table 33-5</A
>.
</P
><DIV
CLASS="TABLE"
><A
NAME="XINDEX-GIN-ARRAY-STRAT-TABLE"
></A
><P
><B
>Table 33-5. GIN Array Strategies</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><THEAD
><TR
><TH
>Operation</TH
><TH
>Strategy Number</TH
></TR
></THEAD
><TBODY
><TR
><TD
>overlap</TD
><TD
>1</TD
></TR
><TR
><TD
>contains</TD
><TD
>2</TD
></TR
><TR
><TD
>is contained by</TD
><TD
>3</TD
></TR
><TR
><TD
>equal</TD
><TD
>4</TD
></TR
></TBODY
></TABLE
></DIV
><P
> Note that all strategy operators return Boolean values. In
practice, all operators defined as index method strategies must
return type <TT
CLASS="TYPE"
>boolean</TT
>, since they must appear at the top
level of a <TT
CLASS="LITERAL"
>WHERE</TT
> clause to be used with an index.
</P
><P
> By the way, the <TT
CLASS="STRUCTFIELD"
>amorderstrategy</TT
> column
in <CODE
CLASS="CLASSNAME"
>pg_am</CODE
> tells whether
the index method supports ordered scans. Zero means it doesn't; if it
does, <TT
CLASS="STRUCTFIELD"
>amorderstrategy</TT
> is the strategy
number that corresponds to the ordering operator. For example, B-tree
has <TT
CLASS="STRUCTFIELD"
>amorderstrategy</TT
> = 1, which is its
<SPAN
CLASS="QUOTE"
>"less than"</SPAN
> strategy number.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="XINDEX-SUPPORT"
>33.14.3. Index Method Support Routines</A
></H2
><P
> 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.
</P
><P
> 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 <SPAN
CLASS="QUOTE"
>"support function numbers"</SPAN
>.
</P
><P
> B-trees require a single support function, shown in <A
HREF="xindex.html#XINDEX-BTREE-SUPPORT-TABLE"
>Table 33-6</A
>.
</P
><DIV
CLASS="TABLE"
><A
NAME="XINDEX-BTREE-SUPPORT-TABLE"
></A
><P
><B
>Table 33-6. B-tree Support Functions</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><THEAD
><TR
><TH
>Function</TH
><TH
>Support Number</TH
></TR
></THEAD
><TBODY
><TR
><TD
> 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.
</TD
><TD
>1</TD
></TR
></TBODY
></TABLE
></DIV
><P
> Hash indexes likewise require one support function, shown in <A
HREF="xindex.html#XINDEX-HASH-SUPPORT-TABLE"
>Table 33-7</A
>.
</P
><DIV
CLASS="TABLE"
><A
NAME="XINDEX-HASH-SUPPORT-TABLE"
></A
><P
><B
>Table 33-7. Hash Support Functions</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><THEAD
><TR
><TH
>Function</TH
><TH
>Support Number</TH
></TR
></THEAD
><TBODY
><TR
><TD
>Compute the hash value for a key</TD
><TD
>1</TD
></TR
></TBODY
></TABLE
></DIV
><P
> GiST indexes require seven support functions,
shown in <A
HREF="xindex.html#XINDEX-GIST-SUPPORT-TABLE"
>Table 33-8</A
>.
</P
><DIV
CLASS="TABLE"
><A
NAME="XINDEX-GIST-SUPPORT-TABLE"
></A
><P
><B
>Table 33-8. GiST Support Functions</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><THEAD
><TR
><TH
>Function</TH
><TH
>Support Number</TH
></TR
></THEAD
><TBODY
><TR
><TD
>consistent - determine whether key satisfies the
query qualifier</TD
><TD
>1</TD
></TR
><TR
><TD
>union - compute union of a set of keys</TD
><TD
>2</TD
></TR
><TR
><TD
>compress - compute a compressed representation of a key or value
to be indexed</TD
><TD
>3</TD
></TR
><TR
><TD
>decompress - compute a decompressed representation of a
compressed key</TD
><TD
>4</TD
></TR
><TR
><TD
>penalty - compute penalty for inserting new key into subtree
with given subtree's key</TD
><TD
>5</TD
></TR
><TR
><TD
>picksplit - determine which entries of a page are to be moved
to the new page and compute the union keys for resulting pages</TD
><TD
>6</TD
></TR
><TR
><TD
>equal - compare two keys and return true if they are equal</TD
><TD
>7</TD
></TR
></TBODY
></TABLE
></DIV
><P
> GIN indexes require four support functions,
shown in <A
HREF="xindex.html#XINDEX-GIN-SUPPORT-TABLE"
>Table 33-9</A
>.
</P
><DIV
CLASS="TABLE"
><A
NAME="XINDEX-GIN-SUPPORT-TABLE"
></A
><P
><B
>Table 33-9. GIN Support Functions</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><THEAD
><TR
><TH
>Function</TH
><TH
>Support Number</TH
></TR
></THEAD
><TBODY
><TR
><TD
> 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
</TD
><TD
>1</TD
></TR
><TR
><TD
>extractValue - extract keys from a value to be indexed</TD
><TD
>2</TD
></TR
><TR
><TD
>extractQuery - extract keys from a query condition</TD
><TD
>3</TD
></TR
><TR
><TD
>consistent - determine whether value matches query condition</TD
><TD
>4</TD
></TR
></TBODY
></TABLE
></DIV
><P
> 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.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="XINDEX-EXAMPLE"
>33.14.4. An Example</A
></H2
><P
> 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
<TT
CLASS="FILENAME"
>src/tutorial/complex.c</TT
> and
<TT
CLASS="FILENAME"
>src/tutorial/complex.sql</TT
> in the source
distribution.)
The operator class encapsulates
operators that sort complex numbers in absolute value order, so we
choose the name <TT
CLASS="LITERAL"
>complex_abs_ops</TT
>. First, we need
a set of operators. The procedure for defining operators was
discussed in <A
HREF="xoper.html"
>Section 33.12</A
>. For an operator class on
B-trees, the operators we require are:
<P
></P
></P><UL
COMPACT="COMPACT"
><LI
><SPAN
>absolute-value less-than (strategy 1)</SPAN
></LI
><LI
><SPAN
>absolute-value less-than-or-equal (strategy 2)</SPAN
></LI
><LI
><SPAN
>absolute-value equal (strategy 3)</SPAN
></LI
><LI
><SPAN
>absolute-value greater-than-or-equal (strategy 4)</SPAN
></LI
><LI
><SPAN
>absolute-value greater-than (strategy 5)</SPAN
></LI
></UL
><P>
</P
><P
> 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
</P><PRE
CLASS="PROGRAMLISTING"
>#define Mag(c) ((c)->x*(c)->x + (c)->y*(c)->y)
static int
complex_abs_cmp_internal(Complex *a, Complex *b)
{
double amag = Mag(a),
bmag = Mag(b);
if (amag < bmag)
return -1;
if (amag > bmag)
return 1;
return 0;
}</PRE
><P>
Now the less-than function looks like
</P><PRE
CLASS="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) < 0);
}</PRE
><P>
The other four functions differ only in how they compare the internal
function's result to zero.
</P
><P
> Next we declare the functions and the operators based on the functions
to SQL:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE FUNCTION complex_abs_lt(complex, complex) RETURNS bool
AS '<TT
CLASS="REPLACEABLE"
><I
>filename</I
></TT
>', 'complex_abs_lt'
LANGUAGE C IMMUTABLE STRICT;
CREATE OPERATOR < (
leftarg = complex, rightarg = complex, procedure = complex_abs_lt,
commutator = > , negator = >= ,
restrict = scalarltsel, join = scalarltjoinsel
);</PRE
><P>
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.
</P
><P
> Other things worth noting are happening here:
<P
></P
></P><UL
><LI
><P
> There can only be one operator named, say, <TT
CLASS="LITERAL"
>=</TT
>
and taking type <TT
CLASS="TYPE"
>complex</TT
> for both operands. In this
case we don't have any other operator <TT
CLASS="LITERAL"
>=</TT
> for
<TT
CLASS="TYPE"
>complex</TT
>, but if we were building a practical data
type we'd probably want <TT
CLASS="LITERAL"
>=</TT
> 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 <CODE
CLASS="FUNCTION"
>complex_abs_eq</CODE
>.
</P
></LI
><LI
><P
> Although <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> 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 <TT
CLASS="FILENAME"
>abs_eq</TT
>. 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.
</P
></LI
><LI
><P
> We could have made the SQL name
of the function <TT
CLASS="FILENAME"
>abs_eq</TT
>, relying on
<SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> 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.
</P
></LI
></UL
><P>
</P
><P
> 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:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE FUNCTION complex_abs_cmp(complex, complex)
RETURNS integer
AS '<TT
CLASS="REPLACEABLE"
><I
>filename</I
></TT
>'
LANGUAGE C IMMUTABLE STRICT;</PRE
><P>
</P
><P
> Now that we have the required operators and support routine,
we can finally create the operator class:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE OPERATOR CLASS complex_abs_ops
DEFAULT FOR TYPE complex USING btree AS
OPERATOR 1 < ,
OPERATOR 2 <= ,
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
FUNCTION 1 complex_abs_cmp(complex, complex);</PRE
><P>
</P
><P
> And we're done! It should now be possible to create
and use B-tree indexes on <TT
CLASS="TYPE"
>complex</TT
> columns.
</P
><P
> We could have written the operator entries more verbosely, as in
</P><PRE
CLASS="PROGRAMLISTING"
> OPERATOR 1 < (complex, complex) ,</PRE
><P>
but there is no need to do so when the operators take the same data type
we are defining the operator class for.
</P
><P
> The above example assumes that you want to make this new operator class the
default B-tree operator class for the <TT
CLASS="TYPE"
>complex</TT
> data type.
If you don't, just leave out the word <TT
CLASS="LITERAL"
>DEFAULT</TT
>.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="XINDEX-OPCLASS-CROSSTYPE"
>33.14.5. Cross-Data-Type Operator Classes</A
></H2
><P
> 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.
</P
><P
> 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 <TT
CLASS="TYPE"
>bigint</TT
> (<TT
CLASS="TYPE"
>int8</TT
>)
allows cross-type comparisons to <TT
CLASS="TYPE"
>int4</TT
> and <TT
CLASS="TYPE"
>int2</TT
>. It
could be duplicated by this definition:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE OPERATOR CLASS int8_ops
DEFAULT FOR TYPE int8 USING btree AS
-- standard int8 comparisons
OPERATOR 1 < ,
OPERATOR 2 <= ,
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
FUNCTION 1 btint8cmp(int8, int8) ,
-- cross-type comparisons to int2 (smallint)
OPERATOR 1 < (int8, int2) ,
OPERATOR 2 <= (int8, int2) ,
OPERATOR 3 = (int8, int2) ,
OPERATOR 4 >= (int8, int2) ,
OPERATOR 5 > (int8, int2) ,
FUNCTION 1 btint82cmp(int8, int2) ,
-- cross-type comparisons to int4 (integer)
OPERATOR 1 < (int8, int4) ,
OPERATOR 2 <= (int8, int4) ,
OPERATOR 3 = (int8, int4) ,
OPERATOR 4 >= (int8, int4) ,
OPERATOR 5 > (int8, int4) ,
FUNCTION 1 btint84cmp(int8, int4) ;</PRE
><P>
Notice that this definition <SPAN
CLASS="QUOTE"
>"overloads"</SPAN
> 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.
</P
><P
> 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 <TT
CLASS="LITERAL"
>consistent</TT
>
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.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="XINDEX-OPCLASS-DEPENDENCIES"
>33.14.6. System Dependencies on Operator Classes</A
></H2
><A
NAME="AEN38250"
></A
><P
> <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> 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.
</P
><P
> In particular, there are SQL features such as <TT
CLASS="LITERAL"
>ORDER BY</TT
> and
<TT
CLASS="LITERAL"
>DISTINCT</TT
> that require comparison and sorting of values.
To implement these features on a user-defined data type,
<SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> looks for the default B-tree operator
class for the data type. The <SPAN
CLASS="QUOTE"
>"equals"</SPAN
> member of this operator
class defines the system's notion of equality of values for
<TT
CLASS="LITERAL"
>GROUP BY</TT
> and <TT
CLASS="LITERAL"
>DISTINCT</TT
>, and the sort ordering
imposed by the operator class defines the default <TT
CLASS="LITERAL"
>ORDER BY</TT
>
ordering.
</P
><P
> Comparison of arrays of user-defined types also relies on the semantics
defined by the default B-tree operator class.
</P
><P
> 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.
</P
><P
> When there is no default operator class for a data type, you will get
errors like <SPAN
CLASS="QUOTE"
>"could not identify an ordering operator"</SPAN
> if you
try to use these SQL features with the data type.
</P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
> In <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> versions before 7.4,
sorting and grouping operations would implicitly use operators named
<TT
CLASS="LITERAL"
>=</TT
>, <TT
CLASS="LITERAL"
><</TT
>, and <TT
CLASS="LITERAL"
>></TT
>. The new
behavior of relying on default operator classes avoids having to make
any assumption about the behavior of operators with particular names.
</P
></BLOCKQUOTE
></DIV
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="XINDEX-OPCLASS-FEATURES"
>33.14.7. Special Features of Operator Classes</A
></H2
><P
> 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.
</P
><P
> 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 <TT
CLASS="LITERAL"
>WHERE</TT
> condition using the operator. For example,
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT * FROM table WHERE integer_column < 4;</PRE
><P>
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 objects, then it cannot exactly satisfy a <TT
CLASS="LITERAL"
>WHERE</TT
>
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 <SPAN
CLASS="QUOTE"
>"lossy"</SPAN
> for the
operator, and we add <TT
CLASS="LITERAL"
>RECHECK</TT
> to the <TT
CLASS="LITERAL"
>OPERATOR</TT
> clause
in the <TT
CLASS="COMMAND"
>CREATE OPERATOR CLASS</TT
> command.
<TT
CLASS="LITERAL"
>RECHECK</TT
> 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.
</P
><P
> 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 — we may as well store just a simpler object of type
<TT
CLASS="TYPE"
>box</TT
>. This situation is expressed by the <TT
CLASS="LITERAL"
>STORAGE</TT
>
option in <TT
CLASS="COMMAND"
>CREATE OPERATOR CLASS</TT
>: we'd write something like
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE OPERATOR CLASS polygon_ops
DEFAULT FOR TYPE polygon USING gist AS
...
STORAGE box;</PRE
><P>
At present, only the GiST and GIN index methods support a
<TT
CLASS="LITERAL"
>STORAGE</TT
> type that's different from the column data type.
The GiST <CODE
CLASS="FUNCTION"
>compress</CODE
> and <CODE
CLASS="FUNCTION"
>decompress</CODE
> support
routines must deal with data-type conversion when <TT
CLASS="LITERAL"
>STORAGE</TT
>
is used. In GIN, the <TT
CLASS="LITERAL"
>STORAGE</TT
> type identifies the type of
the <SPAN
CLASS="QUOTE"
>"key"</SPAN
> values, which normally is different from the type
of the indexed column — for example, an operator class for
integer array columns might have keys that are just integers. The
GIN <CODE
CLASS="FUNCTION"
>extractValue</CODE
> and <CODE
CLASS="FUNCTION"
>extractQuery</CODE
> support
routines are responsible for extracting keys from indexed values.
</P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="xoper-optimization.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="triggers.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Operator Optimization Information</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="extend.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Triggers</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
distrib
>
Mandriva
>
2010.0
>
i586
>
media
>
contrib-release
>
by-pkgid
>
a866202fe868538f89a755dbcabc378b
>
files
>
820
