12.3. 細部控制
To implement full text searching there must be a function to create atsvectorfrom a document and atsqueryfrom a user query. Also, we need to return results in a useful order, so we need a function that compares documents with respect to their relevance to the query. It's also important to be able to display the results nicely.PostgreSQLprovides support for all of these functions.
12.3.1. Parsing Documents
PostgreSQLprovides the functionto_tsvectorfor converting a document to thetsvectordata type.
to_tsvectorparses a textual document into tokens, reduces the tokens to lexemes, and returns atsvectorwhich 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:
In the example above we see that the resultingtsvectordoes not contain the wordsa,on, orit, the wordratsbecamerat, and the punctuation sign-was ignored.
The functionsetweightcan be used to label the entries of atsvectorwith a givenweight, where a weight is one of the lettersA,B,C, orD. This is typically used to mark entries coming from different parts of a document, such as title versus body. Later, this information can be used for ranking of search results.
Becauseto_tsvector(NULL) will returnNULL, it is recommended to usecoalescewhenever a field might be null. Here is the recommended method for creating atsvectorfrom a structured document:
12.3.2. Parsing Queries
PostgreSQLprovides the functionsto_tsquery,plainto_tsquery, andphraseto_tsqueryfor converting a query to thetsquerydata type.to_tsqueryoffers access to more features than eitherplainto_tsqueryorphraseto_tsquery, but it is less forgiving about its input.
As in basictsqueryinput, weight(s) can be attached to each lexeme to restrict it to match onlytsvectorlexemes of those weight(s). For example:
Also,*can be attached to a lexeme to specify prefix matching:
Such a lexeme will match any word in atsvectorthat begins with the given string.
to_tsquerycan 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 rulesupernovae stars : sn:
Without quotes,to_tsquerywill generate a syntax error for tokens that are not separated by an AND, OR, or FOLLOWED BY operator.
plainto_tsquerytransforms the unformatted text_querytext_to atsqueryvalue. The text is parsed and normalized much as forto_tsvector, then the&(AND)tsqueryoperator is inserted between surviving words.
Example:
Note thatplainto_tsquerywill not recognizetsqueryoperators, weight labels, or prefix-match labels in its input:
Here, all the input punctuation was discarded as being space symbols.
phraseto_tsquerybehaves much likeplainto_tsquery, except that it inserts the<->(FOLLOWED BY) operator between surviving words instead of the&(AND) operator. Also, stop words are not simply discarded, but are accounted for by inserting<N>operators rather than<->operators. This function is useful when searching for exact lexeme sequences, since the FOLLOWED BY operators check lexeme order not just the presence of all the lexemes.
Example:
Likeplainto_tsquery, thephraseto_tsqueryfunction will not recognizetsqueryoperators, weight labels, or prefix-match labels in its input:
12.3.3. Ranking Search Results
Ranking attempts to measure how relevant documents are to a particular query, so that when there are many matches the most relevant ones can be shown first.PostgreSQLprovides 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.
The two ranking functions currently available are:
ts_rank([
weights
float4[]
,
]
vector
tsvector
,
query
tsquery
[
,
normalization
integer
]) returns
float4
Ranks vectors based on the frequency of their matching lexemes.
ts_rank_cd([
weights
float4[]
,
]
vector
tsvector
,
query
tsquery
[
,
normalization
integer
]) returns
float4
This function computes the_cover density_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. Cover density is similar tots_rankranking except that the proximity of matching lexemes to each other is taken into consideration.
For both these functions, the optional_weights_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:
If no_weights_are provided, then these defaults are used:
Typically weights are used to mark words from special areas of the document, like the title or an initial abstract, so they can be treated with more or less importance than words in the document body.
Since a longer document has a greater chance of containing a query term it is reasonable to take into account document size, e.g., a hundred-word 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_normalization_option that 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|(for example,2|4).
0 (the default) ignores the document length
1 divides the rank by 1 + the logarithm of the document length
2 divides the rank by the document length
4 divides the rank by the mean harmonic distance between extents (this is implemented only by
ts_rank_cd)8 divides the rank by the number of unique words in document
16 divides the rank by 1 + the logarithm of the number of unique words in document
32 divides the rank by itself + 1
If more than one flag bit is specified, the transformations are applied in the order listed.
It is important to note that the ranking functions do not use any global information, so it is impossible to produce a fair normalization to 1% or 100% as sometimes desired. Normalization option 32 (rank/(rank+1)) 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.
Here is an example that selects only the ten highest-ranked matches:
This is the same example using normalized ranking:
Ranking can be expensive since it requires consulting thetsvectorof 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.
12.3.4. Highlighting Results
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 the document with marked search terms.PostgreSQLprovides a functionts_headlinethat implements this functionality.
ts_headlineaccepts a document along with a query, and returns an excerpt from the document in which terms from the query are highlighted. The configuration to be used to parse the document can be specified byconfig; if_config_is omitted, thedefault_text_search_configconfiguration is used.
If anoptions_string is specified it must consist of a comma-separated list of one or moreoption=value_pairs. The available options are:
StartSel,StopSel: the strings with which to delimit query words appearing in the document, to distinguish them from other excerpted words. You must double-quote these strings if they contain spaces or commas.MaxWords,MinWords: these numbers determine the longest and shortest headlines to output.ShortWord: words of this length or less will be dropped at the start and end of a headline. The default value of three eliminates common English articles.HighlightAll: Boolean flag; iftruethe whole document will be used as the headline, ignoring the preceding three parameters.MaxFragments: maximum number of text excerpts or fragments to display. The default value of zero selects a non-fragment-oriented headline generation method. A value greater than zero selects fragment-based headline generation. This method finds text fragments with as many query words as possible and stretches those fragments around the query words. As a result query words are close to the middle of each fragment and have words on each side. Each fragment will be of at mostMaxWordsand words of lengthShortWordor less are dropped at the start and end of each fragment. If not all query words are found in the document, then a single fragment of the firstMinWordsin the document will be displayed.FragmentDelimiter: When more than one fragment is displayed, the fragments will be separated by this string.
Any unspecified options receive these defaults:
For example:
ts_headlineuses the original document, not atsvectorsummary, so it can be slow and should be used with care.
Last updated
Was this helpful?