<p>We need to create the index on the column idxFTI. Keep in mind

that the database will update the index when some action is taken.

In this case we _need_ the index (The whole point of Full Text

INDEXINGi ;-)), so don't worry about any indexing overhead. We will

create an index based on the gist function. GiST is an index

structure for Generalized Search Tree.</p>

INDEXING ;-)), so don't worry about any indexing overhead. We will

create an index based on the gistor ginfunction. GiST is an index

structure for Generalized Search Tree, GIN is a inverted index (see<ahref="tsearch2-ref.html#indexes">The tsearch2 Reference: Indexes</a>).</p>

CREATE INDEX idxFTI_idx ON tblMessages USING gist(idxFTI);

VACUUM FULL ANALYZE;

<linktype="text/css"rel="stylesheet"href="/~megera/postgres/gist/tsearch/tsearch.css">

<title>tsearch2 guide</title>

<h1align=center>The tsearch2 Guide</h1>

<palign=center>

Brandon Craig Rhodes<br>30 June 2003

<br>Updated to 8.2 release by Oleg Bartunov, October 2006</br>

This Guide introduces the reader to the PostgreSQL tsearch2 module,

version&nbsp;2.

More formal descriptions of the module's types and functions

are provided in the<ahref="tsearch2-ref.html">tsearch2 Reference</a>,

which is a companion to this document.

You can retrieve a beta copy of the tsearch2 module from the

<ahref="http://www.sai.msu.su/~megera/postgres/gist/">GiST for PostgreSQL</a>

page &mdash; look under the section entitled<i>Development History</i>

for the current version.

First we will examine the<tt>tsvector</tt> and<tt>tsquery</tt> types

and how they are used to search documents;

<h2>Table of Contents</h2>

<ahref="#intro">Introduction to FTS with tsearch2</a><br>

<ahref="#vectors_queries">Vectors and Queries</a><br>

<ahref="#simple_search">A Simple Search Engine</a><br>

<ahref="#weights">Ranking and Position Weights</a><br>

<ahref="#casting">Casting Vectors and Queries</a><br>

<ahref="#parsing_lexing">Parsing and Lexing</a><br>

<ahref="#ref">Additional information</a>

<h2><aname="intro">Introduction to FTS with tsearch2</a></h2>

The purpose of FTS is to

find<b>documents</b>, which satisfy<b>query</b> and optionally return

them in some<b>order</b>.

Most common case: Find documents containing all query terms and return them in order

of their similarity to the query. Document in database can be

any text attribute, or combination of text attributes from one or many tables

(using joins).

Text search operators existed for years, in PostgreSQL they are

<tt><b>~,~*, LIKE, ILIKE</b></tt>, but they lack linguistic support,

tends to be slow and have no relevance ranking. The idea behind tsearch2 is

is rather simple - preprocess document at index time to save time at search stage.

Preprocessing includes

<li>document parsing onto words

<li>linguistic - normalize words to obtain lexemes

<li>store document in optimized for searching way

Tsearch2, in a nutshell, provides FTS operator (contains) for two new data types,

which represent document and query -<tt>tsquery @@ tsvector</tt>.

<h2><aname=vectors_queries>Vectors and Queries</a></h2>

on the<tt>tsvector</tt> column of a table,

which implements a form of the Berkeley

<ahref="http://gist.cs.berkeley.edu/"><i>Generalized Search Tree</i></a>.

Since PostgreSQL 8.2 tsearch2 supports<ahref="http://www.sigaev.ru/gin/">Gin</a> index,

which is an inverted index, commonly used in search engines. It adds scalability to tsearch2.

Once your documents are indexed,

performing a search involves:

=#<b>SELECT to_tsquery('the')</b>

NOTICE: Query contains only stopword(s) or doesn't containlexeme(s), ignored

NOTICE: Query contains only stopword(s) or doesn't containlexem(s), ignored

to_tsquery

------------

and has the feature that you can assign different weights

to words from different sections of your document.

The<tt>rank_cd()</tt> uses a recent technique for weighting results

but does not allow different weight to be given

to different sections of your document.

and also allows different weight to be given

to different sections of your document (since 8.2).

Both ranking functions allow you to specify,

as an optional last argument,

see the<ahref="tsearch2-ref.html#ranking">section on ranking</a>

in the Reference.

The<tt>rank()</tt> function offers more flexibility

because it pays attention to the<i>weights</i>

with which you have labelled lexeme positions.

Currently tsearch2 supports four different weight labels:

<tt>'D'</tt>, the default weight;

and<tt>'A'</tt>,<tt>'B'</tt>, and<tt>'C'</tt>.

are important<i>both</i> to PostgreSQL when it is interpreting a string,

<i>and</i> to the<tt>tsvector</tt> conversion function.

You may want to review section

<ahref="http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-syntax.html#SQL-SYNTAX-STRINGS">1.1.2.1,

<ahref="http://www.postgresql.org/docs/current/static/sql-syntax.html#SQL-SYNTAX-STRINGS">

&ldquo;String Constants&rdquo;</a>

in the PostgreSQL documentation before proceeding.

with the difference that the query parser recognizes as special

the boolean operators that separate query words.

<h2><aname="ref">Additional information</a></h2>

More information about tsearch2 is available from

<ahref="http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2">tsearch2</a> page.

Also, it's worth to check

<ahref="http://www.sai.msu.su/~megera/wiki/Tsearch2">tsearch2 wiki</a> pages.