BM25高性能全文检索

功能简介

pgsearch插件实现了全新的BM25（Best Matching 25）索引，该索引基于高性能全文检索引擎Tantivy构建。BM25是许多现代搜索引擎（如 Elasticsearch）的优先选择。它通过考虑一个词项在记录行中出现的频率和该词项在记录行中的独特性来对行进行排名。当您在记录行中检索关键词或短语时，BM25十分有用。BM25索引能够在云原生数据仓库 AnalyticDB PostgreSQL 版的表中进行全文检索并使用BM25算法进行相关性得分，从而匹配更准确的检索结果。

安装与卸载

pgsearch暂不支持白屏化安装，如有需要请提交工单。联系工作人员协助安装（需要重启实例）。如有卸载插件需求，也请提交工单联系工作人员协助卸载。

测试表

为方便测试，本文提供了一个样例表，样例表中预先插入了几十条数据。执行pgsearch.create_test_table()会自动创建该样例表。

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
);

索引管理

创建索引

您可以使用pgsearch.create_bm25()函数创建BM25索引。该函数支持在多个字段上创建索引，并为每个字段指定相关的索引配置。创建多个索引会消耗过多的资源，从而影响使用体验。因此建议一张表只创建一个BM25索引。

语法

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()'
)

参数说明

Record

pgsearch目前支持三种Record。

• raw：不做分词处理。

• freq：记录行ID以及词项频率。

• position：记录行ID、词项频率和出现位置。

分词器

pgsearch插件内置了多种分词器，包括jieba、ngram、lindera、en_stem、whitespace等常用的分词器，能够满足您大部分的需求，无需单独安装pg_jieba或者zhparser分词插件。

pgsearch.tokenizer()函数用于生成分词器的配置串，避免手动构造配置串所带来的不便。其语法和参数说明如下。

语法

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

参数说明

• name：文本类型，分词器的名称，可取值请参见分词器。

• 关于min_gram、max_gram、prefix_only、search、hmm参数详情，请参见分词器。

• dict：文本类型，用于指定jieba分词器使用的分词词典。详情请参见配置自定义分词词典。

• stopword：文本类型，停用词词典，默认不配置。详情请参见配置停用词词典。

• lowercase：布尔类型，默认为true，将关键词转换为小写。

• remove_long：整型，默认不配置。配置后将移除长度大于等于remove_long字节数的关键词。每个中文字符占用3个字节。

• stemmer：文本类型，默认不配置。配置后将提取词干，例如将run、running、runs、ran缩减为 run。无论用户搜索哪个run，都可以检索到相关结果。词干提取仅支持英文，对应的取值为en。

创建索引示例

• 使用text_fields创建索引。

  -- 使用ngram分词器，注意，ngram的所有配置字段都必须显示给出
  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}}}'
  );
  
  -- 使用jieba分词器
  CALL pgsearch.create_bm25(
      index_name => 'search_idx',
      table_name => 'mock_items',
      text_fields => '{description: { tokenizer: {type: "jieba"}}}'
  );
  
  -- 使用lindera分词器
  CALL pgsearch.create_bm25(
      index_name => 'search_idx',
      table_name => 'mock_items',
      text_fields => '{description: { tokenizer: {type: "chinese_lindera"}}}'
  );
  --在多字段上创建索引
  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} }'
  );
  
  --大部分场景下，只需要对text_fileds以及json_fields中的字段按需配置tokenizer，剩余参数通常采用默认值即可。即：
  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: {} }'
  );

• 使用pgsearch.field()和pgsearch.tokenizer()函数创建索引、查看分词效果等。

  -- description列的fast设置为true，record设置为'position'，分词器为jieba分词器（配置了非search模式，使用自定义分词词典user_dict、使用内置停用词 CN_SIMPLE
  -- category列全部使用默认配置
  -- 两个列的配置可以用 '||' 连接
  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: {} }'
  );
  
  -- 使用指定的jieba分词器查看分词效果
  SELECT pgsearch.tokenizer(pgsearch.tokenizer('jieba', hmm=>false, search=>false, dict=>'custom_dict_1'), '永和服装饰品有限公司');
           tokenizer         
  
  -- 使用各类Filter对分词结果进行处理
  SELECT pgsearch.tokenizer(
      pgsearch.tokenizer('jieba',
      search=>false, dict=>'user_dict', stopword=>'CN_SIMPLE',
      lowercase=>false, remove_long=>27, stemmer=>'en')::text,
      '永和服装饰品有限公司。Shoping'
  );

