AnalyticDB:BM25 high-performance full-text search

Overview

The pgsearch extension helps you create and manage Best Matching 25 (BM25) indexes that are built based on Tantivy, a high-performance full-text search engine. The BM25 algorithm is preferentially used in search engines, such as Elasticsearch, to score and rank rows based on the number of occurrences of a term in a row and the rarity of a term across rows. The BM25 algorithm is suitable for scenarios in which you want to search for keywords or phrases from a large number of rows. You can use BM25 indexes to perform full-text search in AnalyticDB for PostgreSQL tables and use the BM25 algorithm to match accurate search results based on relevance scores.

Installation and uninstallation

The pgsearch extension is installed by default in AnalyticDB for PostgreSQL instances with kernel version 7.2.1.2 or later. For improved stability and security, we recommend that you upgrade to version 7.2.1.7+ or 7.3.2.2+. To uninstall the extension, submit a ticket.

Test table

This topic provides a sample table pre-populated with data. Run the pgsearch.create_test_table() function to create it.

CALL pgsearch.create_test_table(table_name => 'mock_items', schema_name => 'public');

CREATE TABLE mock_items (
 description TEXT,
 rating INTEGER CHECK (
 rating BETWEEN 1
 AND 5
 ),
 category VARCHAR(255),
 in_stock BOOLEAN,
 metadata JSONB,
 created_at TIMESTAMP,
 last_updated_date DATE,
 latest_available_time TIME
);

Index management

Create an index

Call the pgsearch.create_bm25() function to create a BM25 index. This function allows you to create indexes on multiple fields and specify index configurations for each field. Creating multiple indexes consumes excessive resources and can affect performance. Therefore, we recommend creating only one BM25 index per table.

Syntax

CALL pgsearch.create_bm25(
  index_name => '<index_name>',
  table_name => '<table_name>',
  schema_name => '<schem_name>'
  text_fields => '<text_fields>|pgsearch.field()',
  numeric_fields => '<numeric_fields>|pgsearch.field()',
  boolean_fields => '<boolean_fields>|pgsearch.field()',
  json_fields => '<json_fields>|pgsearch.field()',
  datetime_fields => '<datetime_fields>|pgsearch.field()'
)

Parameters

Records

The pgsearch extension supports three types of records.

• raw: does not perform word segmentation.

• freq: records the row ID and the term frequency.

• position: records the row ID, term frequency, and occurrence position.

Tokenizers

The pgsearch extension provides a variety of built-in tokenizers, such as jieba, ngram, lindera, en_stem, and whitespace. The tokenizers can meet your business requirements without the need to install the pg_jieba or zhparser extension.

You can use the pgsearch.tokenizer() function to generate a configuration string for a tokenizer, which is more convenient than constructing the string manually. Its syntax and parameters are as follows.

Syntax

pgsearch.tokenizer(<name>, <min_gram>, <max_gram>, <prefix_only>, <search>, <hmm>, <dict>,<stopword>,<lowercase>,<remove_long>, <stemmer>);

Parameters

• name: The name of the tokenizer (text type). For a list of valid values, see Tokenizers.

• For details on the min_gram, max_gram, prefix_only, search, and hmm parameters, see Tokenizers.

• dict: The word segmentation dictionary (text type) used by the jieba tokenizer. For more information, see Configure a custom word segmentation dictionary.

• stopword: The stop word dictionary. The data type is String. This parameter is not configured by default. For more information, see Configure a stop word dictionary.

• lowercase: A boolean that specifies whether to convert keywords to lowercase. The default is true.

• remove_long: An integer. If configured, keywords with a length greater than or equal to remove_long bytes are removed. Each Chinese character occupies 3 bytes. This parameter is optional.

• stemmer: The data type is text. This parameter is not configured by default. If configured, this parameter performs stemming. For example, run, running, runs, and ran are reduced to run. This allows users to retrieve relevant results by searching for any form of run. Stemming is supported only for English, and the corresponding value is en.

Examples of creating indexes

