用gist和gist__int_ops缓慢创建索引

Question

我看到频繁更新/插入表的性能问题，我的假设之一是int数组上的gin索引。

因为我们有"X存在于数组中“的查询，所以添加了gin索引？在高调端点中。

我创建了一个生产DB的一次性副本，并删除了索引，并将其替换为GIST和gist__int_ops，以检查它的性能(因为它应该在频繁更新的/inserts表上运行得更好)。

gist索引的创建从未完成，我让它运行了几个小时，在另一边没有结果，gin索引的创建需要几分钟。

我试着清理数据--我有50到120个条目的数组，我删除了它们，现在我的所有数据都是一个只有一个元素的数组，但是索引的创建很慢。

DB: PostgreSQL 11.5 on RDS (db.m4.4xlarge)表大小: 9373 MB

我能在这里做些什么？

Answer 1

我也遇到过这个问题，除了“不要使用gist__int_ops”之外，我从来没有找到过解决方案。在大桌子上，泡菜或惩罚功能似乎在病理上出错了。我不知道是否有人能用足够的努力修复这个错误，或者它是否是一个固有的问题。

(因为它应该在频繁更新的/inserts表上运行得更好)

我不知道这个建议到底有多好(或曾经)。请注意，它是从9.5版的文档中删除的。但是，如果你永远无法让构建完成，那么它肯定不会是对你的情况的好建议。

Answer 2

最有可能的解释是，有一个并发会话对表有一些锁，并且是idle in transaction。检查pg_stat_activity和pg_locks以查找故障会话，并检查pg_terminate_backend以终止会话。

由于这是从转储中新建的数据库，因此您不太可能受到数据损坏的影响。对于一个小于10 on的表，即使在托管数据库上，工作时间也太长了。

Answer 3

通过在列中创建带有空值的索引，然后在之后填充数据，我能够解决这个问题，但是仍然需要花费大约20个小时，一个只有1400万行的表。我不确定这对于生产环境是否可行，但我想在GiST和GIN上运行性能测试。

1. 将创建索引的列中的数据复制到新的临时列中
2. 通过删除和重新创建数据或将其设置为'{}'::int[]从原始列中清除数据
3. 创建GiST索引
4. 运行并更新以将数据移回原始列