PostgreSQL数据库全文搜索功能详解

发表时间: 2023-12-28 10:24

·说起全文搜索,最先想到的一定是LuceneElasticsearch;其中ES是基于Lucene,并且为开发者提供丰富的接口和工具


PostgreSQL提供了函数to_tsvector将一个文档转换成tsvector数据类型,to_tsvector('english', search_vector)函数将search_vector字段转换成tsvector类型,GIN是一种适合高速搜索tsvector的索引类型。

to_tsquery('english', friend)函数将“friend”转换成tsquery类型,@@操作符用于判断一个tsvector是否包含另一个tsquery。

加速全文搜索——GIN 和 GIST 索引类型

GIN 索引是更好的文本搜索索引类型。作为倒排索引,每个词(词位)在 其中都有一个索引项,其中有压缩过的匹配位置的列表。多次搜索可以找到 第一个匹配,然后使用该索引移除缺少额外词的行。GIN 索引只存储 tsvector值的词(词位),并且不存储它们的权重标签。因此, 在使用涉及权重的查询时需要一次在表行上的重新检查;

CREATE INDEX name ON table USING GIN(column);

创建一个基于 GIN(通用倒排索引)的索引。column必须是tsvector类型

GiST 索引是有损的(是因为每一个文档在索引中被表示为一个定长的签名,以字节为单位的签名长度由可选整数参数 siglen 的值决定,这表示索引可能产生假匹配,并且有必要检查真实的表行来消除这种假匹配(PostgreSQL在需要时会自动做这一步);

GiST 索引可以被覆盖,例如使用INCLUDE子句。包含的列可以具有没有任何 GiST 操作符类的数据类型。包含的属性将非压缩存储。

CREATE INDEX name ON table USING GIST (column [ { DEFAULT | tsvector_ops } (siglen = number) ] );

创建一个基于 GiST(通用搜索树)的索引。column可以是tsvector或tsquery类型。可选整数参数siglen确定以字节为单位的签名长度。

在实际生产应该中一般会使用触发器函数生成向量进行全文检索!!!!!!

触发器声明了 当执行一种特定类型的操作时 数据库 应该自动执行一个特殊的函数。触发器可以被附加到表(分区的或者不分区的)、视图和外部表。可以被定义为在 INSERT、UPDATE或 DELETE操作之前或之后被执行,也可以被 TRUNCATE语句触发

CREATE TRIGGER 创建触发器;


语句级BEFORE触发器在语句开始做任何事情之前被触发,而语句级AFTER触发器则在语句做完所有事情之后被触发; 行级BEFORE触发器在每一个行被操作之前被触发,而行级AFTER触发器在语句结束之后被触发(但在任何语句级AFTER触发器之前)。