Home • Explore • Blog • CI
☰



postgresql
5th chunk of `doc/src/sgml/intarray.sgml`
81801fce2ec5f3bc0fb123321859c02d5661de1e8763b90a00000001000009ac
 <literal>&lt;@</literal> are equivalent to <productname>PostgreSQL</productname>'s built-in
   operators of the same names, except that they work only on integer arrays
   that do not contain nulls, while the built-in operators work for any array
   type.  This restriction makes them faster than the built-in operators
   in many cases.
  </para>

  <para>
   The <literal>@@</literal> and <literal>~~</literal> operators test whether an array
   satisfies a <firstterm>query</firstterm>, which is expressed as a value of a
   specialized data type <type>query_int</type>.  A <firstterm>query</firstterm>
   consists of integer values that are checked against the elements of
   the array, possibly combined using the operators <literal>&amp;</literal>
   (AND), <literal>|</literal> (OR), and <literal>!</literal> (NOT).  Parentheses
   can be used as needed.  For example,
   the query <literal>1&amp;(2|3)</literal> matches arrays that contain 1
   and also contain either 2 or 3.
  </para>
 </sect2>

 <sect2 id="intarray-index">
  <title>Index Support</title>

  <para>
   <filename>intarray</filename> provides index support for the
   <literal>&amp;&amp;</literal>, <literal>@&gt;</literal>,
   and <literal>@@</literal> operators, as well as regular array equality.
  </para>

  <para>
   Two parameterized GiST index operator classes are provided:
   <literal>gist__int_ops</literal> (used by default) is suitable for
   small- to medium-size data sets, while
   <literal>gist__intbig_ops</literal> uses a larger signature and is more
   suitable for indexing large data sets (i.e., columns containing
   a large number of distinct array values).
   The implementation uses an RD-tree data structure with
   built-in lossy compression.
  </para>

  <para>
   <literal>gist__int_ops</literal> approximates an integer set as an array of
   integer ranges.  Its optional integer parameter <literal>numranges</literal>
   determines the maximum number of ranges in
   one index key.  The default value of <literal>numranges</literal> is 100.
   Valid values are between 1 and 253.  Using larger arrays as GiST index
   keys leads to a more precise search (scanning a smaller fraction of the index and
   fewer heap pages), at the cost of a larger index.
  </para>

  <para>
   <literal>gist__intbig_ops</literal> approximates an integer set as a bitmap
   signature.  Its optional integer parameter <literal>siglen</literal>
   determines the signature length in bytes.
Title: Integer Array Indexing in PostgreSQL
Summary
This section discusses index support for integer arrays in PostgreSQL's intarray module. It provides GiST index support for operators &&, @>, and @@, as well as regular array equality. Two parameterized GiST index operator classes are offered: gist__int_ops (default) for small to medium-size data sets, and gist__intbig_ops for larger data sets. The gist__int_ops class uses an RD-tree structure with lossy compression, approximating integer sets as arrays of integer ranges. Its 'numranges' parameter (default 100) determines the maximum number of ranges in an index key. The gist__intbig_ops class approximates integer sets as bitmap signatures, with the 'siglen' parameter determining signature length in bytes. These indexing methods aim to balance search precision and index size for different data set sizes.