indices.sgml

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

    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 may be among the billed or among the 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</> 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</> condition
   or the index will not be recognized to be usable.
  </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</> entry for
    a given subject and target combination, but there might be any number of
    <quote>unsuccessful</> 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>
    This is a particularly efficient way of doing it when there are few
    successful tests and many unsuccessful ones.
   </para>
  </example>

  <para>
   Finally, a partial index can also be used to override the system's
   query plan choices.  It may occur that data sets with peculiar
   distributions will cause the system to use an index when it really
   should not.  In that case the index can be set up so that it is not
   available for the offending query.  Normally,
   <productname>PostgreSQL</> makes reasonable choices about index
   usage (e.g., it avoids them when retrieving common values, so the
   earlier example really only saves index size, it is not required to
   avoid index usage), and grossly incorrect plan choices are cause
   for a bug report.
  </para>

  <para>
   Keep in mind that setting up a partial index indicates that you
   know at least as much as the query planner knows, in particular you
   know when an index might be profitable.  Forming this knowledge
   requires experience and understanding of how indexes in
   <productname>PostgreSQL</> work.  In most cases, the advantage of a
   partial index over a regular index will not be much.
  </para>

  <para>
   More information about partial indexes can be found in <xref
   linkend="STON89b">, <xref linkend="OLSON93">, and <xref
   linkend="SESHADRI95">.
  </para>
 </sect1>


 <sect1 id="indexes-opclass">
  <title>Operator Classes</title>

  <indexterm zone="indexes-opclass">
   <primary>operator class</primary>
  </indexterm>

  <para>
   An index definition may specify an <firstterm>operator
   class</firstterm> for each column of an index.
<synopsis>
CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> (<replaceable>column</replaceable> <replaceable>opclass</replaceable> <optional>, ...</optional>);
</synopsis>
   The operator class identifies the operators to be used by the index
   for that column.  For example, a B-tree index on the type <type>int4</type>
   would use the <literal>int4_ops</literal> class; this operator
   class includes comparison functions for values of type <type>int4</type>.
   In practice the default operator class for the column's data type is
   usually sufficient.  The main point of having operator classes is
   that for some data types, there could be more than one meaningful
   index behavior.  For example, we might want to sort a complex-number data
   type either by absolute value or by real part.  We could do this by
   defining two operator classes for the data type and then selecting
   the proper class when making an index.
  </para>

  <para>
   There are also some built-in operator classes besides the default ones:

   <itemizedlist>
    <listitem>
     <para>
      The operator classes <literal>text_pattern_ops</literal>,
      <literal>varchar_pattern_ops</literal>,
      <literal>bpchar_pattern_ops</literal>, and
      <literal>name_pattern_ops</literal> support B-tree indexes on
      the types <type>text</type>, <type>varchar</type>,
      <type>char</type>, and <type>name</type>, respectively.  The
      difference from the default operator classes is that the values
      are compared strictly character by character rather than
      according to the locale-specific collation rules.  This makes
      these operator classes suitable for use by queries involving
      pattern matching expressions (<literal>LIKE</literal> or POSIX
      regular expressions) when the server does not use the standard
      <quote>C</quote> locale.  As an example, you might index a
      <type>varchar</type> column like this:
<programlisting>
CREATE INDEX test_index ON test_table (col varchar_pattern_ops);
</programlisting>
      Note that you should also create an index with the default operator
      class if you want queries involving ordinary comparisons to use an
      index.  Such queries cannot use the
      <literal><replaceable>xxx</replaceable>_pattern_ops</literal>
      operator classes.  It is allowed to create multiple
      indexes on the same column with different operator classes.
      If you do use the C locale, you do not need the
      <literal><replaceable>xxx</replaceable>_pattern_ops</literal>
      operator classes, because an index with the default operator class
      is usable for pattern-matching queries in the C locale.
     </para>
    </listitem>
   </itemizedlist>
  </para>

  <para>
    The following query shows all defined operator classes:

<programlisting>
SELECT am.amname AS index_method,
       opc.opcname AS opclass_name
    FROM pg_am am, pg_opclass opc
    WHERE opc.opcamid = am.oid
    ORDER BY index_method, opclass_name;
</programlisting>

    It can be extended to show all the operators included in each class:
<programlisting>
SELECT am.amname AS index_method,
       opc.opcname AS opclass_name,
       opr.oid::regoperator AS opclass_operator
    FROM pg_am am, pg_opclass opc, pg_amop amop, pg_operator opr
    WHERE opc.opcamid = am.oid AND
          amop.amopclaid = opc.oid AND
          amop.amopopr = opr.oid
    ORDER BY index_method, opclass_name, opclass_operator;