• Use the text_fields parameter to create an index.

  -- Use the ngram tokenizer. You must specify all configuration fields of the ngram tokenizer.
  CALL pgsearch.create_bm25(
      index_name => 'search_idx',
      table_name => 'mock_items',
      text_fields => '{description: { tokenizer: {type: "ngram", min_gram: 2, max_gram: 3, prefix_only: false}}}'
  );
  
  -- Use the jieba tokenizer.
  CALL pgsearch.create_bm25(
      index_name => 'search_idx',
      table_name => 'mock_items',
      text_fields => '{description: { tokenizer: {type: "jieba"}}}'
  );
  
  -- Use the lindera tokenizer.
  CALL pgsearch.create_bm25(
      index_name => 'search_idx',
      table_name => 'mock_items',
      text_fields => '{description: { tokenizer: {type: "chinese_lindera"}}}'
  );
  -- Create an index on multiple fields.
  CALL pgsearch.create_bm25(
      index_name => 'search_idx',
      table_name => 'mock_items',
      text_fields => '{description: {fast: false, filednorms: true, tokenizer: {type: "jieba"}}, category: {}}',
      datetime_fields => '{created_at: {fast: true}, last_updated_date: {fast: true}}',
      numeric_fields => '{rating: {fast: true}}',
      json_fields => '{metadata: {fast: true, expand_dost: true, tokenizer: {type: "en_stem"}, record: "position"}}',
      boolean_fields => '{in_stock: {fast: true} }'
  );
  
  -- In most cases, you can configure only the tokenizer field for the text_fields and json_fields parameters. Use the default values for other parameters.  
  CALL pgsearch.create_bm25(
      index_name => 'search_idx',
      table_name => 'mock_items',
      text_fields => '{description: {tokenizer: {type: "jieba"}}, category: {}}',
      datetime_fields => '{created_at: {}, last_updated_date: {}}',
      numeric_fields => '{rating: {}}',
      json_fields => '{metadata: {tokenizer: {type: "en_stem"}}}',
      boolean_fields => '{in_stock: {} }'
  );

• Use the pgsearch.field() and pgsearch.tokenizer() functions to create an index and view word segmentation effects.

  -- Set the fast parameter in the description column to true, set the record parameter to position, and set the tokenizer parameter to jieba. In the jieba tokenizer, the non-search mode is configured, user_dict is used as the custom word segmentation dictionary, and CN_SIMPLE is used as the built-in stop word.
  -- Use the default configuration for the category column.
  -- The configurations of the two columns can be concatenated by using a double vertical bars (||).
  CALL pgsearch.create_bm25(
      index_name => 'search_idx',
      table_name => 'mock_items',
      text_fields => pgsearch.field('description', fast=>true, record=>'position', tokenizer=>pgsearch.tokenizer('jieba', search=>false, dict=>'user_dict', stopword=>'CN_SIMPLE')) 
                      || pgsearch.field('category')
  );
  
  CALL pgsearch.create_bm25(
      index_name => 'search_idx',
      table_name => 'mock_items',
      text_fields => pgsearch.field('description', tokenizer=>pgsearch.tokenizer('jieba'),
      datetime_fields => '{created_at: {}, last_updated_date: {}}',
      numeric_fields => '{rating: {}}',
      json_fields => '{metadata: {tokenizer: {type: "en_stem"}}}',
      boolean_fields => '{in_stock: {} }'
  );
  
  -- Query the word segmentation effect by using the specified jieba tokenizer.
  SELECT pgsearch.tokenizer(pgsearch.tokenizer('jieba', hmm=>false, search=>false, dict=>'custom_dict_1'), '永和服装饰品有限公司');
           tokenizer         
  
  -- Use various filters to process word segmentation results.
  SELECT pgsearch.tokenizer(
      pgsearch.tokenizer('jieba',
      search=>false, dict=>'user_dict', stopword=>'CN_SIMPLE',
      lowercase=>false, remove_long=>27, stemmer=>'en')::text,
      '永和服装饰品有限公司。 Shoping'
  );

Query the configuration of an index

SELECT * FROM pgsearch.schema_bm25('index_name'::regclass);

Delete an index

DROP INDEX index_name;

Rebuild an index

REINDEX INDEX index_name;

Search syntax

@@@ operator

You can use the @@@ operator in k-nearest neighbor (KNN) search to sort the search results based on BM25 scores and return the top results.

Syntax

SELECT * FROM <table_name> 
ORDER BY <index_col> @@@ pgsearch.config('<query>');

Parameters

• <table_name>: The name of the table.

• <index_col>: Any indexed field specified during index creation. We recommend using the first indexed field from your search query.

• <query>: The search keywords.

• pgsearch.config: Accepts a search string or a more complex search object. Search objects can be combined, allowing for granular queries. When you pass a search keyword string to pgsearch.config, it implicitly uses pgsearch.parse to convert the string into a search object. For example, the following two queries are equivalent:

SELECT * FROM mock_items 
ORDER BY description @@@ pgsearch.config('description:socks');

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
  query => pgsearch.parse('description:socks')
);

