codekingpro/portable-devtools

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 16.3 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, if4   you have a table of this form:5</p><pre class="programlisting">6CREATE TABLE test2 (7  major int,8  minor int,9  name varchar10);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">15SELECT 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 columns18   <code class="structfield">major</code> and19   <code class="structfield">minor</code> together, e.g.:20</p><pre class="programlisting">21CREATE 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 support25   multiple-key-column indexes.  Whether there can be multiple key26   columns is independent of whether <code class="literal">INCLUDE</code> columns27   can be added to the index.  Indexes can have up to 32 columns,28   including <code class="literal">INCLUDE</code> columns.  (This limit can be29   altered when building <span class="productname">PostgreSQL</span>; see the30   file <code class="filename">pg_config_manual.h</code>.)31  </p><p>32   A multicolumn B-tree index can be used with query conditions that33   involve any subset of the index's columns, but the index is most34   efficient when there are constraints on the leading (leftmost) columns.35   The exact rule is that equality constraints on leading columns, plus36   any inequality constraints on the first column that does not have an37   equality constraint, will be used to limit the portion of the index38   that is scanned.  Constraints on columns to the right of these columns39   are checked in the index, so they save visits to the table proper, but40   they do not reduce the portion of the index that has to be scanned.41   For example, given an index on <code class="literal">(a, b, c)</code> and a42   query condition <code class="literal">WHERE a = 5 AND b &gt;= 42 AND c &lt; 77</code>,43   the index would have to be scanned from the first entry with44   <code class="literal">a</code> = 5 and <code class="literal">b</code> = 42 up through the last entry with45   <code class="literal">a</code> = 5.  Index entries with <code class="literal">c</code> &gt;= 77 would be46   skipped, but they'd still have to be scanned through.47   This index could in principle be used for queries that have constraints48   on <code class="literal">b</code> and/or <code class="literal">c</code> with no constraint on <code class="literal">a</code>49   — but the entire index would have to be scanned, so in most cases50   the planner would prefer a sequential table scan over using the index.51  </p><p>52   A multicolumn GiST index can be used with query conditions that53   involve any subset of the index's columns. Conditions on additional54   columns restrict the entries returned by the index, but the condition on55   the first column is the most important one for determining how much of56   the index needs to be scanned.  A GiST index will be relatively57   ineffective if its first column has only a few distinct values, even if58   there are many distinct values in additional columns.59  </p><p>60   A multicolumn GIN index can be used with query conditions that61   involve any subset of the index's columns. Unlike B-tree or GiST,62   index search effectiveness is the same regardless of which index column(s)63   the query conditions use.64  </p><p>65   A multicolumn BRIN index can be used with query conditions that66   involve any subset of the index's columns. Like GIN and unlike B-tree or67   GiST, index search effectiveness is the same regardless of which index68   column(s) the query conditions use. The only reason to have multiple BRIN69   indexes instead of one multicolumn BRIN index on a single table is to have70   a different <code class="literal">pages_per_range</code> storage parameter.71  </p><p>72   Of course, each column must be used with operators appropriate to the index73   type; clauses that involve other operators will not be considered.74  </p><p>75   Multicolumn indexes should be used sparingly.  In most situations,76   an index on a single column is sufficient and saves space and time.77   Indexes with more than three columns are unlikely to be helpful78   unless the usage of the table is extremely stylized.  See also79   <a class="xref" href="indexes-bitmap-scans.html" title="11.5. Combining Multiple Indexes">Section 11.5</a> and80   <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 the81   merits of different index configurations.82  </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 16.3 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>