</programlisting>
  </para>
 </sect1>


 <sect1 id="indexes-examine">
  <title>Examining Index Usage</title>

  <indexterm zone="indexes-examine">
   <primary>index</primary>
   <secondary>examining usage</secondary>
  </indexterm>

  <para>
   Although indexes in <productname>PostgreSQL</> do not need
   maintenance and tuning, it is still important to check
   which indexes are actually used by the real-life query workload.
   Examining index usage for an individual query is done with the
   <xref linkend="sql-explain" endterm="sql-explain-title">
   command; its application for this purpose is
   illustrated in <xref linkend="using-explain">.
   It is also possible to gather overall statistics about index usage
   in a running server, as described in <xref linkend="monitoring-stats">.
  </para>

  <para>
   It is difficult to formulate a general procedure for determining
   which indexes to set up.  There are a number of typical cases that
   have been shown in the examples throughout the previous sections.
   A good deal of experimentation will be necessary in most cases.
   The rest of this section gives some tips for that.
  </para>

  <itemizedlist>
   <listitem>
    <para>
     Always run <xref linkend="sql-analyze" endterm="sql-analyze-title">
     first.  This command
     collects statistics about the distribution of the values in the
     table.  This information is required to guess the number of rows
     returned by a query, which is needed by the planner to assign
     realistic costs to each possible query plan.  In absence of any
     real statistics, some default values are assumed, which are
     almost certain to be inaccurate.  Examining an application's
     index usage without having run <command>ANALYZE</command> is
     therefore a lost cause.
    </para>
   </listitem>

   <listitem>
    <para>
     Use real data for experimentation.  Using test data for setting
     up indexes will tell you what indexes you need for the test data,
     but that is all.
    </para>

    <para>
     It is especially fatal to use very small test data sets.
     While selecting 1000 out of 100000 rows could be a candidate for
     an index, selecting 1 out of 100 rows will hardly be, because the
     100 rows will probably fit within a single disk page, and there
     is no plan that can beat sequentially fetching 1 disk page.
    </para>

    <para>
     Also be careful when making up test data, which is often
     unavoidable when the application is not in production use yet.
     Values that are very similar, completely random, or inserted in
     sorted order will skew the statistics away from the distribution
     that real data would have.
    </para>
   </listitem>

   <listitem>
    <para>
     When indexes are not used, it can be useful for testing to force
     their use.  There are run-time parameters that can turn off
     various plan types (see <xref linkend="runtime-config-query-enable">).
     For instance, turning off sequential scans
     (<varname>enable_seqscan</>) and nested-loop joins
     (<varname>enable_nestloop</>), which are the most basic plans,
     will force the system to use a different plan.  If the system
     still chooses a sequential scan or nested-loop join then there is
     probably a more fundamental reason why the index is not
     used; for example, the query condition does not match the index.
     (What kind of query can use what kind of index is explained in
     the previous sections.)
    </para>
   </listitem>

   <listitem>
    <para>
     If forcing index usage does use the index, then there are two
     possibilities: Either the system is right and using the index is
     indeed not appropriate, or the cost estimates of the query plans
     are not reflecting reality.  So you should time your query with
     and without indexes.  The <command>EXPLAIN ANALYZE</command>
     command can be useful here.
    </para>
   </listitem>

   <listitem>
    <para>
     If it turns out that the cost estimates are wrong, there are,
     again, two possibilities.  The total cost is computed from the
     per-row costs of each plan node times the selectivity estimate of
     the plan node.  The costs estimated for the plan nodes can be adjusted
     via run-time parameters (described in <xref
     linkend="runtime-config-query-constants">).
     An inaccurate selectivity estimate is due to
     insufficient statistics.  It may be possible to improve this by
     tuning the statistics-gathering parameters (see
     <xref linkend="sql-altertable" endterm="sql-altertable-title">).
    </para>

    <para>
     If you do not succeed in adjusting the costs to be more
     appropriate, then you may have to resort to forcing index usage
     explicitly.  You may also want to contact the
     <productname>PostgreSQL</> developers to examine the issue.
    </para>
   </listitem>
  </itemizedlist>
 </sect1>
</chapter>

<!-- Keep this comment at the end of the file
Local variables:
mode:sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:("/usr/lib/sgml/catalog")
sgml-local-ecat-files:nil
End:
-->