Baoding-FenShuaJiang
/
Green_GoodERP18_FSJ_Standard
Следить 1
В избранное 0
Вики 捐赠
gooderp18绿色标准版
Вы не можете выбрать более 25 тем Темы должны начинаться с буквы или цифры, могут содержать дефисы(-) и должны содержать не более 35 символов.
ветка: master
Ветки Теги
${ item.name }
Создать ветку ${ searchTerm }
из 'master'
${ noResults }
Green_GoodERP18_FSJ_Standard/PostgreSQL/doc/postgresql/html/xindex.html

742 lines
54KB
Исходник Постоянная ссылка Blame История 

  1. <?xml version="1.0" encoding="UTF-8" standalone="no"?>

  2. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>37.16. Interfacing Extensions to Indexes</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets V1.79.1" /><link rel="prev" href="xoper-optimization.html" title="37.15. Operator Optimization Information" /><link rel="next" href="extend-extensions.html" title="37.17. Packaging Related Objects into an Extension" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">37.16. Interfacing Extensions to Indexes</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="xoper-optimization.html" title="37.15. Operator Optimization Information">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="extend.html" title="Chapter 37. Extending SQL">Up</a></td><th width="60%" align="center">Chapter 37. Extending <acronym xmlns="http://www.w3.org/1999/xhtml" class="acronym">SQL</acronym></th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 12.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="extend-extensions.html" title="37.17. Packaging Related Objects into an Extension">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="XINDEX"><div class="titlepage"><div><div><h2 class="title" style="clear: both">37.16. Interfacing Extensions to Indexes</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="xindex.html#XINDEX-OPCLASS">37.16.1. Index Methods and Operator Classes</a></span></dt><dt><span class="sect2"><a href="xindex.html#XINDEX-STRATEGIES">37.16.2. Index Method Strategies</a></span></dt><dt><span class="sect2"><a href="xindex.html#XINDEX-SUPPORT">37.16.3. Index Method Support Routines</a></span></dt><dt><span class="sect2"><a href="xindex.html#XINDEX-EXAMPLE">37.16.4. An Example</a></span></dt><dt><span class="sect2"><a href="xindex.html#XINDEX-OPFAMILY">37.16.5. Operator Classes and Operator Families</a></span></dt><dt><span class="sect2"><a href="xindex.html#XINDEX-OPCLASS-DEPENDENCIES">37.16.6. System Dependencies on Operator Classes</a></span></dt><dt><span class="sect2"><a href="xindex.html#XINDEX-ORDERING-OPS">37.16.7. Ordering Operators</a></span></dt><dt><span class="sect2"><a href="xindex.html#XINDEX-OPCLASS-FEATURES">37.16.8. Special Features of Operator Classes</a></span></dt></dl></div><a id="id-1.8.3.19.2" class="indexterm"></a><p>

  3.    The procedures described thus far let you define new types, new

  4.    functions, and new operators. However, we cannot yet define an

  5.    index on a column of a new data type.  To do this, we must define an

  6.    <em class="firstterm">operator class</em> for the new data type.  Later in this

  7.    section, we will illustrate this concept in an example: a new

  8.    operator class for the B-tree index method that stores and sorts

  9.    complex numbers in ascending absolute value order.

  10.   </p><p>

  11.    Operator classes can be grouped into <em class="firstterm">operator families</em>

  12.    to show the relationships between semantically compatible classes.

  13.    When only a single data type is involved, an operator class is sufficient,

  14.    so we'll focus on that case first and then return to operator families.

  15.   </p><div class="sect2" id="XINDEX-OPCLASS"><div class="titlepage"><div><div><h3 class="title">37.16.1. Index Methods and Operator Classes</h3></div></div></div><p>

  16.    The <code class="classname">pg_am</code> table contains one row for every

  17.    index method (internally known as access method).  Support for

  18.    regular access to tables is built into

  19.    <span class="productname">PostgreSQL</span>, but all index methods are

  20.    described in <code class="classname">pg_am</code>.  It is possible to add a

  21.    new index access method by writing the necessary code and

  22.    then creating an entry in <code class="classname">pg_am</code> — but that is

  23.    beyond the scope of this chapter (see <a class="xref" href="indexam.html" title="Chapter 61. Index Access Method Interface Definition">Chapter 61</a>).

  24.   </p><p>

  25.    The routines for an index method do not directly know anything

  26.    about the data types that the index method will operate on.

  27.    Instead, an <em class="firstterm">operator

  28.    class</em><a id="id-1.8.3.19.5.3.2" class="indexterm"></a>

  29.    identifies the set of operations that the index method needs to use

  30.    to work with a particular data type.  Operator classes are so

  31.    called because one thing they specify is the set of

  32.    <code class="literal">WHERE</code>-clause operators that can be used with an index

  33.    (i.e., can be converted into an index-scan qualification).  An

  34.    operator class can also specify some <em class="firstterm">support

  35.    function</em> that are needed by the internal operations of the

  36.    index method, but do not directly correspond to any

  37.    <code class="literal">WHERE</code>-clause operator that can be used with the index.

  38.   </p><p>

  39.    It is possible to define multiple operator classes for the same

  40.    data type and index method.  By doing this, multiple

  41.    sets of indexing semantics can be defined for a single data type.

  42.    For example, a B-tree index requires a sort ordering to be defined

  43.    for each data type it works on.

  44.    It might be useful for a complex-number data type

  45.    to have one B-tree operator class that sorts the data by complex

  46.    absolute value, another that sorts by real part, and so on.

  47.    Typically, one of the operator classes will be deemed most commonly

  48.    useful and will be marked as the default operator class for that

  49.    data type and index method.

  50.   </p><p>

  51.    The same operator class name

  52.    can be used for several different index methods (for example, both B-tree

  53.    and hash index methods have operator classes named

  54.    <code class="literal">int4_ops</code>), but each such class is an independent

  55.    entity and must be defined separately.

  56.   </p></div><div class="sect2" id="XINDEX-STRATEGIES"><div class="titlepage"><div><div><h3 class="title">37.16.2. Index Method Strategies</h3></div></div></div><p>

  57.    The operators associated with an operator class are identified by

  58.    <span class="quote">“<span class="quote">strategy numbers</span>”</span>, which serve to identify the semantics of

  59.    each operator within the context of its operator class.

  60.    For example, B-trees impose a strict ordering on keys, lesser to greater,

  61.    and so operators like <span class="quote">“<span class="quote">less than</span>”</span> and <span class="quote">“<span class="quote">greater than or equal

  62.    to</span>”</span> are interesting with respect to a B-tree.

  63.    Because

  64.    <span class="productname">PostgreSQL</span> allows the user to define operators,

  65.    <span class="productname">PostgreSQL</span> cannot look at the name of an operator

  66.    (e.g., <code class="literal">&lt;</code> or <code class="literal">&gt;=</code>) and tell what kind of

  67.    comparison it is.  Instead, the index method defines a set of

  68.    <span class="quote">“<span class="quote">strategies</span>”</span>, which can be thought of as generalized operators.

  69.    Each operator class specifies which actual operator corresponds to each

  70.    strategy for a particular data type and interpretation of the index

  71.    semantics.

  72.   </p><p>

  73.    The B-tree index method defines five strategies, shown in <a class="xref" href="xindex.html#XINDEX-BTREE-STRAT-TABLE" title="Table 37.2. B-Tree Strategies">Table 37.2</a>.

  74.   </p><div class="table" id="XINDEX-BTREE-STRAT-TABLE"><p class="title"><strong>Table 37.2. B-Tree Strategies</strong></p><div class="table-contents"><table class="table" summary="B-Tree Strategies" border="1"><colgroup><col /><col /></colgroup><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></div><br class="table-break" /><p>

  75.    Hash indexes support only equality comparisons, and so they use only one

  76.    strategy, shown in <a class="xref" href="xindex.html#XINDEX-HASH-STRAT-TABLE" title="Table 37.3. Hash Strategies">Table 37.3</a>.

  77.   </p><div class="table" id="XINDEX-HASH-STRAT-TABLE"><p class="title"><strong>Table 37.3. Hash Strategies</strong></p><div class="table-contents"><table class="table" summary="Hash Strategies" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Operation</th><th>Strategy Number</th></tr></thead><tbody><tr><td>equal</td><td>1</td></tr></tbody></table></div></div><br class="table-break" /><p>

  78.    GiST indexes are more flexible: they do not have a fixed set of

  79.    strategies at all.  Instead, the <span class="quote">“<span class="quote">consistency</span>”</span> support routine

  80.    of each particular GiST operator class interprets the strategy numbers

  81.    however it likes.  As an example, several of the built-in GiST index

  82.    operator classes index two-dimensional geometric objects, providing

  83.    the <span class="quote">“<span class="quote">R-tree</span>”</span> strategies shown in

  84.    <a class="xref" href="xindex.html#XINDEX-RTREE-STRAT-TABLE" title="Table 37.4. GiST Two-Dimensional “R-tree” Strategies">Table 37.4</a>.  Four of these are true

  85.    two-dimensional tests (overlaps, same, contains, contained by);

  86.    four of them consider only the X direction; and the other four

  87.    provide the same tests in the Y direction.

  88.   </p><div class="table" id="XINDEX-RTREE-STRAT-TABLE"><p class="title"><strong>Table 37.4. GiST Two-Dimensional <span class="quote">“<span class="quote">R-tree</span>”</span> Strategies</strong></p><div class="table-contents"><table class="table" summary="GiST Two-Dimensional R-tree Strategies" border="1"><colgroup><col /><col /></colgroup><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></div><br class="table-break" /><p>

  89.    SP-GiST indexes are similar to GiST indexes in flexibility: they don't have

  90.    a fixed set of strategies. Instead the support routines of each operator

  91.    class interpret the strategy numbers according to the operator class's

  92.    definition. As an example, the strategy numbers used by the built-in

  93.    operator classes for points are shown in <a class="xref" href="xindex.html#XINDEX-SPGIST-POINT-STRAT-TABLE" title="Table 37.5. SP-GiST Point Strategies">Table 37.5</a>.

  94.   </p><div class="table" id="XINDEX-SPGIST-POINT-STRAT-TABLE"><p class="title"><strong>Table 37.5. SP-GiST Point Strategies</strong></p><div class="table-contents"><table class="table" summary="SP-GiST Point Strategies" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Operation</th><th>Strategy Number</th></tr></thead><tbody><tr><td>strictly left of</td><td>1</td></tr><tr><td>strictly right of</td><td>5</td></tr><tr><td>same</td><td>6</td></tr><tr><td>contained by</td><td>8</td></tr><tr><td>strictly below</td><td>10</td></tr><tr><td>strictly above</td><td>11</td></tr></tbody></table></div></div><br class="table-break" /><p>

  95.    GIN indexes are similar to GiST and SP-GiST indexes, in that they don't

  96.    have a fixed set of strategies either. Instead the support routines of

  97.    each operator class interpret the strategy numbers according to the

  98.    operator class's definition. As an example, the strategy numbers used by

  99.    the built-in operator class for arrays are shown in

  100.    <a class="xref" href="xindex.html#XINDEX-GIN-ARRAY-STRAT-TABLE" title="Table 37.6. GIN Array Strategies">Table 37.6</a>.

  101.   </p><div class="table" id="XINDEX-GIN-ARRAY-STRAT-TABLE"><p class="title"><strong>Table 37.6. GIN Array Strategies</strong></p><div class="table-contents"><table class="table" summary="GIN Array Strategies" border="1"><colgroup><col /><col /></colgroup><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></div><br class="table-break" /><p>

  102.    BRIN indexes are similar to GiST, SP-GiST and GIN indexes in that they

  103.    don't have a fixed set of strategies either.  Instead the support routines

  104.    of each operator class interpret the strategy numbers according to the

  105.    operator class's definition. As an example, the strategy numbers used by

  106.    the built-in <code class="literal">Minmax</code> operator classes are shown in

  107.    <a class="xref" href="xindex.html#XINDEX-BRIN-MINMAX-STRAT-TABLE" title="Table 37.7. BRIN Minmax Strategies">Table 37.7</a>.

  108.   </p><div class="table" id="XINDEX-BRIN-MINMAX-STRAT-TABLE"><p class="title"><strong>Table 37.7. BRIN Minmax Strategies</strong></p><div class="table-contents"><table class="table" summary="BRIN Minmax Strategies" border="1"><colgroup><col /><col /></colgroup><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></div><br class="table-break" /><p>

  109.    Notice that all the operators listed above return Boolean values.  In

  110.    practice, all operators defined as index method search operators must

  111.    return type <code class="type">boolean</code>, since they must appear at the top

  112.    level of a <code class="literal">WHERE</code> clause to be used with an index.

  113.    (Some index access methods also support <em class="firstterm">ordering operators</em>,

  114.    which typically don't return Boolean values; that feature is discussed

  115.    in <a class="xref" href="xindex.html#XINDEX-ORDERING-OPS" title="37.16.7. Ordering Operators">Section 37.16.7</a>.)

  116.   </p></div><div class="sect2" id="XINDEX-SUPPORT"><div class="titlepage"><div><div><h3 class="title">37.16.3. Index Method Support Routines</h3></div></div></div><p>

  117.    Strategies aren't usually enough information for the system to figure

  118.    out how to use an index.  In practice, the index methods require

  119.    additional support routines in order to work. For example, the B-tree

  120.    index method must be able to compare two keys and determine whether one

  121.    is greater than, equal to, or less than the other.  Similarly, the

  122.    hash index method must be able to compute hash codes for key values.

  123.    These operations do not correspond to operators used in qualifications in

  124.    SQL commands;  they are administrative routines used by

  125.    the index methods, internally.

  126.   </p><p>

  127.    Just as with strategies, the operator class identifies which specific

  128.    functions should play each of these roles for a given data type and

  129.    semantic interpretation.  The index method defines the set

  130.    of functions it needs, and the operator class identifies the correct

  131.    functions to use by assigning them to the <span class="quote">“<span class="quote">support function numbers</span>”</span>

  132.    specified by the index method.

  133.   </p><p>

  134.    B-trees require a comparison support function,

  135.    and allow two additional support functions to be

  136.    supplied at the operator class author's option, as shown in <a class="xref" href="xindex.html#XINDEX-BTREE-SUPPORT-TABLE" title="Table 37.8. B-Tree Support Functions">Table 37.8</a>.

  137.    The requirements for these support functions are explained further in

  138.    <a class="xref" href="btree-support-funcs.html" title="63.3. B-Tree Support Functions">Section 63.3</a>.

  139.   </p><div class="table" id="XINDEX-BTREE-SUPPORT-TABLE"><p class="title"><strong>Table 37.8. B-Tree Support Functions</strong></p><div class="table-contents"><table class="table" summary="B-Tree Support Functions" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Function</th><th>Support Number</th></tr></thead><tbody><tr><td>

  140.         Compare two keys and return an integer less than zero, zero, or

  141.         greater than zero, indicating whether the first key is less than,

  142.         equal to, or greater than the second

  143.        </td><td>1</td></tr><tr><td>

  144.         Return the addresses of C-callable sort support function(s)

  145.         (optional)

  146.        </td><td>2</td></tr><tr><td>

  147.         Compare a test value to a base value plus/minus an offset, and return

  148.         true or false according to the comparison result (optional)

  149.        </td><td>3</td></tr></tbody></table></div></div><br class="table-break" /><p>

  150.    Hash indexes require one support function, and allow a second one to be

  151.    supplied at the operator class author's option, as shown in <a class="xref" href="xindex.html#XINDEX-HASH-SUPPORT-TABLE" title="Table 37.9. Hash Support Functions">Table 37.9</a>.

  152.   </p><div class="table" id="XINDEX-HASH-SUPPORT-TABLE"><p class="title"><strong>Table 37.9. Hash Support Functions</strong></p><div class="table-contents"><table class="table" summary="Hash Support Functions" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Function</th><th>Support Number</th></tr></thead><tbody><tr><td>Compute the 32-bit hash value for a key</td><td>1</td></tr><tr><td>

  153.          Compute the 64-bit hash value for a key given a 64-bit salt; if

  154.          the salt is 0, the low 32 bits of the result must match the value

  155.          that would have been computed by function 1

  156.          (optional)

  157.        </td><td>2</td></tr></tbody></table></div></div><br class="table-break" /><p>

  158.    GiST indexes have nine support functions, two of which are optional,

  159.    as shown in <a class="xref" href="xindex.html#XINDEX-GIST-SUPPORT-TABLE" title="Table 37.10. GiST Support Functions">Table 37.10</a>.

  160.    (For more information see <a class="xref" href="gist.html" title="Chapter 64. GiST Indexes">Chapter 64</a>.)

  161.   </p><div class="table" id="XINDEX-GIST-SUPPORT-TABLE"><p class="title"><strong>Table 37.10. GiST Support Functions</strong></p><div class="table-contents"><table class="table" summary="GiST Support Functions" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Function</th><th>Description</th><th>Support Number</th></tr></thead><tbody><tr><td><code class="function">consistent</code></td><td>determine whether key satisfies the

  162.         query qualifier</td><td>1</td></tr><tr><td><code class="function">union</code></td><td>compute union of a set of keys</td><td>2</td></tr><tr><td><code class="function">compress</code></td><td>compute a compressed representation of a key or value

  163.         to be indexed</td><td>3</td></tr><tr><td><code class="function">decompress</code></td><td>compute a decompressed representation of a

  164.         compressed key</td><td>4</td></tr><tr><td><code class="function">penalty</code></td><td>compute penalty for inserting new key into subtree

  165.        with given subtree's key</td><td>5</td></tr><tr><td><code class="function">picksplit</code></td><td>determine which entries of a page are to be moved

  166.        to the new page and compute the union keys for resulting pages</td><td>6</td></tr><tr><td><code class="function">equal</code></td><td>compare two keys and return true if they are equal</td><td>7</td></tr><tr><td><code class="function">distance</code></td><td>determine distance from key to query value (optional)</td><td>8</td></tr><tr><td><code class="function">fetch</code></td><td>compute original representation of a compressed key for

  167.        index-only scans (optional)</td><td>9</td></tr></tbody></table></div></div><br class="table-break" /><p>

  168.    SP-GiST indexes require five support functions, as

  169.    shown in <a class="xref" href="xindex.html#XINDEX-SPGIST-SUPPORT-TABLE" title="Table 37.11. SP-GiST Support Functions">Table 37.11</a>.

  170.    (For more information see <a class="xref" href="spgist.html" title="Chapter 65. SP-GiST Indexes">Chapter 65</a>.)

  171.   </p><div class="table" id="XINDEX-SPGIST-SUPPORT-TABLE"><p class="title"><strong>Table 37.11. SP-GiST Support Functions</strong></p><div class="table-contents"><table class="table" summary="SP-GiST Support Functions" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Function</th><th>Description</th><th>Support Number</th></tr></thead><tbody><tr><td><code class="function">config</code></td><td>provide basic information about the operator class</td><td>1</td></tr><tr><td><code class="function">choose</code></td><td>determine how to insert a new value into an inner tuple</td><td>2</td></tr><tr><td><code class="function">picksplit</code></td><td>determine how to partition a set of values</td><td>3</td></tr><tr><td><code class="function">inner_consistent</code></td><td>determine which sub-partitions need to be searched for a

  172.         query</td><td>4</td></tr><tr><td><code class="function">leaf_consistent</code></td><td>determine whether key satisfies the

  173.         query qualifier</td><td>5</td></tr></tbody></table></div></div><br class="table-break" /><p>

  174.    GIN indexes have six support functions, three of which are optional,

  175.    as shown in <a class="xref" href="xindex.html#XINDEX-GIN-SUPPORT-TABLE" title="Table 37.12. GIN Support Functions">Table 37.12</a>.

  176.    (For more information see <a class="xref" href="gin.html" title="Chapter 66. GIN Indexes">Chapter 66</a>.)

  177.   </p><div class="table" id="XINDEX-GIN-SUPPORT-TABLE"><p class="title"><strong>Table 37.12. GIN Support Functions</strong></p><div class="table-contents"><table class="table" summary="GIN Support Functions" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Function</th><th>Description</th><th>Support Number</th></tr></thead><tbody><tr><td><code class="function">compare</code></td><td>

  178.         compare two keys and return an integer less than zero, zero,

  179.         or greater than zero, indicating whether the first key is less than,

  180.         equal to, or greater than the second

  181.        </td><td>1</td></tr><tr><td><code class="function">extractValue</code></td><td>extract keys from a value to be indexed</td><td>2</td></tr><tr><td><code class="function">extractQuery</code></td><td>extract keys from a query condition</td><td>3</td></tr><tr><td><code class="function">consistent</code></td><td>

  182.         determine whether value matches query condition (Boolean variant)

  183.         (optional if support function 6 is present)

  184.        </td><td>4</td></tr><tr><td><code class="function">comparePartial</code></td><td>

  185.         compare partial key from

  186.         query and key from index, and return an integer less than zero, zero,

  187.         or greater than zero, indicating whether GIN should ignore this index

  188.         entry, treat the entry as a match, or stop the index scan (optional)

  189.        </td><td>5</td></tr><tr><td><code class="function">triConsistent</code></td><td>

  190.         determine whether value matches query condition (ternary variant)

  191.         (optional if support function 4 is present)

  192.        </td><td>6</td></tr></tbody></table></div></div><br class="table-break" /><p>

  193.    BRIN indexes have four basic support functions, as shown in

  194.    <a class="xref" href="xindex.html#XINDEX-BRIN-SUPPORT-TABLE" title="Table 37.13. BRIN Support Functions">Table 37.13</a>; those basic functions

  195.    may require additional support functions to be provided.

  196.    (For more information see <a class="xref" href="brin-extensibility.html" title="67.3. Extensibility">Section 67.3</a>.)

  197.   </p><div class="table" id="XINDEX-BRIN-SUPPORT-TABLE"><p class="title"><strong>Table 37.13. BRIN Support Functions</strong></p><div class="table-contents"><table class="table" summary="BRIN Support Functions" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Function</th><th>Description</th><th>Support Number</th></tr></thead><tbody><tr><td><code class="function">opcInfo</code></td><td>

  198.         return internal information describing the indexed columns'

  199.         summary data

  200.        </td><td>1</td></tr><tr><td><code class="function">add_value</code></td><td>add a new value to an existing summary index tuple</td><td>2</td></tr><tr><td><code class="function">consistent</code></td><td>determine whether value matches query condition</td><td>3</td></tr><tr><td><code class="function">union</code></td><td>

  201.         compute union of two summary tuples

  202.        </td><td>4</td></tr></tbody></table></div></div><br class="table-break" /><p>

  203.    Unlike search operators, support functions return whichever data

  204.    type the particular index method expects; for example in the case

  205.    of the comparison function for B-trees, a signed integer.  The number

  206.    and types of the arguments to each support function are likewise

  207.    dependent on the index method.  For B-tree and hash the comparison and

  208.    hashing support functions take the same input data types as do the

  209.    operators included in the operator class, but this is not the case for

  210.    most GiST, SP-GiST, GIN, and BRIN support functions.

  211.   </p></div><div class="sect2" id="XINDEX-EXAMPLE"><div class="titlepage"><div><div><h3 class="title">37.16.4. An Example</h3></div></div></div><p>

  212.    Now that we have seen the ideas, here is the promised example of

  213.    creating a new operator class.

  214.    (You can find a working copy of this example in

  215.    <code class="filename">src/tutorial/complex.c</code> and

  216.    <code class="filename">src/tutorial/complex.sql</code> in the source

  217.    distribution.)

  218.    The operator class encapsulates

  219.    operators that sort complex numbers in absolute value order, so we

  220.    choose the name <code class="literal">complex_abs_ops</code>.  First, we need

  221.    a set of operators.  The procedure for defining operators was

  222.    discussed in <a class="xref" href="xoper.html" title="37.14. User-Defined Operators">Section 37.14</a>.  For an operator class on

  223.    B-trees, the operators we require are:

  224. 

  225.    </p><div class="itemizedlist"><ul class="itemizedlist compact" style="list-style-type: disc; "><li class="listitem">absolute-value less-than (strategy 1)</li><li class="listitem">absolute-value less-than-or-equal (strategy 2)</li><li class="listitem">absolute-value equal (strategy 3)</li><li class="listitem">absolute-value greater-than-or-equal (strategy 4)</li><li class="listitem">absolute-value greater-than (strategy 5)</li></ul></div><p>

  226.   </p><p>

  227.    The least error-prone way to define a related set of comparison operators

  228.    is to write the B-tree comparison support function first, and then write the

  229.    other functions as one-line wrappers around the support function.  This

  230.    reduces the odds of getting inconsistent results for corner cases.

  231.    Following this approach, we first write:

  232. 

  233. </p><pre class="programlisting">

  234. #define Mag(c)  ((c)-&gt;x*(c)-&gt;x + (c)-&gt;y*(c)-&gt;y)

  235. 

  236. static int

  237. complex_abs_cmp_internal(Complex *a, Complex *b)

  238. {

  239.     double      amag = Mag(a),

  240.                 bmag = Mag(b);

  241. 

  242.     if (amag &lt; bmag)

  243.         return -1;

  244.     if (amag &gt; bmag)

  245.         return 1;

  246.     return 0;

  247. }

  248. 

  249. </pre><p>

  250. 

  251.    Now the less-than function looks like:

  252. 

  253. </p><pre class="programlisting">

  254. PG_FUNCTION_INFO_V1(complex_abs_lt);

  255. 

  256. Datum

  257. complex_abs_lt(PG_FUNCTION_ARGS)

  258. {

  259.     Complex    *a = (Complex *) PG_GETARG_POINTER(0);

  260.     Complex    *b = (Complex *) PG_GETARG_POINTER(1);

  261. 

  262.     PG_RETURN_BOOL(complex_abs_cmp_internal(a, b) &lt; 0);

  263. }

  264. 

  265. </pre><p>

  266. 

  267.    The other four functions differ only in how they compare the internal

  268.    function's result to zero.

  269.   </p><p>

  270.    Next we declare the functions and the operators based on the functions

  271.    to SQL:

  272. 

  273. </p><pre class="programlisting">

  274. CREATE FUNCTION complex_abs_lt(complex, complex) RETURNS bool

  275.     AS '<em class="replaceable"><code>filename</code></em>', 'complex_abs_lt'

  276.     LANGUAGE C IMMUTABLE STRICT;

  277. 

  278. CREATE OPERATOR &lt; (

  279.    leftarg = complex, rightarg = complex, procedure = complex_abs_lt,

  280.    commutator = &gt; , negator = &gt;= ,

  281.    restrict = scalarltsel, join = scalarltjoinsel

  282. );

  283. </pre><p>

  284.    It is important to specify the correct commutator and negator operators,

  285.    as well as suitable restriction and join selectivity

  286.    functions, otherwise the optimizer will be unable to make effective

  287.    use of the index.

  288.   </p><p>

  289.    Other things worth noting are happening here:

  290. 

  291.   </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>

  292.      There can only be one operator named, say, <code class="literal">=</code>

  293.      and taking type <code class="type">complex</code> for both operands.  In this

  294.      case we don't have any other operator <code class="literal">=</code> for

  295.      <code class="type">complex</code>, but if we were building a practical data

  296.      type we'd probably want <code class="literal">=</code> to be the ordinary

  297.      equality operation for complex numbers (and not the equality of

  298.      the absolute values).  In that case, we'd need to use some other

  299.      operator name for <code class="function">complex_abs_eq</code>.

  300.     </p></li><li class="listitem"><p>

  301.      Although <span class="productname">PostgreSQL</span> can cope with

  302.      functions having the same SQL name as long as they have different

  303.      argument data types, C can only cope with one global function

  304.      having a given name.  So we shouldn't name the C function

  305.      something simple like <code class="filename">abs_eq</code>.  Usually it's

  306.      a good practice to include the data type name in the C function

  307.      name, so as not to conflict with functions for other data types.

  308.     </p></li><li class="listitem"><p>

  309.      We could have made the SQL name

  310.      of the function <code class="filename">abs_eq</code>, relying on

  311.      <span class="productname">PostgreSQL</span> to distinguish it by

  312.      argument data types from any other SQL function of the same name.

  313.      To keep the example simple, we make the function have the same

  314.      names at the C level and SQL level.

  315.     </p></li></ul></div><p>

  316.   </p><p>

  317.    The next step is the registration of the support routine required

  318.    by B-trees.  The example C code that implements this is in the same

  319.    file that contains the operator functions.  This is how we declare

  320.    the function:

  321. 

  322. </p><pre class="programlisting">

  323. CREATE FUNCTION complex_abs_cmp(complex, complex)

  324.     RETURNS integer

  325.     AS '<em class="replaceable"><code>filename</code></em>'

  326.     LANGUAGE C IMMUTABLE STRICT;

  327. </pre><p>

  328.   </p><p>

  329.    Now that we have the required operators and support routine,

  330.    we can finally create the operator class:

  331. 

  332. </p><pre class="programlisting">

  333. CREATE OPERATOR CLASS complex_abs_ops

  334.     DEFAULT FOR TYPE complex USING btree AS

  335.         OPERATOR        1       &lt; ,

  336.         OPERATOR        2       &lt;= ,

  337.         OPERATOR        3       = ,

  338.         OPERATOR        4       &gt;= ,

  339.         OPERATOR        5       &gt; ,

  340.         FUNCTION        1       complex_abs_cmp(complex, complex);

  341. 

  342. </pre><p>

  343.   </p><p>

  344.    And we're done!  It should now be possible to create

  345.    and use B-tree indexes on <code class="type">complex</code> columns.

  346.   </p><p>

  347.    We could have written the operator entries more verbosely, as in:

  348. </p><pre class="programlisting">

  349.         OPERATOR        1       &lt; (complex, complex) ,

  350. </pre><p>

  351.    but there is no need to do so when the operators take the same data type

  352.    we are defining the operator class for.

  353.   </p><p>

  354.    The above example assumes that you want to make this new operator class the

  355.    default B-tree operator class for the <code class="type">complex</code> data type.

  356.    If you don't, just leave out the word <code class="literal">DEFAULT</code>.

  357.   </p></div><div class="sect2" id="XINDEX-OPFAMILY"><div class="titlepage"><div><div><h3 class="title">37.16.5. Operator Classes and Operator Families</h3></div></div></div><p>

  358.    So far we have implicitly assumed that an operator class deals with

  359.    only one data type.  While there certainly can be only one data type in

  360.    a particular index column, it is often useful to index operations that

  361.    compare an indexed column to a value of a different data type.  Also,

  362.    if there is use for a cross-data-type operator in connection with an

  363.    operator class, it is often the case that the other data type has a

  364.    related operator class of its own.  It is helpful to make the connections

  365.    between related classes explicit, because this can aid the planner in

  366.    optimizing SQL queries (particularly for B-tree operator classes, since

  367.    the planner contains a great deal of knowledge about how to work with them).

  368.   </p><p>

  369.    To handle these needs, <span class="productname">PostgreSQL</span>

  370.    uses the concept of an <em class="firstterm">operator

  371.    family</em><a id="id-1.8.3.19.9.3.3" class="indexterm"></a>.

  372.    An operator family contains one or more operator classes, and can also

  373.    contain indexable operators and corresponding support functions that

  374.    belong to the family as a whole but not to any single class within the

  375.    family.  We say that such operators and functions are <span class="quote">“<span class="quote">loose</span>”</span>

  376.    within the family, as opposed to being bound into a specific class.

  377.    Typically each operator class contains single-data-type operators

  378.    while cross-data-type operators are loose in the family.

  379.   </p><p>

  380.    All the operators and functions in an operator family must have compatible

  381.    semantics, where the compatibility requirements are set by the index

  382.    method.  You might therefore wonder why bother to single out particular

  383.    subsets of the family as operator classes; and indeed for many purposes

  384.    the class divisions are irrelevant and the family is the only interesting

  385.    grouping.  The reason for defining operator classes is that they specify

  386.    how much of the family is needed to support any particular index.

  387.    If there is an index using an operator class, then that operator class

  388.    cannot be dropped without dropping the index — but other parts of

  389.    the operator family, namely other operator classes and loose operators,

  390.    could be dropped.  Thus, an operator class should be specified to contain

  391.    the minimum set of operators and functions that are reasonably needed

  392.    to work with an index on a specific data type, and then related but

  393.    non-essential operators can be added as loose members of the operator

  394.    family.

  395.   </p><p>

  396.    As an example, <span class="productname">PostgreSQL</span> has a built-in

  397.    B-tree operator family <code class="literal">integer_ops</code>, which includes operator

  398.    classes <code class="literal">int8_ops</code>, <code class="literal">int4_ops</code>, and

  399.    <code class="literal">int2_ops</code> for indexes on <code class="type">bigint</code> (<code class="type">int8</code>),

  400.    <code class="type">integer</code> (<code class="type">int4</code>), and <code class="type">smallint</code> (<code class="type">int2</code>)

  401.    columns respectively.  The family also contains cross-data-type comparison

  402.    operators allowing any two of these types to be compared, so that an index

  403.    on one of these types can be searched using a comparison value of another

  404.    type.  The family could be duplicated by these definitions:

  405. 

  406. </p><pre class="programlisting">

  407. CREATE OPERATOR FAMILY integer_ops USING btree;

  408. 

  409. CREATE OPERATOR CLASS int8_ops

  410. DEFAULT FOR TYPE int8 USING btree FAMILY integer_ops AS

  411.   -- standard int8 comparisons

  412.   OPERATOR 1 &lt; ,

  413.   OPERATOR 2 &lt;= ,

  414.   OPERATOR 3 = ,

  415.   OPERATOR 4 &gt;= ,

  416.   OPERATOR 5 &gt; ,

  417.   FUNCTION 1 btint8cmp(int8, int8) ,

  418.   FUNCTION 2 btint8sortsupport(internal) ,

  419.   FUNCTION 3 in_range(int8, int8, int8, boolean, boolean) ;

  420. 

  421. CREATE OPERATOR CLASS int4_ops

  422. DEFAULT FOR TYPE int4 USING btree FAMILY integer_ops AS

  423.   -- standard int4 comparisons

  424.   OPERATOR 1 &lt; ,

  425.   OPERATOR 2 &lt;= ,

  426.   OPERATOR 3 = ,

  427.   OPERATOR 4 &gt;= ,

  428.   OPERATOR 5 &gt; ,

  429.   FUNCTION 1 btint4cmp(int4, int4) ,

  430.   FUNCTION 2 btint4sortsupport(internal) ,

  431.   FUNCTION 3 in_range(int4, int4, int4, boolean, boolean) ;

  432. 

  433. CREATE OPERATOR CLASS int2_ops

  434. DEFAULT FOR TYPE int2 USING btree FAMILY integer_ops AS

  435.   -- standard int2 comparisons

  436.   OPERATOR 1 &lt; ,

  437.   OPERATOR 2 &lt;= ,

  438.   OPERATOR 3 = ,

  439.   OPERATOR 4 &gt;= ,

  440.   OPERATOR 5 &gt; ,

  441.   FUNCTION 1 btint2cmp(int2, int2) ,

  442.   FUNCTION 2 btint2sortsupport(internal) ,

  443.   FUNCTION 3 in_range(int2, int2, int2, boolean, boolean) ;

  444. 

  445. ALTER OPERATOR FAMILY integer_ops USING btree ADD

  446.   -- cross-type comparisons int8 vs int2

  447.   OPERATOR 1 &lt; (int8, int2) ,

  448.   OPERATOR 2 &lt;= (int8, int2) ,

  449.   OPERATOR 3 = (int8, int2) ,

  450.   OPERATOR 4 &gt;= (int8, int2) ,

  451.   OPERATOR 5 &gt; (int8, int2) ,

  452.   FUNCTION 1 btint82cmp(int8, int2) ,

  453. 

  454.   -- cross-type comparisons int8 vs int4

  455.   OPERATOR 1 &lt; (int8, int4) ,

  456.   OPERATOR 2 &lt;= (int8, int4) ,

  457.   OPERATOR 3 = (int8, int4) ,

  458.   OPERATOR 4 &gt;= (int8, int4) ,

  459.   OPERATOR 5 &gt; (int8, int4) ,

  460.   FUNCTION 1 btint84cmp(int8, int4) ,

  461. 

  462.   -- cross-type comparisons int4 vs int2

  463.   OPERATOR 1 &lt; (int4, int2) ,

  464.   OPERATOR 2 &lt;= (int4, int2) ,

  465.   OPERATOR 3 = (int4, int2) ,

  466.   OPERATOR 4 &gt;= (int4, int2) ,

  467.   OPERATOR 5 &gt; (int4, int2) ,

  468.   FUNCTION 1 btint42cmp(int4, int2) ,

  469. 

  470.   -- cross-type comparisons int4 vs int8

  471.   OPERATOR 1 &lt; (int4, int8) ,

  472.   OPERATOR 2 &lt;= (int4, int8) ,

  473.   OPERATOR 3 = (int4, int8) ,

  474.   OPERATOR 4 &gt;= (int4, int8) ,

  475.   OPERATOR 5 &gt; (int4, int8) ,

  476.   FUNCTION 1 btint48cmp(int4, int8) ,

  477. 

  478.   -- cross-type comparisons int2 vs int8

  479.   OPERATOR 1 &lt; (int2, int8) ,

  480.   OPERATOR 2 &lt;= (int2, int8) ,

  481.   OPERATOR 3 = (int2, int8) ,

  482.   OPERATOR 4 &gt;= (int2, int8) ,

  483.   OPERATOR 5 &gt; (int2, int8) ,

  484.   FUNCTION 1 btint28cmp(int2, int8) ,

  485. 

  486.   -- cross-type comparisons int2 vs int4

  487.   OPERATOR 1 &lt; (int2, int4) ,

  488.   OPERATOR 2 &lt;= (int2, int4) ,

  489.   OPERATOR 3 = (int2, int4) ,

  490.   OPERATOR 4 &gt;= (int2, int4) ,

  491.   OPERATOR 5 &gt; (int2, int4) ,

  492.   FUNCTION 1 btint24cmp(int2, int4) ,

  493. 

  494.   -- cross-type in_range functions

  495.   FUNCTION 3 in_range(int4, int4, int8, boolean, boolean) ,

  496.   FUNCTION 3 in_range(int4, int4, int2, boolean, boolean) ,

  497.   FUNCTION 3 in_range(int2, int2, int8, boolean, boolean) ,

  498.   FUNCTION 3 in_range(int2, int2, int4, boolean, boolean) ;

  499. 

  500. </pre><p>

  501. 

  502.    Notice that this definition <span class="quote">“<span class="quote">overloads</span>”</span> the operator strategy and

  503.    support function numbers: each number occurs multiple times within the

  504.    family.  This is allowed so long as each instance of a

  505.    particular number has distinct input data types.  The instances that have

  506.    both input types equal to an operator class's input type are the

  507.    primary operators and support functions for that operator class,

  508.    and in most cases should be declared as part of the operator class rather

  509.    than as loose members of the family.

  510.   </p><p>

  511.    In a B-tree operator family, all the operators in the family must sort

  512.    compatibly, as is specified in detail in <a class="xref" href="btree-behavior.html" title="63.2. Behavior of B-Tree Operator Classes">Section 63.2</a>.

  513.    For each

  514.    operator in the family there must be a support function having the same

  515.    two input data types as the operator.  It is recommended that a family be

  516.    complete, i.e., for each combination of data types, all operators are

  517.    included.  Each operator class should include just the non-cross-type

  518.    operators and support function for its data type.

  519.   </p><p>

  520.    To build a multiple-data-type hash operator family, compatible hash

  521.    support functions must be created for each data type supported by the

  522.    family.  Here compatibility means that the functions are guaranteed to

  523.    return the same hash code for any two values that are considered equal

  524.    by the family's equality operators, even when the values are of different

  525.    types.  This is usually difficult to accomplish when the types have

  526.    different physical representations, but it can be done in some cases.

  527.    Furthermore, casting a value from one data type represented in the operator

  528.    family to another data type also represented in the operator family via

  529.    an implicit or binary coercion cast must not change the computed hash value.

  530.    Notice that there is only one support function per data type, not one

  531.    per equality operator.  It is recommended that a family be complete, i.e.,

  532.    provide an equality operator for each combination of data types.

  533.    Each operator class should include just the non-cross-type equality

  534.    operator and the support function for its data type.

  535.   </p><p>

  536.    GiST, SP-GiST, and GIN indexes do not have any explicit notion of

  537.    cross-data-type operations.  The set of operators supported is just

  538.    whatever the primary support functions for a given operator class can

  539.    handle.

  540.   </p><p>

  541.    In BRIN, the requirements depends on the framework that provides the

  542.    operator classes.  For operator classes based on <code class="literal">minmax</code>,

  543.    the behavior required is the same as for B-tree operator families:

  544.    all the operators in the family must sort compatibly, and casts must

  545.    not change the associated sort ordering.

  546.   </p><div class="note"><h3 class="title">Note</h3><p>

  547.     Prior to <span class="productname">PostgreSQL</span> 8.3, there was no concept

  548.     of operator families, and so any cross-data-type operators intended to be

  549.     used with an index had to be bound directly into the index's operator

  550.     class.  While this approach still works, it is deprecated because it

  551.     makes an index's dependencies too broad, and because the planner can

  552.     handle cross-data-type comparisons more effectively when both data types

  553.     have operators in the same operator family.

  554.    </p></div></div><div class="sect2" id="XINDEX-OPCLASS-DEPENDENCIES"><div class="titlepage"><div><div><h3 class="title">37.16.6. System Dependencies on Operator Classes</h3></div></div></div><a id="id-1.8.3.19.10.2" class="indexterm"></a><p>

  555.    <span class="productname">PostgreSQL</span> uses operator classes to infer the

  556.    properties of operators in more ways than just whether they can be used

  557.    with indexes.  Therefore, you might want to create operator classes

  558.    even if you have no intention of indexing any columns of your data type.

  559.   </p><p>

  560.    In particular, there are SQL features such as <code class="literal">ORDER BY</code> and

  561.    <code class="literal">DISTINCT</code> that require comparison and sorting of values.

  562.    To implement these features on a user-defined data type,

  563.    <span class="productname">PostgreSQL</span> looks for the default B-tree operator

  564.    class for the data type.  The <span class="quote">“<span class="quote">equals</span>”</span> member of this operator

  565.    class defines the system's notion of equality of values for

  566.    <code class="literal">GROUP BY</code> and <code class="literal">DISTINCT</code>, and the sort ordering

  567.    imposed by the operator class defines the default <code class="literal">ORDER BY</code>

  568.    ordering.

  569.   </p><p>

  570.    If there is no default B-tree operator class for a data type, the system

  571.    will look for a default hash operator class.  But since that kind of

  572.    operator class only provides equality, it is only able to support grouping

  573.    not sorting.

  574.   </p><p>

  575.    When there is no default operator class for a data type, you will get

  576.    errors like <span class="quote">“<span class="quote">could not identify an ordering operator</span>”</span> if you

  577.    try to use these SQL features with the data type.

  578.   </p><div class="note"><h3 class="title">Note</h3><p>

  579.      In <span class="productname">PostgreSQL</span> versions before 7.4,

  580.      sorting and grouping operations would implicitly use operators named

  581.      <code class="literal">=</code>, <code class="literal">&lt;</code>, and <code class="literal">&gt;</code>.  The new

  582.      behavior of relying on default operator classes avoids having to make

  583.      any assumption about the behavior of operators with particular names.

  584.     </p></div><p>

  585.    Sorting by a non-default B-tree operator class is possible by specifying

  586.    the class's less-than operator in a <code class="literal">USING</code> option,

  587.    for example

  588. </p><pre class="programlisting">

  589. SELECT * FROM mytable ORDER BY somecol USING ~&lt;~;

  590. </pre><p>

  591.    Alternatively, specifying the class's greater-than operator

  592.    in <code class="literal">USING</code> selects a descending-order sort.

  593.   </p><p>

  594.    Comparison of arrays of a user-defined type also relies on the semantics

  595.    defined by the type's default B-tree operator class.  If there is no

  596.    default B-tree operator class, but there is a default hash operator class,

  597.    then array equality is supported, but not ordering comparisons.

  598.   </p><p>

  599.    Another SQL feature that requires even more data-type-specific knowledge

  600.    is the <code class="literal">RANGE</code> <em class="replaceable"><code>offset</code></em>

  601.    <code class="literal">PRECEDING</code>/<code class="literal">FOLLOWING</code> framing option

  602.    for window functions (see <a class="xref" href="sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS" title="4.2.8. Window Function Calls">Section 4.2.8</a>).

  603.    For a query such as

  604. </p><pre class="programlisting">

  605. SELECT sum(x) OVER (ORDER BY x RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING)

  606.   FROM mytable;

  607. </pre><p>

  608.    it is not sufficient to know how to order by <code class="literal">x</code>;

  609.    the database must also understand how to <span class="quote">“<span class="quote">subtract 5</span>”</span> or

  610.    <span class="quote">“<span class="quote">add 10</span>”</span> to the current row's value of <code class="literal">x</code>

  611.    to identify the bounds of the current window frame.  Comparing the

  612.    resulting bounds to other rows' values of <code class="literal">x</code> is

  613.    possible using the comparison operators provided by the B-tree operator

  614.    class that defines the <code class="literal">ORDER BY</code> ordering — but

  615.    addition and subtraction operators are not part of the operator class, so

  616.    which ones should be used?  Hard-wiring that choice would be undesirable,

  617.    because different sort orders (different B-tree operator classes) might

  618.    need different behavior.  Therefore, a B-tree operator class can specify

  619.    an <em class="firstterm">in_range</em> support function that encapsulates the

  620.    addition and subtraction behaviors that make sense for its sort order.

  621.    It can even provide more than one in_range support function, in case

  622.    there is more than one data type that makes sense to use as the offset

  623.    in <code class="literal">RANGE</code> clauses.

  624.    If the B-tree operator class associated with the window's <code class="literal">ORDER

  625.    BY</code> clause does not have a matching in_range support function,

  626.    the <code class="literal">RANGE</code> <em class="replaceable"><code>offset</code></em>

  627.    <code class="literal">PRECEDING</code>/<code class="literal">FOLLOWING</code>

  628.    option is not supported.

  629.   </p><p>

  630.    Another important point is that an equality operator that

  631.    appears in a hash operator family is a candidate for hash joins,

  632.    hash aggregation, and related optimizations.  The hash operator family

  633.    is essential here since it identifies the hash function(s) to use.

  634.   </p></div><div class="sect2" id="XINDEX-ORDERING-OPS"><div class="titlepage"><div><div><h3 class="title">37.16.7. Ordering Operators</h3></div></div></div><p>

  635.    Some index access methods (currently, only GiST and SP-GiST) support the concept of

  636.    <em class="firstterm">ordering operators</em>.  What we have been discussing so far

  637.    are <em class="firstterm">search operators</em>.  A search operator is one for which

  638.    the index can be searched to find all rows satisfying

  639.    <code class="literal">WHERE</code>

  640.    <em class="replaceable"><code>indexed_column</code></em>

  641.    <em class="replaceable"><code>operator</code></em>

  642.    <em class="replaceable"><code>constant</code></em>.

  643.    Note that nothing is promised about the order in which the matching rows

  644.    will be returned.  In contrast, an ordering operator does not restrict the

  645.    set of rows that can be returned, but instead determines their order.

  646.    An ordering operator is one for which the index can be scanned to return

  647.    rows in the order represented by

  648.    <code class="literal">ORDER BY</code>

  649.    <em class="replaceable"><code>indexed_column</code></em>

  650.    <em class="replaceable"><code>operator</code></em>

  651.    <em class="replaceable"><code>constant</code></em>.

  652.    The reason for defining ordering operators that way is that it supports

  653.    nearest-neighbor searches, if the operator is one that measures distance.

  654.    For example, a query like

  655. </p><pre class="programlisting">

  656. SELECT * FROM places ORDER BY location &lt;-&gt; point '(101,456)' LIMIT 10;

  657. 

  658. </pre><p>

  659.    finds the ten places closest to a given target point.  A GiST index

  660.    on the location column can do this efficiently because

  661.    <code class="literal">&lt;-&gt;</code> is an ordering operator.

  662.   </p><p>

  663.    While search operators have to return Boolean results, ordering operators

  664.    usually return some other type, such as float or numeric for distances.

  665.    This type is normally not the same as the data type being indexed.

  666.    To avoid hard-wiring assumptions about the behavior of different data

  667.    types, the definition of an ordering operator is required to name

  668.    a B-tree operator family that specifies the sort ordering of the result

  669.    data type.  As was stated in the previous section, B-tree operator families

  670.    define <span class="productname">PostgreSQL</span>'s notion of ordering, so

  671.    this is a natural representation.  Since the point <code class="literal">&lt;-&gt;</code>

  672.    operator returns <code class="type">float8</code>, it could be specified in an operator

  673.    class creation command like this:

  674. </p><pre class="programlisting">

  675. OPERATOR 15    &lt;-&gt; (point, point) FOR ORDER BY float_ops

  676. 

  677. </pre><p>

  678.    where <code class="literal">float_ops</code> is the built-in operator family that includes

  679.    operations on <code class="type">float8</code>.  This declaration states that the index

  680.    is able to return rows in order of increasing values of the

  681.    <code class="literal">&lt;-&gt;</code> operator.

  682.   </p></div><div class="sect2" id="XINDEX-OPCLASS-FEATURES"><div class="titlepage"><div><div><h3 class="title">37.16.8. Special Features of Operator Classes</h3></div></div></div><p>

  683.    There are two special features of operator classes that we have

  684.    not discussed yet, mainly because they are not useful

  685.    with the most commonly used index methods.

  686.   </p><p>

  687.    Normally, declaring an operator as a member of an operator class

  688.    (or family) means that the index method can retrieve exactly the set of rows

  689.    that satisfy a <code class="literal">WHERE</code> condition using the operator.  For example:

  690. </p><pre class="programlisting">

  691. SELECT * FROM table WHERE integer_column &lt; 4;

  692. </pre><p>

  693.    can be satisfied exactly by a B-tree index on the integer column.

  694.    But there are cases where an index is useful as an inexact guide to

  695.    the matching rows.  For example, if a GiST index stores only bounding boxes

  696.    for geometric objects, then it cannot exactly satisfy a <code class="literal">WHERE</code>

  697.    condition that tests overlap between nonrectangular objects such as

  698.    polygons.  Yet we could use the index to find objects whose bounding

  699.    box overlaps the bounding box of the target object, and then do the

  700.    exact overlap test only on the objects found by the index.  If this

  701.    scenario applies, the index is said to be <span class="quote">“<span class="quote">lossy</span>”</span> for the

  702.    operator.  Lossy index searches are implemented by having the index

  703.    method return a <em class="firstterm">recheck</em> flag when a row might or might

  704.    not really satisfy the query condition.  The core system will then

  705.    test the original query condition on the retrieved row to see whether

  706.    it should be returned as a valid match.  This approach works if

  707.    the index is guaranteed to return all the required rows, plus perhaps

  708.    some additional rows, which can be eliminated by performing the original

  709.    operator invocation.  The index methods that support lossy searches

  710.    (currently, GiST, SP-GiST and GIN) allow the support functions of individual

  711.    operator classes to set the recheck flag, and so this is essentially an

  712.    operator-class feature.

  713.   </p><p>

  714.    Consider again the situation where we are storing in the index only

  715.    the bounding box of a complex object such as a polygon.  In this

  716.    case there's not much value in storing the whole polygon in the index

  717.    entry — we might as well store just a simpler object of type

  718.    <code class="type">box</code>.  This situation is expressed by the <code class="literal">STORAGE</code>

  719.    option in <code class="command">CREATE OPERATOR CLASS</code>: we'd write something like:

  720. 

  721. </p><pre class="programlisting">

  722. CREATE OPERATOR CLASS polygon_ops

  723.     DEFAULT FOR TYPE polygon USING gist AS

  724.         ...

  725.         STORAGE box;

  726. </pre><p>

  727. 

  728.    At present, only the GiST, GIN and BRIN index methods support a

  729.    <code class="literal">STORAGE</code> type that's different from the column data type.

  730.    The GiST <code class="function">compress</code> and <code class="function">decompress</code> support

  731.    routines must deal with data-type conversion when <code class="literal">STORAGE</code>

  732.    is used.  In GIN, the <code class="literal">STORAGE</code> type identifies the type of

  733.    the <span class="quote">“<span class="quote">key</span>”</span> values, which normally is different from the type

  734.    of the indexed column — for example, an operator class for

  735.    integer-array columns might have keys that are just integers.  The

  736.    GIN <code class="function">extractValue</code> and <code class="function">extractQuery</code> support

  737.    routines are responsible for extracting keys from indexed values.

  738.    BRIN is similar to GIN: the <code class="literal">STORAGE</code> type identifies the

  739.    type of the stored summary values, and operator classes' support

  740.    procedures are responsible for interpreting the summary values

  741.    correctly.

  742.   </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="xoper-optimization.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="extend.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="extend-extensions.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">37.15. Operator Optimization Information </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 37.17. Packaging Related Objects into an Extension</td></tr></table></div></body></html>
上海开阖软件有限公司 沪ICP备12045867号-1