about
   how to find out whether an index is used and when and why the
   planner might choose <emphasis>not</emphasis> to use an index.
  </para>

  <para>
   Indexes can also benefit <command>UPDATE</command> and
   <command>DELETE</command> commands with search conditions.
   Indexes can moreover be used in join searches.  Thus,
   an index defined on a column that is part of a join condition can
   also significantly speed up queries with joins.
  </para>

  <para>
   In general, <productname>PostgreSQL</productname> indexes can be used
   to optimize queries that contain one or more <literal>WHERE</literal>
   or <literal>JOIN</literal> clauses of the form

<synopsis>
<replaceable>indexed-column</replaceable> <replaceable>indexable-operator</replaceable> <replaceable>comparison-value</replaceable>
</synopsis>

   Here, the <replaceable>indexed-column</replaceable> is whatever
   column or expression the index has been defined on.
   The <replaceable>indexable-operator</replaceable> is an operator that
   is a member of the index's <firstterm>operator class</firstterm> for
   the indexed column.  (More details about that appear below.)
   And the <replaceable>comparison-value</replaceable> can be any
   expression that is not volatile and does not reference the index's
   table.
  </para>

  <para>
   In some cases the query planner can extract an indexable clause of
   this form from another SQL construct.  A simple example is that if
   the original clause was

<synopsis>
<replaceable>comparison-value</replaceable> <replaceable>operator</replaceable> <replaceable>indexed-column</replaceable>
</synopsis>

   then it can be flipped around into indexable form if the
   original <replaceable>operator</replaceable> has a commutator
   operator that is a member of the index's operator class.
  </para>

  <para>
   Creating an index on a large table can take a long time.  By default,
   <productname>PostgreSQL</productname> allows reads (<command>SELECT</command> statements) to occur
   on the table in parallel with index creation, but writes (<command>INSERT</command>,
   <command>UPDATE</command>, <command>DELETE</command>) are blocked until the index build is finished.
   In production environments this is often unacceptable.
   It is possible to allow writes to occur in parallel with index
   creation, but there are several caveats to be aware of &mdash;
   for more information see <xref linkend="sql-createindex-concurrently"/>.
  </para>

  <para>
   After an index is created, the system has to keep it synchronized with the
   table.  This adds overhead to data manipulation operations.  Indexes can
   also prevent the creation of <link linkend="storage-hot">heap-only
   tuples</link>.
   Therefore indexes that are seldom or never used in queries
   should be removed.
  </para>
 </sect1>


 <sect1 id="indexes-types">
  <title>Index Types</title>

  <para>
   <productname>PostgreSQL</productname> provides several index types:
   B-tree, Hash, GiST, SP-GiST, GIN, BRIN, and the extension <link
   linkend="bloom">bloom</link>.
   Each index type uses a different
   algorithm that is best suited to different types of indexable clauses.
   By default, the <link linkend="sql-createindex"><command>CREATE
   INDEX</command></link> command creates
   B-tree indexes, which fit the most common situations.
   The other index types are selected by writing the keyword
   <literal>USING</literal> followed by the index type name.
   For example, to create a Hash index:
<programlisting>
CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> USING HASH (<replaceable>column</replaceable>);
</programlisting>
  </para>

  <sect2 id="indexes-types-btree">
   <title>B-Tree</title>

   <indexterm>
    <primary>index</primary>
    <secondary>B-Tree</secondary>
   </indexterm>
   <indexterm>
    <primary>B-Tree</primary>
    <see>index</see>
   </indexterm>

  <para>
   B-trees can handle equality and range queries on data that