diff options
Diffstat (limited to 'doc/src/sgml/html/indexes-multicolumn.html')
| -rw-r--r-- | doc/src/sgml/html/indexes-multicolumn.html | 82 |
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..a759814 --- /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.4 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 >= 42 AND c < 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> >= 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.4 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 |