PolarDB:Control text search

Assigning weights to document fields

Use setweight to label lexemes with a weight (A, B, C, or D), typically to reflect the importance of different document sections (title, keywords, abstract, body). Ranking functions use these weights when scoring results.

Because to_tsvector(NULL) returns NULL, wrap nullable columns in coalesce:

UPDATE tt SET ti =
    setweight(to_tsvector(coalesce(title,'')), 'A')    ||
    setweight(to_tsvector(coalesce(keyword,'')), 'B')  ||
    setweight(to_tsvector(coalesce(abstract,'')), 'C') ||
    setweight(to_tsvector(coalesce(body,'')), 'D');

The || operator concatenates the labeled tsvector values into a single indexed representation.

Default weights used by ranking functions:

to_tsquery

to_tsquery([ config regconfig, ] querytext text) returns tsquery

to_tsquery normalizes each token into a lexeme and discards stop words, just like to_tsvector. The input must already use tsquery operators: & (AND), | (OR), ! (NOT), and <-> (FOLLOWED BY). Parentheses are supported for grouping. Tokens not separated by a valid operator cause a syntax error.

SELECT to_tsquery('english', 'The & Fat & Rats');

  to_tsquery
---------------
 'fat' & 'rat'

Attach weight labels to restrict matching to specific labeled lexemes:

SELECT to_tsquery('english', 'Fat | Rats:AB');

    to_tsquery
------------------
 'fat' | 'rat':AB

Append * to a lexeme for prefix matching — it matches any word in a tsvector that begins with that string:

SELECT to_tsquery('supern:*A & star:A*B');

        to_tsquery
--------------------------
 'supern':*A & 'star':*AB

When the text search configuration includes a thesaurus dictionary, to_tsquery accepts single-quoted phrases. In this example the thesaurus maps supernovae stars to sn:

SELECT to_tsquery('''supernovae stars'' & !crab');

  to_tsquery
---------------
 'sn' & !'crab'

plainto_tsquery

plainto_tsquery([ config regconfig, ] querytext text) returns tsquery

plainto_tsquery accepts unformatted text and inserts & between all surviving words after normalization. It does not recognize tsquery operators, weight labels, or prefix-match labels in its input — all punctuation is treated as plain text and discarded.

SELECT plainto_tsquery('english', 'The Fat Rats');

 plainto_tsquery
-----------------
 'fat' & 'rat'

SELECT plainto_tsquery('english', 'The Fat & Rats:C');

   plainto_tsquery
---------------------
 'fat' & 'rat' & 'c'

phraseto_tsquery

phraseto_tsquery([ config regconfig, ] querytext text) returns tsquery

phraseto_tsquery behaves like plainto_tsquery but inserts <-> (FOLLOWED BY) between surviving words, enforcing word order. Stop words are not simply discarded — they are accounted for by inserting <> operators rather than <-> operators to preserve positional accuracy.

SELECT phraseto_tsquery('english', 'The Fat Rats');

 phraseto_tsquery
------------------
 'fat' <-> 'rat'

SELECT phraseto_tsquery('english', 'The Fat & Rats:C');

      phraseto_tsquery
-----------------------------
 'fat' <-> 'rat' <-> 'c'

websearch_to_tsquery

websearch_to_tsquery([ config regconfig, ] querytext text) returns tsquery

websearch_to_tsquery accepts web search syntax and never raises a syntax error, making it safe to pass raw user input directly. This is the recommended function for accepting input from a search box. Supported syntax:

• Unquoted text — converted to &-separated terms (like plainto_tsquery)

• "Quoted text" — converted to <->-separated terms (like phraseto_tsquery)

• OR — converted to |

• - (dash) — converted to ! (NOT)

Other punctuation is ignored. Operators, weight labels, and prefix-match labels in the input are not recognized.

SELECT websearch_to_tsquery('english', 'The fat rats');

 websearch_to_tsquery
----------------------
 'fat' & 'rat'

SELECT websearch_to_tsquery('english', '"supernovae stars" -crab');

       websearch_to_tsquery
----------------------------------
 'supernova' <-> 'star' & !'crab'

SELECT websearch_to_tsquery('english', '"sad cat" or "fat rat"');

       websearch_to_tsquery
-----------------------------------
 'sad' <-> 'cat' | 'fat' <-> 'rat'

SELECT websearch_to_tsquery('english', 'signal -"segmentation fault"');

         websearch_to_tsquery
---------------------------------------
 'signal' & !( 'segment' <-> 'fault' )

SELECT websearch_to_tsquery('english', '""" )( dummy \\ query <->');

 websearch_to_tsquery
----------------------
 'dummi' & 'queri'

The weights argument

Pass a float4[] array to adjust the influence of each weight label:

{D-weight, C-weight, B-weight, A-weight}

Default: {0.1, 0.2, 0.4, 1.0}

Lexemes labeled A (typically the document title) contribute up to 10x more than D-labeled lexemes (body text).

The normalization option

By default, longer documents score higher simply because they contain more words. The normalization integer option adjusts for document length. It is a bit mask — combine values with |:

When multiple flags are set, the transformations apply in the order listed.

The 32 normalization (rank/(rank+1)) scales ranks into the 0-1 range, but it does not affect the ordering of results — it is a cosmetic transformation only.

Example: top 10 matches

SELECT title, ts_rank_cd(textsearch, query) AS rank
FROM apod, to_tsquery('neutrino|(dark & matter)') query
WHERE query @@ textsearch
ORDER BY rank DESC
LIMIT 10;

                         title                     |   rank
-----------------------------------------------+----------
 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

With normalization=32:

SELECT title, ts_rank_cd(textsearch, query, 32 /* rank/(rank+1) */ ) AS rank
FROM apod, to_tsquery('neutrino|(dark & matter)') query
WHERE  query @@ textsearch
ORDER BY rank DESC
LIMIT 10;

                         title                     |        rank
-----------------------------------------------+-------------------
 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

Ranking can be expensive since it requires consulting the tsvector 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.

Options

Pass a comma-separated string of option=value pairs:

Option names are case-insensitive. Double-quote string values that contain spaces or commas.

Non-fragment mode (default, MaxFragments=0): selects the single best excerpt that contains the most query terms within the headline length limit.

Fragment mode (MaxFragments > 0): locates all query matches, splits each into fragments of at most MaxWords words, and prefers fragments with more query terms. More useful when matches span large sections of the document.

In either mode, if no query terms are found, the first MinWords words of the document are returned.

Examples

SELECT ts_headline('english',
  '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('english', 'query & similarity'));

                        ts_headline
------------------------------------------------------------
 containing given <b>query</b> terms                       +
 and return them in order of their <b>similarity</b> to the+
 <b>query</b>.

SELECT ts_headline('english',
  'Search terms may occur
many times in a document,
requiring ranking of the search matches to decide which
occurrences to display in the result.',
  to_tsquery('english', 'search & term'),
  'MaxFragments=10, MaxWords=7, MinWords=3, StartSel=<<, StopSel=>>');

                        ts_headline
------------------------------------------------------------
 <<Search>> <<terms>> may occur                            +
 many times ... ranking of the <<search>> matches to decide