Postgres哈希索引与唯一约束

Question

随着Postgres 10对哈希索引的支持，我想使用哈希索引进行ID查找（哈希索引与btree相比尺寸更小，理论上更快）。

我有一个表：

create table t (id int);
create unique index on t using hash (id);

但是我得到了以下错误：

ERROR：访问方法“hash”不支持唯一索引。

为什么哈希索引不允许唯一约束？ 有没有绕过这个问题的方法？

- Green

Answer 1

这份文档毫不含糊地表明：

当前，只有B树索引可以被声明为唯一。

最近在黑客列表上进行了讨论，并得出结论：允许使用UNIQUE哈希索引并不简单。

- klin

Answer 2

您可以使用排除约束来实现此目的：

create table t (id int);
alter table t add constraint c exclude using hash (id with =);

- jbg

---

你知道大小与唯一索引相比如何吗？我假设它会更小，但你有检查过吗？另外，你知道在十亿行上排除索引的性能如何吗？ - Arvind Sridharan

@jbg，你是怎样做 upsert 的？我在语法上遇到了麻烦。conn.execute( f"""INSERT INTO {TABLE_NAME} VALUES ($1, $2) ON CONFLICT (id) DO UPDATE SET value = ($2)""", hash_id, str_profile, ) 显示“没有符合 ON CONFLICT 规范的唯一或排除约束条件”。 - Lukasz Madon

@LukaszMadon 在 ON CONFLICT 子句中命名约束，而不是让 Postgres 从列中推断它（这仅适用于唯一索引，而不适用于排除约束）。在我的答案示例中，它将是 ON CONFLICT (c) DO ...。请参阅文档：https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT - jbg

谢谢您的回复，我得出了相同的结论。使用ON CONFLICT ON CONSTRAINT cnp_profiles_id_excl DO UPDATE导致WrongObjectTypeError: ON CONFLICT DO UPDATE not supported with exclusion constraints。我无法在这种类型的upserts中使用哈希索引。 - Lukasz Madon

不需要使用“create index”命令 - 约束创建已经自动创建了索引。 - Elifarley

Answer 3

为什么不直接为这个列添加一个约束呢？

create table t (id int);
create index on t using hash (id);

-- simply create a new constraint for this column, do not mix it with hash index
alter table t add constraint unique_id unique (id); 

- Siwei

---

由于约束将创建B树索引。在长字符串（约3k）的情况下，B树将失败！ - Alex Povolotsky

Answer 4

非常老的想法

在链接上创建唯一索引（将md5（url）转换为uuid）;

我现在正在尝试

更新。它很慢，就像在每次插入时重新计算每个总和。

更新2。添加功能唯一索引不会加速列上的搜索。

要查找相关字符串，应该使用相同的转换进行搜索或添加哈希索引。

- Alex Povolotsky