多租户PostgreSQL数据库中全文搜索的索引策略

发布时间：2020-12-13 15:54:05 所属栏目：百科来源：网络整理

导读：我有一个PostgreSQL数据库,存储多个用户帐户的联系信息表(名字,姓氏).每个联系人行都有一个用户ID列.设置索引的最高效方法是什么,以便用户可以搜索其联系人名字或姓氏的前几个字母？我知道传统的b-tree索引和PG特定的GIN和GiST,但我只是不确定他们如何能够(

我有一个PostgreSQL数据库,存储多个用户帐户的联系信息表(名字,姓氏).每个联系人行都有一个用户ID列.设置索引的最高效方法是什么,以便用户可以搜索其联系人名字或姓氏的前几个字母？

我知道传统的b-tree索引和PG特定的GIN和GiST,但我只是不确定他们如何能够(或不能)一起工作,只有几个联系人的用户不必搜索在user_id过滤之前的所有联系人.

解决方法

您应该将帐户标识符添加为您创建的任何索引的第一列.这实际上首先将搜索范围缩小到属于该帐户的行.对于gist或gin全文索引,您需要安装btree_gist或btree_gin扩展.

如果您只需要搜索第一个字母,那么最简单且可能最快的将是使用支持两列的文本操作的常规btree并执行2次查找.您需要使用text_pattern_ops opclass来支持文本前缀查询,并使用lower()字段来确保不区分大小写：

CREATE INDEX contacts_firstname_idx ON contacts(aid,lower(firstname) text_pattern_ops);
CREATE INDEX contacts_lastname_idx ON contacts(aid,lower(lastname) text_pattern_ops);

然后查询将如下所示：

SELECT * FROM contacts WHERE aid = 123 AND
    (lower(firstname) LIKE 'an%' OR lower(lastname) LIKE 'an%')

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!