these index types also support
   index searches for regular-expression matches
   (<literal>~</literal> and <literal>~*</literal> operators), for example
<programlisting>
SELECT * FROM test_trgm WHERE t ~ '(foo|bar)';
</programlisting>
   The index search works by extracting trigrams from the regular expression
   and then looking these up in the index.  The more trigrams that can be
   extracted from the regular expression, the more effective the index search
   is.  Unlike B-tree based searches, the search string need not be
   left-anchored.
  </para>

  <para>
   For both <literal>LIKE</literal> and regular-expression searches, keep in mind
   that a pattern with no extractable trigrams will degenerate to a full-index
   scan.
  </para>

  <para>
   The choice between GiST and GIN indexing depends on the relative
   performance characteristics of GiST and GIN, which are discussed elsewhere.
  </para>
 </sect2>

 <sect2 id="pgtrgm-text-search">
  <title>Text Search Integration</title>

  <para>
   Trigram matching is a very useful tool when used in conjunction
   with a full text index.  In particular it can help to recognize
   misspelled input words that will not be matched directly by the
   full text search mechanism.
  </para>

  <para>
   The first step is to generate an auxiliary table containing all
   the unique words in the documents:

<programlisting>
CREATE TABLE words AS SELECT word FROM
        ts_stat('SELECT to_tsvector(''simple'', bodytext) FROM documents');
</programlisting>

   where <structname>documents</structname> is a table that has a text field
   <structfield>bodytext</structfield> that we wish to search.  The reason for using
   the <literal>simple</literal> configuration with the <function>to_tsvector</function>
   function, instead of using a language-specific configuration,
   is that we want a list of the original (unstemmed) words.
  </para>

  <para>
   Next, create a trigram index on the word column:

<programlisting>
CREATE INDEX words_idx ON words USING GIN (word gin_trgm_ops);
</programlisting>

   Now, a <command>SELECT</command> query similar to the previous example can
   be used to suggest spellings for misspelled words in user search terms.
   A useful extra test is to require that the selected words are also of
   similar length to the misspelled word.
  </para>

  <note>
   <para>
    Since the <structname>words</structname> table has been generated as a separate,
    static table, it will need to be periodically regenerated so that
    it remains reasonably up-to-date with the document collection.
    Keeping it exactly current is usually unnecessary.
   </para>
  </note>
 </sect2>

 <sect2 id="pgtrgm-references">
  <title>References</title>

  <para>
   GiST Development Site
   <ulink url="http://www.sai.msu.su/~megera/postgres/gist/"></ulink>
  </para>
  <para>
   Tsearch2 Development Site
   <ulink url="http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/"></ulink>
  </para>
 </sect2>

 <sect2 id="pgtrgm-authors">
  <title>Authors</title>

  <para>
   Oleg Bartunov <email>oleg@sai.msu.su</email>, Moscow, Moscow University, Russia
  </para>
  <para>
   Teodor Sigaev <email>teodor@sigaev.ru</email>, Moscow, Delta-Soft Ltd.,Russia
  </para>
  <para>
   Alexander Korotkov <email>a.korotkov@postgrespro.ru</email>, Moscow, Postgres Professional, Russia
  </para>
  <para>
   Documentation: Christopher Kings-Lynne
  </para>
  <para>
   This module is sponsored by Delta-Soft Ltd., Moscow, Russia.
  </para>
 </sect2>

</sect1>