主页 / dba / 问题 / 62822
Accepted
Alexandros
Asked: 2014-04-10 04:10:30 +0800 CST

PostgreSQL，整数数组，相等索引

  • 772

我有一个巨大的整数数组列表（300,000,000 条记录）存储在 Postgres 9.2 DB 中。我想有效地搜索这些记录以获得完全匹配（仅相等）。我听说过 intarray 模块和相应的 gist-gin 索引。我想问以下问题：

  • PostgreSQL 是否使用哈希函数来检查整数数组的相等性，还是执行暴力算法逐一比较数组的元素？
  • 如果 PostgreSQL 使用散列函数，是否有一些 PostgreSQL 函数代码可以实际获取特定数组的散列函数结果？
  • 哪个索引最适合这样的任务？B-tree，还是 intarray 模块提供的 gist - gin 索引？数据集将是静态的，即，一旦插入所有记录，就不会再插入了。所以，建立索引/更新索引时间对我来说并不重要。
postgresql btree

2 个回答

  1. 问：PostgreSQL 是否使用散列函数来检查整数数组的相等性，还是执行蛮力算法逐一比较数组元素？

    不根据文档中的数组函数和运算符：

    数组比较逐个元素比较数组内容，使用元素数据类型的默认 B 树比较函数

    没有提到哈希。

    intarray提供了其他运算符，但不替换之间的相等运算符int[]。它公开的最接近的函数_int_same()在语义上是不同的（元素的顺序无关紧要），并且实现为排序+顺序比较，而不是散列。

    幸运的是，在 SQL 级别实现基于散列的快速搜索并不难，在您的情况下（大型数组、无更新、完全匹配），它甚至可能是最有效的方法。

    脚步：

    1）选择一个哈希函数。我建议md5数组的文本表示：

    create function arr_hash(int[]) returns bytea as
$$ select digest($1::text, 'md5');$$
language sql immutable;

    该功能digest(text,text)是pgcrypto扩展的一部分。与之相比，md5它具有生成二进制（16 字节）而不是十六进制（32 字节）的优势，以实现更精简的索引。

    2）创建功能索引：

    create index index_name on table_name(arr_hash(col_name));

    对于您拥有的数据集类型，它将比 GIN 索引快几个数量级（实际上我会担心创建 GIN 索引会花费非常不合理的时间，但请尝试一下）。

    3）像这样使用它：

    select 1 from table_name
 where arr_hash(col_name)=arr_hash('{10,20,30,...lot of values}'::int[])
 and   col_name='{10,20,30,...lot of values}'::int[];
    • 7
  2. Best Answer

    1）正如您已经发现的那样，您不能使用 b-tree，因为索引大小大于页面大小

    2）给定：

    根据经验，GIN 索引的搜索速度比 GiST 索引快，但构建或更新速度较慢；所以 GIN 更适合静态数据，GiST 更适合经常更新的数据。

    您将不得不使用 GIN。不，GIN 不使用散列函数，也不使用蛮力算法。这是一个反向索引：

    GIN 索引存储一组（键，发布列表）对，其中发布列表是其中出现键的一组行 ID。相同的行 ID 可以出现在多个发布列表中，因为一个项目可以包含多个键。每个键值只存储一次，因此对于同一个键多次出现的情况，GIN 索引非常紧凑。

    在内部，GIN 索引包含基于键构建的 B 树索引，其中每个键是一个或多个索引项的元素（例如，数组的成员）

    • 2

相关问题