如何在PostgreSQL的GIST索引类型中使用UUID？

Question

我无法直接使用uuid与gist索引

CREATE INDEX idx_leaderboads_values_gist
  ON leaderboard_entry
  USING gist
  (id_leaderboard , value);

我遇到了以下错误：

错误：数据类型uuid在访问方法“gist”中没有默认运算符类别

提示：您必须为索引指定一个运算符类，或者为该数据类型定义一个默认运算符类。

- xjodoin

---

是的，我有我的答案。 - xjodoin

Answer 1

Postgres 10或更新版本

btree_gist现在也支持uuid这种数据类型，正如Paul所评论的那样。（还有其他一些数据类型，尤其是所有的enum类型。）

现在您只需要为每个数据库安装一次扩展即可：

CREATE EXTENSION btree_gist;

那么你的索引应该可以正常工作。

相关：

• 在使用位与运算符的位字符串列上设置排除约束
• 在PostgreSQL中创建含有标量和数组列的多列索引

Postgres 9.6或更早版本

(原始回答。)
通常我会建议使用额外的模块btree_gist，但是类型uuid没有被其覆盖。

理论上，由于UUID是128位数量（根据文档），最有效的方法是将其转换为两个bigint或float8以用于索引目的。但这些类型转换都未在标准的Postgres中定义。

我在pgsql-hackers列表中发现了一个与此相关的讨论，但它似乎没有成功。

剩下的选择是在text表示上使用功能GiST索引：

CREATE INDEX idx_leaderboads_values_gist
ON leaderboard_entry USING gist (id_leaderboard, cast("value" AS text));

为了使用这个函数索引，查询必须匹配该表达式。您可以在查询中使用简写"value"::text（但在索引定义中不加括号不起作用）。

补充一点：不要使用value作为列名，它是标准SQL中的保留字。

问题是：为什么需要GiST索引。最佳解决方案取决于目标。

- Erwin Brandstetter

---

我需要在排行榜中查找与某个值相近的值，并且希望避免创建两个查询来完成这个任务。 - xjodoin

现在Postgres 10已经发布，btree_gist支持UUID，因此您不再需要进行强制转换。 - Paul A Jungwirth

@Paul：太好了！谢谢你的更新！我猜你也是这个补丁的作者，棒极了！ - Erwin Brandstetter

哈哈，我是创始人，但有几个人帮忙完成了它。 :-) - Paul A Jungwirth

Answer 2

如果您正在使用Postgres 10，并且通过dump/restore从先前的版本迁移数据库，则可能需要运行以下命令：

ALTER EXTENSION btree_gist UPDATE;

为了让Gist索引与UUID一起工作。

- Ryan

---

谢谢！我觉得你刚刚拯救了我们的部署:D - Connor Shea