postgresql - 一起使用 postgresql UNIQUE INDEX 和 FUNCTIONAL INDEX

Question

我有一个如下所示的 postgresql 表。

CREATE TABLE "user" (
"id" integer NOT NULL,
"hash" char(40) NOT NULL,
"username" char(255) NOT NULL,
PRIMARY KEY ("id"),
UNIQUE ("hash"));

但是，由于 hash 是 40 个字母，我想做一个像下面这样的功能索引来减少内存需求。

CREATE INDEX CONCURRENTLY on user (substr(hash, 0, 20))

这样做可以吗，还是只会生成另一个无用的索引？如何确保 UNIQUE 索引仅索引哈希中的前 20 个字符？

谢谢。

Answer 1

如果你需要散列是唯一的，你必须在整个事物上有一个唯一的索引。否则，您将遇到仅在最后 20 个字符不同的散列的独特违规行为。

您可以在左边的 20 个字符上创建一个非唯一索引，就像您展示的那样:

CREATE INDEX on user (left(hash, 20))

但它可能没有任何用处。当您将其声明为 UNIQUE 约束时，PostgreSQL 将自动在整个 40 个字符的散列上创建一个唯一索引。您不能在不删除约束的情况下删除该索引。所以如果你想强制哈希的唯一性，你就只能使用全尺寸索引。鉴于此，功能索引不太可能带来多大好处。即使在这样的查询中:

SELECT ...
FROM "user"
WHERE left(hash, 20) = left($1, 20) AND hash = $1 

您可能认为通过使用较小的索引首先进行快速检查可以节省时间，但实际上 PostgreSQL 很可能会忽略功能索引而更喜欢完整索引，因为它更具选择性。

我不完全清楚您要实现的目标，但如果它正在执行部分索引或功能索引来实现唯一约束，则您不能那样做。

此外，将hash 存储为bytea 并使用索引表达式left(hash, 20)。或者可能是 10，如果您当前存储为每字节 2 个字符的十六进制表示。