PostgreSQL - 集群永远不会完成 - 长密钥？
我在对表进行聚类时遇到问题，其中键由一个 char(23) 字段和两个 TimeStamp 字段组成。 char(23) 字段包含字母数字值。聚类操作永远不会完成。我让它运行了24小时，但它仍然没有完成。
以前有人遇到过这种问题吗？我的理论认为原因是长关键字段有道理吗？我们处理过没有长键的大得多的表，并且我们始终能够毫无问题地对它们执行数据库操作。这让我认为这可能与本例中密钥的大小有关。
I am having problems with clustering a table where the key consists of one char(23) field and two TimeStamp fields. The char(23) field contains Alpha-Numeric values. The clustering operation never finishes. I have let it run for 24 hours and it still did not finish.
Has anyone run into this kind of problem before? Is my theory that the reason is the long key field makes sense? We have dealt with much larger tables that do not have long keys and we have always been able to perform DB operations on them without any problem. That makes me think that it might have to do with the size of the key in this case.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
集群重写表，因此它必须等待锁。它有可能永远无法获得所需的锁。为什么要设置 varchar(64000)？为什么不只是无限制的 varchar 呢？这个指数有多大？
如果大小是一个问题，它必须基于索引大小而不是密钥大小。我不知道烤关键属性对集群有什么影响，因为这些属性已移至扩展存储中。 TOAST 可能会使 CLUSTER 变得复杂，而且我从未听说过有人在 TOAST 属性上进行集群。这样做没有多大意义。对于大小超过 4k 的任何属性，TOAST 都是必需的。
更好的选择是为没有可能经过烘烤的值的值创建索引，然后对其进行聚类。这应该会给你一些非常接近你会得到的东西。
Cluster rewrites the table so it must wait on locks. It is possible that it is never getting the lock it needs. Why are you setting varchar(64000)? Why not just unrestricted varchar? And how big is this index?
If size is a problem it has to be based on the index size not the key size. I don't know what the effect of toasted key attributes is on cluster because these are moved into extended storage. TOAST might complicate CLUSTER and I have never heard of anyone clustering on a TOASTed attribute. It wouldn't make much sense to do so. TOASTing is necessary for any attribute more than 4k in size.
A better option is to create an index for the values without the possibly toasted value, and then cluster on that. That should give you something very close to what you'd get otherwise.