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.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 16.3 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 a4   subset of a table; the subset is defined by a conditional5   expression (called the <em class="firstterm">predicate</em> of the6   partial index).  The index contains entries only for those table7   rows that satisfy the predicate.  Partial indexes are a specialized8   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 common11   values.  Since a query searching for a common value (one that12   accounts for more than a few percent of all the table rows) will not13   use the index anyway, there is no point in keeping those rows in the14   index at all.  This reduces the size of the index, which will speed15   up those queries that do use the index.  It will also speed up many table16   update operations because the index does not need to be17   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 a18   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 but22    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 your25    organization's subnet.26   </p><p>27    Assume a table like this:28</p><pre class="programlisting">29CREATE 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 command37    such as this:38</p><pre class="programlisting">39CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)40WHERE NOT (client_ip &gt; inet '192.168.100.0' AND41           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">46SELECT *47FROM access_log48WHERE 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.  The51    following query cannot use the partial index, as it uses an IP address52    that is excluded from the index:53</p><pre class="programlisting">54SELECT *55FROM access_log56WHERE 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 common60    values be predetermined, so such partial indexes are best used for61    data distributions that do not change.  Such indexes can be recreated62    occasionally to adjust for new data distributions, but this adds63    maintenance effort.64   </p></div></div><br class="example-break" /><p>65   Another possible use for a partial index is to exclude values from the66   index that the67   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 same68   advantages as listed above, but it prevents the69   <span class="quote">“<span class="quote">uninteresting</span>”</span> values from being accessed via that70   index, even if an index scan might be profitable in that71   case.  Obviously, setting up partial indexes for this kind of72   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 total76    table and yet those are the most-accessed rows, you can improve77    performance by creating an index on just the unbilled rows.  The78    command to create the index would look like this:79</p><pre class="programlisting">80CREATE 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">86SELECT * 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 involve89    <code class="structfield">order_nr</code> at all, e.g.:90</p><pre class="programlisting">91SELECT * 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 the94    <code class="structfield">amount</code> column would be, since the system has to95    scan the entire index.  Yet, if there are relatively few unbilled96    orders, using this partial index just to find the unbilled orders97    could be a win.98   </p><p>99    Note that this query cannot use this index:100</p><pre class="programlisting">101SELECT * FROM orders WHERE order_nr = 3501;102</pre><p>103    The order 3501 might be among the billed or unbilled104    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 the107   indexed column and the column used in the predicate do not need to108   match.  <span class="productname">PostgreSQL</span> supports partial109   indexes with arbitrary predicates, so long as only columns of the110   table being indexed are involved.  However, keep in mind that the111   predicate must match the conditions used in the queries that112   are supposed to benefit from the index.  To be precise, a partial113   index can be used in a query only if the system can recognize that114   the <code class="literal">WHERE</code> condition of the query mathematically implies115   the predicate of the index.116   <span class="productname">PostgreSQL</span> does not have a sophisticated117   theorem prover that can recognize mathematically equivalent118   expressions that are written in different forms.  (Not119   only is such a general theorem prover extremely difficult to120   create, it would probably be too slow to be of any real use.)121   The system can recognize simple inequality implications, for example122   <span class="quote">“<span class="quote">x &lt; 1</span>”</span> implies <span class="quote">“<span class="quote">x &lt; 2</span>”</span>; otherwise123   the predicate condition must exactly match part of the query's124   <code class="literal">WHERE</code> condition125   or the index will not be recognized as usable. Matching takes126   place at query planning time, not at run time. As a result,127   parameterized query clauses do not work with a partial index. For128   example a prepared query with a parameter might specify129   <span class="quote">“<span class="quote">x &lt; ?</span>”</span> which will never imply130   <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 the133   index to be used in queries at all.  The idea here is to create134   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 uniqueness135   among the rows that satisfy the index predicate, without constraining136   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 wish139    to ensure that there is only one <span class="quote">“<span class="quote">successful</span>”</span> entry for140    a given subject and target combination, but there might be any number of141    <span class="quote">“<span class="quote">unsuccessful</span>”</span> entries.  Here is one way to do it:142</p><pre class="programlisting">143CREATE TABLE tests (144    subject text,145    target text,146    success boolean,147    ...148);149 150CREATE 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 few154    successful tests and many unsuccessful ones.  It is also possible to155    allow only one null in a column by creating a unique partial index156    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's159   query plan choices.  Also, data sets with peculiar160   distributions might cause the system to use an index when it really161   should not.  In that case the index can be set up so that it is not162   available for the offending query.  Normally,163   <span class="productname">PostgreSQL</span> makes reasonable choices about index164   usage (e.g., it avoids them when retrieving common values, so the165   earlier example really only saves index size, it is not required to166   avoid index usage), and grossly incorrect plan choices are cause167   for a bug report.168  </p><p>169   Keep in mind that setting up a partial index indicates that you170   know at least as much as the query planner knows, in particular you171   know when an index might be profitable.  Forming this knowledge172   requires experience and understanding of how indexes in173   <span class="productname">PostgreSQL</span> work.  In most cases, the174   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 partial178    indexes, for example179 180</p><pre class="programlisting">181CREATE INDEX mytable_cat_1 ON mytable (data) WHERE category = 1;182CREATE INDEX mytable_cat_2 ON mytable (data) WHERE category = 2;183CREATE INDEX mytable_cat_3 ON mytable (data) WHERE category = 3;184...185CREATE 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 a189    single non-partial index, declared like190 191</p><pre class="programlisting">192CREATE INDEX mytable_cat_data ON mytable (category, data);193</pre><p>194 195    (Put the category column first, for the reasons described in196    <a class="xref" href="indexes-multicolumn.html" title="11.3. Multicolumn Indexes">Section 11.3</a>.)  While a search in this larger197    index might have to descend through a couple more tree levels than a198    search in a smaller index, that's almost certainly going to be cheaper199    than the planner effort needed to select the appropriate one of the200    partial indexes.  The core of the problem is that the system does not201    understand the relationship among the partial indexes, and will202    laboriously test each one to see if it's applicable to the current203    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 (see207    <a class="xref" href="ddl-partitioning.html" title="5.11. Table Partitioning">Section 5.11</a>).  With that mechanism, the system208    does understand that the tables and indexes are non-overlapping, so209    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 16.3 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>