Uninteresting Values</title>

   <para>
    If you have a table that contains both billed and unbilled orders,
    where the unbilled orders take up a small fraction of the total
    table and yet those are the most-accessed rows, you can improve
    performance by creating an index on just the unbilled rows.  The
    command to create the index would look like this:
<programlisting>
CREATE INDEX orders_unbilled_index ON orders (order_nr)
    WHERE billed is not true;
</programlisting>
   </para>

   <para>
    A possible query to use this index would be:
<programlisting>
SELECT * FROM orders WHERE billed is not true AND order_nr &lt; 10000;
</programlisting>
    However, the index can also be used in queries that do not involve
    <structfield>order_nr</structfield> at all, e.g.:
<programlisting>
SELECT * FROM orders WHERE billed is not true AND amount &gt; 5000.00;
</programlisting>
    This is not as efficient as a partial index on the
    <structfield>amount</structfield> column would be, since the system has to
    scan the entire index.  Yet, if there are relatively few unbilled
    orders, using this partial index just to find the unbilled orders
    could be a win.
   </para>

   <para>
    Note that this query cannot use this index:
<programlisting>
SELECT * FROM orders WHERE order_nr = 3501;
</programlisting>
    The order 3501 might be among the billed or unbilled
    orders.
   </para>
  </example>

  <para>
   <xref linkend="indexes-partial-ex2"/> also illustrates that the
   indexed column and the column used in the predicate do not need to
   match.  <productname>PostgreSQL</productname> supports partial
   indexes with arbitrary predicates, so long as only columns of the
   table being indexed are involved.  However, keep in mind that the
   predicate must match the conditions used in the queries that
   are supposed to benefit from the index.  To be precise, a partial
   index can be used in a query only if the system can recognize that
   the <literal>WHERE</literal> condition of the query mathematically implies
   the predicate of the index.
   <productname>PostgreSQL</productname> does not have a sophisticated
   theorem prover that can recognize mathematically equivalent
   expressions that are written in different forms.  (Not
   only is such a general theorem prover extremely difficult to
   create, it would probably be too slow to be of any real use.)
   The system can recognize simple inequality implications, for example
   <quote>x &lt; 1</quote> implies <quote>x &lt; 2</quote>; otherwise
   the predicate condition must exactly match part of the query's
   <literal>WHERE</literal> condition
   or the index will not be recognized as usable. Matching takes
   place at query planning time, not at run time. As a result,
   parameterized query clauses do not work with a partial index. For
   example a prepared query with a parameter might specify
   <quote>x &lt; ?</quote> which will never imply
   <quote>x &lt; 2</quote> for all possible values of the parameter.
  </para>

  <para>
   A third possible use for partial indexes does not require the
   index to be used in queries at all.  The idea here is to create
   a unique index over a subset of a table, as in <xref
   linkend="indexes-partial-ex3"/>.  This enforces uniqueness
   among the rows that satisfy the index predicate, without constraining
   those that do not.
  </para>

  <example id="indexes-partial-ex3">
   <title>Setting up a Partial Unique Index</title>

   <para>
    Suppose that we have a table describing test outcomes.  We wish
    to ensure that there is only one <quote>successful</quote> entry for
    a given subject and target combination, but there might be any number of
    <quote>unsuccessful</quote> entries.  Here is one way to do it:
<programlisting>
CREATE TABLE tests (
    subject text,
    target text,
    success boolean,
    ...
);

CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
    WHERE success;
</programlisting>