Home • Explore • Blog • CI
☰



postgresql
3rd chunk of `doc/src/sgml/xindex.sgml`
069faea12367b0f69bea5a14d8776a11bc4549369fcc63020000000100000fa0
 index
   operator classes index two-dimensional geometric objects, providing
   the <quote>R-tree</quote> strategies shown in
   <xref linkend="xindex-rtree-strat-table"/>.  Four of these are true
   two-dimensional tests (overlaps, same, contains, contained by);
   four of them consider only the X direction; and the other four
   provide the same tests in the Y direction.
  </para>

   <table tocentry="1" id="xindex-rtree-strat-table">
    <title>GiST Two-Dimensional <quote>R-tree</quote> Strategies</title>
    <tgroup cols="2">
     <thead>
      <row>
       <entry>Operation</entry>
       <entry>Strategy Number</entry>
      </row>
     </thead>
     <tbody>
      <row>
       <entry>strictly left of</entry>
       <entry>1</entry>
      </row>
      <row>
       <entry>does not extend to right of</entry>
       <entry>2</entry>
      </row>
      <row>
       <entry>overlaps</entry>
       <entry>3</entry>
      </row>
      <row>
       <entry>does not extend to left of</entry>
       <entry>4</entry>
      </row>
      <row>
       <entry>strictly right of</entry>
       <entry>5</entry>
      </row>
      <row>
       <entry>same</entry>
       <entry>6</entry>
      </row>
      <row>
       <entry>contains</entry>
       <entry>7</entry>
      </row>
      <row>
       <entry>contained by</entry>
       <entry>8</entry>
      </row>
      <row>
       <entry>does not extend above</entry>
       <entry>9</entry>
      </row>
      <row>
       <entry>strictly below</entry>
       <entry>10</entry>
      </row>
      <row>
       <entry>strictly above</entry>
       <entry>11</entry>
      </row>
      <row>
       <entry>does not extend below</entry>
       <entry>12</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

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

   <table tocentry="1" id="xindex-spgist-point-strat-table">
    <title>SP-GiST Point Strategies</title>
    <tgroup cols="2">
     <thead>
      <row>
       <entry>Operation</entry>
       <entry>Strategy Number</entry>
      </row>
     </thead>
     <tbody>
      <row>
       <entry>strictly left of</entry>
       <entry>1</entry>
      </row>
      <row>
       <entry>strictly right of</entry>
       <entry>5</entry>
      </row>
      <row>
       <entry>same</entry>
       <entry>6</entry>
      </row>
      <row>
       <entry>contained by</entry>
       <entry>8</entry>
      </row>
      <row>
       <entry>strictly below</entry>
       <entry>10</entry>
      </row>
      <row>
       <entry>strictly above</entry>
       <entry>11</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

  <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>
Title: GiST, SP-GiST, and GIN Index Strategies in PostgreSQL
Summary
This section details the strategy numbers used in various PostgreSQL index types. For GiST indexes, it provides a comprehensive table of R-tree strategies for two-dimensional geometric objects, including operations like 'strictly left of', 'overlaps', and 'contains'. SP-GiST indexes are described as flexible, with strategy numbers interpreted by each operator class. An example of SP-GiST point strategies is given, showing operations like 'strictly left of' and 'same'. Similarly, GIN indexes are explained to have flexible strategy numbers, with an example of GIN array strategies provided, including operations such as 'overlap' and 'contains'. This flexibility allows these index types to support a wide range of custom operations and data types.