Basic queries

Specific field search

Text field search

Query the rows that include the specified word or phrase. If the phrase contains spaces, you must enclose the phrase in double quotation marks (").

Query the rows that include the "keyboard" word or the "hello world" phrase.

SELECT * FROM mock_items 
ORDER BY description @@@ pgsearch.config('description:keyboard');

-- Query the rows that include the space-separated phrase "hello world".
SELECT * FROM mock_items 
ORDER BY description @@@ pgsearch.config('description:"hello world"');

JSON field search

The following query searches for rows containing {"metadata": {"color": "white"}}.

SELECT * FROM mock_items 
ORDER BY metadata @@@ pgsearch.config('metadata.color:white');

DATETIME field search

When you query a DATETIME field, the UTC time zone and the RFC 3339 format are used.

SELECT * FROM mock_items ORDER BY created_at @@@ pgsearch.config('created_at:"2023-05-01T09:12:34Z"') LIMIT 10;

SELECT * FROM mock_items ORDER BY created_at @@@ pgsearch.config('created_at:"2023-05-01T04:12:34-05:00"') LIMIT 10;

Proximity operator search

The ~ (slop) operator matches words that are within a specified distance of each other. For example, if the description field contains "ergonomic metal keyboard", the following query will find it because the words "ergonomic" and "keyboard" are separated by one word.

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config('description:"ergonomic keyboard"~1');

Efficient filter search

Filters apply only to indexed numeric and boolean fields and can be faster than a standard SQL WHERE clause.

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config('description:keyboard AND rating:<4');

Rank improvement search

To boost the ranking of results for a keyword, use the ^ character followed by a boost factor. This factor is applied to the results matching the keyword, increasing their relevance score and thus raising their rank in the result set.

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config('description:keyboard^2 OR category:electronics^3');

Boolean operator search

The AND, OR, and NOT operators can be used to combine and filter multiple keywords. Parentheses can be used for grouping and controlling the order of operations.

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config('description:keyboard OR category:toy');

Set operator search

A set operator search uses one or more OR conditions. It is more CPU-efficient.

In a set operator search, each phrase is separated by a space, and each phrase must tokenize to a single term. Because the number of tokens generated from Chinese word segmentation depends on the tokenizer and is difficult to control, set operators are suitable for English but not for Chinese. For Chinese text, we recommend using multiple OR operators instead of this syntax.

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config('description:IN [keyboard toy]');

Limit- and offset-based search

The OFFSET and LIMIT clauses are supported.

SELECT * FROM mock_items
ORDER BY description @@@ pgsearch.config('description:socks') OFFSET 2 LIMIT 10;

Advanced query

Obtain the word segmentation result

Use the pgsearch.tokenizer() function to view word segmentation results. It returns an array of the resulting terms.

Syntax

SELECT pgsearch.tokenizer(<tokenizer_config>, <query_str>);

Parameters

• tokenizer_config: A JSON string containing the tokenizer configuration. You can also generate this string by using the pgsearch.tokenizer() function. For more information, see Tokenizers.

• query_str: The string to be tokenized.

Example

SELECT pgsearch.tokenizer('{type: "ngram", min_gram: 1, max_gram: 2, prefix_only: true}', 'hell');
SELECT pgsearch.tokenizer('{type: "jieba"}', '数据仓库');
SELECT pgsearch.tokenizer(pgsearch.tokenizer('jieba'), '永和服装饰品有限公司');

Term set search

You can search for rows that include any keyword in an array of terms.

Example

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
    query =>  pgsearch.term_set(
	    terms => ARRAY[
	        pgsearch.term(field => 'description', VALUE => 'socks'),
	        pgsearch.term(field => 'description', VALUE => 'novel')
            
	    ]
	)
);

Parameters

• field: The field to search. If omitted, all indexed fields are searched.

• terms: An array of search objects.

Phrase search

You can query the rows that include an array of ordered keywords. Before you perform phrase search, make sure that the record configuration item of the index is set to position.

Example

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
    query => pgsearch.phrase(
    field => 'description',
    phrases => ARRAY['little', 'red', 'riding' 'hood'],
        slop => 0
    )
);

Parameters

• field: The field to search. If omitted, all indexed fields are searched.

• phrases: An array of keywords. The text must contain all keywords from the array, and they must appear in the same order as in the array.

• slop: An optional parameter that specifies the maximum distance allowed between keywords. A value of 0 means the keywords must be adjacent and in order. A value greater than 0 allows other words to appear between the keywords.

Phrase prefix search

