Home Explore Blog CI



postgresql

18th chunk of `doc/src/sgml/xindex.sgml`
6112a91e214fc6b4b1d98fd47cbb31aeaa3fb798b9b057450000000100000ebd
 mainly because they are not useful
   with the most commonly used index methods.
  </para>

  <para>
   Normally, declaring an operator as a member of an operator class
   (or family) means that the index method can retrieve exactly the set of rows
   that satisfy a <literal>WHERE</literal> condition using the operator.  For example:
<programlisting>
SELECT * FROM table WHERE integer_column &lt; 4;
</programlisting>
   can be satisfied exactly by a B-tree index on the integer column.
   But there are cases where an index is useful as an inexact guide to
   the matching rows.  For example, if a GiST index stores only bounding boxes
   for geometric objects, then it cannot exactly satisfy a <literal>WHERE</literal>
   condition that tests overlap between nonrectangular objects such as
   polygons.  Yet we could use the index to find objects whose bounding
   box overlaps the bounding box of the target object, and then do the
   exact overlap test only on the objects found by the index.  If this
   scenario applies, the index is said to be <quote>lossy</quote> for the
   operator.  Lossy index searches are implemented by having the index
   method return a <firstterm>recheck</firstterm> flag when a row might or might
   not really satisfy the query condition.  The core system will then
   test the original query condition on the retrieved row to see whether
   it should be returned as a valid match.  This approach works if
   the index is guaranteed to return all the required rows, plus perhaps
   some additional rows, which can be eliminated by performing the original
   operator invocation.  The index methods that support lossy searches
   (currently, GiST, SP-GiST and GIN) allow the support functions of individual
   operator classes to set the recheck flag, and so this is essentially an
   operator-class feature.
  </para>

  <para>
   Consider again the situation where we are storing in the index only
   the bounding box of a complex object such as a polygon.  In this
   case there's not much value in storing the whole polygon in the index
   entry &mdash; we might as well store just a simpler object of type
   <type>box</type>.  This situation is expressed by the <literal>STORAGE</literal>
   option in <command>CREATE OPERATOR CLASS</command>: we'd write something like:

<programlisting>
CREATE OPERATOR CLASS polygon_ops
    DEFAULT FOR TYPE polygon USING gist AS
        ...
        STORAGE box;
</programlisting>

   At present, only the GiST, SP-GiST, GIN and BRIN index methods support a
   <literal>STORAGE</literal> type that's different from the column data type.
   The GiST <function>compress</function> and <function>decompress</function> support
   routines must deal with data-type conversion when <literal>STORAGE</literal>
   is used.  SP-GiST likewise requires a <function>compress</function>
   support function to convert to the storage type, when that is different;
   if an SP-GiST opclass also supports retrieving data, the reverse
   conversion must be handled by the <function>consistent</function> function.
   In GIN, the <literal>STORAGE</literal> type identifies the type of
   the <quote>key</quote> values, which normally is different from the type
   of the indexed column &mdash; for example, an operator class for
   integer-array columns might have keys that are just integers.  The
   GIN <function>extractValue</function> and <function>extractQuery</function> support
   routines are responsible for extracting keys from indexed values.
   BRIN is similar to GIN: the <literal>STORAGE</literal> type identifies the
   type of the stored summary values, and operator classes' support
   procedures are responsible for interpreting the summary values
   correctly.
  </para>
 </sect2>

</sect1>

Title: Special Features of PostgreSQL Operator Classes
Summary
This section discusses two special features of PostgreSQL operator classes that are less common in typical index methods. It explains the concept of 'lossy' index searches, where an index serves as an inexact guide to matching rows, particularly useful in GiST indexes for geometric queries. The text describes how lossy searches are implemented using a 'recheck' flag and supported by GiST, SP-GiST, and GIN index methods. The second feature discussed is the STORAGE option in CREATE OPERATOR CLASS, which allows storing a simpler object type in the index entry. This is supported by GiST, SP-GiST, GIN, and BRIN index methods, with each method having specific requirements for handling data-type conversion and key extraction when STORAGE is used.