textsearch.sgml 127 KB
Newer Older
1
<!-- $PostgreSQL: pgsql/doc/src/sgml/textsearch.sgml,v 1.38 2007/11/20 15:58:52 tgl Exp $ -->
2

3
<chapter id="textsearch">
4
 <title id="textsearch-title">Full Text Search</title>
5

6 7 8
  <indexterm zone="textsearch">
   <primary>full text search</primary>
  </indexterm>
9

10 11 12
  <indexterm zone="textsearch">
   <primary>text search</primary>
  </indexterm>
13 14

 <sect1 id="textsearch-intro">
15
  <title>Introduction</title>
16 17

  <para>
18
   Full Text Searching (or just <firstterm>text search</firstterm>) provides
19 20 21
   the capability to identify natural-language <firstterm>documents</> that
   satisfy a <firstterm>query</firstterm>, and optionally to sort them by
   relevance to the query.  The most common type of search
22 23
   is to find all documents containing given <firstterm>query terms</firstterm>
   and return them in order of their <firstterm>similarity</firstterm> to the
24
   query.  Notions of <varname>query</varname> and
25 26 27
   <varname>similarity</varname> are very flexible and depend on the specific
   application. The simplest search considers <varname>query</varname> as a
   set of words and <varname>similarity</varname> as the frequency of query
28
   words in the document.
29 30 31 32 33
  </para>

  <para>
   Textual search operators have existed in databases for years.
   <productname>PostgreSQL</productname> has
34
   <literal>~</literal>, <literal>~*</literal>, <literal>LIKE</literal>, and
35 36 37 38 39 40 41
   <literal>ILIKE</literal> operators for textual datatypes, but they lack
   many essential properties required by modern information systems:
  </para>

  <itemizedlist  spacing="compact" mark="bullet">
   <listitem>
    <para>
42 43 44
     There is no linguistic support, even for English.  Regular expressions
     are not sufficient because they cannot easily handle derived words, e.g.,
     <literal>satisfies</literal> and <literal>satisfy</literal>. You might
45
     miss documents that contain <literal>satisfies</literal>, although you
46 47
     probably would like to find them when searching for
     <literal>satisfy</literal>. It is possible to use <literal>OR</literal>
48 49
     to search for multiple derived forms, but this is tedious and error-prone
     (some words can have several thousand derivatives).
50 51 52 53 54 55 56 57 58 59 60 61
    </para>
   </listitem>

   <listitem>
    <para>
     They provide no ordering (ranking) of search results, which makes them
     ineffective when thousands of matching documents are found.
    </para>
   </listitem>

   <listitem>
    <para>
62 63
     They tend to be slow because there is no index support, so they must
     process all documents for every search.
64 65 66 67 68 69 70 71 72 73 74 75
    </para>
   </listitem>
  </itemizedlist>

  <para>
   Full text indexing allows documents to be <emphasis>preprocessed</emphasis>
   and an index saved for later rapid searching. Preprocessing includes:
  </para>

  <itemizedlist  mark="none">
   <listitem>
    <para>
76 77
     <emphasis>Parsing documents into <firstterm>tokens</></emphasis>. It is
     useful to identify various classes of tokens, e.g. numbers, words,
78
     complex words, email addresses, so that they can be processed
79 80
     differently.  In principle token classes depend on the specific
     application, but for most purposes it is adequate to use a predefined
81 82 83 84
     set of classes.
     <productname>PostgreSQL</productname> uses a <firstterm>parser</> to
     perform this step.  A standard parser is provided, and custom parsers
     can be created for specific needs.
85 86 87 88 89
    </para>
   </listitem>

   <listitem>
    <para>
90 91 92 93 94 95 96
     <emphasis>Converting tokens into <firstterm>lexemes</></emphasis>.
     A lexeme is a string, just like a token, but it has been
     <firstterm>normalized</> so that different forms of the same word
     are made alike.  For example, normalization almost always includes
     folding upper-case letters to lower-case, and often involves removal
     of suffixes (such as <literal>s</> or <literal>es</> in English).
     This allows searches to find variant forms of the
97 98 99
     same word, without tediously entering all the possible variants.
     Also, this step typically eliminates <firstterm>stop words</>, which
     are words that are so common that they are useless for searching.
100 101
     (In short, then, tokens are raw fragments of the document text, while
     lexemes are words that are believed useful for indexing and searching.)
102 103 104
     <productname>PostgreSQL</productname> uses <firstterm>dictionaries</> to
     perform this step.  Various standard dictionaries are provided, and
     custom ones can be created for specific needs.
105 106 107 108 109
    </para>
   </listitem>

   <listitem>
    <para>
110 111 112
     <emphasis>Storing preprocessed documents optimized for
     searching</emphasis>.  For example, each document can be represented
     as a sorted array of normalized lexemes. Along with the lexemes it is
113 114 115
     often desirable to store positional information to use for
     <firstterm>proximity ranking</firstterm>, so that a document that
     contains a more <quote>dense</> region of query words is 
116
     assigned a higher rank than one with scattered query words.
117 118 119 120 121
    </para>
   </listitem>
  </itemizedlist>

  <para>
122 123
   Dictionaries allow fine-grained control over how tokens are normalized.
   With appropriate dictionaries, you can:
124 125 126 127 128
  </para>

  <itemizedlist  spacing="compact" mark="bullet">
   <listitem>
    <para>
129
     Define stop words that should not be indexed.
130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154
    </para>
   </listitem>

   <listitem>
    <para>
     Map synonyms to a single word using <application>ispell</>.
    </para>
   </listitem>

   <listitem>
    <para>
     Map phrases to a single word using a thesaurus.
    </para>
   </listitem>

   <listitem>
    <para>
     Map different variations of a word to a canonical form using
     an <application>ispell</> dictionary.
    </para>
   </listitem>

   <listitem>
    <para>
     Map different variations of a word to a canonical form using
155
     <application>Snowball</> stemmer rules.
156 157 158 159 160
    </para>
   </listitem>
  </itemizedlist>

  <para>
161 162
   A data type <type>tsvector</type> is provided for storing preprocessed
   documents, along with a type <type>tsquery</type> for representing processed
163 164 165 166
   queries (<xref linkend="datatype-textsearch">).  There are many
   functions and operators available for these data types
   (<xref linkend="functions-textsearch">), the most important of which is
   the match operator <literal>@@</literal>, which we introduce in
167
   <xref linkend="textsearch-matching">.  Full text searches can be accelerated
168
   using indexes (<xref linkend="textsearch-indexes">).
169 170 171 172
  </para>


  <sect2 id="textsearch-document">
173
   <title>What Is a Document?</title>
174

175
   <indexterm zone="textsearch-document">
176 177
    <primary>document</primary>
    <secondary>text search</secondary>
178
   </indexterm>
179

180
   <para>
181 182 183 184
    A <firstterm>document</> is the unit of searching in a full text search
    system; for example, a magazine article or email message.  The text search
    engine must be able to parse documents and store associations of lexemes
    (key words) with their parent document. Later, these associations are
185
    used to search for documents that contain query words.
186
   </para>
187

188
   <para>
189 190 191 192 193 194
    For searches within <productname>PostgreSQL</productname>,
    a document is normally a textual field within a row of a database table,
    or possibly a combination (concatenation) of such fields, perhaps stored
    in several tables or obtained dynamically. In other words, a document can
    be constructed from different parts for indexing and it might not be
    stored anywhere as a whole. For example:
195

196 197 198 199
<programlisting>
SELECT title || ' ' ||  author || ' ' ||  abstract || ' ' || body AS document
FROM messages
WHERE mid = 12;
200

201 202 203 204 205
SELECT m.title || ' ' || m.author || ' ' || m.abstract || ' ' || d.body AS document
FROM messages m, docs d
WHERE mid = did AND mid = 12;
</programlisting>
   </para>
206

207
   <note>
208
    <para>
209
     Actually, in these example queries, <function>coalesce</function>
210
     should be used to prevent a single <literal>NULL</literal> attribute from
211
     causing a <literal>NULL</literal> result for the whole document.
212
    </para>
213
   </note>
214 215 216 217 218 219 220 221

   <para>
    Another possibility is to store the documents as simple text files in the
    file system. In this case, the database can be used to store the full text
    index and to execute searches, and some unique identifier can be used to
    retrieve the document from the file system.  However, retrieving files
    from outside the database requires superuser permissions or special
    function support, so this is usually less convenient than keeping all
222 223 224 225 226 227 228 229 230 231 232 233 234 235
    the data inside <productname>PostgreSQL</productname>.  Also, keeping
    everything inside the database allows easy access
    to document metadata to assist in indexing and display.
   </para>

   <para>
    For text search purposes, each document must be reduced to the
    preprocessed <type>tsvector</> format.  Searching and ranking
    are performed entirely on the <type>tsvector</> representation
    of a document &mdash; the original text need only be retrieved
    when the document has been selected for display to a user.
    We therefore often speak of the <type>tsvector</> as being the
    document, but of course it is only a compact representation of
    the full document.
236
   </para>
237
  </sect2>
238

239 240
  <sect2 id="textsearch-matching">
   <title>Basic Text Matching</title>
241 242 243

   <para>
    Full text searching in <productname>PostgreSQL</productname> is based on
244 245 246 247
    the match operator <literal>@@</literal>, which returns
    <literal>true</literal> if a <type>tsvector</type>
    (document) matches a <type>tsquery</type> (query).
    It doesn't matter which data type is written first:
248 249

<programlisting>
250 251 252 253 254 255 256 257 258
SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat &amp; rat'::tsquery;
 ?column?
----------
 t

SELECT 'fat &amp; cow'::tsquery @@ 'a fat cat sat on a mat and ate a fat rat'::tsvector;
 ?column?
----------
 f
259
</programlisting>
260
   </para>
261

262
   <para>
263 264
    As the above example suggests, a <type>tsquery</type> is not just raw
    text, any more than a <type>tsvector</type> is.  A <type>tsquery</type>
265 266
    contains search terms, which must be already-normalized lexemes, and
    may combine multiple terms using AND, OR, and NOT operators.
267 268 269 270 271 272 273
    (For details see <xref linkend="datatype-textsearch">.)  There are
    functions <function>to_tsquery</> and <function>plainto_tsquery</>
    that are helpful in converting user-written text into a proper
    <type>tsquery</type>, for example by normalizing words appearing in
    the text.  Similarly, <function>to_tsvector</> is used to parse and
    normalize a document string.  So in practice a text search match would
    look more like this:
274

275
<programlisting>
276 277
SELECT to_tsvector('fat cats ate fat rats') @@ to_tsquery('fat &amp; rat');
 ?column? 
278 279
----------
 t
280
</programlisting>
281

282 283 284 285 286
    Observe that this match would not succeed if written as

<programlisting>
SELECT 'fat cats ate fat rats'::tsvector @@ to_tsquery('fat &amp; rat');
 ?column? 
287 288
----------
 f
289 290
</programlisting>

291 292 293
    since here no normalization of the word <literal>rats</> will occur.
    The elements of a <type>tsvector</> are lexemes, which are assumed
    already normalized, so <literal>rats</> does not match <literal>rat</>.
294 295 296 297 298 299 300 301 302 303 304 305 306
   </para>

   <para>
    The <literal>@@</literal> operator also
    supports <type>text</type> input, allowing explicit conversion of a text
    string to <type>tsvector</type> or <type>tsquery</> to be skipped
    in simple cases.  The variants available are:

<programlisting>
tsvector @@ tsquery
tsquery  @@ tsvector
text @@ tsquery
text @@ text
307 308
</programlisting>
   </para>
309

310
   <para>
311
    The first two of these we saw already.
312 313 314 315 316
    The form <type>text</type> <literal>@@</literal> <type>tsquery</type>
    is equivalent to <literal>to_tsvector(x) @@ y</literal>.
    The form <type>text</type> <literal>@@</literal> <type>text</type>
    is equivalent to <literal>to_tsvector(x) @@ plainto_tsquery(y)</literal>.
   </para>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
317
  </sect2>
318

319
  <sect2 id="textsearch-intro-configurations">
320
   <title>Configurations</title>
321

322 323 324 325 326
   <para>
    The above are all simple text search examples.  As mentioned before, full
    text search functionality includes the ability to do many more things:
    skip indexing certain words (stop words), process synonyms, and use
    sophisticated parsing, e.g. parse based on more than just white space.
327 328 329 330 331
    This functionality is controlled by <firstterm>text search
    configurations</>.  <productname>PostgreSQL</> comes with predefined
    configurations for many languages, and you can easily create your own
    configurations.  (<application>psql</>'s <command>\dF</> command
    shows all available configurations.)
332 333 334
   </para>

   <para>
335 336
    During installation an appropriate configuration is selected and
    <xref linkend="guc-default-text-search-config"> is set accordingly
337 338
    in <filename>postgresql.conf</>.  If you are using the same text search
    configuration for the entire cluster you can use the value in
339 340 341 342
    <filename>postgresql.conf</>.  To use different configurations
    throughout the cluster but the same configuration within any one database,
    use <command>ALTER DATABASE ... SET</>.  Otherwise, you can set
    <varname>default_text_search_config</varname> in each session.
343 344 345 346 347 348 349
   </para>

   <para>
    Each text search function that depends on a configuration has an optional
    <type>regconfig</> argument, so that the configuration to use can be
    specified explicitly.  <varname>default_text_search_config</varname>
    is used only when this argument is omitted.
350 351 352 353 354 355 356 357 358 359 360 361
   </para>

   <para>
    To make it easier to build custom text search configurations, a
    configuration is built up from simpler database objects.
    <productname>PostgreSQL</>'s text search facility provides
    four types of configuration-related database objects:
   </para>

  <itemizedlist  spacing="compact" mark="bullet">
   <listitem>
    <para>
362 363
     <firstterm>Text search parsers</> break documents into tokens
     and classify each token (for example, as words or numbers).
364 365 366 367 368
    </para>
   </listitem>

   <listitem>
    <para>
369
     <firstterm>Text search dictionaries</> convert tokens to normalized
370 371 372 373 374 375 376 377 378 379 380 381 382 383
     form and reject stop words.
    </para>
   </listitem>

   <listitem>
    <para>
     <firstterm>Text search templates</> provide the functions underlying
     dictionaries.  (A dictionary simply specifies a template and a set
     of parameters for the template.)
    </para>
   </listitem>

   <listitem>
    <para>
384
     <firstterm>Text search configurations</> select a parser and a set
385
     of dictionaries to use to normalize the tokens produced by the parser.
386 387 388 389 390 391 392 393 394 395 396 397 398 399
    </para>
   </listitem>
  </itemizedlist>

   <para>
    Text search parsers and templates are built from low-level C functions;
    therefore it requires C programming ability to develop new ones, and
    superuser privileges to install one into a database.  (There are examples
    of add-on parsers and templates in the <filename>contrib/</> area of the
    <productname>PostgreSQL</> distribution.)  Since dictionaries and
    configurations just parameterize and connect together some underlying
    parsers and templates, no special privilege is needed to create a new
    dictionary or configuration.  Examples of creating custom dictionaries and
    configurations appear later in this chapter.
400
   </para>
401

402
  </sect2>
403

404
 </sect1>
405

406 407
 <sect1 id="textsearch-tables">
  <title>Tables and Indexes</title>
408

409
  <para>
410 411 412
   The examples in the previous section illustrated full text matching using
   simple constant strings.  This section shows how to search table data,
   optionally using indexes.
413
  </para>
414

415 416
  <sect2 id="textsearch-tables-search">
   <title>Searching a Table</title>
417

418
   <para>
419 420 421
    It is possible to do full text search with no index.  A simple query
    to print the <structname>title</> of each row that contains the word
    <literal>friend</> in its <structfield>body</> field is:
422

423
<programlisting>
424 425
SELECT title
FROM pgweb
426
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'friend');
427
</programlisting>
428

429 430 431 432 433 434
    This will also find related words such as <literal>friends</>
    and <literal>friendly</>, since all these are reduced to the same
    normalized lexeme.
   </para>

   <para>
435 436 437 438 439 440 441
    The query above specifies that the <literal>english</> configuration
    is to be used to parse and normalize the strings.  Alternatively we
    could omit the configuration parameters:

<programlisting>
SELECT title
FROM pgweb
442
WHERE to_tsvector(body) @@ to_tsquery('friend');
443 444 445
</programlisting>

    This query will use the configuration set by <xref
446 447 448 449 450
    linkend="guc-default-text-search-config">.
   </para>

   <para>
    A more complex example is to
451 452
    select the ten most recent documents that contain <literal>create</> and
    <literal>table</> in the <structname>title</> or <structname>body</>:
453 454

<programlisting>
455 456
SELECT title
FROM pgweb
457
WHERE to_tsvector(title || body) @@ to_tsquery('create &amp; table')
458
ORDER BY last_mod_date DESC LIMIT 10;
459
</programlisting>
460

461
    For clarity we omitted the <function>coalesce</function> function
462 463
    which would be needed to search rows that contain <literal>NULL</literal>
    in one of the two fields.
464
   </para>
465

