主页 / dba / 问题 / 249022
Accepted
Morris de Oryx
Asked: 2019-09-18 18:15:50 +0800 CST

忽略 citext 列上的表达式索引，为什么？

  • 772

在大约 32M 行的 RDS 上运行。

PostgreSQL 11.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

还在 macOS 上进行本地测试，行数约为 8M。

PostgreSQL 11.5 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM version 8.1.0 (clang-802.0.42), 64-bit

我有一个名为old_valuecitext 类型的列。我已经问过这个问题，但在此过程中发布了我的许多发现步骤。这是一个简化的版本，我希望能说到点子上。

背景

我有一个名为 record_changes_log_detail 的字段更改日志表，其中包含一个名为 old_value 的 citext 字段，其中包含 32M 行并且在不断增长。

数据非常倾斜。大多数值少于十几个字符，有些值超过 5,000。

Postgres 因 B 树条目被限制为 2172 个字符的错误而阻塞大值。所以我相信对于B树，我需要对源值进行子串化。

我的用户主要感兴趣的是 = 搜索、以开头搜索，有时还有包含此子字符串的搜索。所以 = string% 和 %string%

目标

创建一个支持计划器使用的搜索的索引。

尝试并失败

在某些情况下，由于值太长，无法构建直 B 树。

像这样的表达式 B-tree 构建，但未使用

CREATE INDEX record_changes_log_detail_old_value_ix_btree
    ON  record_changes_log_detail 
    USING btree (substring(old_value,1,1024));

添加 text_pattern_opts 没有帮助。

CREATE INDEX record_changes_log_detail_old_value_ix_btree
    ON  record_changes_log_detail 
    USING btree (substring(old_value,1,1024) text_pattern_opts);

尝试并部分工作

哈希索引有效，但仅用于相等。（就像它在罐头上说的那样。）

这是我最接近成功的地方：

CREATE INDEX record_changes_log_detail_old_value_ix_btree
    ON record_changes_log_detail 
    USING btree (old_value citext_pattern_ops);

这适用于质量，但不适用于 LIKE。PG 11 的发行说明说它应该适用于 LIKE：

https://www.postgresql.org/docs/11/release-11.html

“工作”是指“使用索引”。

我无法使用这种方法成功地进行子串化。

人们在这种情况下对 citext 字段做了什么？

postgresql index

3 个回答

  1. 请编辑您的问题，而不是发布不回答的答案。

    如果在表达式上创建索引substring(old_value,1,1024)，那么只有在查询涉及时才能使用该索引substring(old_value,1,1024)。

    如果你对子字符串的内部有足够的了解，理论上可以证明这old_value='foo'意味着（因此是相反的）， PostgreSQL 不会试图证明. 您需要以不需要此类证明的方式编写查询。substring(old_value,1,1024)='foo'

    • 3
  2. Best Answer

    完全索引这么长的列是不寻常的。

    三个想法：

    1. 像这样修改查询：

      WHERE substring(old_value, 1, 100) LIKE substring(pattern, 1, 100)
  AND old_value LIKE pattern

      （pattern这将是模式字符串，例如'string%'。）

      然后可以使用 b-tree 索引substring(old_value, 1, 100)（如果模式当然不是以通配符开头）。

    2. 根据具体要求（您是否在自然语言文本中搜索完整的单词或单词前缀），全文搜索可能是一个很好的解决方案。

    3. 另一个选择当然是三元索引：

      CREATE INDEX ON record_changes_log_detail USING gin (old_value gin_trgm_ops);

      这需要pg_trgm安装扩展。

      这样的索引也适用于以通配符开头的搜索模式。为了获得良好的性能，请对搜索字符串强制使用最小长度。

    • 2

  3. 我回来结束这个问题。根据 Laurenz Albe 的建议，我尝试了 Postgres tri-gram 实现。他们统治！

    DROP INDEX IF EXISTS record_changes_log_detail_old_value_ix_tgrm;
CREATE INDEX record_changes_log_detail_old_value_ix_tgrm
    ON record_changes_log_detail 
    USING gin (old_value gin_trgm_ops);

    使用 citext 的秘诀是将值转换为 ::text，如下所示：

    select * from record_changes_log_detail 
where old_value::text LIKE '%Gold Kerrison Neuro%';

    用解释分析运行它确认使用了索引。我注意到我必须使用 LIKE 进行 = 搜索，但这没关系。

    • 0

相关问题