begriffs open source - ai-pg/blob - full-docs/src/sgml/html/indexes-multicolumn.html

   1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
   2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>11.3. Multicolumn Indexes</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="indexes-types.html" title="11.2. Index Types" /><link rel="next" href="indexes-ordering.html" title="11.4. Indexes and ORDER BY" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">11.3. Multicolumn Indexes</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="indexes-types.html" title="11.2. Index Types">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="indexes.html" title="Chapter 11. Indexes">Up</a></td><th width="60%" align="center">Chapter 11. Indexes</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="indexes-ordering.html" title="11.4. Indexes and ORDER BY">Next</a></td></tr></table><hr /></div><div class="sect1" id="INDEXES-MULTICOLUMN"><div class="titlepage"><div><div><h2 class="title" style="clear: both">11.3. Multicolumn Indexes <a href="#INDEXES-MULTICOLUMN" class="id_link">#</a></h2></div></div></div><a id="id-1.5.10.6.2" class="indexterm"></a><p>
   3    An index can be defined on more than one column of a table.  For example, if
   4    you have a table of this form:
   5 </p><pre class="programlisting">
   6 CREATE TABLE test2 (
   7   major int,
   8   minor int,
   9   name varchar
  10 );
  11 </pre><p>
  12    (say, you keep your <code class="filename">/dev</code>
  13    directory in a database...) and you frequently issue queries like:
  14 </p><pre class="programlisting">
  15 SELECT name FROM test2 WHERE major = <em class="replaceable"><code>constant</code></em> AND minor = <em class="replaceable"><code>constant</code></em>;
  16 </pre><p>
  17    then it might be appropriate to define an index on the columns
  18    <code class="structfield">major</code> and
  19    <code class="structfield">minor</code> together, e.g.:
  20 </p><pre class="programlisting">
  21 CREATE INDEX test2_mm_idx ON test2 (major, minor);
  22 </pre><p>
  23   </p><p>
  24    Currently, only the B-tree, GiST, GIN, and BRIN index types support
  25    multiple-key-column indexes.  Whether there can be multiple key
  26    columns is independent of whether <code class="literal">INCLUDE</code> columns
  27    can be added to the index.  Indexes can have up to 32 columns,
  28    including <code class="literal">INCLUDE</code> columns.  (This limit can be
  29    altered when building <span class="productname">PostgreSQL</span>; see the
  30    file <code class="filename">pg_config_manual.h</code>.)
  31   </p><p>
  32    A multicolumn B-tree index can be used with query conditions that
  33    involve any subset of the index's columns, but the index is most
  34    efficient when there are constraints on the leading (leftmost) columns.
  35    The exact rule is that equality constraints on leading columns, plus
  36    any inequality constraints on the first column that does not have an
  37    equality constraint, will always be used to limit the portion of the index
  38    that is scanned.  Constraints on columns to the right of these columns
  39    are checked in the index, so they'll always save visits to the table
  40    proper, but they do not necessarily reduce the portion of the index that
  41    has to be scanned.  If a B-tree index scan can apply the skip scan
  42    optimization effectively, it will apply every column constraint when
  43    navigating through the index via repeated index searches.  This can reduce
  44    the portion of the index that has to be read, even though one or more
  45    columns (prior to the least significant index column from the query
  46    predicate) lacks a conventional equality constraint.  Skip scan works by
  47    generating a dynamic equality constraint internally, that matches every
  48    possible value in an index column (though only given a column that lacks
  49    an equality constraint that comes from the query predicate, and only when
  50    the generated constraint can be used in conjunction with a later column
  51    constraint from the query predicate).
  52   </p><p>
  53    For example, given an index on <code class="literal">(x, y)</code>, and a query
  54    condition <code class="literal">WHERE y = 7700</code>, a B-tree index scan might be
  55    able to apply the skip scan optimization.  This generally happens when the
  56    query planner expects that repeated <code class="literal">WHERE x = N AND y = 7700</code>
  57    searches for every possible value of <code class="literal">N</code> (or for every
  58    <code class="literal">x</code> value that is actually stored in the index) is the
  59    fastest possible approach, given the available indexes on the table.  This
  60    approach is generally only taken when there are so few distinct
  61    <code class="literal">x</code> values that the planner expects the scan to skip over
  62    most of the index (because most of its leaf pages cannot possibly contain
  63    relevant tuples).  If there are many distinct <code class="literal">x</code> values,
  64    then the entire index will have to be scanned, so in most cases the planner
  65    will prefer a sequential table scan over using the index.
  66   </p><p>
  67    The skip scan optimization can also be applied selectively, during B-tree
  68    scans that have at least some useful constraints from the query predicate.
  69    For example, given an index on <code class="literal">(a, b, c)</code> and a
  70    query condition <code class="literal">WHERE a = 5 AND b &gt;= 42 AND c &lt; 77</code>,
  71    the index might have to be scanned from the first entry with
  72    <code class="literal">a</code> = 5 and <code class="literal">b</code> = 42 up through the last
  73    entry with <code class="literal">a</code> = 5.  Index entries with
  74    <code class="literal">c</code> &gt;= 77 will never need to be filtered at the table
  75    level, but it may or may not be profitable to skip over them within the
  76    index.  When skipping takes place, the scan starts a new index search to
  77    reposition itself from the end of the current <code class="literal">a</code> = 5 and
  78    <code class="literal">b</code> = N grouping (i.e. from the position in the index
  79    where the first tuple <code class="literal">a = 5 AND b = N AND c &gt;= 77</code>
  80    appears), to the start of the next such grouping (i.e. the position in the
  81    index where the first tuple <code class="literal">a = 5 AND b = N + 1</code>
  82    appears).
  83   </p><p>
  84    A multicolumn GiST index can be used with query conditions that
  85    involve any subset of the index's columns. Conditions on additional
  86    columns restrict the entries returned by the index, but the condition on
  87    the first column is the most important one for determining how much of
  88    the index needs to be scanned.  A GiST index will be relatively
  89    ineffective if its first column has only a few distinct values, even if
  90    there are many distinct values in additional columns.
  91   </p><p>
  92    A multicolumn GIN index can be used with query conditions that
  93    involve any subset of the index's columns. Unlike B-tree or GiST,
  94    index search effectiveness is the same regardless of which index column(s)
  95    the query conditions use.
  96   </p><p>
  97    A multicolumn BRIN index can be used with query conditions that
  98    involve any subset of the index's columns. Like GIN and unlike B-tree or
  99    GiST, index search effectiveness is the same regardless of which index
 100    column(s) the query conditions use. The only reason to have multiple BRIN
 101    indexes instead of one multicolumn BRIN index on a single table is to have
 102    a different <code class="literal">pages_per_range</code> storage parameter.
 103   </p><p>
 104    Of course, each column must be used with operators appropriate to the index
 105    type; clauses that involve other operators will not be considered.
 106   </p><p>
 107    Multicolumn indexes should be used sparingly.  In most situations,
 108    an index on a single column is sufficient and saves space and time.
 109    Indexes with more than three columns are unlikely to be helpful
 110    unless the usage of the table is extremely stylized.  See also
 111    <a class="xref" href="indexes-bitmap-scans.html" title="11.5. Combining Multiple Indexes">Section 11.5</a> and
 112    <a class="xref" href="indexes-index-only-scans.html" title="11.9. Index-Only Scans and Covering Indexes">Section 11.9</a> for some discussion of the
 113    merits of different index configurations.
 114   </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="indexes-types.html" title="11.2. Index Types">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="indexes.html" title="Chapter 11. Indexes">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="indexes-ordering.html" title="11.4. Indexes and ORDER BY">Next</a></td></tr><tr><td width="40%" align="left" valign="top">11.2. Index Types </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 11.4. Indexes and <code class="literal">ORDER BY</code></td></tr></table></div></body></html>