summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/indexes-multicolumn.html
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
commit5e45211a64149b3c659b90ff2de6fa982a5a93ed (patch)
tree739caf8c461053357daa9f162bef34516c7bf452 /doc/src/sgml/html/indexes-multicolumn.html
parentInitial commit. (diff)
downloadpostgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.tar.xz
postgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.zip
Adding upstream version 15.5.upstream/15.5
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/indexes-multicolumn.html')
-rw-r--r--doc/src/sgml/html/indexes-multicolumn.html82
1 files changed, 82 insertions, 0 deletions
diff --git a/doc/src/sgml/html/indexes-multicolumn.html b/doc/src/sgml/html/indexes-multicolumn.html
new file mode 100644
index 0000000..e0d0182
--- /dev/null
+++ b/doc/src/sgml/html/indexes-multicolumn.html
@@ -0,0 +1,82 @@
+<?xml version="1.0" encoding="UTF-8" standalone="no"?>
+<!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 15.5 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</h2></div></div></div><a id="id-1.5.10.6.2" class="indexterm"></a><p>
+ An index can be defined on more than one column of a table. For example, if
+ you have a table of this form:
+</p><pre class="programlisting">
+CREATE TABLE test2 (
+ major int,
+ minor int,
+ name varchar
+);
+</pre><p>
+ (say, you keep your <code class="filename">/dev</code>
+ directory in a database...) and you frequently issue queries like:
+</p><pre class="programlisting">
+SELECT name FROM test2 WHERE major = <em class="replaceable"><code>constant</code></em> AND minor = <em class="replaceable"><code>constant</code></em>;
+</pre><p>
+ then it might be appropriate to define an index on the columns
+ <code class="structfield">major</code> and
+ <code class="structfield">minor</code> together, e.g.:
+</p><pre class="programlisting">
+CREATE INDEX test2_mm_idx ON test2 (major, minor);
+</pre><p>
+ </p><p>
+ Currently, only the B-tree, GiST, GIN, and BRIN index types support
+ multiple-key-column indexes. Whether there can be multiple key
+ columns is independent of whether <code class="literal">INCLUDE</code> columns
+ can be added to the index. Indexes can have up to 32 columns,
+ including <code class="literal">INCLUDE</code> columns. (This limit can be
+ altered when building <span class="productname">PostgreSQL</span>; see the
+ file <code class="filename">pg_config_manual.h</code>.)
+ </p><p>
+ A multicolumn B-tree index can be used with query conditions that
+ involve any subset of the index's columns, but the index is most
+ efficient when there are constraints on the leading (leftmost) columns.
+ The exact rule is that equality constraints on leading columns, plus
+ any inequality constraints on the first column that does not have an
+ equality constraint, will be used to limit the portion of the index
+ that is scanned. Constraints on columns to the right of these columns
+ are checked in the index, so they save visits to the table proper, but
+ they do not reduce the portion of the index that has to be scanned.
+ For example, given an index on <code class="literal">(a, b, c)</code> and a
+ query condition <code class="literal">WHERE a = 5 AND b &gt;= 42 AND c &lt; 77</code>,
+ the index would have to be scanned from the first entry with
+ <code class="literal">a</code> = 5 and <code class="literal">b</code> = 42 up through the last entry with
+ <code class="literal">a</code> = 5. Index entries with <code class="literal">c</code> &gt;= 77 would be
+ skipped, but they'd still have to be scanned through.
+ This index could in principle be used for queries that have constraints
+ on <code class="literal">b</code> and/or <code class="literal">c</code> with no constraint on <code class="literal">a</code>
+ — but the entire index would have to be scanned, so in most cases
+ the planner would prefer a sequential table scan over using the index.
+ </p><p>
+ A multicolumn GiST index can be used with query conditions that
+ involve any subset of the index's columns. Conditions on additional
+ columns restrict the entries returned by the index, but the condition on
+ the first column is the most important one for determining how much of
+ the index needs to be scanned. A GiST index will be relatively
+ ineffective if its first column has only a few distinct values, even if
+ there are many distinct values in additional columns.
+ </p><p>
+ A multicolumn GIN index can be used with query conditions that
+ involve any subset of the index's columns. Unlike B-tree or GiST,
+ index search effectiveness is the same regardless of which index column(s)
+ the query conditions use.
+ </p><p>
+ A multicolumn BRIN index can be used with query conditions that
+ involve any subset of the index's columns. Like GIN and unlike B-tree or
+ GiST, index search effectiveness is the same regardless of which index
+ column(s) the query conditions use. The only reason to have multiple BRIN
+ indexes instead of one multicolumn BRIN index on a single table is to have
+ a different <code class="literal">pages_per_range</code> storage parameter.
+ </p><p>
+ Of course, each column must be used with operators appropriate to the index
+ type; clauses that involve other operators will not be considered.
+ </p><p>
+ Multicolumn indexes should be used sparingly. In most situations,
+ an index on a single column is sufficient and saves space and time.
+ Indexes with more than three columns are unlikely to be helpful
+ unless the usage of the table is extremely stylized. See also
+ <a class="xref" href="indexes-bitmap-scans.html" title="11.5. Combining Multiple Indexes">Section 11.5</a> and
+ <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
+ merits of different index configurations.
+ </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 15.5 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> \ No newline at end of file