查看索引配置

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

删除索引

DROP INDEX index_name;

重建索引

REINDEX INDEX index_name;

查询语法

@@@排序操作符

@@@排序操作符主要用于KNN检索，即需要将检索结果按照BM25得分排序，然后按需获取TOP结果。

语法

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

参数说明

• <table_name>：表的名称。

• <index_col>：可以是创建索引时指定的任意一个索引字段。建议使用在检索关键词query中出现的第一个索引字段。

• <query>：检索的关键词。

• pgsearch.config：不仅接受检索字符串外，还接受其他检索对象。检索对象是可以组合的，允许任意粒度的检索。实际上，当传递检索关键词给pgsearch.config时，隐式地利用pgsearch.parse将检索关键词解析为检索对象。下面示例中两个查询是相同的效果。

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')
);

基础查询

指定字段检索

文本字段检索

检索包含指定短语的文本。如果短语中包含空格，应使用双引号将该短语包裹起来。

以下查询将分别检索含“keyboard”的行和含“hello world”的行。

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

-- 带有空格的短语
SELECT * FROM mock_items 
ORDER BY description @@@ pgsearch.config('description:"hello world"');

JSON字段检索

以下查询将检索含{"metadata": {"color": "white"}}的行。

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

DATETIME字段检索

在DATETIME字段上检索时，默认采用UTC时区和RFC3339格式。

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;

邻近性操作符检索

slop~运算符用于匹配中间有单词分隔的短语。例如，description字段存在“ergonomic metal keyboard”，由于单词“ergonomic”和“keyboard”之间有一个单词分隔，因此以下查询将找到“ergonomic metal keyboard”。

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

高效过滤检索

过滤器只适用于已创建BM25索引的数值字段（numeric_fields）和布尔字段（boolean_fields）。与标准SQL语句中WHERE子句相比，过滤检索可以缩短检索时间。

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

提升排名检索

如果您希望提升检索关键词的结果排名，可以使用^字符加提升系数实现。该系数作用于关键词匹配的结果，提高BM25得分，从而提升这些匹配行在结果中的排名。

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

布尔运算符检索

AND、OR和NOT可用于组合并筛选多个关键词。括号可用于分组和控制运算顺序。

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

集合运算符检索

集合运算符使用一个或者多个OR。优点：更节省CPU资源。

在集合运算符中，每个短语以空格分隔，且每个短语的分词结果不得超过一个token。由于中文分词后的token数量取决于分词器，难以进行有效控制，因此集合运算符只适用于英文，不适用于中文。在中文场景下建议使用多个OR代替此语法。

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

限制和偏移

支持OFFSET和LIMIT语句。

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

高级查询

获取分词结果

通过pgsearch.tokenizer()函数获取分词结果。函数返回一个数组，数组元素是分词后的关键词。

语法

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

参数说明

• tokenizer_config：包含分词器配置信息的JSON字符串，也可以使用同名的pgsearch.tokenizer()生成配置串，更多详情请参见分词器。

• query_str：需要被分词的字符串。

使用示例

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）

检索包含任意一个关键词的行。

使用示例

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')
            
	    ]
	)
);

参数说明

• field：指定用于检索的字段。如果省略，将检索所有索引字段。

• terms：检索对象ARRAY。

短语检索（phrase）

检索包含所有关键词且符合关键词顺序的行。短语检索需要索引的record配置为position。

使用示例

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

参数说明

• field：指定用于检索的字段。如果省略，将检索所有索引字段。

• phrases：关键词数组。要求文本包含数组中的所有关键词，且这些词在文本中出现的顺序与数组中关键词的顺序一致。只有词语匹配到全部关键词，且词语的顺序与关键词的顺序一致时，才会返回该行。

