postgresql - 如何使用部分字段搜索正确构建多列索引

Question

使用 full_name 列和 state 列设置多列索引的最佳方法是什么？搜索将使用精确状态，并在 full_name 列上进行部分搜索。查询将像这样:

WHERE full_name ~* 'jones' AND state = 'CA';

搜索大约 2000 万条记录。

谢谢!

约翰

Answer 1

状态看起来很简单——一个普通的索引就足够了。至于全名搜索，这是很多工作，但有 2000 万条记录，我认为红利不言自明。

在您的表中创建一个新字段作为 tsvector，并为本示例将其命名为 full_name_search:

alter table <blah> add column full_name_search tsvector;

对列进行初始填充:

update <blah>
set full_name_search = to_tsvector (full_name);

如果可能，使该字段不可为空。

创建一个触发器，现在它会在更新时自动填充此字段:

create trigger <blah>_insert_update
before insert or update on <blah>
for each row execute procedure
    tsvector_update_trigger(full_name_search,'pg_catalog.english',full_name);

在新字段上添加索引:

create index <blah>_ix1 on <blah>
using gin(full_name_search);

从这里开始，重构查询以在 tsvector 字段而不是文本字段上进行搜索:

WHERE full_name_search @@ to_tsquery('jones') AND state = 'CA';

您可以在其中一些步骤上走捷径(例如，不要创建额外的字段，而是使用基于函数的索引)，它会让您提高性能，但不如您能得到的那么好.

一个警告 -- 我认为 to_tsvector 将根据内容中的逻辑中断拆分为矢量组件，因此:

Catherine Jones Is a Nice Lady

可以正常工作，但是:

I've been Jonesing all day

可能不会。