Home • Explore • Blog • CI
☰



postgresql
4th chunk of `doc/src/sgml/xindex.sgml`
19880cf4ba6ef9b4699527b933ba7d17f5086f7e4467af4e0000000100000fa2

  <para>
   GIN indexes are similar to GiST and SP-GiST indexes, in that they don't
   have a fixed set of strategies either. Instead the support routines of
   each operator class interpret the strategy numbers according to the
   operator class's definition. As an example, the strategy numbers used by
   the built-in operator class for arrays are shown in
   <xref linkend="xindex-gin-array-strat-table"/>.
  </para>

   <table tocentry="1" id="xindex-gin-array-strat-table">
    <title>GIN Array Strategies</title>
    <tgroup cols="2">
     <thead>
      <row>
       <entry>Operation</entry>
       <entry>Strategy Number</entry>
      </row>
     </thead>
     <tbody>
      <row>
       <entry>overlap</entry>
       <entry>1</entry>
      </row>
      <row>
       <entry>contains</entry>
       <entry>2</entry>
      </row>
      <row>
       <entry>is contained by</entry>
       <entry>3</entry>
      </row>
      <row>
       <entry>equal</entry>
       <entry>4</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

  <para>
   BRIN indexes are similar to GiST, SP-GiST and GIN indexes in that they
   don't have a fixed set of strategies either.  Instead the support routines
   of each operator class interpret the strategy numbers according to the
   operator class's definition. As an example, the strategy numbers used by
   the built-in <literal>Minmax</literal> operator classes are shown in
   <xref linkend="xindex-brin-minmax-strat-table"/>.
  </para>

   <table tocentry="1" id="xindex-brin-minmax-strat-table">
    <title>BRIN Minmax Strategies</title>
    <tgroup cols="2">
     <thead>
      <row>
       <entry>Operation</entry>
       <entry>Strategy Number</entry>
      </row>
     </thead>
     <tbody>
      <row>
       <entry>less than</entry>
       <entry>1</entry>
      </row>
      <row>
       <entry>less than or equal</entry>
       <entry>2</entry>
      </row>
      <row>
       <entry>equal</entry>
       <entry>3</entry>
      </row>
      <row>
       <entry>greater than or equal</entry>
       <entry>4</entry>
      </row>
      <row>
       <entry>greater than</entry>
       <entry>5</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

  <para>
   Notice that all the operators listed above return Boolean values.  In
   practice, all operators defined as index method search operators must
   return type <type>boolean</type>, since they must appear at the top
   level of a <literal>WHERE</literal> clause to be used with an index.
   (Some index access methods also support <firstterm>ordering operators</firstterm>,
   which typically don't return Boolean values; that feature is discussed
   in <xref linkend="xindex-ordering-ops"/>.)
  </para>
 </sect2>

 <sect2 id="xindex-support">
  <title>Index Method Support Routines</title>

  <para>
   Strategies aren't usually enough information for the system to figure
   out how to use an index.  In practice, the index methods require
   additional support routines in order to work. For example, the B-tree
   index method must be able to compare two keys and determine whether one
   is greater than, equal to, or less than the other.  Similarly, the
   hash index method must be able to compute hash codes for key values.
   These operations do not correspond to operators used in qualifications in
   SQL commands;  they are administrative routines used by
   the index methods, internally.
  </para>

  <para>
   Just as with strategies, the operator class identifies which specific
   functions should play each of these roles for a given data type and
   semantic interpretation.  The index method defines the set
   of functions it needs, and the operator class identifies the correct
   functions to use by assigning them to the <quote>support function numbers</quote>
   specified by the index method.
  </para>

  <para>
   Additionally, some opclasses allow users to specify parameters which
   control their behavior.  Each builtin index access method has an
Title: GIN, BRIN, and General Index Strategies in PostgreSQL
Summary
This section describes the flexible nature of strategy numbers in GIN and BRIN indexes in PostgreSQL. GIN indexes, like GiST and SP-GiST, don't have fixed strategies; instead, each operator class interprets the numbers. An example of GIN array strategies is provided, including operations like 'overlap' and 'contains'. BRIN indexes are similarly flexible, with an example of Minmax operator class strategies given, such as 'less than' and 'equal'. The text emphasizes that all index method search operators must return Boolean values. It also introduces the concept of index method support routines, which are administrative functions used internally by index methods to perform operations like key comparison. These routines are identified by the operator class and assigned to support function numbers specified by the index method.