• slop：可选参数，表示各个关键词之间的最大距离。slop值为0，表示词语在文本中出现的顺序与关键词的顺序一致，并且相邻。例如，'little' 'red' 'riding' 'hood'按顺序连续出现，才算满足匹配条件。如果slop的值大于0，表示允许关键词之间可以有一定的间隔，也就是说'little' 'red' 'riding' 'hood'中间可以有其他词。

短语前缀检索（phrase_prefix）

检索包含指定关键词且符合关键词顺序（包含检索词前缀功能）的行。短语前缀检索需要对被检索字段建立索引。

使用示例

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

参数说明

• field：指定用于检索的字段。如果省略，将检索所有索引字段。

• phrases：关键词数组。query表示检索的关键词序列。数组里面的关键词序列作为前缀，可匹配到指定检索关键词后有任意字符的字符串（须顺序一致）。例如：ARRAY['little', 're'] ，前缀为“little re”，因此可以匹配到“little red riding hood”。

• max_expansion：可选参数，限制前缀在检索过程中可以扩展的关键词变体的数量。即该参数设置限制了关键词匹配的变体数量的上限，缩小了检索的范围。

分词检索（tokenizer_terms）

使用指定的分词器对查询文本做分词处理，随后查询分词后的token。支持在指定token之间应用布尔逻辑。

使用示例

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

参数说明

• tokenizer： 使用pgsearch.tokenizer()指定的分词器配置串，默认为jieba分词器。

• operator：用于解释分词后token之间的布尔逻辑，取值为OR或AND，默认为OR。例如“朝阳百货”分词后为“朝阳/百货”。当operator为OR时，查询结果会返回包含“朝阳”或“百货”的行。当operator为AND时，查询结果只会返回同时包含“朝阳”和“百货”的行。

全部检索（all）

此检索类型无区别地匹配索引字段中的每个结果行，并为每一行分配一个统一的分数1.0。即每一行的分数一样，检索结果也不分优先级。

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

布尔检索（boolean）

布尔检索根据子查询定义的逻辑关系过滤匹配结果行。

使用示例

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)
        ]
    )
);
-- 可以只配置should、must_not、must中的一个或几个
SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
    query => pgsearch.boolean(
	    should => pgsearch.parse('description:socks')
    )
);

参数说明

• must：一组ARRAY检索对象作为必须匹配的条件。只有满足所有must的行才会出现在检索结果中。

• must_not：一组ARRAY检索对象作为不得匹配的条件。满足任意must_not条件的行会被排除在检索结果中。

• should：一组ARRAY检索对象作为条件，当must不存在时，其中至少有一个必须匹配。

• 当must不存在时，满足至少一个should条件的行会出现在检索结果中。

提升排名检索（boost）

提升查询作用于子查询（即以下示例中query => pgsearch.parse('description:socks')）以放大其得分影响，从而提升子查询的结果排名。

使用示例

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

参数说明

• boost：与每个结果的得分相乘的因子。

• query：检索对象。

恒等分数检索（const_score）

在与子查询（即以下示例中的query => pgsearch.all()）匹配的所有行中应用常量分数。它可以避免在子查询上进行不必要的分数计算。

使用示例

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

参数说明

• score：用于子查询的每个结果的恒定得分。

• query：检索对象。

最大析取检索（disjunction_max）

最大析取检索返回与一个或多个指定子查询匹配的行。如果某行符合的匹配条件越多，则该行的得分越高。

使用示例

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
    )
);

参数说明

• disjuncts： 子查询对象数组，可以包含一个或多个子查询。

• tie_breaker：可选配置，用于某行匹配多个子查询时，调整该行的BM25得分。当某行同时满足多个子查询时，该行的BM25得分计算规则：首先计算所有匹配子查询中最高的BM25得分，然后对于每个额外匹配的子查询，会计算递增分数，最后计算BM25总分。在上面的例子中，假设有一行与子查询pgsearch.parse('description:socks')的BM25得分为1.0，与子查询pgsearch.parse('description:Generic')的BM25得分为0.5，那么该行最终的BM25得分为1.0+0.75*0.5=1.375。

空匹配（empty）

空匹配用作占位符，不匹配任何行。它可用于测试场景或特定边缘情况。

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

