doc/src/sgml/xindex.sgml

   1 <!-- doc/src/sgml/xindex.sgml -->
   2
   3 <sect1 id="xindex">
   4  <title>Interfacing Extensions to Indexes</title>
   5
   6  <indexterm zone="xindex">
   7   <primary>index</primary>
   8   <secondary>for user-defined data type</secondary>
   9  </indexterm>
  10
  11   <para>
  12    The procedures described thus far let you define new types, new
  13    functions, and new operators. However, we cannot yet define an
  14    index on a column of a new data type.  To do this, we must define an
  15    <firstterm>operator class</firstterm> for the new data type.  Later in this
  16    section, we will illustrate this concept in an example: a new
  17    operator class for the B-tree index method that stores and sorts
  18    complex numbers in ascending absolute value order.
  19   </para>
  20
  21   <para>
  22    Operator classes can be grouped into <firstterm>operator families</firstterm>
  23    to show the relationships between semantically compatible classes.
  24    When only a single data type is involved, an operator class is sufficient,
  25    so we'll focus on that case first and then return to operator families.
  26   </para>
  27
  28  <sect2 id="xindex-opclass">
  29   <title>Index Methods and Operator Classes</title>
  30
  31   <para>
  32    Operator classes are associated with an index access method, such
  33    as <link linkend="btree">B-Tree</link>
  34    or <link linkend="gin">GIN</link>. Custom index access method may be
  35    defined with <xref linkend="sql-create-access-method"/>. See
  36    <xref linkend="indexam"/> for details.
  37   </para>
  38
  39   <para>
  40    The routines for an index method do not directly know anything
  41    about the data types that the index method will operate on.
  42    Instead, an <firstterm>operator
  43    class</firstterm><indexterm><primary>operator class</primary></indexterm>
  44    identifies the set of operations that the index method needs to use
  45    to work with a particular data type.  Operator classes are so
  46    called because one thing they specify is the set of
  47    <literal>WHERE</literal>-clause operators that can be used with an index
  48    (i.e., can be converted into an index-scan qualification).  An
  49    operator class can also specify some <firstterm>support
  50    function</firstterm> that are needed by the internal operations of the
  51    index method, but do not directly correspond to any
  52    <literal>WHERE</literal>-clause operator that can be used with the index.
  53   </para>
  54
  55   <para>
  56    It is possible to define multiple operator classes for the same
  57    data type and index method.  By doing this, multiple
  58    sets of indexing semantics can be defined for a single data type.
  59    For example, a B-tree index requires a sort ordering to be defined
  60    for each data type it works on.
  61    It might be useful for a complex-number data type
  62    to have one B-tree operator class that sorts the data by complex
  63    absolute value, another that sorts by real part, and so on.
  64    Typically, one of the operator classes will be deemed most commonly
  65    useful and will be marked as the default operator class for that
  66    data type and index method.
  67   </para>
  68
  69   <para>
  70    The same operator class name
  71    can be used for several different index methods (for example, both B-tree
  72    and hash index methods have operator classes named
  73    <literal>int4_ops</literal>), but each such class is an independent
  74    entity and must be defined separately.
  75   </para>
  76  </sect2>
  77
  78  <sect2 id="xindex-strategies">
  79   <title>Index Method Strategies</title>
  80
  81   <para>
  82    The operators associated with an operator class are identified by
  83    <quote>strategy numbers</quote>, which serve to identify the semantics of
  84    each operator within the context of its operator class.
  85    For example, B-trees impose a strict ordering on keys, lesser to greater,
  86    and so operators like <quote>less than</quote> and <quote>greater than or equal
  87    to</quote> are interesting with respect to a B-tree.
  88    Because
  89    <productname>PostgreSQL</productname> allows the user to define operators,
  90    <productname>PostgreSQL</productname> cannot look at the name of an operator
  91    (e.g., <literal>&lt;</literal> or <literal>&gt;=</literal>) and tell what kind of
  92    comparison it is.  Instead, the index method defines a set of
  93    <quote>strategies</quote>, which can be thought of as generalized operators.
  94    Each operator class specifies which actual operator corresponds to each
  95    strategy for a particular data type and interpretation of the index
  96    semantics.
  97   </para>
  98
  99   <para>
 100    The B-tree index method defines five strategies, shown in <xref
 101    linkend="xindex-btree-strat-table"/>.
 102   </para>
 103
 104    <table tocentry="1" id="xindex-btree-strat-table">
 105     <title>B-Tree Strategies</title>
 106     <tgroup cols="2">
 107      <thead>
 108       <row>
 109        <entry>Operation</entry>
 110        <entry>Strategy Number</entry>
 111       </row>
 112      </thead>
 113      <tbody>
 114       <row>
 115        <entry>less than</entry>
 116        <entry>1</entry>
 117       </row>
 118       <row>
 119        <entry>less than or equal</entry>
 120        <entry>2</entry>
 121       </row>
 122       <row>
 123        <entry>equal</entry>
 124        <entry>3</entry>
 125       </row>
 126       <row>
 127        <entry>greater than or equal</entry>
 128        <entry>4</entry>
 129       </row>
 130       <row>
 131        <entry>greater than</entry>
 132        <entry>5</entry>
 133       </row>
 134      </tbody>
 135     </tgroup>
 136    </table>
 137
 138   <para>
 139    Hash indexes support only equality comparisons, and so they use only one
 140    strategy, shown in <xref linkend="xindex-hash-strat-table"/>.
 141   </para>
 142
 143    <table tocentry="1" id="xindex-hash-strat-table">
 144     <title>Hash Strategies</title>
 145     <tgroup cols="2">
 146      <thead>
 147       <row>
 148        <entry>Operation</entry>
 149        <entry>Strategy Number</entry>
 150       </row>
 151      </thead>
 152      <tbody>
 153       <row>
 154        <entry>equal</entry>
 155        <entry>1</entry>
 156       </row>
 157      </tbody>
 158     </tgroup>
 159    </table>
 160
 161   <para>
 162    GiST indexes are more flexible: they do not have a fixed set of
 163    strategies at all.  Instead, the <quote>consistency</quote> support routine
 164    of each particular GiST operator class interprets the strategy numbers
 165    however it likes.  As an example, several of the built-in GiST index
 166    operator classes index two-dimensional geometric objects, providing
 167    the <quote>R-tree</quote> strategies shown in
 168    <xref linkend="xindex-rtree-strat-table"/>.  Four of these are true
 169    two-dimensional tests (overlaps, same, contains, contained by);
 170    four of them consider only the X direction; and the other four
 171    provide the same tests in the Y direction.
 172   </para>
 173
 174    <table tocentry="1" id="xindex-rtree-strat-table">
 175     <title>GiST Two-Dimensional <quote>R-tree</quote> Strategies</title>
 176     <tgroup cols="2">
 177      <thead>
 178       <row>
 179        <entry>Operation</entry>
 180        <entry>Strategy Number</entry>
 181       </row>
 182      </thead>
 183      <tbody>
 184       <row>
 185        <entry>strictly left of</entry>
 186        <entry>1</entry>
 187       </row>
 188       <row>
 189        <entry>does not extend to right of</entry>
 190        <entry>2</entry>
 191       </row>
 192       <row>
 193        <entry>overlaps</entry>
 194        <entry>3</entry>
 195       </row>
 196       <row>
 197        <entry>does not extend to left of</entry>
 198        <entry>4</entry>
 199       </row>
 200       <row>
 201        <entry>strictly right of</entry>
 202        <entry>5</entry>
 203       </row>
 204       <row>
 205        <entry>same</entry>
 206        <entry>6</entry>
 207       </row>
 208       <row>
 209        <entry>contains</entry>
 210        <entry>7</entry>
 211       </row>
 212       <row>
 213        <entry>contained by</entry>
 214        <entry>8</entry>
 215       </row>
 216       <row>
 217        <entry>does not extend above</entry>
 218        <entry>9</entry>
 219       </row>
 220       <row>
 221        <entry>strictly below</entry>
 222        <entry>10</entry>
 223       </row>
 224       <row>
 225        <entry>strictly above</entry>
 226        <entry>11</entry>
 227       </row>
 228       <row>
 229        <entry>does not extend below</entry>
 230        <entry>12</entry>
 231       </row>
 232      </tbody>
 233     </tgroup>
 234    </table>
 235
 236   <para>
 237    SP-GiST indexes are similar to GiST indexes in flexibility: they don't have
 238    a fixed set of strategies. Instead the support routines of each operator
 239    class interpret the strategy numbers according to the operator class's
 240    definition. As an example, the strategy numbers used by the built-in
 241    operator classes for points are shown in <xref
 242    linkend="xindex-spgist-point-strat-table"/>.
 243   </para>
 244
 245    <table tocentry="1" id="xindex-spgist-point-strat-table">
 246     <title>SP-GiST Point Strategies</title>
 247     <tgroup cols="2">
 248      <thead>
 249       <row>
 250        <entry>Operation</entry>
 251        <entry>Strategy Number</entry>
 252       </row>
 253      </thead>
 254      <tbody>
 255       <row>
 256        <entry>strictly left of</entry>
 257        <entry>1</entry>
 258       </row>
 259       <row>
 260        <entry>strictly right of</entry>
 261        <entry>5</entry>
 262       </row>
 263       <row>
 264        <entry>same</entry>
 265        <entry>6</entry>
 266       </row>
 267       <row>
 268        <entry>contained by</entry>
 269        <entry>8</entry>
 270       </row>
 271       <row>
 272        <entry>strictly below</entry>
 273        <entry>10</entry>
 274       </row>
 275       <row>
 276        <entry>strictly above</entry>
 277        <entry>11</entry>
 278       </row>
 279      </tbody>
 280     </tgroup>
 281    </table>
 282
 283   <para>
 284    GIN indexes are similar to GiST and SP-GiST indexes, in that they don't
 285    have a fixed set of strategies either. Instead the support routines of
 286    each operator class interpret the strategy numbers according to the
 287    operator class's definition. As an example, the strategy numbers used by
 288    the built-in operator class for arrays are shown in
 289    <xref linkend="xindex-gin-array-strat-table"/>.
 290   </para>
 291
 292    <table tocentry="1" id="xindex-gin-array-strat-table">
 293     <title>GIN Array Strategies</title>
 294     <tgroup cols="2">
 295      <thead>
 296       <row>
 297        <entry>Operation</entry>
 298        <entry>Strategy Number</entry>
 299       </row>
 300      </thead>
 301      <tbody>
 302       <row>
 303        <entry>overlap</entry>
 304        <entry>1</entry>
 305       </row>
 306       <row>
 307        <entry>contains</entry>
 308        <entry>2</entry>
 309       </row>
 310       <row>
 311        <entry>is contained by</entry>
 312        <entry>3</entry>
 313       </row>
 314       <row>
 315        <entry>equal</entry>
 316        <entry>4</entry>
 317       </row>
 318      </tbody>
 319     </tgroup>
 320    </table>
 321
 322   <para>
 323    BRIN indexes are similar to GiST, SP-GiST and GIN indexes in that they
 324    don't have a fixed set of strategies either.  Instead the support routines
 325    of each operator class interpret the strategy numbers according to the
 326    operator class's definition. As an example, the strategy numbers used by
 327    the built-in <literal>Minmax</literal> operator classes are shown in
 328    <xref linkend="xindex-brin-minmax-strat-table"/>.
 329   </para>
 330
 331    <table tocentry="1" id="xindex-brin-minmax-strat-table">
 332     <title>BRIN Minmax Strategies</title>
 333     <tgroup cols="2">
 334      <thead>
 335       <row>
 336        <entry>Operation</entry>
 337        <entry>Strategy Number</entry>
 338       </row>
 339      </thead>
 340      <tbody>
 341       <row>
 342        <entry>less than</entry>
 343        <entry>1</entry>
 344       </row>
 345       <row>
 346        <entry>less than or equal</entry>
 347        <entry>2</entry>
 348       </row>
 349       <row>
 350        <entry>equal</entry>
 351        <entry>3</entry>
 352       </row>
 353       <row>
 354        <entry>greater than or equal</entry>
 355        <entry>4</entry>
 356       </row>
 357       <row>
 358        <entry>greater than</entry>
 359        <entry>5</entry>
 360       </row>
 361      </tbody>
 362     </tgroup>
 363    </table>
 364
 365   <para>
 366    Notice that all the operators listed above return Boolean values.  In
 367    practice, all operators defined as index method search operators must
 368    return type <type>boolean</type>, since they must appear at the top
 369    level of a <literal>WHERE</literal> clause to be used with an index.
 370    (Some index access methods also support <firstterm>ordering operators</firstterm>,
 371    which typically don't return Boolean values; that feature is discussed
 372    in <xref linkend="xindex-ordering-ops"/>.)
 373   </para>
 374  </sect2>
 375
 376  <sect2 id="xindex-support">
 377   <title>Index Method Support Routines</title>
 378
 379   <para>
 380    Strategies aren't usually enough information for the system to figure
 381    out how to use an index.  In practice, the index methods require
 382    additional support routines in order to work. For example, the B-tree
 383    index method must be able to compare two keys and determine whether one
 384    is greater than, equal to, or less than the other.  Similarly, the
 385    hash index method must be able to compute hash codes for key values.
 386    These operations do not correspond to operators used in qualifications in
 387    SQL commands;  they are administrative routines used by
 388    the index methods, internally.
 389   </para>
 390
 391   <para>
 392    Just as with strategies, the operator class identifies which specific
 393    functions should play each of these roles for a given data type and
 394    semantic interpretation.  The index method defines the set
 395    of functions it needs, and the operator class identifies the correct
 396    functions to use by assigning them to the <quote>support function numbers</quote>
 397    specified by the index method.
 398   </para>
 399
 400   <para>
 401    Additionally, some opclasses allow users to specify parameters which
 402    control their behavior.  Each builtin index access method has an optional
 403    <function>options</function> support function, which defines a set of
 404    opclass-specific parameters.
 405   </para>
 406
 407   <para>
 408    B-trees require a comparison support function,
 409    and allow four additional support functions to be
 410    supplied at the operator class author's option, as shown in <xref
 411    linkend="xindex-btree-support-table"/>.
 412    The requirements for these support functions are explained further in
 413    <xref linkend="btree-support-funcs"/>.
 414   </para>
 415
 416    <table tocentry="1" id="xindex-btree-support-table">
 417     <title>B-Tree Support Functions</title>
 418     <tgroup cols="2">
 419      <colspec colname="col1" colwidth="3*"/>
 420      <colspec colname="col2" colwidth="1*"/>
 421      <thead>
 422       <row>
 423        <entry>Function</entry>
 424        <entry>Support Number</entry>
 425       </row>
 426      </thead>
 427      <tbody>
 428       <row>
 429        <entry>
 430         Compare two keys and return an integer less than zero, zero, or
 431         greater than zero, indicating whether the first key is less than,
 432         equal to, or greater than the second
 433        </entry>
 434        <entry>1</entry>
 435       </row>
 436       <row>
 437        <entry>
 438         Return the addresses of C-callable sort support function(s)
 439         (optional)
 440        </entry>
 441        <entry>2</entry>
 442       </row>
 443       <row>
 444        <entry>
 445         Compare a test value to a base value plus/minus an offset, and return
 446         true or false according to the comparison result (optional)
 447        </entry>
 448        <entry>3</entry>
 449       </row>
 450       <row>
 451        <entry>
 452         Determine if it is safe for indexes that use the operator
 453         class to apply the btree deduplication optimization (optional)
 454        </entry>
 455        <entry>4</entry>
 456       </row>
 457       <row>
 458        <entry>
 459         Define options that are specific to this operator class
 460         (optional)
 461        </entry>
 462        <entry>5</entry>
 463       </row>
 464      </tbody>
 465     </tgroup>
 466    </table>
 467
 468   <para>
 469    Hash indexes require one support function, and allow two additional ones to
 470    be supplied at the operator class author's option, as shown in <xref
 471    linkend="xindex-hash-support-table"/>.
 472   </para>
 473
 474    <table tocentry="1" id="xindex-hash-support-table">
 475     <title>Hash Support Functions</title>
 476     <tgroup cols="2">
 477      <colspec colname="col1" colwidth="3*"/>
 478      <colspec colname="col2" colwidth="1*"/>
 479      <thead>
 480       <row>
 481        <entry>Function</entry>
 482        <entry>Support Number</entry>
 483       </row>
 484      </thead>
 485      <tbody>
 486       <row>
 487        <entry>Compute the 32-bit hash value for a key</entry>
 488        <entry>1</entry>
 489       </row>
 490       <row>
 491        <entry>
 492          Compute the 64-bit hash value for a key given a 64-bit salt; if
 493          the salt is 0, the low 32 bits of the result must match the value
 494          that would have been computed by function 1
 495          (optional)
 496        </entry>
 497        <entry>2</entry>
 498       </row>
 499       <row>
 500        <entry>
 501         Define options that are specific to this operator class
 502         (optional)
 503        </entry>
 504        <entry>3</entry>
 505       </row>
 506      </tbody>
 507     </tgroup>
 508    </table>
 509
 510   <para>
 511    GiST indexes have eleven support functions, six of which are optional,
 512    as shown in <xref linkend="xindex-gist-support-table"/>.
 513    (For more information see <xref linkend="gist"/>.)
 514   </para>
 515
 516    <table tocentry="1" id="xindex-gist-support-table">
 517     <title>GiST Support Functions</title>
 518     <tgroup cols="3">
 519      <colspec colname="col1" colwidth="2*"/>
 520      <colspec colname="col2" colwidth="3*"/>
 521      <colspec colname="col3" colwidth="1*"/>
 522      <thead>
 523       <row>
 524        <entry>Function</entry>
 525        <entry>Description</entry>
 526        <entry>Support Number</entry>
 527       </row>
 528      </thead>
 529      <tbody>
 530       <row>
 531        <entry><function>consistent</function></entry>
 532        <entry>determine whether key satisfies the
 533         query qualifier</entry>
 534        <entry>1</entry>
 535       </row>
 536       <row>
 537        <entry><function>union</function></entry>
 538        <entry>compute union of a set of keys</entry>
 539        <entry>2</entry>
 540       </row>
 541       <row>
 542        <entry><function>compress</function></entry>
 543        <entry>compute a compressed representation of a key or value
 544         to be indexed (optional)</entry>
 545        <entry>3</entry>
 546       </row>
 547       <row>
 548        <entry><function>decompress</function></entry>
 549        <entry>compute a decompressed representation of a
 550         compressed key (optional)</entry>
 551        <entry>4</entry>
 552       </row>
 553       <row>
 554        <entry><function>penalty</function></entry>
 555        <entry>compute penalty for inserting new key into subtree
 556        with given subtree's key</entry>
 557        <entry>5</entry>
 558       </row>
 559       <row>
 560        <entry><function>picksplit</function></entry>
 561        <entry>determine which entries of a page are to be moved
 562        to the new page and compute the union keys for resulting pages</entry>
 563        <entry>6</entry>
 564       </row>
 565       <row>
 566        <entry><function>same</function></entry>
 567        <entry>compare two keys and return true if they are equal</entry>
 568        <entry>7</entry>
 569       </row>
 570       <row>
 571        <entry><function>distance</function></entry>
 572        <entry>determine distance from key to query value (optional)</entry>
 573        <entry>8</entry>
 574       </row>
 575       <row>
 576        <entry><function>fetch</function></entry>
 577        <entry>compute original representation of a compressed key for
 578        index-only scans (optional)</entry>
 579        <entry>9</entry>
 580       </row>
 581       <row>
 582        <entry><function>options</function></entry>
 583        <entry>define options that are specific to this operator class
 584         (optional)</entry>
 585        <entry>10</entry>
 586       </row>
 587       <row>
 588        <entry><function>sortsupport</function></entry>
 589        <entry>provide a sort comparator to be used in fast index builds
 590         (optional)</entry>
 591        <entry>11</entry>
 592       </row>
 593      </tbody>
 594     </tgroup>
 595    </table>
 596
 597   <para>
 598    SP-GiST indexes have six support functions, one of which is optional, as
 599    shown in <xref linkend="xindex-spgist-support-table"/>.
 600    (For more information see <xref linkend="spgist"/>.)
 601   </para>
 602
 603    <table tocentry="1" id="xindex-spgist-support-table">
 604     <title>SP-GiST Support Functions</title>
 605     <tgroup cols="3">
 606      <colspec colname="col1" colwidth="2*"/>
 607      <colspec colname="col2" colwidth="3*"/>
 608      <colspec colname="col3" colwidth="1*"/>
 609      <thead>
 610       <row>
 611        <entry>Function</entry>
 612        <entry>Description</entry>
 613        <entry>Support Number</entry>
 614       </row>
 615      </thead>
 616      <tbody>
 617       <row>
 618        <entry><function>config</function></entry>
 619        <entry>provide basic information about the operator class</entry>
 620        <entry>1</entry>
 621       </row>
 622       <row>
 623        <entry><function>choose</function></entry>
 624        <entry>determine how to insert a new value into an inner tuple</entry>
 625        <entry>2</entry>
 626       </row>
 627       <row>
 628        <entry><function>picksplit</function></entry>
 629        <entry>determine how to partition a set of values</entry>
 630        <entry>3</entry>
 631       </row>
 632       <row>
 633        <entry><function>inner_consistent</function></entry>
 634        <entry>determine which sub-partitions need to be searched for a
 635         query</entry>
 636        <entry>4</entry>
 637       </row>
 638       <row>
 639        <entry><function>leaf_consistent</function></entry>
 640        <entry>determine whether key satisfies the
 641         query qualifier</entry>
 642        <entry>5</entry>
 643       </row>
 644       <row>
 645        <entry><function>options</function></entry>
 646        <entry>define options that are specific to this operator class
 647         (optional)</entry>
 648        <entry>6</entry>
 649       </row>
 650      </tbody>
 651     </tgroup>
 652    </table>
 653
 654   <para>
 655    GIN indexes have seven support functions, four of which are optional,
 656    as shown in <xref linkend="xindex-gin-support-table"/>.
 657    (For more information see <xref linkend="gin"/>.)
 658   </para>
 659
 660    <table tocentry="1" id="xindex-gin-support-table">
 661     <title>GIN Support Functions</title>
 662     <tgroup cols="3">
 663      <colspec colname="col1" colwidth="2*"/>
 664      <colspec colname="col2" colwidth="3*"/>
 665      <colspec colname="col3" colwidth="1*"/>
 666      <thead>
 667       <row>
 668        <entry>Function</entry>
 669        <entry>Description</entry>
 670        <entry>Support Number</entry>
 671       </row>
 672      </thead>
 673      <tbody>
 674       <row>
 675        <entry><function>compare</function></entry>
 676        <entry>
 677         compare two keys and return an integer less than zero, zero,
 678         or greater than zero, indicating whether the first key is less than,
 679         equal to, or greater than the second
 680        </entry>
 681        <entry>1</entry>
 682       </row>
 683       <row>
 684        <entry><function>extractValue</function></entry>
 685        <entry>extract keys from a value to be indexed</entry>
 686        <entry>2</entry>
 687       </row>
 688       <row>
 689        <entry><function>extractQuery</function></entry>
 690        <entry>extract keys from a query condition</entry>
 691        <entry>3</entry>
 692       </row>
 693       <row>
 694        <entry><function>consistent</function></entry>
 695        <entry>
 696         determine whether value matches query condition (Boolean variant)
 697         (optional if support function 6 is present)
 698        </entry>
 699        <entry>4</entry>
 700       </row>
 701       <row>
 702        <entry><function>comparePartial</function></entry>
 703        <entry>
 704         compare partial key from
 705         query and key from index, and return an integer less than zero, zero,
 706         or greater than zero, indicating whether GIN should ignore this index
 707         entry, treat the entry as a match, or stop the index scan (optional)
 708        </entry>
 709        <entry>5</entry>
 710       </row>
 711       <row>
 712        <entry><function>triConsistent</function></entry>
 713        <entry>
 714         determine whether value matches query condition (ternary variant)
 715         (optional if support function 4 is present)
 716        </entry>
 717        <entry>6</entry>
 718       </row>
 719       <row>
 720        <entry><function>options</function></entry>
 721        <entry>
 722         define options that are specific to this operator class
 723         (optional)
 724        </entry>
 725        <entry>7</entry>
 726       </row>
 727      </tbody>
 728     </tgroup>
 729    </table>
 730
 731   <para>
 732    BRIN indexes have five basic support functions, one of which is optional,
 733    as shown in <xref linkend="xindex-brin-support-table"/>.  Some versions of
 734    the basic functions require additional support functions to be provided.
 735    (For more information see <xref linkend="brin-extensibility"/>.)
 736   </para>
 737
 738    <table tocentry="1" id="xindex-brin-support-table">
 739     <title>BRIN Support Functions</title>
 740     <tgroup cols="3">
 741      <colspec colname="col1" colwidth="2*"/>
 742      <colspec colname="col2" colwidth="3*"/>
 743      <colspec colname="col3" colwidth="1*"/>
 744      <thead>
 745       <row>
 746        <entry>Function</entry>
 747        <entry>Description</entry>
 748        <entry>Support Number</entry>
 749       </row>
 750      </thead>
 751      <tbody>
 752       <row>
 753        <entry><function>opcInfo</function></entry>
 754        <entry>
 755         return internal information describing the indexed columns'
 756         summary data
 757        </entry>
 758        <entry>1</entry>
 759       </row>
 760       <row>
 761        <entry><function>add_value</function></entry>
 762        <entry>add a new value to an existing summary index tuple</entry>
 763        <entry>2</entry>
 764       </row>
 765       <row>
 766        <entry><function>consistent</function></entry>
 767        <entry>determine whether value matches query condition</entry>
 768        <entry>3</entry>
 769       </row>
 770       <row>
 771        <entry><function>union</function></entry>
 772        <entry>
 773         compute union of two summary tuples
 774        </entry>
 775        <entry>4</entry>
 776       </row>
 777       <row>
 778        <entry><function>options</function></entry>
 779        <entry>
 780         define options that are specific to this operator class
 781         (optional)
 782        </entry>
 783        <entry>5</entry>
 784       </row>
 785      </tbody>
 786     </tgroup>
 787    </table>
 788
 789   <para>
 790    Unlike search operators, support functions return whichever data
 791    type the particular index method expects; for example in the case
 792    of the comparison function for B-trees, a signed integer.  The number
 793    and types of the arguments to each support function are likewise
 794    dependent on the index method.  For B-tree and hash the comparison and
 795    hashing support functions take the same input data types as do the
 796    operators included in the operator class, but this is not the case for
 797    most GiST, SP-GiST, GIN, and BRIN support functions.
 798   </para>
 799  </sect2>
 800
 801  <sect2 id="xindex-example">
 802   <title>An Example</title>
 803
 804   <para>
 805    Now that we have seen the ideas, here is the promised example of
 806    creating a new operator class.
 807    (You can find a working copy of this example in
 808    <filename>src/tutorial/complex.c</filename> and
 809    <filename>src/tutorial/complex.sql</filename> in the source
 810    distribution.)
 811    The operator class encapsulates
 812    operators that sort complex numbers in absolute value order, so we
 813    choose the name <literal>complex_abs_ops</literal>.  First, we need
 814    a set of operators.  The procedure for defining operators was
 815    discussed in <xref linkend="xoper"/>.  For an operator class on
 816    B-trees, the operators we require are:
 817
 818    <itemizedlist spacing="compact">
 819     <listitem><simpara>absolute-value less-than (strategy 1)</simpara></listitem>
 820     <listitem><simpara>absolute-value less-than-or-equal (strategy 2)</simpara></listitem>
 821     <listitem><simpara>absolute-value equal (strategy 3)</simpara></listitem>
 822     <listitem><simpara>absolute-value greater-than-or-equal (strategy 4)</simpara></listitem>
 823     <listitem><simpara>absolute-value greater-than (strategy 5)</simpara></listitem>
 824    </itemizedlist>
 825   </para>
 826
 827   <para>
 828    The least error-prone way to define a related set of comparison operators
 829    is to write the B-tree comparison support function first, and then write the
 830    other functions as one-line wrappers around the support function.  This
 831    reduces the odds of getting inconsistent results for corner cases.
 832    Following this approach, we first write:
 833
 834 <programlisting><![CDATA[
 835 #define Mag(c)  ((c)->x*(c)->x + (c)->y*(c)->y)
 836
 837 static int
 838 complex_abs_cmp_internal(Complex *a, Complex *b)
 839 {
 840     double      amag = Mag(a),
 841                 bmag = Mag(b);
 842
 843     if (amag < bmag)
 844         return -1;
 845     if (amag > bmag)
 846         return 1;
 847     return 0;
 848 }
 849 ]]>
 850 </programlisting>
 851
 852    Now the less-than function looks like:
 853
 854 <programlisting><![CDATA[
 855 PG_FUNCTION_INFO_V1(complex_abs_lt);
 856
 857 Datum
 858 complex_abs_lt(PG_FUNCTION_ARGS)
 859 {
 860     Complex    *a = (Complex *) PG_GETARG_POINTER(0);
 861     Complex    *b = (Complex *) PG_GETARG_POINTER(1);
 862
 863     PG_RETURN_BOOL(complex_abs_cmp_internal(a, b) < 0);
 864 }
 865 ]]>
 866 </programlisting>
 867
 868    The other four functions differ only in how they compare the internal
 869    function's result to zero.
 870   </para>
 871
 872   <para>
 873    Next we declare the functions and the operators based on the functions
 874    to SQL:
 875
 876 <programlisting>
 877 CREATE FUNCTION complex_abs_lt(complex, complex) RETURNS bool
 878     AS '<replaceable>filename</replaceable>', 'complex_abs_lt'
 879     LANGUAGE C IMMUTABLE STRICT;
 880
 881 CREATE OPERATOR &lt; (
 882    leftarg = complex, rightarg = complex, procedure = complex_abs_lt,
 883    commutator = &gt; , negator = &gt;= ,
 884    restrict = scalarltsel, join = scalarltjoinsel
 885 );
 886 </programlisting>
 887    It is important to specify the correct commutator and negator operators,
 888    as well as suitable restriction and join selectivity
 889    functions, otherwise the optimizer will be unable to make effective
 890    use of the index.
 891   </para>
 892
 893   <para>
 894    Other things worth noting are happening here:
 895
 896   <itemizedlist>
 897    <listitem>
 898     <para>
 899      There can only be one operator named, say, <literal>=</literal>
 900      and taking type <type>complex</type> for both operands.  In this
 901      case we don't have any other operator <literal>=</literal> for
 902      <type>complex</type>, but if we were building a practical data
 903      type we'd probably want <literal>=</literal> to be the ordinary
 904      equality operation for complex numbers (and not the equality of
 905      the absolute values).  In that case, we'd need to use some other
 906      operator name for <function>complex_abs_eq</function>.
 907     </para>
 908    </listitem>
 909
 910    <listitem>
 911     <para>
 912      Although <productname>PostgreSQL</productname> can cope with
 913      functions having the same SQL name as long as they have different
 914      argument data types, C can only cope with one global function
 915      having a given name.  So we shouldn't name the C function
 916      something simple like <filename>abs_eq</filename>.  Usually it's
 917      a good practice to include the data type name in the C function
 918      name, so as not to conflict with functions for other data types.
 919     </para>
 920    </listitem>
 921
 922    <listitem>
 923     <para>
 924      We could have made the SQL name
 925      of the function <filename>abs_eq</filename>, relying on
 926      <productname>PostgreSQL</productname> to distinguish it by
 927      argument data types from any other SQL function of the same name.
 928      To keep the example simple, we make the function have the same
 929      names at the C level and SQL level.
 930     </para>
 931    </listitem>
 932   </itemizedlist>
 933   </para>
 934
 935   <para>
 936    The next step is the registration of the support routine required
 937    by B-trees.  The example C code that implements this is in the same
 938    file that contains the operator functions.  This is how we declare
 939    the function:
 940
 941 <programlisting>
 942 CREATE FUNCTION complex_abs_cmp(complex, complex)
 943     RETURNS integer
 944     AS '<replaceable>filename</replaceable>'
 945     LANGUAGE C IMMUTABLE STRICT;
 946 </programlisting>
 947   </para>
 948
 949   <para>
 950    Now that we have the required operators and support routine,
 951    we can finally create the operator class:
 952
 953 <programlisting><![CDATA[
 954 CREATE OPERATOR CLASS complex_abs_ops
 955     DEFAULT FOR TYPE complex USING btree AS
 956         OPERATOR        1       < ,
 957         OPERATOR        2       <= ,
 958         OPERATOR        3       = ,
 959         OPERATOR        4       >= ,
 960         OPERATOR        5       > ,
 961         FUNCTION        1       complex_abs_cmp(complex, complex);
 962 ]]>
 963 </programlisting>
 964   </para>
 965
 966   <para>
 967    And we're done!  It should now be possible to create
 968    and use B-tree indexes on <type>complex</type> columns.
 969   </para>
 970
 971   <para>
 972    We could have written the operator entries more verbosely, as in:
 973 <programlisting>
 974         OPERATOR        1       &lt; (complex, complex) ,
 975 </programlisting>
 976    but there is no need to do so when the operators take the same data type
 977    we are defining the operator class for.
 978   </para>
 979
 980   <para>
 981    The above example assumes that you want to make this new operator class the
 982    default B-tree operator class for the <type>complex</type> data type.
 983    If you don't, just leave out the word <literal>DEFAULT</literal>.
 984   </para>
 985  </sect2>
 986
 987  <sect2 id="xindex-opfamily">
 988   <title>Operator Classes and Operator Families</title>
 989
 990   <para>
 991    So far we have implicitly assumed that an operator class deals with
 992    only one data type.  While there certainly can be only one data type in
 993    a particular index column, it is often useful to index operations that
 994    compare an indexed column to a value of a different data type.  Also,
 995    if there is use for a cross-data-type operator in connection with an
 996    operator class, it is often the case that the other data type has a
 997    related operator class of its own.  It is helpful to make the connections
 998    between related classes explicit, because this can aid the planner in
 999    optimizing SQL queries (particularly for B-tree operator classes, since
1000    the planner contains a great deal of knowledge about how to work with them).
1001   </para>
1002
1003   <para>
1004    To handle these needs, <productname>PostgreSQL</productname>
1005    uses the concept of an <firstterm>operator
1006    family</firstterm><indexterm><primary>operator family</primary></indexterm>.
1007    An operator family contains one or more operator classes, and can also
1008    contain indexable operators and corresponding support functions that
1009    belong to the family as a whole but not to any single class within the
1010    family.  We say that such operators and functions are <quote>loose</quote>
1011    within the family, as opposed to being bound into a specific class.
1012    Typically each operator class contains single-data-type operators
1013    while cross-data-type operators are loose in the family.
1014   </para>
1015
1016   <para>
1017    All the operators and functions in an operator family must have compatible
1018    semantics, where the compatibility requirements are set by the index
1019    method.  You might therefore wonder why bother to single out particular
1020    subsets of the family as operator classes; and indeed for many purposes
1021    the class divisions are irrelevant and the family is the only interesting
1022    grouping.  The reason for defining operator classes is that they specify
1023    how much of the family is needed to support any particular index.
1024    If there is an index using an operator class, then that operator class
1025    cannot be dropped without dropping the index &mdash; but other parts of
1026    the operator family, namely other operator classes and loose operators,
1027    could be dropped.  Thus, an operator class should be specified to contain
1028    the minimum set of operators and functions that are reasonably needed
1029    to work with an index on a specific data type, and then related but
1030    non-essential operators can be added as loose members of the operator
1031    family.
1032   </para>
1033
1034   <para>
1035    As an example, <productname>PostgreSQL</productname> has a built-in
1036    B-tree operator family <literal>integer_ops</literal>, which includes operator
1037    classes <literal>int8_ops</literal>, <literal>int4_ops</literal>, and
1038    <literal>int2_ops</literal> for indexes on <type>bigint</type> (<type>int8</type>),
1039    <type>integer</type> (<type>int4</type>), and <type>smallint</type> (<type>int2</type>)
1040    columns respectively.  The family also contains cross-data-type comparison
1041    operators allowing any two of these types to be compared, so that an index
1042    on one of these types can be searched using a comparison value of another
1043    type.  The family could be duplicated by these definitions:
1044
1045 <programlisting><![CDATA[
1046 CREATE OPERATOR FAMILY integer_ops USING btree;
1047
1048 CREATE OPERATOR CLASS int8_ops
1049 DEFAULT FOR TYPE int8 USING btree FAMILY integer_ops AS
1050   -- standard int8 comparisons
1051   OPERATOR 1 < ,
1052   OPERATOR 2 <= ,
1053   OPERATOR 3 = ,
1054   OPERATOR 4 >= ,
1055   OPERATOR 5 > ,
1056   FUNCTION 1 btint8cmp(int8, int8) ,
1057   FUNCTION 2 btint8sortsupport(internal) ,
1058   FUNCTION 3 in_range(int8, int8, int8, boolean, boolean) ,
1059   FUNCTION 4 btequalimage(oid) ;
1060
1061 CREATE OPERATOR CLASS int4_ops
1062 DEFAULT FOR TYPE int4 USING btree FAMILY integer_ops AS
1063   -- standard int4 comparisons
1064   OPERATOR 1 < ,
1065   OPERATOR 2 <= ,
1066   OPERATOR 3 = ,
1067   OPERATOR 4 >= ,
1068   OPERATOR 5 > ,
1069   FUNCTION 1 btint4cmp(int4, int4) ,
1070   FUNCTION 2 btint4sortsupport(internal) ,
1071   FUNCTION 3 in_range(int4, int4, int4, boolean, boolean) ,
1072   FUNCTION 4 btequalimage(oid) ;
1073
1074 CREATE OPERATOR CLASS int2_ops
1075 DEFAULT FOR TYPE int2 USING btree FAMILY integer_ops AS
1076   -- standard int2 comparisons
1077   OPERATOR 1 < ,
1078   OPERATOR 2 <= ,
1079   OPERATOR 3 = ,
1080   OPERATOR 4 >= ,
1081   OPERATOR 5 > ,
1082   FUNCTION 1 btint2cmp(int2, int2) ,
1083   FUNCTION 2 btint2sortsupport(internal) ,
1084   FUNCTION 3 in_range(int2, int2, int2, boolean, boolean) ,
1085   FUNCTION 4 btequalimage(oid) ;
1086
1087 ALTER OPERATOR FAMILY integer_ops USING btree ADD
1088   -- cross-type comparisons int8 vs int2
1089   OPERATOR 1 < (int8, int2) ,
1090   OPERATOR 2 <= (int8, int2) ,
1091   OPERATOR 3 = (int8, int2) ,
1092   OPERATOR 4 >= (int8, int2) ,
1093   OPERATOR 5 > (int8, int2) ,
1094   FUNCTION 1 btint82cmp(int8, int2) ,
1095
1096   -- cross-type comparisons int8 vs int4
1097   OPERATOR 1 < (int8, int4) ,
1098   OPERATOR 2 <= (int8, int4) ,
1099   OPERATOR 3 = (int8, int4) ,
1100   OPERATOR 4 >= (int8, int4) ,
1101   OPERATOR 5 > (int8, int4) ,
1102   FUNCTION 1 btint84cmp(int8, int4) ,
1103
1104   -- cross-type comparisons int4 vs int2
1105   OPERATOR 1 < (int4, int2) ,
1106   OPERATOR 2 <= (int4, int2) ,
1107   OPERATOR 3 = (int4, int2) ,
1108   OPERATOR 4 >= (int4, int2) ,
1109   OPERATOR 5 > (int4, int2) ,
1110   FUNCTION 1 btint42cmp(int4, int2) ,
1111
1112   -- cross-type comparisons int4 vs int8
1113   OPERATOR 1 < (int4, int8) ,
1114   OPERATOR 2 <= (int4, int8) ,
1115   OPERATOR 3 = (int4, int8) ,
1116   OPERATOR 4 >= (int4, int8) ,
1117   OPERATOR 5 > (int4, int8) ,
1118   FUNCTION 1 btint48cmp(int4, int8) ,
1119
1120   -- cross-type comparisons int2 vs int8
1121   OPERATOR 1 < (int2, int8) ,
1122   OPERATOR 2 <= (int2, int8) ,
1123   OPERATOR 3 = (int2, int8) ,
1124   OPERATOR 4 >= (int2, int8) ,
1125   OPERATOR 5 > (int2, int8) ,
1126   FUNCTION 1 btint28cmp(int2, int8) ,
1127
1128   -- cross-type comparisons int2 vs int4
1129   OPERATOR 1 < (int2, int4) ,
1130   OPERATOR 2 <= (int2, int4) ,
1131   OPERATOR 3 = (int2, int4) ,
1132   OPERATOR 4 >= (int2, int4) ,
1133   OPERATOR 5 > (int2, int4) ,
1134   FUNCTION 1 btint24cmp(int2, int4) ,
1135
1136   -- cross-type in_range functions
1137   FUNCTION 3 in_range(int4, int4, int8, boolean, boolean) ,
1138   FUNCTION 3 in_range(int4, int4, int2, boolean, boolean) ,
1139   FUNCTION 3 in_range(int2, int2, int8, boolean, boolean) ,
1140   FUNCTION 3 in_range(int2, int2, int4, boolean, boolean) ;
1141 ]]>
1142 </programlisting>
1143
1144    Notice that this definition <quote>overloads</quote> the operator strategy and
1145    support function numbers: each number occurs multiple times within the
1146    family.  This is allowed so long as each instance of a
1147    particular number has distinct input data types.  The instances that have
1148    both input types equal to an operator class's input type are the
1149    primary operators and support functions for that operator class,
1150    and in most cases should be declared as part of the operator class rather
1151    than as loose members of the family.
1152   </para>
1153
1154   <para>
1155    In a B-tree operator family, all the operators in the family must sort
1156    compatibly, as is specified in detail in <xref linkend="btree-behavior"/>.
1157    For each
1158    operator in the family there must be a support function having the same
1159    two input data types as the operator.  It is recommended that a family be
1160    complete, i.e., for each combination of data types, all operators are
1161    included.  Each operator class should include just the non-cross-type
1162    operators and support function for its data type.
1163   </para>
1164
1165   <para>
1166    To build a multiple-data-type hash operator family, compatible hash
1167    support functions must be created for each data type supported by the
1168    family.  Here compatibility means that the functions are guaranteed to
1169    return the same hash code for any two values that are considered equal
1170    by the family's equality operators, even when the values are of different
1171    types.  This is usually difficult to accomplish when the types have
1172    different physical representations, but it can be done in some cases.
1173    Furthermore, casting a value from one data type represented in the operator
1174    family to another data type also represented in the operator family via
1175    an implicit or binary coercion cast must not change the computed hash value.
1176    Notice that there is only one support function per data type, not one
1177    per equality operator.  It is recommended that a family be complete, i.e.,
1178    provide an equality operator for each combination of data types.
1179    Each operator class should include just the non-cross-type equality
1180    operator and the support function for its data type.
1181   </para>
1182
1183   <para>
1184    GiST, SP-GiST, and GIN indexes do not have any explicit notion of
1185    cross-data-type operations.  The set of operators supported is just
1186    whatever the primary support functions for a given operator class can
1187    handle.
1188   </para>
1189
1190   <para>
1191    In BRIN, the requirements depends on the framework that provides the
1192    operator classes.  For operator classes based on <literal>minmax</literal>,
1193    the behavior required is the same as for B-tree operator families:
1194    all the operators in the family must sort compatibly, and casts must
1195    not change the associated sort ordering.
1196   </para>
1197
1198   <note>
1199    <para>
1200     Prior to <productname>PostgreSQL</productname> 8.3, there was no concept
1201     of operator families, and so any cross-data-type operators intended to be
1202     used with an index had to be bound directly into the index's operator
1203     class.  While this approach still works, it is deprecated because it
1204     makes an index's dependencies too broad, and because the planner can
1205     handle cross-data-type comparisons more effectively when both data types
1206     have operators in the same operator family.
1207    </para>
1208   </note>
1209  </sect2>
1210
1211  <sect2 id="xindex-opclass-dependencies">
1212   <title>System Dependencies on Operator Classes</title>
1213
1214    <indexterm>
1215     <primary>ordering operator</primary>
1216    </indexterm>
1217
1218   <para>
1219    <productname>PostgreSQL</productname> uses operator classes to infer the
1220    properties of operators in more ways than just whether they can be used
1221    with indexes.  Therefore, you might want to create operator classes
1222    even if you have no intention of indexing any columns of your data type.
1223   </para>
1224
1225   <para>
1226    In particular, there are SQL features such as <literal>ORDER BY</literal> and
1227    <literal>DISTINCT</literal> that require comparison and sorting of values.
1228    To implement these features on a user-defined data type,
1229    <productname>PostgreSQL</productname> looks for the default B-tree operator
1230    class for the data type.  The <quote>equals</quote> member of this operator
1231    class defines the system's notion of equality of values for
1232    <literal>GROUP BY</literal> and <literal>DISTINCT</literal>, and the sort ordering
1233    imposed by the operator class defines the default <literal>ORDER BY</literal>
1234    ordering.
1235   </para>
1236
1237   <para>
1238    If there is no default B-tree operator class for a data type, the system
1239    will look for a default hash operator class.  But since that kind of
1240    operator class only provides equality, it is only able to support grouping
1241    not sorting.
1242   </para>
1243
1244   <para>
1245    When there is no default operator class for a data type, you will get
1246    errors like <quote>could not identify an ordering operator</quote> if you
1247    try to use these SQL features with the data type.
1248   </para>
1249
1250    <note>
1251     <para>
1252      In <productname>PostgreSQL</productname> versions before 7.4,
1253      sorting and grouping operations would implicitly use operators named
1254      <literal>=</literal>, <literal>&lt;</literal>, and <literal>&gt;</literal>.  The new
1255      behavior of relying on default operator classes avoids having to make
1256      any assumption about the behavior of operators with particular names.
1257     </para>
1258    </note>
1259
1260   <para>
1261    Sorting by a non-default B-tree operator class is possible by specifying
1262    the class's less-than operator in a <literal>USING</literal> option,
1263    for example
1264 <programlisting>
1265 SELECT * FROM mytable ORDER BY somecol USING ~&lt;~;
1266 </programlisting>
1267    Alternatively, specifying the class's greater-than operator
1268    in <literal>USING</literal> selects a descending-order sort.
1269   </para>
1270
1271   <para>
1272    Comparison of arrays of a user-defined type also relies on the semantics
1273    defined by the type's default B-tree operator class.  If there is no
1274    default B-tree operator class, but there is a default hash operator class,
1275    then array equality is supported, but not ordering comparisons.
1276   </para>
1277
1278   <para>
1279    Another SQL feature that requires even more data-type-specific knowledge
1280    is the <literal>RANGE</literal> <replaceable>offset</replaceable>
1281    <literal>PRECEDING</literal>/<literal>FOLLOWING</literal> framing option
1282    for window functions (see <xref linkend="syntax-window-functions"/>).
1283    For a query such as
1284 <programlisting>
1285 SELECT sum(x) OVER (ORDER BY x RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING)
1286   FROM mytable;
1287 </programlisting>
1288    it is not sufficient to know how to order by <literal>x</literal>;
1289    the database must also understand how to <quote>subtract 5</quote> or
1290    <quote>add 10</quote> to the current row's value of <literal>x</literal>
1291    to identify the bounds of the current window frame.  Comparing the
1292    resulting bounds to other rows' values of <literal>x</literal> is
1293    possible using the comparison operators provided by the B-tree operator
1294    class that defines the <literal>ORDER BY</literal> ordering &mdash; but
1295    addition and subtraction operators are not part of the operator class, so
1296    which ones should be used?  Hard-wiring that choice would be undesirable,
1297    because different sort orders (different B-tree operator classes) might
1298    need different behavior.  Therefore, a B-tree operator class can specify
1299    an <firstterm>in_range</firstterm> support function that encapsulates the
1300    addition and subtraction behaviors that make sense for its sort order.
1301    It can even provide more than one in_range support function, in case
1302    there is more than one data type that makes sense to use as the offset
1303    in <literal>RANGE</literal> clauses.
1304    If the B-tree operator class associated with the window's <literal>ORDER
1305    BY</literal> clause does not have a matching in_range support function,
1306    the <literal>RANGE</literal> <replaceable>offset</replaceable>
1307    <literal>PRECEDING</literal>/<literal>FOLLOWING</literal>
1308    option is not supported.
1309   </para>
1310
1311   <para>
1312    Another important point is that an equality operator that
1313    appears in a hash operator family is a candidate for hash joins,
1314    hash aggregation, and related optimizations.  The hash operator family
1315    is essential here since it identifies the hash function(s) to use.
1316   </para>
1317  </sect2>
1318
1319  <sect2 id="xindex-ordering-ops">
1320   <title>Ordering Operators</title>
1321
1322   <para>
1323    Some index access methods (currently, only GiST and SP-GiST) support the concept of
1324    <firstterm>ordering operators</firstterm>.  What we have been discussing so far
1325    are <firstterm>search operators</firstterm>.  A search operator is one for which
1326    the index can be searched to find all rows satisfying
1327    <literal>WHERE</literal>
1328    <replaceable>indexed_column</replaceable>
1329    <replaceable>operator</replaceable>
1330    <replaceable>constant</replaceable>.
1331    Note that nothing is promised about the order in which the matching rows
1332    will be returned.  In contrast, an ordering operator does not restrict the
1333    set of rows that can be returned, but instead determines their order.
1334    An ordering operator is one for which the index can be scanned to return
1335    rows in the order represented by
1336    <literal>ORDER BY</literal>
1337    <replaceable>indexed_column</replaceable>
1338    <replaceable>operator</replaceable>
1339    <replaceable>constant</replaceable>.
1340    The reason for defining ordering operators that way is that it supports
1341    nearest-neighbor searches, if the operator is one that measures distance.
1342    For example, a query like
1343 <programlisting><![CDATA[
1344 SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;
1345 ]]>
1346 </programlisting>
1347    finds the ten places closest to a given target point.  A GiST index
1348    on the location column can do this efficiently because
1349    <literal>&lt;-&gt;</literal> is an ordering operator.
1350   </para>
1351
1352   <para>
1353    While search operators have to return Boolean results, ordering operators
1354    usually return some other type, such as float or numeric for distances.
1355    This type is normally not the same as the data type being indexed.
1356    To avoid hard-wiring assumptions about the behavior of different data
1357    types, the definition of an ordering operator is required to name
1358    a B-tree operator family that specifies the sort ordering of the result
1359    data type.  As was stated in the previous section, B-tree operator families
1360    define <productname>PostgreSQL</productname>'s notion of ordering, so
1361    this is a natural representation.  Since the point <literal>&lt;-&gt;</literal>
1362    operator returns <type>float8</type>, it could be specified in an operator
1363    class creation command like this:
1364 <programlisting><![CDATA[
1365 OPERATOR 15    <-> (point, point) FOR ORDER BY float_ops
1366 ]]>
1367 </programlisting>
1368    where <literal>float_ops</literal> is the built-in operator family that includes
1369    operations on <type>float8</type>.  This declaration states that the index
1370    is able to return rows in order of increasing values of the
1371    <literal>&lt;-&gt;</literal> operator.
1372   </para>
1373  </sect2>
1374
1375  <sect2 id="xindex-opclass-features">
1376   <title>Special Features of Operator Classes</title>
1377
1378   <para>
1379    There are two special features of operator classes that we have
1380    not discussed yet, mainly because they are not useful
1381    with the most commonly used index methods.
1382   </para>
1383
1384   <para>
1385    Normally, declaring an operator as a member of an operator class
1386    (or family) means that the index method can retrieve exactly the set of rows
1387    that satisfy a <literal>WHERE</literal> condition using the operator.  For example:
1388 <programlisting>
1389 SELECT * FROM table WHERE integer_column &lt; 4;
1390 </programlisting>
1391    can be satisfied exactly by a B-tree index on the integer column.
1392    But there are cases where an index is useful as an inexact guide to
1393    the matching rows.  For example, if a GiST index stores only bounding boxes
1394    for geometric objects, then it cannot exactly satisfy a <literal>WHERE</literal>
1395    condition that tests overlap between nonrectangular objects such as
1396    polygons.  Yet we could use the index to find objects whose bounding
1397    box overlaps the bounding box of the target object, and then do the
1398    exact overlap test only on the objects found by the index.  If this
1399    scenario applies, the index is said to be <quote>lossy</quote> for the
1400    operator.  Lossy index searches are implemented by having the index
1401    method return a <firstterm>recheck</firstterm> flag when a row might or might
1402    not really satisfy the query condition.  The core system will then
1403    test the original query condition on the retrieved row to see whether
1404    it should be returned as a valid match.  This approach works if
1405    the index is guaranteed to return all the required rows, plus perhaps
1406    some additional rows, which can be eliminated by performing the original
1407    operator invocation.  The index methods that support lossy searches
1408    (currently, GiST, SP-GiST and GIN) allow the support functions of individual
1409    operator classes to set the recheck flag, and so this is essentially an
1410    operator-class feature.
1411   </para>
1412
1413   <para>
1414    Consider again the situation where we are storing in the index only
1415    the bounding box of a complex object such as a polygon.  In this
1416    case there's not much value in storing the whole polygon in the index
1417    entry &mdash; we might as well store just a simpler object of type
1418    <type>box</type>.  This situation is expressed by the <literal>STORAGE</literal>
1419    option in <command>CREATE OPERATOR CLASS</command>: we'd write something like:
1420
1421 <programlisting>
1422 CREATE OPERATOR CLASS polygon_ops
1423     DEFAULT FOR TYPE polygon USING gist AS
1424         ...
1425         STORAGE box;
1426 </programlisting>
1427
1428    At present, only the GiST, SP-GiST, GIN and BRIN index methods support a
1429    <literal>STORAGE</literal> type that's different from the column data type.
1430    The GiST <function>compress</function> and <function>decompress</function> support
1431    routines must deal with data-type conversion when <literal>STORAGE</literal>
1432    is used.  SP-GiST likewise requires a <function>compress</function>
1433    support function to convert to the storage type, when that is different;
1434    if an SP-GiST opclass also supports retrieving data, the reverse
1435    conversion must be handled by the <function>consistent</function> function.
1436    In GIN, the <literal>STORAGE</literal> type identifies the type of
1437    the <quote>key</quote> values, which normally is different from the type
1438    of the indexed column &mdash; for example, an operator class for
1439    integer-array columns might have keys that are just integers.  The
1440    GIN <function>extractValue</function> and <function>extractQuery</function> support
1441    routines are responsible for extracting keys from indexed values.
1442    BRIN is similar to GIN: the <literal>STORAGE</literal> type identifies the
1443    type of the stored summary values, and operator classes' support
1444    procedures are responsible for interpreting the summary values
1445    correctly.
1446   </para>
1447  </sect2>
1448
1449 </sect1>