begriffs open source - ai-pg/blob - full-docs/txt/indexes-partial.txt

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