模糊检索（fuzzy_term）

模糊检索可让用户获得与检索关键词大致匹配的检索结果，能够容忍细微的拼写错误。即使拼写不完全正确，也能检索到相关结果。

使用示例

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)
);

参数说明

• field：指定用于检索的字段。如果省略，将检索所有索引字段。

• value：指定检索的关键词，使用基于Levenshtein距离的模糊匹配来检索与该关键词相似的结果。

• distance：可选配置，默认值为2。将索引字段中的词语视为检索关键词匹配的允许最大编辑距离（即单字符编辑），最大值为2。

• tranposition_cost_one：可选配置，默认值为true。当设置为true，在Levenshtein距离计算中，交换（交换两个相邻的字符）被认为是单个编辑。当设置为false，其被认为是两个单独的编辑（删除和插入）。

• prefix：可选配置，默认值为true。当设置为true，检索关键词的前缀不参与模糊编辑距离计算。设置为false时，则将整个字符串用于计算。

范围检索（range）

检索包含属于指定值范围的行，可以用在参数numeric_fields和datetime_fields。

使用示例

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

参数说明

• field：指定用于检索的字段。如果省略，将检索所有索引字段。

• range：指定与字段匹配的值范围。范围支持的类型包括INT4RANGE、INT8RANGE、DATERANGE、TSRANGE和TSTZRANGE。

正则表达式检索（regex）

如果某行中的词语能够匹配指定的正则表达式，则返回该行。

使用示例

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.*)'
	)
);

参数说明

• field：指定用于检索的字段。如果省略，将检索所有索引字段。

• pattern：正则表达式模式字符串。

其他

获取BM25得分

将@@@操作符前置，并使用AS语法获取BM25得分。

因为云原生数据仓库 AnalyticDB PostgreSQL 版数据库中默认的ORDER BY是升序，而BM25得分是越大越好，因此，返回的实际上是BM25得分的负值。例如，BM25得分为2.86，那么返回的结果为-2.86。除此之外，只有在text_fields和json_fields上的检索才会计算BM25得分，对于numeric_fields、datetime_fields和boolean_fields类型的检索，是无法计算BM25得分的。

语法

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

使用示例

-- 1.只在字符串类型的字段上进行检索，也就是最常见的全文检索，可以返回BM25得分
SELECT description, rating, description @@@ pgsearch.config('description:socks') AS bm25 FROM mock_items ORDER BY bm25 limit 1;

-- 2.在字符串类型的字段上进行全文检索，同时基于数值类型的字段进行过滤，同样可以返回BM25得分
SELECT description, rating, description @@@ pgsearch.config('description:socks AND rating:4') AS bm25 FROM mock_items ORDER BY bm25 limit 4;
 
-- 3.在json字段上进行全文检索，同样可以返回BM25得分
SELECT metadata, metadata @@@ pgsearch.config('metadata.color:White') AS bm25 FROM mock_items ORDER BY bm25 LIMIT 1;

-- 4.在rank字段上进行范围过滤，无法计算BM25得分，返回默认值-1
 SELECT description, rating, rating @@@ pgsearch.config('rating:[4 TO 5]') AS bm25 FROM mock_items ORDER BY bm25 LIMIT 1;

查询结果

 -- 1.只在字符串类型的字段上进行检索，也就是最常见的全文检索，可以返回BM25得分
   description  | rating |    bm25    
---------------+--------+------------
 Generic socks |      4 | -2.1048825
(1 row)

-- 2.在字符串类型的字段上进行全文检索，同时基于数值类型的字段进行过滤，同样可以返回BM25得分
 description  | rating |    bm25    
---------------+--------+------------
 Generic socks |      4 | -3.1081846
(1 row)

-- 3.在json字段上进行全文检索，同样可以返回BM25得分
                metadata                 |   bm25    
-----------------------------------------+-----------
 {"color": "White", "location": "China"} | -3.453373
(1 row)

-- 4.在rank字段上进行范围过滤，无法计算BM25得分，返回默认值-1
  description    | rating | bm25 
------------------+--------+------
 Plastic Keyboard |      4 |   -1
(1 row)