466 467 468
   <para>
    Although these queries will work without an index, most applications
    will find this approach too slow, except perhaps for occasional ad-hoc
469
    searches.  Practical use of text searching usually requires creating
470 471 472
    an index.
   </para>

473
  </sect2>
474

475 476
  <sect2 id="textsearch-tables-index">
   <title>Creating Indexes</title>
477

478
   <para>
479
    We can create a <acronym>GIN</acronym> index (<xref
480
    linkend="textsearch-indexes">) to speed up text searches:
481 482

<programlisting>
483
CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector('english', body));
484
</programlisting>
485

486 487
    Notice that the 2-argument version of <function>to_tsvector</function> is
    used.  Only text search functions that specify a configuration name can
488 489 490 491 492 493 494 495
    be used in expression indexes (<xref linkend="indexes-expressional">).
    This is because the index contents must be unaffected by <xref
    linkend="guc-default-text-search-config">.  If they were affected, the
    index contents might be inconsistent because different entries could
    contain <type>tsvector</>s that were created with different text search
    configurations, and there would be no way to guess which was which.  It
    would be impossible to dump and restore such an index correctly.
   </para>
496

497 498 499 500
   <para>
    Because the two-argument version of <function>to_tsvector</function> was
    used in the index above, only a query reference that uses the 2-argument
    version of <function>to_tsvector</function> with the same configuration
501 502 503 504 505
    name will use that index.  That is, <literal>WHERE
    to_tsvector('english', body) @@ 'a &amp; b'</> can use the index,
    but <literal>WHERE to_tsvector(body) @@ 'a &amp; b'</> cannot.
    This ensures that an index will be used only with the same configuration
    used to create the index entries.
506
   </para>
507

508
  <para>
509
    It is possible to set up more complex expression indexes wherein the
510
    configuration name is specified by another column, e.g.:
511

512 513 514
<programlisting>
CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector(config_name, body));
</programlisting>
515

516 517
    where <literal>config_name</> is a column in the <literal>pgweb</>
    table.  This allows mixed configurations in the same index while
518 519 520
    recording which configuration was used for each index entry.  This
    would be useful, for example, if the document collection contained
    documents in different languages.  Again,
521 522
    queries that are to use the index must be phrased to match, e.g.
    <literal>WHERE to_tsvector(config_name, body) @@ 'a &amp; b'</>.
523
   </para>
524

525 526
   <para>
    Indexes can even concatenate columns:
527

528 529 530 531
<programlisting>
CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector('english', title || body));
</programlisting>
   </para>
532

533
   <para>
534
    Another approach is to create a separate <type>tsvector</> column
535
    to hold the output of <function>to_tsvector</>.  This example is a
536
    concatenation of <literal>title</literal> and <literal>body</literal>,
537 538
    using <function>coalesce</> to ensure that one field will still be
    indexed when the other is <literal>NULL</>:
539

540
<programlisting>
541 542
ALTER TABLE pgweb ADD COLUMN textsearchable_index_col tsvector;
UPDATE pgweb SET textsearchable_index_col =
543
     to_tsvector('english', coalesce(title,'') || coalesce(body,''));
544
</programlisting>
545

546
    Then we create a <acronym>GIN</acronym> index to speed up the search:
547

548
<programlisting>
549
CREATE INDEX textsearch_idx ON pgweb USING gin(textsearchable_index_col);
550
</programlisting>
551

552
    Now we are ready to perform a fast full text search:
553 554

<programlisting>
555 556
SELECT title
FROM pgweb
557
WHERE textsearchable_index_col @@ to_tsquery('create &amp; table')
558
ORDER BY last_mod_date DESC LIMIT 10;
559
</programlisting>
560
   </para>
561

562 563 564 565
   <para>
    When using a separate column to store the <type>tsvector</>
    representation,
    it is necessary to create a trigger to keep the <type>tsvector</>
566
    column current anytime <literal>title</> or <literal>body</> changes.
567
    <xref linkend="textsearch-update-triggers"> explains how to do that.
568 569 570
   </para>

   <para>
571 572 573 574 575 576 577 578 579 580 581
    One advantage of the separate-column approach over an expression index
    is that it is not necessary to explicitly specify the text search
    configuration in queries in order to make use of the index.  As shown
    in the example above, the query can depend on
    <varname>default_text_search_config</>.  Another advantage is that
    searches will be faster, since it will not be necessary to redo the
    <function>to_tsvector</> calls to verify index matches.  (This is more
    important when using a GiST index than a GIN index; see <xref
    linkend="textsearch-indexes">.)  The expression-index approach is
    simpler to set up, however, and it requires less disk space since the
    <type>tsvector</> representation is not stored explicitly.
582
   </para>
583

584
  </sect2>
585

586
 </sect1>
587

588
 <sect1 id="textsearch-controls">
589
  <title>Controlling Text Search</title>
590

591 592 593
  <para>
   To implement full text searching there must be a function to create a
   <type>tsvector</type> from a document and a <type>tsquery</type> from a
594
   user query. Also, we need to return results in a useful order, so we need
595
   a function that compares documents with respect to their relevance to
596
   the query. It's also important to be able to display the results nicely.
597 598 599
   <productname>PostgreSQL</productname> provides support for all of these
   functions.
  </para>
600

601 602 603 604 605 606 607 608
  <sect2 id="textsearch-parsing-documents">
   <title>Parsing Documents</title>

   <para>
    <productname>PostgreSQL</productname> provides the
    function <function>to_tsvector</function> for converting a document to
    the <type>tsvector</type> data type.
   </para>
609

610 611
   <indexterm>
    <primary>to_tsvector</primary>
612 613
   </indexterm>

614 615 616 617
   <synopsis>
    to_tsvector(<optional> <replaceable class="PARAMETER">config</replaceable> <type>regconfig</>, </optional> <replaceable class="PARAMETER">document</replaceable> <type>text</>) returns <type>tsvector</>
   </synopsis>

618
   <para>
619 620 621 622 623 624
    <function>to_tsvector</function> parses a textual document into tokens,
    reduces the tokens to lexemes, and returns a <type>tsvector</type> which
    lists the lexemes together with their positions in the document.
    The document is processed according to the specified or default
    text search configuration.
    Here is a simple example:
625 626 627

<programlisting>
SELECT to_tsvector('english', 'a fat  cat sat on a mat - it ate a fat rats');
628
                  to_tsvector
629 630 631
-----------------------------------------------------
 'ate':9 'cat':3 'fat':2,11 'mat':7 'rat':12 'sat':4
</programlisting>
632 633
   </para>

634
   <para>
635 636 637 638
    In the example above we see that the resulting <type>tsvector</type> does not
    contain the words <literal>a</literal>, <literal>on</literal>, or
    <literal>it</literal>, the word <literal>rats</literal> became
    <literal>rat</literal>, and the punctuation sign <literal>-</literal> was
639 640
    ignored.
   </para>
641 642 643

   <para>
    The <function>to_tsvector</function> function internally calls a parser
644 645
    which breaks the document text into tokens and assigns a type to
    each token.  For each token, a list of
646 647 648 649
    dictionaries (<xref linkend="textsearch-dictionaries">) is consulted,
    where the list can vary depending on the token type.  The first dictionary
    that <firstterm>recognizes</> the token emits one or more normalized
    <firstterm>lexemes</firstterm> to represent the token.  For example,
650 651
    <literal>rats</literal> became <literal>rat</literal> because one of the
    dictionaries recognized that the word <literal>rats</literal> is a plural
652 653 654 655
    form of <literal>rat</literal>.  Some words are recognized as
    <firstterm>stop words</> (<xref linkend="textsearch-stopwords">), which
    causes them to be ignored since they occur too frequently to be useful in
    searching.  In our example these are
656
    <literal>a</literal>, <literal>on</literal>, and <literal>it</literal>.
657 658 659 660 661 662
    If no dictionary in the list recognizes the token then it is also ignored.
    In this example that happened to the punctuation sign <literal>-</literal>
    because there are in fact no dictionaries assigned for its token type
    (<literal>Space symbols</literal>), meaning space tokens will never be
    indexed. The choices of parser, dictionaries and which types of tokens to
    index are determined by the selected text search configuration (<xref
663
    linkend="textsearch-configuration">).  It is possible to have
664 665
    many different configurations in the same database, and predefined
    configurations are available for various languages. In our example
666 667 668 669 670
    we used the default configuration <literal>english</literal> for the
    English language.
   </para>

   <para>
671
    The function <function>setweight</function> can be used to label the
672 673 674 675
    entries of a <type>tsvector</type> with a given <firstterm>weight</>,
    where a weight is one of the letters <literal>A</>, <literal>B</>,
    <literal>C</>, or <literal>D</>.
    This is typically used to mark entries coming from
676 677
    different parts of a document, such as title versus body.  Later, this
    information can be used for ranking of search results.
678 679 680
   </para>

   <para>
681 682 683 684 685
    Because <function>to_tsvector</function>(<literal>NULL</literal>) will
    return <literal>NULL</literal>, it is recommended to use
    <function>coalesce</function> whenever a field might be null.
    Here is the recommended method for creating
    a <type>tsvector</type> from a structured document:
686

687
<programlisting>
688 689 690 691
UPDATE tt SET ti =
    setweight(to_tsvector(coalesce(title,'')), 'A')    ||
    setweight(to_tsvector(coalesce(keyword,'')), 'B')  ||
    setweight(to_tsvector(coalesce(abstract,'')), 'C') ||
692 693
    setweight(to_tsvector(coalesce(body,'')), 'D');
</programlisting>
694

695
    Here we have used <function>setweight</function> to label the source
696
    of each lexeme in the finished <type>tsvector</type>, and then merged
697
    the labeled <type>tsvector</type> values using the <type>tsvector</>
698 699 700
    concatenation operator <literal>||</>.  (<xref
    linkend="textsearch-manipulate-tsvector"> gives details about these
    operations.)
701 702
   </para>

703 704 705 706 707
  </sect2>

  <sect2 id="textsearch-parsing-queries">
   <title>Parsing Queries</title>

708
   <para>
709 710 711 712 713 714 715
    <productname>PostgreSQL</productname> provides the
    functions <function>to_tsquery</function> and
    <function>plainto_tsquery</function> for converting a query to
    the <type>tsquery</type> data type.  <function>to_tsquery</function>
    offers access to more features than <function>plainto_tsquery</function>,
    but is less forgiving about its input.
   </para>
716

717 718 719
   <indexterm>
    <primary>to_tsquery</primary>
   </indexterm>
720

721 722 723
   <synopsis>
    to_tsquery(<optional> <replaceable class="PARAMETER">config</replaceable> <type>regconfig</>, </optional> <replaceable class="PARAMETER">querytext</replaceable> <type>text</>) returns <type>tsquery</>
   </synopsis>
724

725 726 727 728 729 730 731 732 733 734 735 736 737
   <para>
    <function>to_tsquery</function> creates a <type>tsquery</> value from
    <replaceable>querytext</replaceable>, which must consist of single tokens
    separated by the boolean operators <literal>&amp;</literal> (AND),
    <literal>|</literal> (OR) and <literal>!</literal> (NOT).  These operators
    can be grouped using parentheses.  In other words, the input to
    <function>to_tsquery</function> must already follow the general rules for
    <type>tsquery</> input, as described in <xref
    linkend="datatype-textsearch">.  The difference is that while basic
    <type>tsquery</> input takes the tokens at face value,
    <function>to_tsquery</function> normalizes each token to a lexeme using
    the specified or default configuration, and discards any tokens that are
    stop words according to the configuration.  For example:
738

739 740 741 742 743 744
<programlisting>
SELECT to_tsquery('english', 'The &amp; Fat &amp; Rats');
  to_tsquery   
---------------
 'fat' &amp; 'rat'
</programlisting>
745

746 747 748
    As in basic <type>tsquery</> input, weight(s) can be attached to each
    lexeme to restrict it to match only <type>tsvector</> lexemes of those
    weight(s).  For example:
749 750

<programlisting>
751 752 753 754
SELECT to_tsquery('english', 'Fat | Rats:AB');
    to_tsquery    
------------------
 'fat' | 'rat':AB
755
</programlisting>
756

757 758 759 760 761
    <function>to_tsquery</function> can also accept single-quoted
    phrases.  This is primarily useful when the configuration includes a
    thesaurus dictionary that may trigger on such phrases.
    In the example below, a thesaurus contains the rule <literal>supernovae
    stars : sn</literal>:
762

