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

Question

我有一个PostgreSQL数据库，存储多个用户账户的联系人信息表（名字和姓氏）。每个联系人行都有一个用户ID列。为了使用户能够搜索他们联系人姓名的前几个字母，最高效的建立索引的方法是什么？我知道传统的B树索引和PG特定的GIN和GiST索引，但我不确定它们如何结合使用，以便只需搜索用户ID之前的联系人即可过滤结果，而不必搜索所有联系人。

- hpoydar

---

可能是dba.SE一个有趣的候选人（并进行一些格式化以使其更易于阅读）。 - Erwin Brandstetter

Answer 1

您应该在创建索引时将帐户标识符作为第一列添加。这将有效地缩小搜索范围，仅限于属于该帐户的行。对于gist或gin全文本索引，您需要安装btree_gist或btree_gin扩展程序。

如果您只需要搜索前几个字母，最简单且可能最快的方法是使用支持文本操作的常规btree，然后进行2次查找。您需要使用text_pattern_ops opclass来支持文本前缀查询，并将字段转换为小写以确保大小写不敏感：

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%')

- Ants Aasma

---

恐怕搜索要求可能会扩大，包括职位、公司名称等。因此，可以通过触发器或应用程序回调填充额外的文本列，并使用空格分隔的字符串在其他列中进行搜索，以减少对用户ID和该列的单个索引。 - hpoydar

在这种情况下，全文索引方法可能更好。如果停用词限制不是问题，只需在字段串联上创建索引并使用to_tsquery（'searchprefix：*'）。如果您想要能够搜索不仅从单词开头开始的内容，请查看pg_trgm。 - Ants Aasma