begriffs open source - ai-pg/blob - full-docs/src/sgml/html/indexes-partial.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.8. Partial 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-expressional.html" title="11.7. Indexes on Expressions" /><link rel="next" href="indexes-index-only-scans.html" title="11.9. Index-Only Scans and Covering Indexes" /></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.8. Partial Indexes</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="indexes-expressional.html" title="11.7. Indexes on Expressions">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-index-only-scans.html" title="11.9. Index-Only Scans and Covering Indexes">Next</a></td></tr></table><hr /></div><div class="sect1" id="INDEXES-PARTIAL"><div class="titlepage"><div><div><h2 class="title" style="clear: both">11.8. Partial Indexes <a href="#INDEXES-PARTIAL" class="id_link">#</a></h2></div></div></div><a id="id-1.5.10.11.2" class="indexterm"></a><p>
   3    A <em class="firstterm">partial index</em> is an index built over a
   4    subset of a table; the subset is defined by a conditional
   5    expression (called the <em class="firstterm">predicate</em> of the
   6    partial index).  The index contains entries only for those table
   7    rows that satisfy the predicate.  Partial indexes are a specialized
   8    feature, but there are several situations in which they are useful.
   9   </p><p>
  10    One major reason for using a partial index is to avoid indexing common
  11    values.  Since a query searching for a common value (one that
  12    accounts for more than a few percent of all the table rows) will not
  13    use the index anyway, there is no point in keeping those rows in the
  14    index at all.  This reduces the size of the index, which will speed
  15    up those queries that do use the index.  It will also speed up many table
  16    update operations because the index does not need to be
  17    updated in all cases.  <a class="xref" href="indexes-partial.html#INDEXES-PARTIAL-EX1" title="Example 11.1. Setting up a Partial Index to Exclude Common Values">Example 11.1</a> shows a
  18    possible application of this idea.
  19   </p><div class="example" id="INDEXES-PARTIAL-EX1"><p class="title"><strong>Example 11.1. Setting up a Partial Index to Exclude Common Values</strong></p><div class="example-contents"><p>
  20     Suppose you are storing web server access logs in a database.
  21     Most accesses originate from the IP address range of your organization but
  22     some are from elsewhere (say, employees on dial-up connections).
  23     If your searches by IP are primarily for outside accesses,
  24     you probably do not need to index the IP range that corresponds to your
  25     organization's subnet.
  26    </p><p>
  27     Assume a table like this:
  28 </p><pre class="programlisting">
  29 CREATE TABLE access_log (
  30     url varchar,
  31     client_ip inet,
  32     ...
  33 );
  34 </pre><p>
  35    </p><p>
  36     To create a partial index that suits our example, use a command
  37     such as this:
  38 </p><pre class="programlisting">
  39 CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
  40 WHERE NOT (client_ip &gt; inet '192.168.100.0' AND
  41            client_ip &lt; inet '192.168.100.255');
  42 </pre><p>
  43    </p><p>
  44     A typical query that can use this index would be:
  45 </p><pre class="programlisting">
  46 SELECT *
  47 FROM access_log
  48 WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';
  49 </pre><p>
  50     Here the query's IP address is covered by the partial index.  The
  51     following query cannot use the partial index, as it uses an IP address
  52     that is excluded from the index:
  53 </p><pre class="programlisting">
  54 SELECT *
  55 FROM access_log
  56 WHERE url = '/index.html' AND client_ip = inet '192.168.100.23';
  57 </pre><p>
  58    </p><p>
  59     Observe that this kind of partial index requires that the common
  60     values be predetermined, so such partial indexes are best used for
  61     data distributions that do not change.  Such indexes can be recreated
  62     occasionally to adjust for new data distributions, but this adds
  63     maintenance effort.
  64    </p></div></div><br class="example-break" /><p>
  65    Another possible use for a partial index is to exclude values from the
  66    index that the
  67    typical query workload is not interested in; this is shown in <a class="xref" href="indexes-partial.html#INDEXES-PARTIAL-EX2" title="Example 11.2. Setting up a Partial Index to Exclude Uninteresting Values">Example 11.2</a>.  This results in the same
  68    advantages as listed above, but it prevents the
  69    <span class="quote">“<span class="quote">uninteresting</span>”</span> values from being accessed via that
  70    index, even if an index scan might be profitable in that
  71    case.  Obviously, setting up partial indexes for this kind of
  72    scenario will require a lot of care and experimentation.
  73   </p><div class="example" id="INDEXES-PARTIAL-EX2"><p class="title"><strong>Example 11.2. Setting up a Partial Index to Exclude Uninteresting Values</strong></p><div class="example-contents"><p>
  74     If you have a table that contains both billed and unbilled orders,
  75     where the unbilled orders take up a small fraction of the total
  76     table and yet those are the most-accessed rows, you can improve
  77     performance by creating an index on just the unbilled rows.  The
  78     command to create the index would look like this:
  79 </p><pre class="programlisting">
  80 CREATE INDEX orders_unbilled_index ON orders (order_nr)
  81     WHERE billed is not true;
  82 </pre><p>
  83    </p><p>
  84     A possible query to use this index would be:
  85 </p><pre class="programlisting">
  86 SELECT * FROM orders WHERE billed is not true AND order_nr &lt; 10000;
  87 </pre><p>
  88     However, the index can also be used in queries that do not involve
  89     <code class="structfield">order_nr</code> at all, e.g.:
  90 </p><pre class="programlisting">
  91 SELECT * FROM orders WHERE billed is not true AND amount &gt; 5000.00;
  92 </pre><p>
  93     This is not as efficient as a partial index on the
  94     <code class="structfield">amount</code> column would be, since the system has to
  95     scan the entire index.  Yet, if there are relatively few unbilled
  96     orders, using this partial index just to find the unbilled orders
  97     could be a win.
  98    </p><p>
  99     Note that this query cannot use this index:
 100 </p><pre class="programlisting">
 101 SELECT * FROM orders WHERE order_nr = 3501;
 102 </pre><p>
 103     The order 3501 might be among the billed or unbilled
 104     orders.
 105    </p></div></div><br class="example-break" /><p>
 106    <a class="xref" href="indexes-partial.html#INDEXES-PARTIAL-EX2" title="Example 11.2. Setting up a Partial Index to Exclude Uninteresting Values">Example 11.2</a> also illustrates that the
 107    indexed column and the column used in the predicate do not need to
 108    match.  <span class="productname">PostgreSQL</span> supports partial
 109    indexes with arbitrary predicates, so long as only columns of the
 110    table being indexed are involved.  However, keep in mind that the
 111    predicate must match the conditions used in the queries that
 112    are supposed to benefit from the index.  To be precise, a partial
 113    index can be used in a query only if the system can recognize that
 114    the <code class="literal">WHERE</code> condition of the query mathematically implies
 115    the predicate of the index.
 116    <span class="productname">PostgreSQL</span> does not have a sophisticated
 117    theorem prover that can recognize mathematically equivalent
 118    expressions that are written in different forms.  (Not
 119    only is such a general theorem prover extremely difficult to
 120    create, it would probably be too slow to be of any real use.)
 121    The system can recognize simple inequality implications, for example
 122    <span class="quote">“<span class="quote">x &lt; 1</span>”</span> implies <span class="quote">“<span class="quote">x &lt; 2</span>”</span>; otherwise
 123    the predicate condition must exactly match part of the query's
 124    <code class="literal">WHERE</code> condition
 125    or the index will not be recognized as usable. Matching takes
 126    place at query planning time, not at run time. As a result,
 127    parameterized query clauses do not work with a partial index. For
 128    example a prepared query with a parameter might specify
 129    <span class="quote">“<span class="quote">x &lt; ?</span>”</span> which will never imply
 130    <span class="quote">“<span class="quote">x &lt; 2</span>”</span> for all possible values of the parameter.
 131   </p><p>
 132    A third possible use for partial indexes does not require the
 133    index to be used in queries at all.  The idea here is to create
 134    a unique index over a subset of a table, as in <a class="xref" href="indexes-partial.html#INDEXES-PARTIAL-EX3" title="Example 11.3. Setting up a Partial Unique Index">Example 11.3</a>.  This enforces uniqueness
 135    among the rows that satisfy the index predicate, without constraining
 136    those that do not.
 137   </p><div class="example" id="INDEXES-PARTIAL-EX3"><p class="title"><strong>Example 11.3. Setting up a Partial Unique Index</strong></p><div class="example-contents"><p>
 138     Suppose that we have a table describing test outcomes.  We wish
 139     to ensure that there is only one <span class="quote">“<span class="quote">successful</span>”</span> entry for
 140     a given subject and target combination, but there might be any number of
 141     <span class="quote">“<span class="quote">unsuccessful</span>”</span> entries.  Here is one way to do it:
 142 </p><pre class="programlisting">
 143 CREATE TABLE tests (
 144     subject text,
 145     target text,
 146     success boolean,
 147     ...
 148 );
 149
 150 CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
 151     WHERE success;
 152 </pre><p>
 153     This is a particularly efficient approach when there are few
 154     successful tests and many unsuccessful ones.  It is also possible to
 155     allow only one null in a column by creating a unique partial index
 156     with an <code class="literal">IS NULL</code> restriction.
 157    </p></div></div><br class="example-break" /><p>
 158    Finally, a partial index can also be used to override the system's
 159    query plan choices.  Also, data sets with peculiar
 160    distributions might cause the system to use an index when it really
 161    should not.  In that case the index can be set up so that it is not
 162    available for the offending query.  Normally,
 163    <span class="productname">PostgreSQL</span> makes reasonable choices about index
 164    usage (e.g., it avoids them when retrieving common values, so the
 165    earlier example really only saves index size, it is not required to
 166    avoid index usage), and grossly incorrect plan choices are cause
 167    for a bug report.
 168   </p><p>
 169    Keep in mind that setting up a partial index indicates that you
 170    know at least as much as the query planner knows, in particular you
 171    know when an index might be profitable.  Forming this knowledge
 172    requires experience and understanding of how indexes in
 173    <span class="productname">PostgreSQL</span> work.  In most cases, the
 174    advantage of a partial index over a regular index will be minimal.
 175    There are cases where they are quite counterproductive, as in <a class="xref" href="indexes-partial.html#INDEXES-PARTIAL-EX4" title="Example 11.4. Do Not Use Partial Indexes as a Substitute for Partitioning">Example 11.4</a>.
 176   </p><div class="example" id="INDEXES-PARTIAL-EX4"><p class="title"><strong>Example 11.4. Do Not Use Partial Indexes as a Substitute for Partitioning</strong></p><div class="example-contents"><p>
 177     You might be tempted to create a large set of non-overlapping partial
 178     indexes, for example
 179
 180 </p><pre class="programlisting">
 181 CREATE INDEX mytable_cat_1 ON mytable (data) WHERE category = 1;
 182 CREATE INDEX mytable_cat_2 ON mytable (data) WHERE category = 2;
 183 CREATE INDEX mytable_cat_3 ON mytable (data) WHERE category = 3;
 184 ...
 185 CREATE INDEX mytable_cat_<em class="replaceable"><code>N</code></em> ON mytable (data) WHERE category = <em class="replaceable"><code>N</code></em>;
 186 </pre><p>
 187
 188     This is a bad idea!  Almost certainly, you'll be better off with a
 189     single non-partial index, declared like
 190
 191 </p><pre class="programlisting">
 192 CREATE INDEX mytable_cat_data ON mytable (category, data);
 193 </pre><p>
 194
 195     (Put the category column first, for the reasons described in
 196     <a class="xref" href="indexes-multicolumn.html" title="11.3. Multicolumn Indexes">Section 11.3</a>.)  While a search in this larger
 197     index might have to descend through a couple more tree levels than a
 198     search in a smaller index, that's almost certainly going to be cheaper
 199     than the planner effort needed to select the appropriate one of the
 200     partial indexes.  The core of the problem is that the system does not
 201     understand the relationship among the partial indexes, and will
 202     laboriously test each one to see if it's applicable to the current
 203     query.
 204    </p><p>
 205     If your table is large enough that a single index really is a bad idea,
 206     you should look into using partitioning instead (see
 207     <a class="xref" href="ddl-partitioning.html" title="5.12. Table Partitioning">Section 5.12</a>).  With that mechanism, the system
 208     does understand that the tables and indexes are non-overlapping, so
 209     far better performance is possible.
 210    </p></div></div><br class="example-break" /><p>
 211    More information about partial indexes can be found in <a class="xref" href="biblio.html#STON89B">[ston89b]</a>, <a class="xref" href="biblio.html#OLSON93" title="Partial indexing in POSTGRES: research project">[olson93]</a>, and <a class="xref" href="biblio.html#SESHADRI95">[seshadri95]</a>.
 212   </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="indexes-expressional.html" title="11.7. Indexes on Expressions">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-index-only-scans.html" title="11.9. Index-Only Scans and Covering Indexes">Next</a></td></tr><tr><td width="40%" align="left" valign="top">11.7. Indexes on Expressions </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.9. Index-Only Scans and Covering Indexes</td></tr></table></div></body></html>