763 764 765 766 767 768
<programlisting>
SELECT to_tsquery('''supernovae stars'' &amp; !crab');
  to_tsquery
---------------
 'sn' &amp; !'crab'
</programlisting>
769

770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791
    Without quotes, <function>to_tsquery</function> will generate a syntax
    error for tokens that are not separated by an AND or OR operator.
   </para>

   <indexterm>
    <primary>plainto_tsquery</primary>
   </indexterm>

   <synopsis>
    plainto_tsquery(<optional> <replaceable class="PARAMETER">config</replaceable> <type>regconfig</>, </optional> <replaceable class="PARAMETER">querytext</replaceable> <type>text</>) returns <type>tsquery</>
   </synopsis>

   <para>
    <function>plainto_tsquery</> transforms unformatted text
    <replaceable>querytext</replaceable> to <type>tsquery</type>.
    The text is parsed and normalized much as for <function>to_tsvector</>,
    then the <literal>&amp;</literal> (AND) boolean operator is inserted
    between surviving words.
   </para>

   <para>
    Example:
792

793
<programlisting>
794 795 796 797
 SELECT plainto_tsquery('english', 'The Fat Rats');
 plainto_tsquery 
-----------------
 'fat' &amp; 'rat'
798 799
</programlisting>

800 801
    Note that <function>plainto_tsquery</> cannot
    recognize either boolean operators or weight labels in its input:
802

803 804 805 806 807 808 809 810
<programlisting>
SELECT plainto_tsquery('english', 'The Fat &amp; Rats:C');
   plainto_tsquery   
---------------------
 'fat' &amp; 'rat' &amp; 'c'
</programlisting>

    Here, all the input punctuation was discarded as being space symbols.
811
   </para>
812

813
  </sect2>
814

815
  <sect2 id="textsearch-ranking">
816
   <title>Ranking Search Results</title>
817

818 819
   <para>
    Ranking attempts to measure how relevant documents are to a particular
820 821 822 823 824 825 826 827 828 829 830
    query, so that when there are many matches the most relevant ones can be
    shown first.  <productname>PostgreSQL</productname> provides two
    predefined ranking functions, which take into account lexical, proximity,
    and structural information; that is, they consider how often the query
    terms appear in the document, how close together the terms are in the
    document, and how important is the part of the document where they occur.
    However, the concept of relevancy is vague and very application-specific.
    Different applications might require additional information for ranking,
    e.g. document modification time.  The built-in ranking functions are only
    examples.  You can write your own ranking functions and/or combine their
    results with additional factors to fit your specific needs.
831
   </para>
832

833 834
   <para>
    The two ranking functions currently available are:
835

836
    <variablelist>
837

838
     <varlistentry>
839

840 841
      <indexterm>
       <primary>ts_rank</primary>
842
      </indexterm>
843

844 845
      <term>
       <synopsis>
846
        ts_rank(<optional> <replaceable class="PARAMETER">weights</replaceable> <type>float4[]</>, </optional> <replaceable class="PARAMETER">vector</replaceable> <type>tsvector</>, <replaceable class="PARAMETER">query</replaceable> <type>tsquery</> <optional>, <replaceable class="PARAMETER">normalization</replaceable> <type>integer</> </optional>) returns <type>float4</>
847 848 849 850 851
       </synopsis>
      </term>

      <listitem>
       <para>
852
        Standard ranking function.<!-- TODO document this better -->
853 854 855 856 857 858
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>

859 860
      <indexterm>
       <primary>ts_rank_cd</primary>
861 862 863 864
      </indexterm>

      <term>
       <synopsis>
865
        ts_rank_cd(<optional> <replaceable class="PARAMETER">weights</replaceable> <type>float4[]</>, </optional> <replaceable class="PARAMETER">vector</replaceable> <type>tsvector</>, <replaceable class="PARAMETER">query</replaceable> <type>tsquery</> <optional>, <replaceable class="PARAMETER">normalization</replaceable> <type>integer</> </optional>) returns <type>float4</>
866 867 868 869 870
       </synopsis>
      </term>

      <listitem>
       <para>
871 872 873 874 875 876 877 878 879 880 881
        This function computes the <firstterm>cover density</firstterm>
        ranking for the given document vector and query, as described in
        Clarke, Cormack, and Tudhope's "Relevance Ranking for One to Three
        Term Queries" in the journal "Information Processing and Management",
        1999.
       </para>

       <para>
        This function requires positional information in its input.
        Therefore it will not work on <quote>stripped</> <type>tsvector</>
        values &mdash; it will always return zero.
882 883 884 885 886 887 888 889
       </para>
      </listitem>
     </varlistentry>

    </variablelist>

   </para>

890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912
   <para>
    For both these functions,
    the optional <replaceable class="PARAMETER">weights</replaceable>
    argument offers the ability to weigh word instances more or less
    heavily depending on how they are labeled.  The weight arrays specify
    how heavily to weigh each category of word, in the order:

<programlisting>
{D-weight, C-weight, B-weight, A-weight}
</programlisting>

    If no <replaceable class="PARAMETER">weights</replaceable> are provided,
    then these defaults are used:

<programlisting>
{0.1, 0.2, 0.4, 1.0}
</programlisting>

    Typically weights are used to mark words from special areas of the
    document, like the title or an initial abstract, so that they can be
    treated as more or less important than words in the document body.
   </para>

913 914
   <para>
    Since a longer document has a greater chance of containing a query term
915
    it is reasonable to take into account document size, e.g. a hundred-word
916 917 918
    document with five instances of a search word is probably more relevant
    than a thousand-word document with five instances.  Both ranking functions
    take an integer <replaceable>normalization</replaceable> option that
919 920 921
    specifies whether and how a document's length should impact its rank.
    The integer option controls several behaviors, so it is a bit mask:
    you can specify one or more behaviors using
922
    <literal>|</literal> (for example, <literal>2|4</literal>).
923 924 925 926 927 928 929 930 931 932 933 934 935 936

    <itemizedlist  spacing="compact" mark="bullet">
     <listitem>
      <para>
       0 (the default) ignores the document length
      </para>
     </listitem>
     <listitem>
      <para>
       1 divides the rank by 1 + the logarithm of the document length
      </para>
     </listitem>
     <listitem>
      <para>
937
       2 divides the rank by the document length
938 939 940 941 942
      </para>
     </listitem>
     <listitem>
      <para>
       4 divides the rank by the mean harmonic distance between extents
943
       (this is implemented only by <function>ts_rank_cd</>)
944 945 946 947 948 949 950 951 952
      </para>
     </listitem>
     <listitem>
      <para>
       8 divides the rank by the number of unique words in document
      </para>
     </listitem>
     <listitem>
      <para>
953 954
       16 divides the rank by 1 + the logarithm of the number
       of unique words in document
955 956
      </para>
     </listitem>
957 958 959 960 961
     <listitem>
      <para>
       32 divides the rank by itself + 1
      </para>
     </listitem>
962 963
    </itemizedlist>

964 965
    If more than one flag bit is specified, the transformations are
    applied in the order listed.
966 967 968
   </para>

   <para>
969
    It is important to note that the ranking functions do not use any global
970 971 972 973 974
    information, so it is impossible to produce a fair normalization to 1% or
    100% as sometimes desired.  Normalization option 32
    (<literal>rank/(rank+1)</literal>) can be applied to scale all ranks
    into the range zero to one, but of course this is just a cosmetic change;
    it will not affect the ordering of the search results.
975 976 977
   </para>

   <para>
978
    Here is an example that selects only the ten highest-ranked matches:
979

980
<programlisting>
981
SELECT title, ts_rank_cd(textsearch, query) AS rank
982 983
FROM apod, to_tsquery('neutrino|(dark &amp; matter)') query
WHERE query @@ textsearch
984 985
ORDER BY rank DESC LIMIT 10;
                     title                     |   rank
986 987 988 989 990 991 992 993 994 995 996
-----------------------------------------------+----------
 Neutrinos in the Sun                          |      3.1
 The Sudbury Neutrino Detector                 |      2.4
 A MACHO View of Galactic Dark Matter          |  2.01317
 Hot Gas and Dark Matter                       |  1.91171
 The Virgo Cluster: Hot Plasma and Dark Matter |  1.90953
 Rafting for Solar Neutrinos                   |      1.9
 NGC 4650A: Strange Galaxy and Dark Matter     |  1.85774
 Hot Gas and Dark Matter                       |   1.6123
 Ice Fishing for Cosmic Neutrinos              |      1.6
 Weak Lensing Distorts the Universe            | 0.818218
997
</programlisting>
998

999 1000 1001
    This is the same example using normalized ranking:

<programlisting>
1002
SELECT title, ts_rank_cd(textsearch, query, 32 /* rank/(rank+1) */ ) AS rank
1003 1004
FROM apod, to_tsquery('neutrino|(dark &amp; matter)') query
WHERE  query @@ textsearch
1005 1006
ORDER BY rank DESC LIMIT 10;
                     title                     |        rank
1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018
-----------------------------------------------+-------------------
 Neutrinos in the Sun                          | 0.756097569485493
 The Sudbury Neutrino Detector                 | 0.705882361190954
 A MACHO View of Galactic Dark Matter          | 0.668123210574724
 Hot Gas and Dark Matter                       |  0.65655958650282
 The Virgo Cluster: Hot Plasma and Dark Matter | 0.656301290640973
 Rafting for Solar Neutrinos                   | 0.655172410958162
 NGC 4650A: Strange Galaxy and Dark Matter     | 0.650072921219637
 Hot Gas and Dark Matter                       | 0.617195790024749
 Ice Fishing for Cosmic Neutrinos              | 0.615384618911517
 Weak Lensing Distorts the Universe            | 0.450010798361481
</programlisting>
1019 1020 1021 1022
   </para>

   <para>
    Ranking can be expensive since it requires consulting the
1023 1024 1025
    <type>tsvector</type> of each matching document, which can be I/O bound and
    therefore slow. Unfortunately, it is almost impossible to avoid since
    practical queries often result in large numbers of matches.
1026 1027 1028 1029 1030
   </para>

  </sect2>

  <sect2 id="textsearch-headline">
1031
   <title>Highlighting Results</title>
1032 1033 1034 1035

   <para>
    To present search results it is ideal to show a part of each document and
    how it is related to the query. Usually, search engines show fragments of
1036
    the document with marked search terms.  <productname>PostgreSQL</>
1037
    provides a function <function>ts_headline</function> that
1038
    implements this functionality.
1039 1040
   </para>

1041 1042 1043
   <indexterm>
    <primary>ts_headline</primary>
   </indexterm>
1044

1045 1046 1047
   <synopsis>
    ts_headline(<optional> <replaceable class="PARAMETER">config</replaceable> <type>regconfig</>, </optional> <replaceable class="PARAMETER">document</replaceable> <type>text</>, <replaceable class="PARAMETER">query</replaceable> <type>tsquery</> <optional>, <replaceable class="PARAMETER">options</replaceable> <type>text</> </optional>) returns <type>text</>
   </synopsis>
1048

1049 1050
   <para>
    <function>ts_headline</function> accepts a document along
1051
    with a query, and returns an excerpt from
1052 1053 1054 1055 1056 1057
    the document in which terms from the query are highlighted.  The
    configuration to be used to parse the document can be specified by
    <replaceable>config</replaceable>; if <replaceable>config</replaceable>
    is omitted, the
    <varname>default_text_search_config</varname> configuration is used.
   </para>
1058 1059

   <para>
1060
    If an <replaceable>options</replaceable> string is specified it must
1061 1062
    consist of a comma-separated list of one or more
    <replaceable>option</><literal>=</><replaceable>value</> pairs.
1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074
    The available options are:

    <itemizedlist  spacing="compact" mark="bullet">
     <listitem>
      <para>
       <literal>StartSel</>, <literal>StopSel</literal>: the strings with which
       query words appearing in the document should be delimited to distinguish
       them from other excerpted words.
      </para>
     </listitem>
     <listitem >
      <para>
1075 1076
       <literal>MaxWords</>, <literal>MinWords</literal>: these numbers
       determine the longest and shortest headlines to output.
1077 1078 1079 1080
      </para>
     </listitem>
     <listitem>
      <para>
1081 1082
       <literal>ShortWord</literal>: words of this length or less will be
       dropped at the start and end of a headline. The default
1083 1084 1085 1086 1087 1088
       value of three eliminates the English articles.
      </para>
     </listitem>
     <listitem>
      <para>
       <literal>HighlightAll</literal>: boolean flag;  if
1089
       <literal>true</literal> the whole document will be highlighted.
1090 1091 1092 1093 1094 1095
      </para>
     </listitem>
    </itemizedlist>

    Any unspecified options receive these defaults:

1096 1097 1098
<programlisting>
StartSel=&lt;b&gt;, StopSel=&lt;/b&gt;, MaxWords=35, MinWords=15, ShortWord=3, HighlightAll=FALSE
</programlisting>
1099
   </para>
1100

1101 1102
   <para>
    For example:
1103 1104

<programlisting>
1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119
SELECT ts_headline('The most common type of search
is to find all documents containing given query terms 
and return them in order of their similarity to the
query.', to_tsquery('query &amp; similarity'));
                        ts_headline                         
------------------------------------------------------------
 given &lt;b&gt;query&lt;/b&gt; terms
 and return them in order of their &lt;b&gt;similarity&lt;/b&gt; to the
 &lt;b&gt;query&lt;/b&gt;.

SELECT ts_headline('The most common type of search
is to find all documents containing given query terms
and return them in order of their similarity to the
query.',
  to_tsquery('query &amp; similarity'), 
1120
  'StartSel = &lt;, StopSel = &gt;');
1121 1122 1123 1124 1125
                      ts_headline                      
-------------------------------------------------------
 given &lt;query&gt; terms
 and return them in order of their &lt;similarity&gt; to the
 &lt;query&gt;.
1126
</programlisting>
1127
   </para>
1128

1129
   <para>
1130 1131 1132
    <function>ts_headline</> uses the original document, not a
    <type>tsvector</type> summary, so it can be slow and should be used with
    care.  A typical mistake is to call <function>ts_headline</function> for
1133
    <emphasis>every</emphasis> matching document when only ten documents are
1134
    to be shown. <acronym>SQL</acronym> subselects can help; here is an
1135
    example:
1136 1137

<programlisting>
1138 1139 1140 1141 1142
SELECT id, ts_headline(body,q), rank
FROM (SELECT id, body, q, ts_rank_cd(ti,q) AS rank
      FROM apod, to_tsquery('stars') q
      WHERE ti @@ q
      ORDER BY rank DESC LIMIT 10) AS foo;
1143 1144 1145 1146 1147 1148 1149
</programlisting>
   </para>

  </sect2>

 </sect1>

1150 1151
 <sect1 id="textsearch-features">
  <title>Additional Features</title>
1152 1153

  <para>
1154 1155
   This section describes additional functions and operators that are
   useful in connection with text search.
1156 1157
  </para>

1158 1159
  <sect2 id="textsearch-manipulate-tsvector">
   <title>Manipulating Documents</title>
1160

1161 1162 1163 1164 1165 1166 1167
   <para>
    <xref linkend="textsearch-parsing-documents"> showed how raw textual
    documents can be converted into <type>tsvector</> values.
    <productname>PostgreSQL</productname> also provides functions and
    operators that can be used to manipulate documents that are already
    in <type>tsvector</> form.
   </para>
1168

1169
   <variablelist>
1170

1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 1549 1550 1551 1552 1553 1554 1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685
    <varlistentry>

     <indexterm>
      <primary>tsvector concatenation</primary>
     </indexterm>

     <term>
      <synopsis>
       <type>tsvector</> || <type>tsvector</>
      </synopsis>
     </term>

     <listitem>
      <para>
       The <type>tsvector</> concatenation operator
       returns a vector which combines the lexemes and positional information
       of the two vectors given as arguments.  Positions and weight labels
       are retained during the concatenation.
       Positions appearing in the right-hand vector are offset by the largest
       position mentioned in the left-hand vector, so that the result is
       nearly equivalent to the result of performing <function>to_tsvector</>
       on the concatenation of the two original document strings.  (The
       equivalence is not exact, because any stop-words removed from the
       end of the left-hand argument will not affect the result, whereas
       they would have affected the positions of the lexemes in the
       right-hand argument if textual concatenation were used.)
      </para>

      <para>
       One advantage of using concatenation in the vector form, rather than
       concatenating text before applying <function>to_tsvector</>, is that
       you can use different configurations to parse different sections
       of the document.  Also, because the <function>setweight</> function
       marks all lexemes of the given vector the same way, it is necessary
       to parse the text and do <function>setweight</> before concatenating
       if you want to label different parts of the document with different
       weights.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>

     <indexterm>
      <primary>setweight</primary>
     </indexterm>

     <term>
      <synopsis>
       setweight(<replaceable class="PARAMETER">vector</replaceable> <type>tsvector</>, <replaceable class="PARAMETER">weight</replaceable> <type>"char"</>) returns <type>tsvector</>
      </synopsis>
     </term>

     <listitem>
      <para>
       This function returns a copy of the input vector in which every
       position has been labeled with the given <replaceable>weight</>, either
       <literal>A</literal>, <literal>B</literal>, <literal>C</literal>, or
       <literal>D</literal>.  (<literal>D</literal> is the default for new
       vectors and as such is not displayed on output.)  These labels are
       retained when vectors are concatenated, allowing words from different
       parts of a document to be weighted differently by ranking functions.
      </para>

      <para>
       Note that weight labels apply to <emphasis>positions</>, not
       <emphasis>lexemes</>.  If the input vector has been stripped of
       positions then <function>setweight</> does nothing.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <indexterm>
      <primary>length(tsvector)</primary>
     </indexterm>

     <term>
      <synopsis>
       length(<replaceable class="PARAMETER">vector</replaceable> <type>tsvector</>) returns <type>integer</>
      </synopsis>
     </term>

     <listitem>
      <para>
       Returns the number of lexemes stored in the vector.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>

     <indexterm>
      <primary>strip</primary>
     </indexterm>

     <term>
      <synopsis>
       strip(<replaceable class="PARAMETER">vector</replaceable> <type>tsvector</>) returns <type>tsvector</>
      </synopsis>
     </term>

     <listitem>
      <para>
       Returns a vector which lists the same lexemes as the given vector, but
       which lacks any position or weight information.  While the returned
       vector is much less useful than an unstripped vector for relevance
       ranking, it will usually be much smaller.
      </para>
     </listitem>

    </varlistentry>

   </variablelist>

  </sect2>

  <sect2 id="textsearch-manipulate-tsquery">
   <title>Manipulating Queries</title>

   <para>
    <xref linkend="textsearch-parsing-queries"> showed how raw textual
    queries can be converted into <type>tsquery</> values.
    <productname>PostgreSQL</productname> also provides functions and
    operators that can be used to manipulate queries that are already
    in <type>tsquery</> form.
   </para>

   <variablelist>

    <varlistentry>

     <term>
      <synopsis>
       <type>tsquery</> &amp;&amp; <type>tsquery</>
      </synopsis>
     </term>

     <listitem>
      <para>
       Returns the AND-combination of the two given queries.
      </para>
     </listitem>

    </varlistentry>

    <varlistentry>

     <term>
      <synopsis>
       <type>tsquery</> || <type>tsquery</>
      </synopsis>
     </term>

     <listitem>
      <para>
       Returns the OR-combination of the two given queries.
      </para>
     </listitem>

    </varlistentry>

    <varlistentry>

     <term>
      <synopsis>
       !! <type>tsquery</>
      </synopsis>
     </term>

     <listitem>
      <para>
       Returns the negation (NOT) of the given query.
      </para>
     </listitem>

    </varlistentry>

    <varlistentry>

     <indexterm>
      <primary>numnode</primary>
     </indexterm>

     <term>
      <synopsis>
       numnode(<replaceable class="PARAMETER">query</replaceable> <type>tsquery</>) returns <type>integer</>
      </synopsis>
     </term>

     <listitem>
      <para>
       Returns the number of nodes (lexemes plus operators) in a
       <type>tsquery</>. This function is useful
       to determine if the <replaceable>query</replaceable> is meaningful
       (returns &gt; 0), or contains only stop words (returns 0).
       Examples:

<programlisting>
SELECT numnode(plainto_tsquery('the any'));
NOTICE:  query contains only stopword(s) or doesn't contain lexeme(s), ignored
 numnode
---------
       0

SELECT numnode('foo &amp; bar'::tsquery);
 numnode
---------
       3
</programlisting>
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>

     <indexterm>
      <primary>querytree</primary>
     </indexterm>

     <term>
      <synopsis>
       querytree(<replaceable class="PARAMETER">query</replaceable> <type>tsquery</>) returns <type>text</>
      </synopsis>
     </term>

     <listitem>
      <para>
       Returns the portion of a <type>tsquery</> that can be used for
       searching an index.  This function is useful for detecting
       unindexable queries, for example those containing only stop words
       or only negated terms.  For example:

<programlisting>
SELECT querytree(to_tsquery('!defined'));
 querytree
-----------

</programlisting>
      </para>
     </listitem>
    </varlistentry>

   </variablelist>

   <sect3 id="textsearch-query-rewriting">
    <title>Query Rewriting</title>

    <indexterm zone="textsearch-query-rewriting">
     <primary>ts_rewrite</primary>
    </indexterm>

    <para>
     The <function>ts_rewrite</function> family of functions search a
     given <type>tsquery</> for occurrences of a target
     subquery, and replace each occurrence with another
     substitute subquery.  In essence this operation is a
     <type>tsquery</>-specific version of substring replacement.
     A target and substitute combination can be
     thought of as a <firstterm>query rewrite rule</>.  A collection
     of such rewrite rules can be a powerful search aid.
     For example, you can expand the search using synonyms
     (e.g., <literal>new york</>, <literal>big apple</>, <literal>nyc</>,
     <literal>gotham</>) or narrow the search to direct the user to some hot
     topic.  There is some overlap in functionality between this feature
     and thesaurus dictionaries (<xref linkend="textsearch-thesaurus">).
     However, you can modify a set of rewrite rules on-the-fly without
     reindexing, whereas updating a thesaurus requires reindexing to be
     effective.
    </para>

    <variablelist>

     <varlistentry>

      <term>
       <synopsis>
        ts_rewrite (<replaceable class="PARAMETER">query</replaceable> <type>tsquery</>, <replaceable class="PARAMETER">target</replaceable> <type>tsquery</>, <replaceable class="PARAMETER">substitute</replaceable> <type>tsquery</>) returns <type>tsquery</>
       </synopsis>
      </term>

      <listitem>
       <para>
        This form of <function>ts_rewrite</> simply applies a single
        rewrite rule: <replaceable class="PARAMETER">target</replaceable>
        is replaced by <replaceable class="PARAMETER">substitute</replaceable>
        wherever it appears in <replaceable
        class="PARAMETER">query</replaceable>.  For example:

<programlisting>
SELECT ts_rewrite('a &amp; b'::tsquery, 'a'::tsquery, 'c'::tsquery);
 ts_rewrite
------------
 'b' &amp; 'c'
</programlisting>
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>

      <term>
       <synopsis>
        ts_rewrite (<replaceable class="PARAMETER">query</> <type>tsquery</>, <replaceable class="PARAMETER">select</> <type>text</>) returns <type>tsquery</>
       </synopsis>
      </term>

      <listitem>
       <para>
        This form of <function>ts_rewrite</> accepts a starting
        <replaceable>query</> and a SQL <replaceable>select</> command, which
        is given as a text string.  The <replaceable>select</> must yield two
        columns of <type>tsquery</> type.  For each row of the
        <replaceable>select</> result, occurrences of the first column value
        (the target) are replaced by the second column value (the substitute)
        within the current <replaceable>query</> value.  For example:

<programlisting>
CREATE TABLE aliases (t tsquery PRIMARY KEY, s tsquery);
INSERT INTO aliases VALUES('a', 'c');

SELECT ts_rewrite('a &amp; b'::tsquery, 'SELECT t,s FROM aliases');
 ts_rewrite
------------
 'b' &amp; 'c'
</programlisting>
       </para>

       <para>
        Note that when multiple rewrite rules are applied in this way,
        the order of application can be important; so in practice you will
        want the source query to <literal>ORDER BY</> some ordering key.
       </para>
      </listitem>
     </varlistentry>

    </variablelist>

    <para>
     Let's consider a real-life astronomical example. We'll expand query
     <literal>supernovae</literal> using table-driven rewriting rules:

<programlisting>
CREATE TABLE aliases (t tsquery primary key, s tsquery);
INSERT INTO aliases VALUES(to_tsquery('supernovae'), to_tsquery('supernovae|sn'));

SELECT ts_rewrite(to_tsquery('supernovae &amp; crab'), 'SELECT * FROM aliases');
           ts_rewrite            
---------------------------------
 'crab' &amp; ( 'supernova' | 'sn' )
</programlisting>

     We can change the rewriting rules just by updating the table:

<programlisting>
UPDATE aliases SET s = to_tsquery('supernovae|sn &amp; !nebulae') WHERE t = to_tsquery('supernovae');

SELECT ts_rewrite(to_tsquery('supernovae &amp; crab'), 'SELECT * FROM aliases');
                 ts_rewrite                  
---------------------------------------------
 'crab' &amp; ( 'supernova' | 'sn' &amp; !'nebula' )
</programlisting>
    </para>

    <para>
     Rewriting can be slow when there are many rewriting rules, since it
     checks every rule for a possible hit. To filter out obvious non-candidate
     rules we can use the containment operators for the <type>tsquery</type>
     type. In the example below, we select only those rules which might match
     the original query:

<programlisting>
SELECT ts_rewrite('a &amp; b'::tsquery,
                  'SELECT t,s FROM aliases WHERE ''a &amp; b''::tsquery @&gt; t');
 ts_rewrite
------------
 'b' &amp; 'c'
</programlisting>
    </para>

   </sect3>

  </sect2>

  <sect2 id="textsearch-update-triggers">
   <title>Triggers for Automatic Updates</title>

   <indexterm>
    <primary>trigger</primary>
    <secondary>for updating a derived tsvector column</secondary>
   </indexterm>

   <para>
    When using a separate column to store the <type>tsvector</> representation
    of your documents, it is necessary to create a trigger to update the
    <type>tsvector</> column when the document content columns change.
    Two built-in trigger functions are available for this, or you can write
    your own.
   </para>

   <synopsis>
    tsvector_update_trigger(<replaceable class="PARAMETER">tsvector_column_name</replaceable>, <replaceable class="PARAMETER">config_name</replaceable>, <replaceable class="PARAMETER">text_column_name</replaceable> <optional>, ... </optional>)
    tsvector_update_trigger_column(<replaceable class="PARAMETER">tsvector_column_name</replaceable>, <replaceable class="PARAMETER">config_column_name</replaceable>, <replaceable class="PARAMETER">text_column_name</replaceable> <optional>, ... </optional>)
   </synopsis>

   <para>
    These trigger functions automatically compute a <type>tsvector</>
    column from one or more textual columns, under the control of
    parameters specified in the <command>CREATE TRIGGER</> command.
    An example of their use is:

<programlisting>
CREATE TABLE messages (
    title       text,
    body        text,
    tsv         tsvector
);

CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON messages FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(tsv, 'pg_catalog.english', title, body);

INSERT INTO messages VALUES('title here', 'the body text is here');

SELECT * FROM messages;
   title    |         body          |            tsv             
------------+-----------------------+----------------------------
 title here | the body text is here | 'bodi':4 'text':5 'titl':1

SELECT title, body FROM messages WHERE tsv @@ to_tsquery('title &amp; body');
   title    |         body          
------------+-----------------------
 title here | the body text is here
</programlisting>

    Having created this trigger, any change in <structfield>title</> or
    <structfield>body</> will automatically be reflected into
    <structfield>tsv</>, without the application having to worry about it.
   </para>

   <para>
    The first trigger argument must be the name of the <type>tsvector</>
    column to be updated.  The second argument specifies the text search
    configuration to be used to perform the conversion.  For
    <function>tsvector_update_trigger</>, the configuration name is simply
    given as the second trigger argument.  It must be schema-qualified as
    shown above, so that the trigger behavior will not change with changes
    in <varname>search_path</>.  For
    <function>tsvector_update_trigger_column</>, the second trigger argument
    is the name of another table column, which must be of type
    <type>regconfig</>.  This allows a per-row selection of configuration
    to be made.  The remaining argument(s) are the names of textual columns
    (of type <type>text</>, <type>varchar</>, or <type>char</>).  These
    will be included in the document in the order given.  NULL values will
    be skipped (but the other columns will still be indexed).
   </para>

   <para>
    A limitation of the built-in triggers is that they treat all the
    input columns alike.  To process columns differently &mdash; for
    example, to weight title differently from body &mdash; it is necessary
    to write a custom trigger.  Here is an example using
    <application>PL/pgSQL</application> as the trigger language:

<programlisting>
CREATE FUNCTION messages_trigger() RETURNS trigger AS $$
begin
  new.tsv :=
     setweight(to_tsvector('pg_catalog.english', coalesce(new.title,'')), 'A') ||
     setweight(to_tsvector('pg_catalog.english', coalesce(new.body,'')), 'D');
  return new;
end
$$ LANGUAGE plpgsql;

CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON messages FOR EACH ROW EXECUTE PROCEDURE messages_trigger();
</programlisting>
   </para>

   <para>
    Keep in mind that it is important to specify the configuration name
    explicitly when creating <type>tsvector</> values inside triggers,
    so that the column's contents will not be affected by changes to
    <varname>default_text_search_config</>.  Failure to do this is likely to
    lead to problems such as search results changing after a dump and reload.
   </para>

  </sect2>

  <sect2 id="textsearch-statistics">
   <title>Gathering Document Statistics</title>

   <indexterm>
    <primary>ts_stat</primary>
   </indexterm>

   <para>
    The function <function>ts_stat</> is useful for checking your
    configuration and for finding stop-word candidates.
   </para>

   <synopsis>
    ts_stat(<replaceable class="PARAMETER">sqlquery</replaceable> <type>text</>, <optional> <replaceable class="PARAMETER">weights</replaceable> <type>text</>, </optional> OUT <replaceable class="PARAMETER">word</replaceable> <type>text</>, OUT <replaceable class="PARAMETER">ndoc</replaceable> <type>integer</>, OUT <replaceable class="PARAMETER">nentry</replaceable> <type>integer</>) returns <type>setof record</>
   </synopsis>

   <para>
    <replaceable>sqlquery</replaceable> is a text value containing a SQL
    query which must return a single <type>tsvector</type> column.
    <function>ts_stat</> executes the query and returns statistics about
    each distinct lexeme (word) contained in the <type>tsvector</type>
    data.  The columns returned are

    <itemizedlist  spacing="compact" mark="bullet">
     <listitem>
      <para>
1686
       <replaceable>word</> <type>text</> &mdash; the value of a lexeme
1687 1688 1689 1690
      </para>
     </listitem>
     <listitem>
      <para>
1691
       <replaceable>ndoc</> <type>integer</> &mdash; number of documents
1692 1693 1694 1695 1696
       (<type>tsvector</>s) the word occurred in
      </para>
     </listitem>
     <listitem>
      <para>
1697
       <replaceable>nentry</> <type>integer</> &mdash; total number of
1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761
       occurrences of the word
      </para>
     </listitem>
    </itemizedlist>

    If <replaceable>weights</replaceable> is supplied, only occurrences
    having one of those weights are counted.
   </para>

   <para>
    For example, to find the ten most frequent words in a document collection:

<programlisting>
SELECT * FROM ts_stat('SELECT vector FROM apod')
ORDER BY nentry DESC, ndoc DESC, word
LIMIT 10;
</programlisting>

    The same, but counting only word occurrences with weight <literal>A</>
    or <literal>B</>:

<programlisting>
SELECT * FROM ts_stat('SELECT vector FROM apod', 'ab')
ORDER BY nentry DESC, ndoc DESC, word
LIMIT 10;
</programlisting>
   </para>

  </sect2>

 </sect1>

 <sect1 id="textsearch-parsers">
  <title>Parsers</title>

  <para>
   Text search parsers are responsible for splitting raw document text
   into <firstterm>tokens</> and identifying each token's type, where
   the set of possible types is defined by the parser itself.
   Note that a parser does not modify the text at all &mdash; it simply
   identifies plausible word boundaries.  Because of this limited scope,
   there is less need for application-specific custom parsers than there is
   for custom dictionaries.  At present <productname>PostgreSQL</productname>
   provides just one built-in parser, which has been found to be useful for a
   wide range of applications.
  </para>

  <para>
   The built-in parser is named <literal>pg_catalog.default</>.
   It recognizes 23 token types:
  </para>

  <table id="textsearch-default-parser">
   <title>Default Parser's Token Types</title>
   <tgroup cols="3">
    <thead>
     <row>
      <entry>Alias</entry>
      <entry>Description</entry>
      <entry>Example</entry>
     </row>
    </thead>
    <tbody>
     <row>
1762 1763
      <entry><literal>asciiword</></entry>
      <entry>Word, all ASCII letters</entry>
1764
      <entry><literal>elephant</literal></entry>
1765 1766
     </row>
     <row>
1767 1768
      <entry><literal>word</></entry>
      <entry>Word, all letters</entry>
1769
      <entry><literal>ma&ntilde;ana</literal></entry>
1770 1771
     </row>
     <row>
1772 1773
      <entry><literal>numword</></entry>
      <entry>Word, letters and digits</entry>
1774 1775 1776
      <entry><literal>beta1</literal></entry>
     </row>
     <row>
1777 1778
      <entry><literal>asciihword</></entry>
      <entry>Hyphenated word, all ASCII</entry>
1779
      <entry><literal>up-to-date</literal></entry>
1780 1781
     </row>
     <row>
1782 1783
      <entry><literal>hword</></entry>
      <entry>Hyphenated word, all letters</entry>
1784
      <entry><literal>l&oacute;gico-matem&aacute;tica</literal></entry>
1785 1786
     </row>
     <row>
1787 1788
      <entry><literal>numhword</></entry>
      <entry>Hyphenated word, letters and digits</entry>
1789
      <entry><literal>postgresql-beta1</literal></entry>
1790 1791
     </row>
     <row>
1792 1793
      <entry><literal>hword_asciipart</></entry>
      <entry>Hyphenated word part, all ASCII</entry>
1794
      <entry><literal>postgresql</literal> in the context <literal>postgresql-beta1</literal></entry>
1795 1796
     </row>
     <row>
1797 1798
      <entry><literal>hword_part</></entry>
      <entry>Hyphenated word part, all letters</entry>
1799 1800
      <entry><literal>l&oacute;gico</literal> or <literal>matem&aacute;tica</literal>
       in the context <literal>l&oacute;gico-matem&aacute;tica</literal></entry>
1801 1802
     </row>
     <row>
1803 1804
      <entry><literal>hword_numpart</></entry>
      <entry>Hyphenated word part, letters and digits</entry>
1805
      <entry><literal>beta1</literal> in the context
1806
       <literal>postgresql-beta1</literal></entry>
1807 1808
     </row>
     <row>
1809
      <entry><literal>email</></entry>
1810
      <entry>Email address</entry>
1811
      <entry><literal>foo@example.com</literal></entry>
1812 1813
     </row>
     <row>
1814
      <entry><literal>protocol</></entry>
1815 1816 1817 1818
      <entry>Protocol head</entry>
      <entry><literal>http://</literal></entry>
     </row>
     <row>
1819
      <entry><literal>url</></entry>
1820
      <entry>URL</entry>
1821
      <entry><literal>example.com/stuff/index.html</literal></entry>
1822 1823
     </row>
     <row>
1824
      <entry><literal>host</></entry>
1825
      <entry>Host</entry>
1826
      <entry><literal>example.com</literal></entry>
1827 1828
     </row>
     <row>
1829 1830
      <entry><literal>url_path</></entry>
      <entry>URL path</entry>
1831 1832 1833
      <entry><literal>/stuff/index.html</literal>, in the context of a URL</entry>
     </row>
     <row>
1834
      <entry><literal>file</></entry>
1835 1836 1837 1838
      <entry>File or path name</entry>
      <entry><literal>/usr/local/foo.txt</literal>, if not within a URL</entry>
     </row>
     <row>
1839
      <entry><literal>sfloat</></entry>
1840 1841 1842 1843
      <entry>Scientific notation</entry>
      <entry><literal>-1.234e56</literal></entry>
     </row>
     <row>
1844
      <entry><literal>float</></entry>
1845 1846 1847 1848
      <entry>Decimal notation</entry>
      <entry><literal>-1.234</literal></entry>
     </row>
     <row>
1849
      <entry><literal>int</></entry>
1850 1851 1852 1853
      <entry>Signed integer</entry>
      <entry><literal>-1234</literal></entry>
     </row>
     <row>
1854
      <entry><literal>uint</></entry>
1855 1856 1857 1858
      <entry>Unsigned integer</entry>
      <entry><literal>1234</literal></entry>
     </row>
     <row>
1859
      <entry><literal>version</></entry>
1860 1861 1862 1863
      <entry>Version number</entry>
      <entry><literal>8.3.0</literal></entry>
     </row>
     <row>
1864
      <entry><literal>tag</></entry>
1865 1866
      <entry>XML tag</entry>
      <entry><literal>&lt;a href="dictionaries.html"&gt;</literal></entry>
1867 1868
     </row>
     <row>
1869
      <entry><literal>entity</></entry>
1870
      <entry>XML entity</entry>
1871 1872 1873
      <entry><literal>&amp;amp;</literal></entry>
     </row>
     <row>
1874
      <entry><literal>blank</></entry>
1875 1876 1877 1878 1879 1880 1881
      <entry>Space symbols</entry>
      <entry>(any whitespace or punctuation not otherwise recognized)</entry>
     </row>
    </tbody>
   </tgroup>
  </table>

1882 1883 1884 1885 1886 1887 1888 1889 1890 1891 1892
  <note>
   <para>
    The parser's notion of a <quote>letter</> is determined by the server's
    locale setting, specifically <varname>lc_ctype</>.  Words containing
    only the basic ASCII letters are reported as a separate token type,
    since it is sometimes useful to distinguish them.  In most European
    languages, token types <literal>word</> and <literal>asciiword</>
    should always be treated alike.
   </para>
  </note>

1893 1894 1895 1896 1897 1898
  <para>
   It is possible for the parser to produce overlapping tokens from the same
   piece of text.  As an example, a hyphenated word will be reported both
   as the entire word and as each component:

<programlisting>
1899
SELECT alias, description, token FROM ts_debug('foo-bar-beta1');
1900 1901 1902 1903 1904 1905 1906 1907
      alias      |               description                |     token     
-----------------+------------------------------------------+---------------
 numhword        | Hyphenated word, letters and digits      | foo-bar-beta1
 hword_asciipart | Hyphenated word part, all ASCII          | foo
 blank           | Space symbols                            | -
 hword_asciipart | Hyphenated word part, all ASCII          | bar
 blank           | Space symbols                            | -
 hword_numpart   | Hyphenated word part, letters and digits | beta1
1908 1909 1910 1911 1912 1913 1914
</programlisting>

   This behavior is desirable since it allows searches to work for both
   the whole compound word and for components.  Here is another
   instructive example:

<programlisting>
1915 1916 1917
SELECT alias, description, token FROM ts_debug('http://example.com/stuff/index.html');
  alias   |  description  |            token             
----------+---------------+------------------------------
1918
 protocol | Protocol head | http://
1919 1920
 url      | URL           | example.com/stuff/index.html
 host     | Host          | example.com
1921
 url_path | URL path      | /stuff/index.html
1922 1923 1924 1925 1926 1927 1928 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 1942 1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970
</programlisting>
  </para>

 </sect1>

 <sect1 id="textsearch-dictionaries">
  <title>Dictionaries</title>

  <para>
   Dictionaries are used to eliminate words that should not be considered in a
   search (<firstterm>stop words</>), and to <firstterm>normalize</> words so
   that different derived forms of the same word will match.  A successfully
   normalized word is called a <firstterm>lexeme</>.  Aside from
   improving search quality, normalization and removal of stop words reduce the
   size of the <type>tsvector</type> representation of a document, thereby
   improving performance.  Normalization does not always have linguistic meaning
   and usually depends on application semantics.
  </para>

  <para>
   Some examples of normalization:

   <itemizedlist  spacing="compact" mark="bullet">

    <listitem>
     <para>
      Linguistic - ispell dictionaries try to reduce input words to a
      normalized form; stemmer dictionaries remove word endings
     </para>
    </listitem>
    <listitem>
     <para>
      <acronym>URL</acronym> locations can be canonicalized to make
      equivalent URLs match:

      <itemizedlist  spacing="compact" mark="bullet">
       <listitem>
        <para>
         http://www.pgsql.ru/db/mw/index.html
        </para>
       </listitem>
       <listitem>
        <para>
         http://www.pgsql.ru/db/mw/
        </para>
       </listitem>
       <listitem>
        <para>
         http://www.pgsql.ru/db/../db/mw/index.html
1971 1972 1973 1974 1975 1976 1977
        </para>
       </listitem>
      </itemizedlist>
     </para>
    </listitem>
    <listitem>
     <para>
1978
      Color names can be replaced by their hexadecimal values, e.g.,
1979 1980 1981 1982 1983
      <literal>red, green, blue, magenta -> FF0000, 00FF00, 0000FF, FF00FF</literal>
     </para>
    </listitem>
    <listitem>
     <para>
1984 1985 1986
      If indexing numbers, we can
      remove some fractional digits to reduce the range of possible
      numbers, so for example <emphasis>3.14</emphasis>159265359,
1987 1988 1989 1990 1991 1992
      <emphasis>3.14</emphasis>15926, <emphasis>3.14</emphasis> will be the same
      after normalization if only two digits are kept after the decimal point.
     </para>
    </listitem>
   </itemizedlist>

1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 2032 2033 2034 2035 2036 2037 2038 2039 2040 2041 2042
  </para>

  <para>
   A dictionary is a program that accepts a token as
   input and returns:
   <itemizedlist  spacing="compact" mark="bullet">
    <listitem>
     <para>
      an array of lexemes if the input token is known to the dictionary
      (notice that one token can produce more than one lexeme)
     </para>
    </listitem>
    <listitem>
     <para>
      an empty array if the dictionary knows the token, but it is a stop word
     </para>
    </listitem>
    <listitem>
     <para>
      <literal>NULL</literal> if the dictionary does not recognize the input token
     </para>
    </listitem>
   </itemizedlist>
  </para>

  <para>
   <productname>PostgreSQL</productname> provides predefined dictionaries for
   many languages.  There are also several predefined templates that can be
   used to create new dictionaries with custom parameters.  Each predefined
   dictionary template is described below.  If no existing
   template is suitable, it is possible to create new ones; see the
   <filename>contrib/</> area of the <productname>PostgreSQL</> distribution
   for examples.
  </para>

  <para>
   A text search configuration binds a parser together with a set of
   dictionaries to process the parser's output tokens.  For each token
   type that the parser can return, a separate list of dictionaries is
   specified by the configuration.  When a token of that type is found
   by the parser, each dictionary in the list is consulted in turn,
   until some dictionary recognizes it as a known word.  If it is identified
   as a stop word, or if no dictionary recognizes the token, it will be
   discarded and not indexed or searched for.
   The general rule for configuring a list of dictionaries
   is to place first the most narrow, most specific dictionary, then the more
   general dictionaries, finishing with a very general dictionary, like
   a <application>Snowball</> stemmer or <literal>simple</>, which
   recognizes everything.  For example, for an astronomy-specific search
   (<literal>astro_en</literal> configuration) one could bind token type
2043
   <type>asciiword</type> (ASCII word) to a synonym dictionary of astronomical
2044 2045 2046 2047 2048
   terms, a general English dictionary and a <application>Snowball</> English
   stemmer:

<programlisting>
ALTER TEXT SEARCH CONFIGURATION astro_en
2049
    ADD MAPPING FOR asciiword WITH astrosyn, english_ispell, english_stem;
2050 2051 2052 2053 2054 2055 2056 2057 2058 2059 2060 2061 2062 2063 2064 2065 2066 2067 2068 2069 2070
</programlisting>
  </para>

  <sect2 id="textsearch-stopwords">
   <title>Stop Words</title>

   <para>
    Stop words are words that are very common, appear in almost every
    document, and have no discrimination value. Therefore, they can be ignored
    in the context of full text searching. For example, every English text
    contains words like <literal>a</literal> and <literal>the</>, so it is
    useless to store them in an index.  However, stop words do affect the
    positions in <type>tsvector</type>, which in turn affect ranking:

<programlisting>
SELECT to_tsvector('english','in the list of stop words');
        to_tsvector
----------------------------
 'list':3 'stop':5 'word':6
</programlisting>

Magnus Hagander's avatar
Magnus Hagander committed
2071
    The missing positions 1,2,4 are because of stop words.  Ranks
2072 2073 2074 2075 2076 2077 2078 2079 2080 2081 2082 2083 2084
    calculated for documents with and without stop words are quite different:

<programlisting>
SELECT ts_rank_cd (to_tsvector('english','in the list of stop words'), to_tsquery('list &amp; stop'));
 ts_rank_cd
------------
       0.05

SELECT ts_rank_cd (to_tsvector('english','list stop words'), to_tsquery('list &amp; stop'));
 ts_rank_cd
------------
        0.1
</programlisting>
2085

2086
   </para>
2087

2088 2089 2090 2091 2092 2093 2094 2095 2096 2097 2098 2099 2100 2101 2102 2103
   <para>
    It is up to the specific dictionary how it treats stop words. For example,
    <literal>ispell</literal> dictionaries first normalize words and then
    look at the list of stop words, while <literal>Snowball</literal> stemmers
    first check the list of stop words. The reason for the different
    behavior is an attempt to decrease noise.
   </para>

  </sect2>

  <sect2 id="textsearch-simple-dictionary">
   <title>Simple Dictionary</title>

   <para>
    The <literal>simple</> dictionary template operates by converting the
    input token to lower case and checking it against a file of stop words.
2104
    If it is found in the file then an empty array is returned, causing
2105
    the token to be discarded.  If not, the lower-cased form of the word
2106 2107 2108
    is returned as the normalized lexeme.  Alternatively, the dictionary
    can be configured to report non-stop-words as unrecognized, allowing
    them to be passed on to the next dictionary in the list.
2109 2110 2111 2112 2113 2114 2115 2116 2117 2118 2119 2120 2121 2122 2123 2124 2125 2126 2127 2128 2129 2130 2131 2132 2133 2134 2135 2136 2137 2138 2139 2140 2141 2142 2143 2144 2145 2146 2147 2148 2149 2150
   </para>

   <para>
    Here is an example of a dictionary definition using the <literal>simple</>
    template:

<programlisting>
CREATE TEXT SEARCH DICTIONARY public.simple_dict (
    TEMPLATE = pg_catalog.simple,
    STOPWORDS = english
);
</programlisting>

    Here, <literal>english</literal> is the base name of a file of stop words.
    The file's full name will be
    <filename>$SHAREDIR/tsearch_data/english.stop</>,
    where <literal>$SHAREDIR</> means the 
    <productname>PostgreSQL</productname> installation's shared-data directory,
    often <filename>/usr/local/share/postgresql</> (use <command>pg_config
    --sharedir</> to determine it if you're not sure).
    The file format is simply a list
    of words, one per line.  Blank lines and trailing spaces are ignored,
    and upper case is folded to lower case, but no other processing is done
    on the file contents.
   </para>

   <para>
    Now we can test our dictionary:

<programlisting>
SELECT ts_lexize('public.simple_dict','YeS');
 ts_lexize
-----------
 {yes}

SELECT ts_lexize('public.simple_dict','The');
 ts_lexize
-----------
 {}
</programlisting>
   </para>

2151 2152 2153 2154 2155 2156 2157 2158 2159 2160 2161 2162 2163 2164 2165 2166 2167 2168 2169 2170 2171 2172 2173 2174 2175 2176 2177 2178 2179
   <para>
    We can also choose to return <literal>NULL</>, instead of the lower-cased
    word, if it is not found in the stop words file.  This behavior is
    selected by setting the dictionary's <literal>Accept</> parameter to
    <literal>false</>.  Continuing the example:

<programlisting>
ALTER TEXT SEARCH DICTIONARY public.simple_dict ( Accept = false );

SELECT ts_lexize('public.simple_dict','YeS');
 ts_lexize
-----------


SELECT ts_lexize('public.simple_dict','The');
 ts_lexize
-----------
 {}
</programlisting>
   </para>

   <para>
    With the default setting of <literal>Accept</> = <literal>true</>,
    it is only useful to place a <literal>simple</> dictionary at the end
    of a list of dictionaries, since it will never pass on any token to
    a following dictionary.  Conversely, <literal>Accept</> = <literal>false</>
    is only useful when there is at least one following dictionary.
   </para>

2180 2181 2182 2183 2184 2185 2186 2187 2188 2189 2190 2191 2192 2193 2194 2195 2196 2197 2198 2199 2200 2201 2202 2203 2204 2205 2206 2207 2208 2209 2210 2211 2212 2213 2214
   <caution>
    <para>
     Most types of dictionaries rely on configuration files, such as files of
     stop words.  These files <emphasis>must</> be stored in UTF-8 encoding.
     They will be translated to the actual database encoding, if that is
     different, when they are read into the server.
    </para>
   </caution>

   <caution>
    <para>
     Normally, a database session will read a dictionary configuration file
     only once, when it is first used within the session.  If you modify a
     configuration file and want to force existing sessions to pick up the
     new contents, issue an <command>ALTER TEXT SEARCH DICTIONARY</> command
     on the dictionary.  This can be a <quote>dummy</> update that doesn't
     actually change any parameter values.
    </para>
   </caution>

  </sect2>

  <sect2 id="textsearch-synonym-dictionary">
   <title>Synonym Dictionary</title>

   <para>
    This dictionary template is used to create dictionaries that replace a
    word with a synonym. Phrases are not supported (use the thesaurus
    template (<xref linkend="textsearch-thesaurus">) for that).  A synonym
    dictionary can be used to overcome linguistic problems, for example, to
    prevent an English stemmer dictionary from reducing the word 'Paris' to
    'pari'.  It is enough to have a <literal>Paris paris</literal> line in the
    synonym dictionary and put it before the <literal>english_stem</> dictionary:

<programlisting>
2215
SELECT * FROM ts_debug('english', 'Paris');
2216 2217 2218
   alias   |   description   | token |  dictionaries  |  dictionary  | lexemes 
-----------+-----------------+-------+----------------+--------------+---------
 asciiword | Word, all ASCII | Paris | {english_stem} | english_stem | {pari}
2219

2220
CREATE TEXT SEARCH DICTIONARY my_synonym (
2221 2222 2223 2224 2225
    TEMPLATE = synonym,
    SYNONYMS = my_synonyms
);

ALTER TEXT SEARCH CONFIGURATION english
2226
    ALTER MAPPING FOR asciiword WITH my_synonym, english_stem;
2227

2228
SELECT * FROM ts_debug('english', 'Paris');
2229 2230 2231
   alias   |   description   | token |       dictionaries        | dictionary | lexemes 
-----------+-----------------+-------+---------------------------+------------+---------
 asciiword | Word, all ASCII | Paris | {my_synonym,english_stem} | my_synonym | {paris}
2232 2233 2234 2235 2236 2237 2238 2239 2240 2241 2242 2243 2244 2245 2246 2247 2248 2249 2250 2251 2252 2253 2254 2255 2256 2257 2258 2259 2260 2261 2262 2263 2264 2265 2266 2267 2268 2269 2270 2271 2272 2273 2274 2275 2276 2277 2278 2279 2280 2281 2282 2283 2284 2285 2286 2287 2288 2289 2290 2291 2292 2293 2294 2295 2296 2297 2298 2299
</programlisting>
   </para>

   <para>
    The only parameter required by the <literal>synonym</> template is
    <literal>SYNONYMS</>, which is the base name of its configuration file
    &mdash; <literal>my_synonyms</> in the above example.
    The file's full name will be
    <filename>$SHAREDIR/tsearch_data/my_synonyms.syn</>
    (where <literal>$SHAREDIR</> means the
    <productname>PostgreSQL</> installation's shared-data directory).
    The file format is just one line
    per word to be substituted, with the word followed by its synonym,
    separated by white space.  Blank lines and trailing spaces are ignored,
    and upper case is folded to lower case.
   </para>

  </sect2>

  <sect2 id="textsearch-thesaurus">
   <title>Thesaurus Dictionary</title>

   <para>
    A thesaurus dictionary (sometimes abbreviated as <acronym>TZ</acronym>) is
    a collection of words that includes information about the relationships
    of words and phrases, i.e., broader terms (<acronym>BT</acronym>), narrower
    terms (<acronym>NT</acronym>), preferred terms, non-preferred terms, related
    terms, etc.
   </para>

   <para>
    Basically a thesaurus dictionary replaces all non-preferred terms by one
    preferred term and, optionally, preserves the original terms for indexing
    as well.  <productname>PostgreSQL</>'s current implementation of the
    thesaurus dictionary is an extension of the synonym dictionary with added
    <firstterm>phrase</firstterm> support.  A thesaurus dictionary requires
    a configuration file of the following format:

<programlisting>
# this is a comment
sample word(s) : indexed word(s)
more sample word(s) : more indexed word(s)
...
</programlisting>

    where  the colon (<symbol>:</symbol>) symbol acts as a delimiter between a
    a phrase and its replacement.
   </para>

   <para>
    A thesaurus dictionary uses a <firstterm>subdictionary</firstterm> (which
    is specified in the dictionary's configuration) to normalize the input
    text before checking for phrase matches. It is only possible to select one
    subdictionary.  An error is reported if the subdictionary fails to
    recognize a word. In that case, you should remove the use of the word or
    teach the subdictionary about it.  You can place an asterisk
    (<symbol>*</symbol>) at the beginning of an indexed word to skip applying
    the subdictionary to it, but all sample words <emphasis>must</> be known
    to the subdictionary.
   </para>

   <para>
    The thesaurus dictionary chooses the longest match if there are multiple
    phrases matching the input, and ties are broken by using the last
    definition.
   </para>

   <para>
2300 2301 2302 2303
    Specific stop words recognized by the subdictionary cannot be
    specified;  instead use <literal>?</> to mark the location where any
    stop word can appear.  For example, assuming that <literal>a</> and
    <literal>the</> are stop words according to the subdictionary:
2304 2305

<programlisting>
2306
? one ? two : swsw
2307 2308
</programlisting>

2309 2310
    matches <literal>a one the two</> and <literal>the one a two</>;
    both would be replaced by <literal>swsw</>.
2311 2312 2313 2314 2315 2316 2317 2318
   </para>

   <para>
    Since a thesaurus dictionary has the capability to recognize phrases it
    must remember its state and interact with the parser. A thesaurus dictionary
    uses these assignments to check if it should handle the next word or stop
    accumulation.  The thesaurus dictionary must be configured
    carefully. For example, if the thesaurus dictionary is assigned to handle
2319
    only the <literal>asciiword</literal> token, then a thesaurus dictionary
2320 2321 2322 2323 2324 2325 2326 2327 2328 2329 2330 2331 2332 2333 2334 2335 2336 2337 2338 2339 2340 2341 2342 2343 2344 2345 2346 2347 2348 2349 2350 2351 2352 2353 2354 2355 2356 2357 2358 2359 2360 2361 2362 2363 2364 2365 2366 2367 2368 2369 2370 2371 2372
    definition like <literal>one 7</> will not work since token type
    <literal>uint</literal> is not assigned to the thesaurus dictionary.
   </para>

   <caution>
    <para>
     Thesauruses are used during indexing so any change in the thesaurus
     dictionary's parameters <emphasis>requires</emphasis> reindexing.
     For most other dictionary types, small changes such as adding or
     removing stopwords does not force reindexing.
    </para>
   </caution>

  <sect3 id="textsearch-thesaurus-config">
   <title>Thesaurus Configuration</title>

   <para>
    To define a new thesaurus dictionary, use the <literal>thesaurus</>
    template.  For example:

<programlisting>
CREATE TEXT SEARCH DICTIONARY thesaurus_simple (
    TEMPLATE = thesaurus,
    DictFile = mythesaurus,
    Dictionary = pg_catalog.english_stem
);
</programlisting>

    Here:
    <itemizedlist  spacing="compact" mark="bullet">
     <listitem>
      <para>
       <literal>thesaurus_simple</literal> is the new dictionary's name
      </para>
     </listitem>
     <listitem>
      <para>
       <literal>mythesaurus</literal> is the base name of the thesaurus
       configuration file.
       (Its full name will be <filename>$SHAREDIR/tsearch_data/mythesaurus.ths</>,
       where <literal>$SHAREDIR</> means the installation shared-data
       directory.)
      </para>
     </listitem>
     <listitem>
      <para>
       <literal>pg_catalog.english_stem</literal> is the subdictionary (here,
       a Snowball English stemmer) to use for thesaurus normalization.
       Notice that the subdictionary will have its own
       configuration (for example, stop words), which is not shown here.
      </para>
     </listitem>
    </itemizedlist>
2373

2374 2375
    Now it is possible to bind the thesaurus dictionary <literal>thesaurus_simple</literal>
    to the desired token types in a configuration, for example:
2376

2377
<programlisting>
2378
ALTER TEXT SEARCH CONFIGURATION russian
2379
    ADD MAPPING FOR asciiword, asciihword, hword_asciipart WITH thesaurus_simple;
2380
</programlisting>
2381
   </para>
2382

2383 2384 2385 2386
  </sect3>

  <sect3 id="textsearch-thesaurus-examples">
   <title>Thesaurus Example</title>
2387

2388
   <para>
2389 2390
    Consider a simple astronomical thesaurus <literal>thesaurus_astro</literal>,
    which contains some astronomical word combinations:
2391

2392
<programlisting>
2393 2394
supernovae stars : sn
crab nebulae : crab
2395
</programlisting>
2396

2397 2398
    Below we create a dictionary and bind some token types to
    an astronomical thesaurus and english stemmer:
2399

2400
<programlisting>
2401 2402 2403 2404 2405
CREATE TEXT SEARCH DICTIONARY thesaurus_astro (
    TEMPLATE = thesaurus,
    DictFile = thesaurus_astro,
    Dictionary = english_stem
);
2406

2407
ALTER TEXT SEARCH CONFIGURATION russian
2408
    ADD MAPPING FOR asciiword, asciihword, hword_asciipart WITH thesaurus_astro, english_stem;
2409 2410 2411 2412 2413 2414 2415 2416 2417 2418 2419 2420 2421 2422 2423 2424 2425 2426 2427 2428 2429 2430 2431 2432 2433 2434
</programlisting>

    Now we can see how it works.
    <function>ts_lexize</function> is not very useful for testing a thesaurus,
    because it treats its input as a single token.  Instead we can use
    <function>plainto_tsquery</function> and <function>to_tsvector</function>
    which will break their input strings into multiple tokens:

<programlisting>
SELECT plainto_tsquery('supernova star');
 plainto_tsquery
-----------------
 'sn'

SELECT to_tsvector('supernova star');
 to_tsvector
-------------
 'sn':1
</programlisting>

    In principle, one can use <function>to_tsquery</function> if you quote
    the argument:

<programlisting>
SELECT to_tsquery('''supernova star''');
 to_tsquery
2435
------------
2436
 'sn'
2437 2438
</programlisting>

2439 2440 2441 2442
    Notice that <literal>supernova star</literal> matches <literal>supernovae
    stars</literal> in <literal>thesaurus_astro</literal> because we specified
    the <literal>english_stem</literal> stemmer in the thesaurus definition.
    The stemmer removed the <literal>e</> and <literal>s</>.
2443 2444 2445
   </para>

   <para>
2446 2447 2448 2449 2450 2451 2452 2453 2454 2455 2456
    To index the original phrase as well as the substitute, just include it
    in the right-hand part of the definition:

<programlisting>
supernovae stars : sn supernovae stars

SELECT plainto_tsquery('supernova star');
       plainto_tsquery
-----------------------------
 'sn' &amp; 'supernova' &amp; 'star'
</programlisting>
2457
   </para>
2458

2459 2460
  </sect3>

2461 2462
  </sect2>

2463 2464
  <sect2 id="textsearch-ispell-dictionary">
   <title><application>Ispell</> Dictionary</title>
2465 2466

   <para>
2467 2468 2469 2470 2471 2472 2473
    The <application>Ispell</> dictionary template supports
    <firstterm>morphological dictionaries</>, which can normalize many
    different linguistic forms of a word into the same lexeme.  For example,
    an English <application>Ispell</> dictionary can match all declensions and
    conjugations of the search term <literal>bank</literal>, e.g.
    <literal>banking</>, <literal>banked</>, <literal>banks</>,
    <literal>banks'</>, and <literal>bank's</>.
2474 2475
   </para>

2476
   <para>
2477 2478 2479 2480 2481 2482 2483 2484 2485 2486 2487 2488 2489 2490 2491 2492
    The standard <productname>PostgreSQL</productname> distribution does
    not include any <application>Ispell</> configuration files.
    Dictionaries for a large number of languages are available from <ulink
    url="http://ficus-www.cs.ucla.edu/geoff/ispell.html">Ispell</ulink>.
    Also, some more modern dictionary file formats are supported &mdash; <ulink
    url="http://en.wikipedia.org/wiki/MySpell">MySpell</ulink> (OO &lt; 2.0.1)
    and <ulink url="http://sourceforge.net/projects/hunspell">Hunspell</ulink>
    (OO &gt;= 2.0.2).  A large list of dictionaries is available on the <ulink
    url="http://wiki.services.openoffice.org/wiki/Dictionaries">OpenOffice
    Wiki</ulink>.
   </para>

   <para>
    To create an <application>Ispell</> dictionary, use the built-in
    <literal>ispell</literal> template and specify several parameters:
   </para>
2493 2494

<programlisting>
2495 2496 2497 2498 2499
CREATE TEXT SEARCH DICTIONARY english_ispell (
    TEMPLATE = ispell,
    DictFile = english,
    AffFile = english,
    StopWords = english
2500
);
2501
</programlisting>
2502

2503 2504 2505 2506 2507 2508
   <para>
    Here, <literal>DictFile</>, <literal>AffFile</>, and <literal>StopWords</>
    specify the base names of the dictionary, affixes, and stop-words files.
    The stop-words file has the same format explained above for the
    <literal>simple</> dictionary type.  The format of the other files is
    not specified here but is available from the above-mentioned web sites.
2509 2510 2511
   </para>

   <para>
2512 2513 2514
    Ispell dictionaries usually recognize a limited set of words, so they
    should be followed by another broader dictionary; for
    example, a Snowball dictionary, which recognizes everything.
2515
   </para>
2516

2517
   <para>
2518 2519 2520 2521 2522 2523
    Ispell dictionaries support splitting compound words.
    This is a nice feature and
    <productname>PostgreSQL</productname> supports it.
    Notice that the affix file should specify a special flag using the
    <literal>compoundwords controlled</literal> statement that marks dictionary
    words that can participate in compound formation:
2524 2525

<programlisting>
2526 2527
compoundwords  controlled z
</programlisting>
2528

2529 2530 2531 2532 2533 2534 2535
    Here are some examples for the Norwegian language:

<programlisting>
SELECT ts_lexize('norwegian_ispell', 'overbuljongterningpakkmesterassistent');
   {over,buljong,terning,pakk,mester,assistent}
SELECT ts_lexize('norwegian_ispell', 'sjokoladefabrikk');
   {sjokoladefabrikk,sjokolade,fabrikk}
2536
</programlisting>
2537 2538
   </para>

2539 2540 2541 2542 2543 2544 2545 2546
   <note>
    <para>
     <application>MySpell</> does not support compound words.
     <application>Hunspell</> has sophisticated support for compound words. At
     present, <productname>PostgreSQL</productname> implements only the basic
     compound word operations of Hunspell.
    </para>
   </note>
2547

2548 2549
  </sect2>

2550 2551
  <sect2 id="textsearch-snowball-dictionary">
   <title><application>Snowball</> Dictionary</title>
2552 2553

   <para>
2554 2555 2556 2557 2558 2559 2560 2561 2562 2563 2564 2565
    The <application>Snowball</> dictionary template is based on the project
    of Martin Porter, inventor of the popular Porter's stemming algorithm
    for the English language.  Snowball now provides stemming algorithms for
    many languages (see the <ulink url="http://snowball.tartarus.org">Snowball
    site</ulink> for more information).  Each algorithm understands how to
    reduce common variant forms of words to a base, or stem, spelling within
    its language.  A Snowball dictionary requires a <literal>language</>
    parameter to identify which stemmer to use, and optionally can specify a
    <literal>stopword</> file name that gives a list of words to eliminate.
    (<productname>PostgreSQL</productname>'s standard stopword lists are also
    provided by the Snowball project.)
    For example, there is a built-in definition equivalent to
2566

2567
<programlisting>
2568 2569 2570 2571 2572
CREATE TEXT SEARCH DICTIONARY english_stem (
    TEMPLATE = snowball,
    Language = english,
    StopWords = english
);
2573
</programlisting>
2574

2575
    The stopword file format is the same as already explained.
2576 2577 2578
   </para>

   <para>
2579 2580
    A <application>Snowball</> dictionary recognizes everything, whether
    or not it is able to simplify the word, so it should be placed
Magnus Hagander's avatar
Magnus Hagander committed
2581
    at the end of the dictionary list. It is useless to have it
2582 2583 2584 2585 2586 2587 2588 2589 2590 2591 2592 2593 2594 2595 2596 2597 2598 2599 2600 2601 2602 2603 2604 2605 2606
    before any other dictionary because a token will never pass through it to
    the next dictionary.
   </para>

  </sect2>

 </sect1>

 <sect1 id="textsearch-configuration">
  <title>Configuration Example</title>

   <para>
    A text search configuration specifies all options necessary to transform a
    document into a <type>tsvector</type>: the parser to use to break text
    into tokens, and the dictionaries to use to transform each token into a
    lexeme.  Every call of
    <function>to_tsvector</function> or <function>to_tsquery</function>
    needs a text search configuration to perform its processing.
    The configuration parameter
    <xref linkend="guc-default-text-search-config">
    specifies the name of the default configuration, which is the
    one used by text search functions if an explicit configuration
    parameter is omitted.
    It can be set in <filename>postgresql.conf</filename>, or set for an
    individual session using the <command>SET</> command.
2607 2608 2609
   </para>

   <para>
2610 2611 2612 2613 2614
    Several predefined text search configurations are available, and
    you can create custom configurations easily.  To facilitate management
    of text search objects, a set of <acronym>SQL</acronym> commands
    is available, and there are several psql commands that display information
    about text search objects (<xref linkend="textsearch-psql">).
2615 2616 2617
   </para>

   <para>
2618 2619 2620
    As an example, we will create a configuration
    <literal>pg</literal>, starting from a duplicate of the built-in
    <literal>english</> configuration.
2621

2622
<programlisting>
2623
CREATE TEXT SEARCH CONFIGURATION public.pg ( COPY = pg_catalog.english );
2624
</programlisting>
2625
   </para>
2626

2627
   <para>
2628 2629 2630
    We will use a PostgreSQL-specific synonym list
    and store it in <filename>$SHAREDIR/tsearch_data/pg_dict.syn</filename>.
    The file contents look like:
2631

2632 2633 2634 2635 2636
<programlisting>
postgres    pg
pgsql       pg
postgresql  pg
</programlisting>
2637

2638
    We define the synonym dictionary like this:
2639 2640

<programlisting>
2641 2642 2643
CREATE TEXT SEARCH DICTIONARY pg_dict (
    TEMPLATE = synonym,
    SYNONYMS = pg_dict
2644
);
2645
</programlisting>
2646

2647 2648
    Next we register the <productname>ispell</> dictionary
    <literal>english_ispell</literal>, which has its own configuration files:
2649

2650 2651 2652 2653 2654 2655 2656 2657 2658
<programlisting>
CREATE TEXT SEARCH DICTIONARY english_ispell (
    TEMPLATE = ispell,
    DictFile = english,
    AffFile = english,
    StopWords = english
);
</programlisting>

2659
    Now we can set up the mappings for words in configuration
2660
    <literal>pg</>:
2661 2662

<programlisting>
2663
ALTER TEXT SEARCH CONFIGURATION pg
2664 2665
    ALTER MAPPING FOR asciiword, asciihword, hword_asciipart,
                      word, hword, hword_part
2666
    WITH pg_dict, english_ispell, english_stem;
2667
</programlisting>
2668

2669 2670
    We choose not to index or search some token types that the built-in
    configuration does handle:
2671

2672 2673
<programlisting>
ALTER TEXT SEARCH CONFIGURATION pg
2674
    DROP MAPPING FOR email, url, url_path, sfloat, float;
2675 2676
</programlisting>
   </para>
2677

2678
   <para>
2679
    Now we can test our configuration:
2680 2681

<programlisting>
2682 2683 2684 2685 2686
SELECT * FROM ts_debug('public.pg', '
PostgreSQL, the highly scalable, SQL compliant, open source object-relational
database management system, is now undergoing beta testing of the next
version of our software.
');
2687
</programlisting>
2688
   </para>
2689

2690 2691 2692
   <para>
    The next step is to set the session to use the new configuration, which was
    created in the <literal>public</> schema:
2693

2694
<programlisting>
2695 2696 2697 2698 2699
=&gt; \dF
   List of text search configurations
 Schema  | Name | Description
---------+------+-------------
 public  | pg   |
2700

2701 2702 2703 2704 2705 2706 2707
SET default_text_search_config = 'public.pg';
SET

SHOW default_text_search_config;
 default_text_search_config
----------------------------
 public.pg
2708
</programlisting>
2709
  </para>
2710

2711
 </sect1>
2712

2713 2714
 <sect1 id="textsearch-debugging">
  <title>Testing and Debugging Text Search</title>
2715

2716 2717 2718 2719 2720 2721 2722 2723 2724 2725 2726 2727 2728 2729 2730 2731 2732 2733 2734 2735
  <para>
   The behavior of a custom text search configuration can easily become
   complicated enough to be confusing or undesirable.  The functions described
   in this section are useful for testing text search objects.  You can
   test a complete configuration, or test parsers and dictionaries separately.
  </para>

  <sect2 id="textsearch-configuration-testing">
   <title>Configuration Testing</title>

  <para>
   The function <function>ts_debug</function> allows easy testing of a
   text search configuration.
  </para>

  <indexterm>
   <primary>ts_debug</primary>
  </indexterm>

  <synopsis>
2736 2737 2738 2739 2740 2741 2742 2743
   ts_debug(<optional> <replaceable class="PARAMETER">config</replaceable> <type>regconfig</>, </optional> <replaceable class="PARAMETER">document</replaceable> <type>text</>,
            OUT <replaceable class="PARAMETER">alias</> <type>text</>,
            OUT <replaceable class="PARAMETER">description</> <type>text</>,
            OUT <replaceable class="PARAMETER">token</> <type>text</>,
            OUT <replaceable class="PARAMETER">dictionaries</> <type>regdictionary[]</>,
            OUT <replaceable class="PARAMETER">dictionary</> <type>regdictionary</>,
            OUT <replaceable class="PARAMETER">lexemes</> <type>text[]</>)
            returns setof record
2744 2745 2746 2747 2748 2749 2750 2751 2752 2753 2754
  </synopsis>

  <para>
   <function>ts_debug</> displays information about every token of
   <replaceable class="PARAMETER">document</replaceable> as produced by the
   parser and processed by the configured dictionaries.  It uses the
   configuration specified by <replaceable
   class="PARAMETER">config</replaceable>,
   or <varname>default_text_search_config</varname> if that argument is
   omitted.
  </para>
2755

2756
  <para>
2757 2758
   <function>ts_debug</> returns one row for each token identified in the text
   by the parser.  The columns returned are
2759

2760 2761 2762 2763 2764 2765 2766 2767 2768 2769 2770 2771 2772 2773 2774 2775 2776 2777 2778 2779 2780 2781 2782 2783 2784 2785 2786 2787 2788 2789 2790 2791 2792 2793 2794 2795 2796 2797
    <itemizedlist  spacing="compact" mark="bullet">
     <listitem>
      <para>
       <replaceable>alias</> <type>text</> &mdash; short name of the token type
      </para>
     </listitem>
     <listitem>
      <para>
       <replaceable>description</> <type>text</> &mdash; description of the
       token type
      </para>
     </listitem>
     <listitem>
      <para>
       <replaceable>token</> <type>text</> &mdash; text of the token
      </para>
     </listitem>
     <listitem>
      <para>
       <replaceable>dictionaries</> <type>regdictionary[]</> &mdash; the
       dictionaries selected by the configuration for this token type
      </para>
     </listitem>
     <listitem>
      <para>
       <replaceable>dictionary</> <type>regdictionary</> &mdash; the dictionary
       that recognized the token, or <literal>NULL</> if none did
      </para>
     </listitem>
     <listitem>
      <para>
       <replaceable>lexemes</> <type>text[]</> &mdash; the lexeme(s) produced
       by the dictionary that recognized the token, or <literal>NULL</> if
       none did; an empty array (<literal>{}</>) means it was recognized as a
       stop word
      </para>
     </listitem>
    </itemizedlist>
2798
  </para>
2799

2800 2801
  <para>
   Here is a simple example:
2802

2803
<programlisting>
2804
SELECT * FROM ts_debug('english','a fat  cat sat on a mat - it ate a fat rats');
2805 2806 2807 2808 2809 2810 2811 2812 2813 2814 2815 2816 2817 2818 2819 2820 2821 2822 2823 2824 2825 2826 2827 2828 2829 2830
   alias   |   description   | token |  dictionaries  |  dictionary  | lexemes 
-----------+-----------------+-------+----------------+--------------+---------
 asciiword | Word, all ASCII | a     | {english_stem} | english_stem | {}
 blank     | Space symbols   |       | {}             |              | 
 asciiword | Word, all ASCII | fat   | {english_stem} | english_stem | {fat}
 blank     | Space symbols   |       | {}             |              | 
 asciiword | Word, all ASCII | cat   | {english_stem} | english_stem | {cat}
 blank     | Space symbols   |       | {}             |              | 
 asciiword | Word, all ASCII | sat   | {english_stem} | english_stem | {sat}
 blank     | Space symbols   |       | {}             |              | 
 asciiword | Word, all ASCII | on    | {english_stem} | english_stem | {}
 blank     | Space symbols   |       | {}             |              | 
 asciiword | Word, all ASCII | a     | {english_stem} | english_stem | {}
 blank     | Space symbols   |       | {}             |              | 
 asciiword | Word, all ASCII | mat   | {english_stem} | english_stem | {mat}
 blank     | Space symbols   |       | {}             |              | 
 blank     | Space symbols   | -     | {}             |              | 
 asciiword | Word, all ASCII | it    | {english_stem} | english_stem | {}
 blank     | Space symbols   |       | {}             |              | 
 asciiword | Word, all ASCII | ate   | {english_stem} | english_stem | {ate}
 blank     | Space symbols   |       | {}             |              | 
 asciiword | Word, all ASCII | a     | {english_stem} | english_stem | {}
 blank     | Space symbols   |       | {}             |              | 
 asciiword | Word, all ASCII | fat   | {english_stem} | english_stem | {fat}
 blank     | Space symbols   |       | {}             |              | 
 asciiword | Word, all ASCII | rats  | {english_stem} | english_stem | {rat}
2831
</programlisting>
2832
  </para>
2833

2834 2835 2836 2837 2838
  <para>
   For a more extensive demonstration, we
   first create a <literal>public.english</literal> configuration and
   ispell dictionary for the English language:
  </para>
2839 2840

<programlisting>
2841 2842
CREATE TEXT SEARCH CONFIGURATION public.english ( COPY = pg_catalog.english );

2843
CREATE TEXT SEARCH DICTIONARY english_ispell (
2844 2845 2846 2847 2848
    TEMPLATE = ispell,
    DictFile = english,
    AffFile = english,
    StopWords = english
);
2849 2850

ALTER TEXT SEARCH CONFIGURATION public.english
2851
   ALTER MAPPING FOR asciiword WITH english_ispell, english_stem;
2852
</programlisting>
2853

2854 2855
<programlisting>
SELECT * FROM ts_debug('public.english','The Brightest supernovaes');
2856 2857 2858 2859 2860 2861 2862
   alias   |   description   |    token    |         dictionaries          |   dictionary   |   lexemes   
-----------+-----------------+-------------+-------------------------------+----------------+-------------
 asciiword | Word, all ASCII | The         | {english_ispell,english_stem} | english_ispell | {}
 blank     | Space symbols   |             | {}                            |                | 
 asciiword | Word, all ASCII | Brightest   | {english_ispell,english_stem} | english_ispell | {bright}
 blank     | Space symbols   |             | {}                            |                | 
 asciiword | Word, all ASCII | supernovaes | {english_ispell,english_stem} | english_stem   | {supernova}
2863
</programlisting>
2864

2865 2866
  <para>
   In this example, the word <literal>Brightest</> was recognized by the
2867
   parser as an <literal>ASCII word</literal> (alias <literal>asciiword</literal>).
2868
   For this token type the dictionary list is
2869 2870 2871
   <literal>english_ispell</> and
   <literal>english_stem</literal>. The word was recognized by
   <literal>english_ispell</literal>, which reduced it to the noun
2872
   <literal>bright</literal>. The word <literal>supernovaes</literal> is
2873
   unknown to the <literal>english_ispell</literal> dictionary so it
2874
   was passed to the next dictionary, and, fortunately, was recognized (in
2875
   fact, <literal>english_stem</literal> is a Snowball dictionary which
2876 2877 2878
   recognizes everything; that is why it was placed at the end of the
   dictionary list).
  </para>
2879

2880 2881
  <para>
   The word <literal>The</literal> was recognized by the
2882
   <literal>english_ispell</literal> dictionary as a stop word (<xref
2883 2884 2885 2886 2887 2888 2889 2890
   linkend="textsearch-stopwords">) and will not be indexed.
   The spaces are discarded too, since the configuration provides no
   dictionaries at all for them.
  </para>

  <para>
   You can reduce the volume of output by explicitly specifying which columns
   you want to see:
2891

2892
<programlisting>
2893
SELECT alias, token, dictionary, lexemes
2894
FROM ts_debug('public.english','The Brightest supernovaes');
2895 2896 2897 2898 2899 2900 2901
   alias   |    token    |   dictionary   |   lexemes   
-----------+-------------+----------------+-------------
 asciiword | The         | english_ispell | {}
 blank     |             |                | 
 asciiword | Brightest   | english_ispell | {bright}
 blank     |             |                | 
 asciiword | supernovaes | english_stem   | {supernova}
2902
</programlisting>
2903
  </para>
2904

2905
  </sect2>
2906

2907 2908
  <sect2 id="textsearch-parser-testing">
   <title>Parser Testing</title>
2909

2910 2911 2912
  <para>
   The following functions allow direct testing of a text search parser.
  </para>
2913

2914 2915 2916
  <indexterm>
   <primary>ts_parse</primary>
  </indexterm>
2917

2918 2919 2920 2921
  <synopsis>
   ts_parse(<replaceable class="PARAMETER">parser_name</replaceable> <type>text</>, <replaceable class="PARAMETER">document</replaceable> <type>text</>, OUT <replaceable class="PARAMETER">tokid</> <type>integer</>, OUT <replaceable class="PARAMETER">token</> <type>text</>) returns <type>setof record</>
   ts_parse(<replaceable class="PARAMETER">parser_oid</replaceable> <type>oid</>, <replaceable class="PARAMETER">document</replaceable> <type>text</>, OUT <replaceable class="PARAMETER">tokid</> <type>integer</>, OUT <replaceable class="PARAMETER">token</> <type>text</>) returns <type>setof record</>
  </synopsis>
2922

2923 2924 2925 2926 2927 2928
  <para>
   <function>ts_parse</> parses the given <replaceable>document</replaceable>
   and returns a series of records, one for each token produced by
   parsing. Each record includes a <varname>tokid</varname> showing the
   assigned token type and a <varname>token</varname> which is the text of the
   token.  For example:
2929

2930
<programlisting>
2931 2932 2933 2934 2935 2936 2937 2938 2939
SELECT * FROM ts_parse('default', '123 - a number');
 tokid | token
-------+--------
    22 | 123
    12 |
    12 | -
     1 | a
    12 |
     1 | number
2940
</programlisting>
2941
  </para>
2942

2943 2944 2945
  <indexterm>
   <primary>ts_token_type</primary>
  </indexterm>
2946

2947 2948 2949 2950 2951 2952 2953 2954 2955 2956 2957 2958 2959 2960 2961
  <synopsis>
   ts_token_type(<replaceable class="PARAMETER">parser_name</> <type>text</>, OUT <replaceable class="PARAMETER">tokid</> <type>integer</>, OUT <replaceable class="PARAMETER">alias</> <type>text</>, OUT <replaceable class="PARAMETER">description</> <type>text</>) returns <type>setof record</>
   ts_token_type(<replaceable class="PARAMETER">parser_oid</> <type>oid</>, OUT <replaceable class="PARAMETER">tokid</> <type>integer</>, OUT <replaceable class="PARAMETER">alias</> <type>text</>, OUT <replaceable class="PARAMETER">description</> <type>text</>) returns <type>setof record</>
  </synopsis>

  <para>
   <function>ts_token_type</> returns a table which describes each type of
   token the specified parser can recognize.  For each token type, the table
   gives the integer <varname>tokid</varname> that the parser uses to label a
   token of that type, the <varname>alias</varname> that names the token type
   in configuration commands, and a short <varname>description</varname>.  For
   example:

<programlisting>
SELECT * FROM ts_token_type('default');
2962 2963 2964 2965 2966 2967 2968 2969 2970 2971 2972 2973 2974 2975
 tokid |      alias      |               description                
-------+-----------------+------------------------------------------
     1 | asciiword       | Word, all ASCII
     2 | word            | Word, all letters
     3 | numword         | Word, letters and digits
     4 | email           | Email address
     5 | url             | URL
     6 | host            | Host
     7 | sfloat          | Scientific notation
     8 | version         | Version number
     9 | hword_numpart   | Hyphenated word part, letters and digits
    10 | hword_part      | Hyphenated word part, all letters
    11 | hword_asciipart | Hyphenated word part, all ASCII
    12 | blank           | Space symbols
2976
    13 | tag             | XML tag
2977 2978 2979 2980
    14 | protocol        | Protocol head
    15 | numhword        | Hyphenated word, letters and digits
    16 | asciihword      | Hyphenated word, all ASCII
    17 | hword           | Hyphenated word, all letters
2981
    18 | url_path        | URL path
2982 2983 2984 2985
    19 | file            | File or path name
    20 | float           | Decimal notation
    21 | int             | Signed integer
    22 | uint            | Unsigned integer
2986
    23 | entity          | XML entity
2987
</programlisting>
2988
   </para>
2989

2990
  </sect2>
2991

2992
  <sect2 id="textsearch-dictionary-testing">
2993
   <title>Dictionary Testing</title>
2994

2995
   <para>
2996 2997
    The <function>ts_lexize</> function facilitates dictionary testing.
   </para>
2998

2999 3000 3001
   <indexterm>
    <primary>ts_lexize</primary>
   </indexterm>
3002

3003 3004 3005
   <synopsis>
    ts_lexize(<replaceable class="PARAMETER">dict</replaceable> <type>regdictionary</>, <replaceable class="PARAMETER">token</replaceable> <type>text</>) returns <type>text[]</>
   </synopsis>
3006

3007 3008 3009 3010 3011 3012 3013
   <para>
    <function>ts_lexize</> returns an array of lexemes if the input
    <replaceable>token</replaceable> is known to the dictionary,
    or an empty array if the token
    is known to the dictionary but it is a stop word, or
    <literal>NULL</literal> if it is an unknown word.
   </para>
3014

3015 3016
   <para>
    Examples:
3017 3018

<programlisting>
3019
SELECT ts_lexize('english_stem', 'stars');
3020 3021
 ts_lexize
-----------
3022
 {star}
3023

3024
SELECT ts_lexize('english_stem', 'a');
3025 3026
 ts_lexize
-----------
3027 3028
 {}
</programlisting>
3029
   </para>
3030

3031 3032
   <note>
    <para>
3033 3034 3035
     The <function>ts_lexize</function> function expects a single
     <emphasis>token</emphasis>, not text. Here is a case
     where this can be confusing:
3036 3037

<programlisting>
3038
SELECT ts_lexize('thesaurus_astro','supernovae stars') is null;
3039 3040 3041 3042
 ?column?
----------
 t
</programlisting>
3043

3044 3045 3046 3047 3048
     The thesaurus dictionary <literal>thesaurus_astro</literal> does know the
     phrase <literal>supernovae stars</literal>, but <function>ts_lexize</>
     fails since it does not parse the input text but treats it as a single
     token. Use <function>plainto_tsquery</> or <function>to_tsvector</> to
     test thesaurus dictionaries, for example:
3049

3050 3051 3052 3053 3054 3055
<programlisting>
SELECT plainto_tsquery('supernovae stars');
 plainto_tsquery
-----------------
 'sn'
</programlisting>
3056 3057 3058 3059
    </para>
   </note>

  </sect2>
3060

3061
 </sect1>
3062

3063
 <sect1 id="textsearch-indexes">
3064
  <title>GiST and GIN Index Types</title>
3065 3066

  <indexterm zone="textsearch-indexes">
3067
   <primary>text search</primary>
3068
   <secondary>indexes</secondary>
3069 3070
  </indexterm>

3071
  <para>
3072
   There are two kinds of indexes that can be used to speed up full text
3073 3074 3075 3076
   searches.
   Note that indexes are not mandatory for full text searching, but in
   cases where a column is searched on a regular basis, an index will
   usually be desirable.
3077

3078
   <variablelist>
3079

3080
    <varlistentry>
3081

3082
     <indexterm zone="textsearch-indexes">
3083 3084 3085
      <primary>index</primary>
      <secondary>GiST</secondary>
      <tertiary>text search</tertiary>
3086
     </indexterm>
3087

3088 3089 3090 3091 3092
     <term>
      <synopsis>
       CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> USING gist(<replaceable>column</replaceable>);
      </synopsis>
     </term>
3093

3094 3095 3096 3097 3098 3099 3100 3101
     <listitem>
      <para>
       Creates a GiST (Generalized Search Tree)-based index.
       The <replaceable>column</replaceable> can be of <type>tsvector</> or
       <type>tsquery</> type.
      </para>
     </listitem>
    </varlistentry>
3102

3103
    <varlistentry>
3104

3105
     <indexterm zone="textsearch-indexes">
3106 3107 3108
      <primary>index</primary>
      <secondary>GIN</secondary>
      <tertiary>text search</tertiary>
3109
     </indexterm>
3110

3111 3112 3113 3114 3115
     <term>
      <synopsis>
       CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> USING gin(<replaceable>column</replaceable>);
      </synopsis>
     </term>
3116

3117 3118 3119 3120 3121 3122 3123
     <listitem>
      <para>
       Creates a GIN (Generalized Inverted Index)-based index.
       The <replaceable>column</replaceable> must be of <type>tsvector</> type.
      </para>
     </listitem>
    </varlistentry>
3124

3125 3126
   </variablelist>
  </para>
3127

3128 3129 3130 3131 3132
  <para>
   There are substantial performance differences between the two index types,
   so it is important to understand which to use.
  </para>

3133
  <para>
3134 3135 3136
   A GiST index is <firstterm>lossy</firstterm>, meaning that the index
   may produce false matches, and it is necessary
   to check the actual table row to eliminate such false matches.
3137 3138 3139
   <productname>PostgreSQL</productname> does this automatically; for
   example, in the query plan below, the <literal>Filter:</literal>
   line indicates the index output will be rechecked:
3140 3141 3142 3143 3144 3145 3146 3147 3148

<programlisting>
EXPLAIN SELECT * FROM apod WHERE textsearch @@ to_tsquery('supernovae');
                               QUERY PLAN
-------------------------------------------------------------------------
 Index Scan using textsearch_gidx on apod  (cost=0.00..12.29 rows=2 width=1469)
   Index Cond: (textsearch @@ '''supernova'''::tsquery)
   Filter: (textsearch @@ '''supernova'''::tsquery)
</programlisting>
3149

3150 3151 3152 3153
   GiST indexes are lossy because each document is represented in the
   index by a fixed-length signature. The signature is generated by hashing
   each word into a random bit in an n-bit string, with all these bits OR-ed
   together to produce an n-bit document signature.  When two words hash to
3154
   the same bit position there will be a false match.  If all words in
3155 3156
   the query have matches (real or false) then the table row must be
   retrieved to see if the match is correct.
3157 3158 3159
  </para>

  <para>
3160 3161 3162
   Lossiness causes performance degradation due to useless fetches of table
   records that turn out to be false matches.  Since random access to table
   records is slow, this limits the usefulness of GiST indexes.  The
3163 3164 3165
   likelihood of false matches depends on several factors, in particular the
   number of unique words, so using dictionaries to reduce this number is
   recommended.
3166 3167 3168 3169 3170 3171 3172 3173
  </para>

  <para>
   GIN indexes are not lossy but their performance depends logarithmically on
   the number of unique words.
  </para>

  <para>
3174 3175 3176 3177 3178 3179 3180 3181 3182 3183 3184 3185 3186 3187 3188 3189 3190 3191 3192 3193 3194 3195 3196 3197 3198 3199 3200 3201 3202 3203 3204 3205 3206 3207 3208 3209 3210 3211
   Actually, GIN indexes store only the words (lexemes) of <type>tsvector</>
   values, and not their weight labels.  Thus, while a GIN index can be
   considered non-lossy for a query that does not specify weights, it is
   lossy for one that does.  Thus a table row recheck is needed when using
   a query that involves weights.  Unfortunately, in the current design of
   <productname>PostgreSQL</>, whether a recheck is needed is a static
   property of a particular operator, and not something that can be enabled
   or disabled on-the-fly depending on the values given to the operator.
   To deal with this situation without imposing the overhead of rechecks
   on queries that do not need them, the following approach has been
   adopted:
  </para>

  <itemizedlist  spacing="compact" mark="bullet">
   <listitem>
    <para>
     The standard text match operator <literal>@@</> is marked as non-lossy
     for GIN indexes.
    </para>
   </listitem>

   <listitem>
    <para>
     An additional match operator <literal>@@@</> is provided, and marked
     as lossy for GIN indexes.  This operator behaves exactly like
     <literal>@@</> otherwise.
    </para>
   </listitem>

   <listitem>
    <para>
     When a GIN index search is initiated with the <literal>@@</> operator,
     the index support code will throw an error if the query specifies any
     weights.  This protects against giving wrong answers due to failure
     to recheck the weights.
    </para>
   </listitem>
  </itemizedlist>
3212

3213 3214 3215 3216 3217 3218 3219
  <para>
   In short, you must use <literal>@@@</> rather than <literal>@@</> to
   perform GIN index searches on queries that involve weight restrictions.
   For queries that do not have weight restrictions, either operator will
   work, but <literal>@@</> will be faster.
   This awkwardness will probably be addressed in a future release of
   <productname>PostgreSQL</>.
3220 3221 3222
  </para>

  <para>
3223 3224 3225
   In choosing which index type to use, GiST or GIN, consider these
   performance differences:

3226 3227 3228
   <itemizedlist  spacing="compact" mark="bullet">
    <listitem>
     <para>
3229
      GIN index lookups are about three times faster than GiST
3230 3231 3232 3233
     </para>
    </listitem>
    <listitem>
     <para>
3234
      GIN indexes take about three times longer to build than GiST
3235 3236 3237 3238
     </para>
    </listitem>
    <listitem>
     <para>
3239
      GIN indexes are about ten times slower to update than GiST
3240 3241 3242 3243
     </para>
    </listitem>
    <listitem>
     <para>
3244
      GIN indexes are two-to-three times larger than GiST
3245 3246 3247 3248 3249 3250
     </para>
    </listitem>
   </itemizedlist>
  </para>

  <para>
3251 3252
   As a rule of thumb, <acronym>GIN</acronym> indexes are best for static data
   because lookups are faster.  For dynamic data, GiST indexes are
3253 3254
   faster to update.  Specifically, <acronym>GiST</acronym> indexes are very
   good for dynamic data and fast if the number of unique words (lexemes) is
3255 3256
   under 100,000, while <acronym>GIN</acronym> indexes will handle 100,000+
   lexemes better but are slower to update.
3257 3258
  </para>

3259 3260 3261 3262 3263 3264 3265
  <para>
   Note that <acronym>GIN</acronym> index build time can often be improved
   by increasing <xref linkend="guc-maintenance-work-mem">, while
   <acronym>GiST</acronym> index build time is not sensitive to that
   parameter.
  </para>

3266 3267 3268 3269
  <para>
   Partitioning of big collections and the proper use of GiST and GIN indexes
   allows the implementation of very fast searches with online update.
   Partitioning can be done at the database level using table inheritance
3270
   and <varname>constraint_exclusion</>, or by distributing documents over
3271 3272 3273 3274 3275 3276 3277 3278
   servers and collecting search results using the <filename>contrib/dblink</>
   extension module. The latter is possible because ranking functions use
   only local information.
  </para>

 </sect1>

 <sect1 id="textsearch-psql">
3279
  <title><application>psql</> Support</title>
3280 3281

  <para>
3282
   Information about text search configuration objects can be obtained
3283
   in <application>psql</application> using a set of commands:
3284
   <synopsis>
3285
   \dF{d,p,t}<optional>+</optional> <optional>PATTERN</optional>
3286 3287 3288 3289 3290 3291
   </synopsis>
   An optional <literal>+</literal> produces more details.
  </para>

  <para>
   The optional parameter <literal>PATTERN</literal> should be the name of
3292 3293
   a text search object, optionally schema-qualified.  If
   <literal>PATTERN</literal> is omitted then information about all
3294 3295 3296
   visible objects will be displayed.  <literal>PATTERN</literal> can be a
   regular expression and can provide <emphasis>separate</emphasis> patterns
   for the schema and object names.  The following examples illustrate this:
3297

3298 3299
<programlisting>
=&gt; \dF *fulltext*
3300
       List of text search configurations
3301 3302 3303 3304 3305 3306 3307
 Schema |  Name        | Description
--------+--------------+-------------
 public | fulltext_cfg |
</programlisting>

<programlisting>
=&gt; \dF *.fulltext*
3308
       List of text search configurations
3309 3310
 Schema   |  Name        | Description
----------+----------------------------
3311
 fulltext | fulltext_cfg |
3312 3313
 public   | fulltext_cfg |
</programlisting>
3314 3315

   The available commands are:
3316
  </para>
3317

3318
  <variablelist>
3319 3320

   <varlistentry>
3321
    <term><synopsis>\dF<optional>+</optional> <optional>PATTERN</optional></synopsis></term>
3322 3323 3324

    <listitem>
     <para>
3325
      List text search configurations (add <literal>+</> for more detail).
3326
     </para>
3327

3328 3329
     <para>

3330 3331
<programlisting>
=&gt; \dF russian
3332 3333 3334 3335
            List of text search configurations
   Schema   |  Name   |            Description             
------------+---------+------------------------------------
 pg_catalog | russian | configuration for russian language
3336 3337

=&gt; \dF+ russian
3338 3339
Text search configuration "pg_catalog.russian"
Parser: "pg_catalog.default"
3340 3341 3342 3343 3344 3345 3346 3347 3348 3349 3350 3351 3352 3353 3354 3355 3356 3357
      Token      | Dictionaries 
-----------------+--------------
 asciihword      | english_stem
 asciiword       | english_stem
 email           | simple
 file            | simple
 float           | simple
 host            | simple
 hword           | russian_stem
 hword_asciipart | english_stem
 hword_numpart   | simple
 hword_part      | russian_stem
 int             | simple
 numhword        | simple
 numword         | simple
 sfloat          | simple
 uint            | simple
 url             | simple
3358
 url_path        | simple
3359 3360
 version         | simple
 word            | russian_stem
3361
</programlisting>
3362
     </para>
3363 3364 3365 3366
    </listitem>
   </varlistentry>

   <varlistentry>
3367
    <term><synopsis>\dFd<optional>+</optional> <optional>PATTERN</optional></synopsis></term>
3368 3369
    <listitem>
     <para>
3370
      List text search dictionaries (add <literal>+</> for more detail).
3371
     </para>
3372 3373

     <para>
3374 3375
<programlisting>
=&gt; \dFd
3376 3377 3378 3379 3380 3381 3382 3383 3384 3385 3386 3387 3388 3389 3390 3391 3392 3393 3394
                            List of text search dictionaries
   Schema   |      Name       |                        Description                        
------------+-----------------+-----------------------------------------------------------
 pg_catalog | danish_stem     | snowball stemmer for danish language
 pg_catalog | dutch_stem      | snowball stemmer for dutch language
 pg_catalog | english_stem    | snowball stemmer for english language
 pg_catalog | finnish_stem    | snowball stemmer for finnish language
 pg_catalog | french_stem     | snowball stemmer for french language
 pg_catalog | german_stem     | snowball stemmer for german language
 pg_catalog | hungarian_stem  | snowball stemmer for hungarian language
 pg_catalog | italian_stem    | snowball stemmer for italian language
 pg_catalog | norwegian_stem  | snowball stemmer for norwegian language
 pg_catalog | portuguese_stem | snowball stemmer for portuguese language
 pg_catalog | romanian_stem   | snowball stemmer for romanian language
 pg_catalog | russian_stem    | snowball stemmer for russian language
 pg_catalog | simple          | simple dictionary: just lower case and check for stopword
 pg_catalog | spanish_stem    | snowball stemmer for spanish language
 pg_catalog | swedish_stem    | snowball stemmer for swedish language
 pg_catalog | turkish_stem    | snowball stemmer for turkish language
3395
</programlisting>
3396
     </para>
3397 3398 3399 3400 3401
    </listitem>
   </varlistentry>

   <varlistentry>

3402
   <term><synopsis>\dFp<optional>+</optional> <optional>PATTERN</optional></synopsis></term>
3403 3404
    <listitem>
     <para>
3405
      List text search parsers (add <literal>+</> for more detail).
3406
     </para>
3407

3408
     <para>
3409
<programlisting>
3410 3411 3412
=&gt; \dFp
        List of text search parsers
   Schema   |  Name   |     Description     
3413 3414 3415
------------+---------+---------------------
 pg_catalog | default | default word parser
=&gt; \dFp+
3416 3417 3418 3419 3420 3421 3422 3423
    Text search parser "pg_catalog.default"
     Method      |    Function    | Description 
-----------------+----------------+-------------
 Start parse     | prsd_start     | 
 Get next token  | prsd_nexttoken | 
 End parse       | prsd_end       | 
 Get headline    | prsd_headline  | 
 Get token types | prsd_lextype   | 
3424

3425 3426 3427 3428 3429 3430 3431
        Token types for parser "pg_catalog.default"
   Token name    |               Description                
-----------------+------------------------------------------
 asciihword      | Hyphenated word, all ASCII
 asciiword       | Word, all ASCII
 blank           | Space symbols
 email           | Email address
3432
 entity          | XML entity
3433 3434 3435 3436 3437 3438 3439 3440 3441 3442 3443 3444
 file            | File or path name
 float           | Decimal notation
 host            | Host
 hword           | Hyphenated word, all letters
 hword_asciipart | Hyphenated word part, all ASCII
 hword_numpart   | Hyphenated word part, letters and digits
 hword_part      | Hyphenated word part, all letters
 int             | Signed integer
 numhword        | Hyphenated word, letters and digits
 numword         | Word, letters and digits
 protocol        | Protocol head
 sfloat          | Scientific notation
3445
 tag             | XML tag
3446 3447
 uint            | Unsigned integer
 url             | URL
3448
 url_path        | URL path
3449 3450
 version         | Version number
 word            | Word, all letters
3451 3452
(23 rows)
</programlisting>
3453
     </para>
3454 3455 3456
    </listitem>
   </varlistentry>

3457 3458
   <varlistentry>

3459
   <term><synopsis>\dFt<optional>+</optional> <optional>PATTERN</optional></synopsis></term>
3460 3461 3462 3463 3464 3465 3466 3467 3468 3469 3470 3471 3472 3473 3474 3475 3476 3477 3478 3479 3480
    <listitem>
     <para>
      List text search templates (add <literal>+</> for more detail).
     </para>

     <para>
<programlisting>
=&gt; \dFt
                           List of text search templates
   Schema   |   Name    |                        Description                        
------------+-----------+-----------------------------------------------------------
 pg_catalog | ispell    | ispell dictionary
 pg_catalog | simple    | simple dictionary: just lower case and check for stopword
 pg_catalog | snowball  | snowball stemmer
 pg_catalog | synonym   | synonym dictionary: replace word by its synonym
 pg_catalog | thesaurus | thesaurus dictionary: phrase by phrase substitution
</programlisting>
     </para>
    </listitem>
   </varlistentry>

3481 3482
  </variablelist>

3483
 </sect1>
3484

3485 3486 3487 3488 3489 3490 3491 3492
 <sect1 id="textsearch-limitations">
  <title>Limitations</title>

  <para>
   The current limitations of <productname>PostgreSQL</productname>'s
   text search features are:
   <itemizedlist  spacing="compact" mark="bullet">
    <listitem>
3493
     <para>The length of each lexeme must be less than 2K bytes</para>
3494 3495
    </listitem>
    <listitem>
3496 3497
     <para>The length of a <type>tsvector</type> (lexemes + positions) must be
     less than 1 megabyte</para>
3498 3499
    </listitem>
    <listitem>
3500 3501 3502
     <!-- TODO: number of lexemes in what?  This is unclear -->
     <para>The number of lexemes must be less than
     2<superscript>64</superscript></para>
3503 3504
    </listitem>
    <listitem>
3505 3506
     <para>Position values in <type>tsvector</> must be greater than 0 and
     no more than 16,383</para>
3507 3508
    </listitem>
    <listitem>
3509
     <para>No more than 256 positions per lexeme</para>
3510 3511
    </listitem>
    <listitem>
3512 3513
     <para>The number of nodes (lexemes + operators) in a <type>tsquery</type>
     must be less than 32,768</para>
3514 3515 3516 3517 3518 3519
    </listitem>
   </itemizedlist>
  </para>

  <para>
   For comparison, the <productname>PostgreSQL</productname> 8.1 documentation
3520 3521 3522
   contained 10,441 unique words, a total of 335,420 words, and the most
   frequent word <quote>postgresql</> was mentioned 6,127 times in 655
   documents.
3523 3524 3525 3526
  </para>

   <!-- TODO we need to put a date on these numbers? -->
  <para>
3527 3528 3529
   Another example &mdash; the <productname>PostgreSQL</productname> mailing
   list archives contained 910,989 unique words with 57,491,343 lexemes in
   461,020 messages.
3530 3531 3532 3533
  </para>

 </sect1>

3534 3535
 <sect1 id="textsearch-migration">
  <title>Migration from Pre-8.3 Text Search</title>
3536 3537

  <para>
3538 3539 3540
   Applications that used the <filename>contrib/tsearch2</> add-on module
   for text searching will need some adjustments to work with the
   built-in features:
3541 3542
  </para>

3543
  <itemizedlist>
3544 3545
   <listitem>
    <para>
3546 3547 3548 3549 3550 3551 3552
     Some functions have been renamed or had small adjustments in their
     argument lists, and all of them are now in the <literal>pg_catalog</>
     schema, whereas in a previous installation they would have been in
     <literal>public</> or another non-system schema.  There is a new
     version of <filename>contrib/tsearch2</> (see <xref linkend="tsearch2">)
     that provides a compatibility layer to solve most problems in this
     area.
3553 3554 3555 3556 3557
    </para>
   </listitem>

   <listitem>
    <para>
3558 3559 3560 3561 3562 3563
     The old <filename>contrib/tsearch2</> functions and other objects
     <emphasis>must</> be suppressed when loading <application>pg_dump</>
     output from a pre-8.3 database.  While many of them won't load anyway,
     a few will and then cause problems.  One simple way to deal with this
     is to load the new <filename>contrib/tsearch2</> module before restoring
     the dump; then it will block the old objects from being loaded.
3564 3565 3566 3567 3568
    </para>
   </listitem>

   <listitem>
    <para>
3569 3570 3571 3572 3573 3574
     Text search configuration setup is completely different now.
     Instead of manually inserting rows into configuration tables,
     search is configured through the specialized SQL commands shown
     earlier in this chapter.  There is not currently any automated
     support for converting an existing custom configuration for 8.3;
     you're on your own here.
3575 3576 3577 3578 3579
    </para>
   </listitem>

   <listitem>
    <para>
3580 3581 3582
     Most types of dictionaries rely on some outside-the-database
     configuration files.  These are largely compatible with pre-8.3
     usage, but note the following differences:
3583

3584 3585 3586 3587 3588 3589 3590 3591 3592 3593
     <itemizedlist  spacing="compact" mark="bullet">
      <listitem>
       <para>
        Configuration files now must be placed in a single specified
        directory (<filename>$SHAREDIR/tsearch_data</>), and must have
        a specific extension depending on the type of file, as noted
        previously in the descriptions of the various dictionary types.
        This restriction was added to forestall security problems.
       </para>
      </listitem>
3594

3595 3596 3597 3598 3599 3600
      <listitem>
       <para>
        Configuration files must be encoded in UTF-8 encoding,
        regardless of what database encoding is used.
       </para>
      </listitem>
3601

3602 3603 3604 3605 3606 3607 3608
      <listitem>
       <para>
        In thesaurus configuration files, stop words must be marked with
        <literal>?</>.
       </para>
      </listitem>
     </itemizedlist>
3609 3610 3611 3612 3613
    </para>
   </listitem>

  </itemizedlist>

3614 3615
 </sect1>

3616
</chapter>