You can search for rows that include the content prefixed with an array of ordered keywords. Before you perform phrase prefix search, make sure that an index is created on the search field.

Example

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
    query => pgsearch.phrase_prefix(
    field => 'description',
    phrases => ARRAY['little', 'red', 'riding' 'hood'],
        max_expansion => 1
    )
);

Parameters

• field: The field to search. If omitted, all indexed fields are searched.

• phrases: An array of keywords. The sequence of keywords in the array is treated as a prefix sequence. It can match strings where the terms begin with the specified keywords in the same order. For example, ARRAY['little', 're'] can match "little red riding hood".

• max_expansion: An optional parameter that limits the number of term variations the prefix can expand to during the search. This restricts the scope of the search.

The word segments search (tokenizer_terms)

You can use the specified tokenizer to segment the query text, and then query the generated tokens. The pgsearch.tokenizer_terms() function supports the use of Boolean logic between the specified tokens.

Example

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
pgsearch.tokenizer_terms('description', '朝阳百货', pgsearch.tokenizer('jieba'), 'OR'));

Parameters

• tokenizer: The tokenizer configuration string specified by pgsearch.tokenizer(). The default is the jieba tokenizer.

• operator: Specifies the boolean logic between tokens after word segmentation. The valid values are OR or AND. The default value is OR. For example, the phrase "Chaoyang Department Store" is segmented into 'Chaoyang' and 'Department Store'. When operator is set to OR, the query returns rows that contain 'Chaoyang' or 'Department Store'. When operator is set to AND, the query returns only rows that contain both 'Chaoyang' and 'Department Store'.

All search

You can search for all rows that include the index field and have the same score of 1.0. All search results are equal in preference.

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
    query =>  pgsearch.all()
);

Boolean search

You can query the rows that are filtered by using the logical relationships defined in a subquery.

Example

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
    query => pgsearch.boolean(
	    should => ARRAY[
		    pgsearch.parse('description:socks'),
		    pgsearch.phrase_prefix(field => 'description', phrases => ARRAY['book']),
		    pgsearch.term(field => 'description', VALUE => 'writer'),
		    pgsearch.fuzzy_term(field => 'description', VALUE => 'wow')
	    ],
        must_not => ARRAY[
            pgsearch.term(field => 'description', VALUE => 'writer')
        ],
        must => ARRAY[
            pgsearch.term(field => 'rating', VALUE => 4)
        ]
    )
);
-- You can configure one or more of the should, must_not, and must parameters.
SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
    query => pgsearch.boolean(
	    should => pgsearch.parse('description:socks')
    )
);

Parameters

• must: An ARRAY of search objects that must match. Only rows that satisfy all must conditions will be included in the search results.

• must_not: An ARRAY of search objects that must not match. Any row that satisfies a must_not condition will be excluded from the search results.

• should: An array of ARRAY query objects, at least one of which must match when the must parameter is not present.

• If must is not specified, a row must match at least one should condition to be included in the results.

Rank boost search

A boost query acts on a subquery (for example, query => pgsearch.parse('description:socks')) to amplify its score, thereby boosting the ranking of its results.

Example

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
    query => pgsearch.boost(query => pgsearch.parse('description:socks'), boost => 2)
);

Parameters

• boost: A factor to multiply with the score of each result.

• query: The search object.

Constant score search

This applies a constant score to all rows that match the subquery (for example, query => pgsearch.all()). It can be used to avoid unnecessary score calculations on the subquery.

Example

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
    query => pgsearch.const_score(query => pgsearch.all(), score => 2)
);

Parameters

• score: The constant score to apply to each result of the subquery.

• query: The search object.

Maximum disjunction search

You can query the rows that match one or more subqueries. The rows that match more conditions can obtain higher BM25 scores.

Example

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
    query => pgsearch.disjunction_max(
      disjuncts => ARRAY[
          pgsearch.parse('description:socks'),
          pgsearch.parse('description:Generic')
      ],
      tie_breaker => 0.75
    )
);

Parameters

• disjuncts: An array of one or more subquery objects.

• tie_breaker: An optional factor between 0.0 and 1.0 that adjusts the score for rows matching multiple subqueries. The final score for a row is the highest score from any single matching subquery, plus the score of each additional matching subquery multiplied by the tie_breaker. In the example above, if a row scores 1.0 for pgsearch.parse('description:socks') and 0.5 for pgsearch.parse('description:Generic'), its final score is 1.0 + (0.75 * 0.5) = 1.375.

Empty search

You can use empty search as a placeholder in test scenarios or edge cases. In this case, no rows are returned.

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
    query => pgsearch.empty()
);

