酷徒LOGO

使用postgres进行全文搜索


在本文中,我将展示如何使用tsvector在cms中搜索文档。

tsvector 类型

postgres的tsvector可用于全文搜索,


SELECT to_tsvector('pg_catalog.english', 'Never gonna give you up. Never gonna let you down');
-- 'give':3 'gonna':2,7 'let':8 'never':1,6

你可以看到词gonnanever 原词里出现两次但在tsvector 只出现了一次。那些旁边的数字是他们在原句子中的位置。

停用词

在上面的例子中你可能注意到的是 youup,和 down 结果中没有出现,传递给的第一个参数是to_tsvector 要使用的字典的名称,每个词典都包含停用词的列表,这些词不包含在结果中,不同的字典有不同的停止词。


SELECT to_tsvector('pg_catalog.simple', 'Never gonna give you up. Never gonna let you down');
-- 'down':10 'give':3 'gonna':2,7 'let':8 'never':1,6 'up':5 'you':4,9

这里我们使用 simple词典,不包含停止词youup

归一化

postgres还将同一个单词的不同变体规范化为一个单词,例如:


SELECT to_tsvector('pg_catalog.english', 'Your heart''s been aching but you''re too shy to say it');
-- 'ach':5 'heart':2 're':8 'say':12 'shi':10

你可以看到这个词 aching 归化为 ach

添加触发器

tsvector 列调用 tsv, 以便我们能够存储和索引向量,而不是动态创建它们,tsv


CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON pages FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(
        tsv, 'pg_catalog.english', path, body, search_params
)

这告诉postgres每次插入或更新页表中的行时,也更新tsv 列,并使用 pathbody 还有 search_params 要创建的列 tsvector

tsvector 大小限制

我们立刻遇到的一个问题是,tsvector大小限制为1MB。


PG::ProgramLimitExceeded: ERROR: string is too long for tsvector (2466260 bytes, max 1048575 bytes)

为了解决这个问题,我们必须更改触发器,以使用能够将输入文本截断到适当长度的存储过程,由于1MB数据已经很大,因此我们决定只对前500KB进行索引。


CREATE OR REPLACE FUNCTION search_params_trigger() RETURNS trigger AS $$
 begin
 new.tsv := to_tsvector(
         'pg_catalog.english', 
                substring(
                 coalesce(new.path,'') ||
                 coalesce(new.search_params,'') || 
                 coalesce(new.body,''), 
                 1, 500000
                )
         );
 return new;
 end
 $$ LANGUAGE plpgsql;

CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
 ON pages FOR EACH ROW EXECUTE PROCEDURE search_params_trigger();

编制索引

tsv 列使用 gin 索引。

Postgres文档:

gin用于处理被索引的项显示为复合值的情况,索引需要由元素搜索处理,例如,项可以是文档,查询可以搜索包含特定单词的文档。

因为我们用复合值索引列,所以gin索引是我们所需要的。

CREATEINDEX index_pages_on_tsv ON pages USING gin (tsv);`

使用JSON

使用了Postgres中的JSON和JSONB列,并且当前使用的是Postgres 9.6,它不支持从JSON创建tsvector,因此我们创建了search_params


UPDATE pages AS p 
SET search_params = array_to_string(
 array(
 SELECT value 
 FROM pages, json_each_text(pages.params) 
 WHERE pages.id = p.id
 ), ' '
)

这将从json对象中去掉顶级键,并将所有值连接成一个长字符串,它并不完美,但足够好。

关于Rails的说明

值得注意的是,Rails不支持将触发器和存储过程导出到schema.rb,我们已经使用structure.sql,总之,这对我们来说不是问题,但是,如果你需要切换,你可以这样改动application.rb,像这样。

config.active_record.schema_format =:sql




Copyright © 2011 HelpLib All rights reserved.    知识分享协议 京ICP备17041772号-2  |  如果智培  |  酷兔英语  |  帮酷