Postgres 13.4。

我有一个设置，其中对包括uuid在内的混合字段类型的Bloom索引可能会非常有用。开箱即用，Bloom扩展仅支持int4和text。如何添加对其他类型的支持？

根据Laurenz Albe的评论，我已经将帖子修改为问答；一种格式。

每隔几年，我都会从研究生专业版的人那里完成关于索引的精彩的十集系列。我在看Bloom过滤器的最后一篇(https://habr.com/en/company/postgrespro/blog/452968/)，并发现了一个如何将支持扩展到其他数据类型的示例。它引起了我的注意，因为我以前没有弄清楚CREATE OPERATOR CLASS。在其他关于Postgres的Bloom索引的讨论中，我也没有发现这些细节。我想我会写一些笔记来保存档案，希望能吸引一些额外的信息和/或更正。

Postgres中的索引是。。。。柔性的

Postgres中的索引选项无疑是其十大五大功能之一。基本索引类型/框架和变体的多样性令人难以置信。不幸的是，pg_catalog的索引相关部分非常密集。如果您有要索引的内容，但数据类型不受支持，那么似乎可以使用CREATE OPERATOR CLASS添加对其他类型的支持。我从未想过如何做到这一点，直到在https://habr.com/en/company/postgrespro/blog/452968/.虽然这个例子和下面的代码是专门针对Bloom过滤器索引的，但我希望大多数机制都能转移到其他索引类型。

Bloom过滤器

我今天不需要Bloom过滤器，而且可能永远都不想要。但是，老实说，他们非常酷*。Postgres中的一个很好的功能，因为它们解决了一个你通常不会期望RDBMS有解决方案的问题。(宽列，跨许多字段的随机搜索组合。)

我以前从未在RDMBS系统中见过Bloom过滤器。我在分布式日志聚合系统中见过它们，它们有助于减少来自远程位置或冷存储的不必要且昂贵的数据负载。Postgres Bloom过滤器与此不同。

我可能很喜欢Bloom索引，因为我们有一个宽表，其中有13个UUID列，可以以任意数量的组合进行搜索，还有各种int4和其他数据。我喜欢提前试验这些东西，这样，如果出现问题，我就已经有了可能的工具。Bloom过滤器是，而不是DBA通常会立即想到的。然而，Bloom索引是CREATE OPERATOR CLASS的一个很好的例子。启动并运行它只需要三个步骤：

如果尚未安装bloom扩展，请安装它。
为感兴趣的数据类型和bloom访问方法定义一个新的运算符类。这需要找到一个合适的哈希函数。
使用新的运算符类创建新索引。

假设您已经安装了bloom扩展(https://www.postgresql.org/docs/current/bloom.html)，这里有一个小搜索来显示与bloom索引相关联的运算符类：

select pg_am.amname,
pg_opclass.opcintype::regtype,
pg_opclass.opcname,
pg_am.amhandler::regproc
from pg_opclass
left join pg_am on pg_am.oid = pg_opclass.opcmethod
where amname = 'bloom'
order by amname,opcname;
+--------+-----------+---------------+-----------+
| amname | opcintype | opcname       | amhandler |
+--------+-----------+---------------+-----------+
| bloom  | integer   | int4_ops      | blhandler |
| bloom  | text      | text_ops      | blhandler |
+--------+-----------+---------------+-----------+

因此，对uuid数据没有帮助。

添加运算符类

以下是这个简单案例所需的代码：

CREATE OPERATOR CLASS uuid_ops
DEFAULT FOR TYPE uuid USING bloom AS
OPERATOR  1  =(uuid,uuid),
FUNCTION  1  uuid_hash;

我认为上面的代码意味着"；OPERATOR策略1 EQUALITY散列"；，或多或少。我猜想，将运算符类添加到具有更广泛运算符的索引类型中会涉及更多。Bloom过滤器仅支持"；可能存在/不存在"；逻辑，所以只需要一个声明和要匹配的哈希函数。如果您再次运行上一个，它现在返回新操作员类的一个条目：

+--------+-----------+---------------+-----------+
| amname | opcintype | opcname       | amhandler |
+--------+-----------+---------------+-----------+
| bloom  | integer   | int4_ops      | blhandler |
| bloom  | text      | text_ops      | blhandler |
| bloom  | uuid      | uuid_ops      | blhandler |
+--------+-----------+---------------+-----------+

寻找合适的哈希函数

有人告诉我，虽然Postgres的源代码没有在文档中列出，但它包含了一系列类型特定的哈希函数。有一整套针对哈希索引实现的特定类型函数：

https://doxygen.postgresql.org/hashfunc_8c_source.html

我没有看到uuid，但这里实现了一个散列：

https://github.com/postgres/postgres/blob/REL_13_STABLE/src/backend/utils/adt/uuid.c#L403

您所需要知道的只是提供CREATE OPERATOR CLASS的函数的名称，而不是它在源代码中的实现位置。

使用新的运算符类

有了这一点，就可以在uuid数据上创建Bloom索引。这是个好主意吗？不知道。我今天需要Bloom索引吗？不。但我喜欢研究Postgres的功能，以了解当我遇到合适的问题时，有什么可用的。Postgres中的索引比我所知道或见过的任何其他索引都要广泛得多。。。他们需要学习。下面是一个在Bloom索引上使用新运算符类的示例，该索引包括十个uuid字段和三个int4字段。

create index analytic_work_bloom_idx on data.analytic_work
using bloom(
id,
hsys_id,
facility_id,
inv_id,
user_id,
activity_id,
assembly_id,
q_event_id,
scan_id,
scase_id,
expected_count,
actual_count,
missing_count)
with (length=96,
col1  = 7,
col2  = 7,
col3  = 7,
col4  = 7,
col5  = 7,
col6  = 7,
col7  = 7,
col8  = 7,
col9  = 7,
col10 = 7,
col11 = 7,
col12 = 7,
col13 = 7);
select pg_size_pretty(pg_total_relation_size('analytic_work_bloom_idx'));  --- Curious.

不要把上面的例子当作如何计算Bloom索引的正确签名长度和每列字节数的好例子。有很多文章解释了机制、理论，并提出了调整签名长度和每列字节数的方法。超过我的工资等级。

粗略结果

我在一个示例数据库中有大约600K+行，并添加了Bloom索引。结果应该变化很大，但在我的情况下，我发现：

索引构建速度不错
13个字段上的CCD_ 19索引比一个CCD_ 20字段上的B树大约4倍
Bloom索引将随机多字段查询时间缩短了一半。您的Millage将发生变化

请添加和更正

我很乐意看到人们提供的任何额外信息或更正。

向Bloom索引添加对其他数据类型的支持