Fuzzy term search

You can query rows that include fuzzy terms of the search keyword. Search results can be returned even if the spelling is not completely correct.

Example

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
    query => pgsearch.fuzzy_term(
      field => 'description', 
      VALUE => 'wow',
      distance => 2,
      tranposition_cost_one => true,
      prefix => true)
);

Parameters

• field: The field to search. If omitted, all indexed fields are searched.

• value: The keyword to search for. The search uses a fuzzy match based on Levenshtein distance to find similar results.

• distance: An optional parameter that specifies the maximum allowed edit distance (number of single-character edits). The maximum value is 2. The default value is 2.

• transposition_cost_one: An optional parameter that, if true, considers transposing two adjacent characters as a single edit in the Levenshtein distance calculation. If false, it is considered two separate edits (a deletion and an insertion). The default value is true.

• prefix: An optional parameter that, if true, excludes the prefix of the search keyword from the fuzzy edit distance calculation. If false, the entire string is used. The default value is true.

Range search

You can query the rows that include values within a specific range. The range search is suitable for the numeric_fields and datetime_fields parameters.

Example

SELECT * FROM mock_items ORDER BY rating @@@ pgsearch.config(
    query => pgsearch.range(
        field => 'rating',
        RANGE => '[1,4)'::int4range
  )
);

Parameters

• field: The field to search. If omitted, all indexed fields are searched.

• range: The value range to match against the field. Supported range types include INT4RANGE, INT8RANGE, DATERANGE, TSRANGE, and TSTZRANGE.

Regular expression search

You can query the rows that include the words that match a specific regular expression.

Example

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
    query => pgsearch.regex(
		field => 'description',
		PATTERN => '(glass|screen|like|cloth|phone)'
	)
);
SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
    query => pgsearch.regex(
		field => 'description',
		PATTERN => '(.*screen.*)'
	)
);

Parameters

• field: The field to search. If omitted, all indexed fields are searched.

• pattern: The regular expression pattern string.

Others

Obtain BM25 scores

You can obtain BM25 scores by placing the @@@ operator in the SELECT list and using the AS keyword to name the resulting score column.

Because the default ORDER BY in an AnalyticDB for PostgreSQL database sorts in ascending order, while a higher BM25 score is better, the database returns the negative of the BM25 score. For example, if a BM25 score is 2.86, the returned result is -2.86. Additionally, BM25 scores are calculated only for searches on text_fields and json_fields, and cannot be calculated for searches on numeric_fields, datetime_fields, or boolean_fields.

Syntax

SELECT *, mock_items @@@ pgsearch.config(<query>) as BM25
FROM mock_items
ORDER BY BM25;

Example

-- 1. Perform full-text search only on a string field. A BM25 score is returned.
SELECT description, rating, description @@@ pgsearch.config('description:socks') AS bm25 FROM mock_items ORDER BY bm25 limit 1;

-- 2. Perform full-text search on a string field and filter data on a numeric field. A BM25 score is returned.
SELECT description, rating, description @@@ pgsearch.config('description:socks AND rating:4') AS bm25 FROM mock_items ORDER BY bm25 limit 4;
 
-- 3. Perform full-text search on a JSON field. A BM25 score is returned.
SELECT metadata, metadata @@@ pgsearch.config('metadata.color:White') AS bm25 FROM mock_items ORDER BY bm25 LIMIT 1;

-- 4. Perform range search on a rank field. BM25 scores cannot be calculated. The default value -1 is returned.
 SELECT description, rating, rating @@@ pgsearch.config('rating:[4 TO 5]') AS bm25 FROM mock_items ORDER BY bm25 LIMIT 1;

Query result

-- 1. Perform full-text search only on a string field. A BM25 score is returned.
   description  | rating |    bm25    
---------------+--------+------------
 Generic socks |      4 | -2.1048825
(1 row)

-- 2. Perform full-text search on a string field and filter data on a numeric field. A BM25 score is returned.
 description  | rating |    bm25    
---------------+--------+------------
 Generic socks |      4 | -3.1081846
(1 row)

-- 3. Perform full-text search on a JSON field. A BM25 score is returned.
                metadata                 |   bm25    
-----------------------------------------+-----------
 {"color": "White", "location": "China"} | -3.453373
(1 row)

-- 4. Perform range search on a rank field. BM25 scores cannot be calculated. The default value -1 is returned.
  description    | rating | bm25 
------------------+--------+------
 Plastic Keyboard |      4 |   -1
(1 row)