PostgreSQL通过pg_trgm扩展的三元索引实现全文检索

本文介绍利用pg_trgm扩展实现全文检索。

1. 概述

PostgreSQL可以使用三元索引增强全文检索。三元算法对字符串进行分词，如“dog” 被分为 “d”,”do”,”dog”,”og”多个词。

pg_trgm扩展支持三元匹配。也在Gist和Gin索引上使用三元匹配操作提升检索速度或性能。

对于全文搜索提供了不同的同义词，同时也可以使用Trigram来实现打字更正和建议。下面通过示例说明。

2. 模糊匹配

这里先介绍postgresql几种模糊操作符：

like都比较熟悉的大小写敏感匹配，ilike操作是大小写不敏感模糊匹配。

Postgresql同时还相应的符号表示:

符号	关键字
~	LIKE
~*	ILIKE
!~	NOT LIKE
!~*	NOT ILIKE

3. 示例

创建示例表：

CREATE TABLE tbl_TestData
(
	ID INT
	,TextData TEXT
);

对text类型字段创建三元模式索引：

CREATE INDEX trgm_idx_tbl_TestData_TextData ON tbl_TestData USING GIST (TextData gist_trgm_ops);

执行上面语句，如果提示下面错误：
ERROR: operator class "gist_trgm_ops" does not exist for access method "gist" .

则需要安装pg_trgm扩展：

CREATE EXTENSION pg_trgm;

插入示例数据：

INSERT INTO tbl_TestData
VALUES 
(1,'Anvesh Patel')
,(2,'Database Research & Development')
,(3,'PostgreSQL is better than SQL Server')
,(4,'PostgreSQL is supported powerful BRIN Indexes');

执行select查询进行文本比较：

SELECT * 
FROM tbl_TestData
WHERE TextData ~ 'PostgreSQL is';
 
SELECT * 
FROM tbl_TestData
WHERE TextData LIKE '%SQL%';
 
SELECT * 
FROM tbl_TestData
WHERE TextData ~* 'ql';

执行下面语句可以查看三元索引生成结果：

select show_trgm('study postgresql and greenplum') as notes;

返回结果：

{  a,  g,  p,  s, an, gr, po, st,and,dy ,een,enp,esq,gre,lum,nd ,npl,ost,plu,pos,ql ,ree,res,sql,stg,stu,tgr,tud,udy,um }

执行下面语句检查相似性：

SELECT SIMILARITY('Anvesh','anv') AS SimilarPercentage;
SELECT SIMILARITY('Anvesh','anve') AS SimilarPercentage;

4. 总结

本文介绍pg_trgm扩展实现模糊检索。我们看到示例都是英文，如果是中文需要先转为bytea类型。