如何在PostgreSQL中为数组元素创建索引？

Question

使用这个模式：

create table object (
   obj_id      serial      primary key,
   name        varchar(80) not null unique,
   description text,
   tag_arr     int[]
);

create table tag (
   tag_id      serial      primary key,
   label       varchar(20) not null unique
);

一个对象可以附加任意数量的标签。我希望用一个数组来保存tag_id，而不是使用一个object X tag表，以便可以轻松地通过对象记录获取它们。

如何在object上创建索引，使得tar_arr的每个元素都成为索引？

话虽如此，有更好的解决方法吗？

讨论：

可以通过以下方式实现：

create table obj_x_tag(
   obj_id    references object,
   tag_id    references tag,
   constraint obj_x_tag_pk primary key( obj_id, tag_id )
);

select obj_id, name, description, array_agg( tag_id )
from object o
join obj_x_tag x using( obj_id )
group by 1, 2;

但对我来说，仅仅将tag_id数组保留在一列中，并且放弃交叉表和array_agg()更加合理。

建议使用PostgresQL SQL: Converting results to array。问题在于，正如所指出的那样，“这实际上并没有索引单个数组值，而是对整个数组进行索引”。

还有人建议使用pg的intarr和gist（或gin）索引。对我而言，问题似乎在于该索引是为标准的pg基于集合的数组运算符而设计的，而不是为了优化查找数组中的一个元素，而是其中一个数组包含另一个数组，与另一个数组相交 - 对我来说，这种宽泛的解决方案对于如此狭窄的问题来说，在大小和速度上都不正确。另外，intarr扩展似乎只限于int，而不涵盖int64或char，从而限制了其有用性。

- cc young

---

你应该使用GIN，看看这个链接：https://dev59.com/RW855IYBdhLWcg3w-JQr - JoseP

采用规范化模型，并将该信息存储在包含“obj_id”和“tag_id”的表中，这将在长期运行中为您节省大量麻烦。只需考虑一个查询，即可获得标签被使用的次数。 - user330315

@a_horse_with_no_name 真的不想来回争论，但我认为，如果数组的每个元素都被索引，那么像“给出一个查询，告诉你标签被使用的次数”的东西将与交叉表格（实际上只是一个索引表格）具有相同的成本。 - cc young

@JoseP - 感谢提供链接。在问题的讨论部分中解决了使用GIN的问题。 - cc young

Answer 1

您可以在标准Postgres上创建任何一维数组的GIN索引。
详见手册中的最后一章。 当使用integer数组（纯int4，不是int2或int8且没有NULL值）时，附加提供的模块intarray提供了更多运算符和通常更好的性能。安装它（每个数据库只需一次）：

CREATE EXTENSION intarray;

您可以在整数数组上创建GIN或GIST索引。手册中有示例。点击这里查看。
要使用CREATE EXTENSION，需要PostgreSQL 9.1或更高版本。对于旧版本，您需要运行提供的脚本。

- Erwin Brandstetter

---

你真的很棒 - 再次感谢。我看了一下 intarr，不确定它是否正确 - 我已经把我的笔记放在问题的正文中了。 - cc young

Answer 2

传统的解决方案是使用标签表和标签与对象之间的多对多关系。然后，您可以索引标签表，并通过连接在单个select语句中获取所有内容。如果您不满意编程模型，请查看您当地友好的ORM供应商。

我并不是PostgreSQL专家，但这似乎不是使用数组的好用例。

- easel

---

我同意对象和标签之间的交叉表是经典解决方案 - 实际上也是我的当前解决方案。但在这种情况下，交叉表对于非常轻量级的东西来说是昂贵且繁琐的。如果数组可以被索引，那么在我看来它将是一个不错的解决方案，尽管缺乏RI。 - cc young

@ccyoung：为什么跨表是“昂贵的”。使用表存储数据是关系型数据库的用途。 - user330315

@ccyoung：只需考虑一个查询，显示每个标签被使用的频率计数。这对于数组来说会非常复杂，但对于标准化模型来说非常容易。此外，查找具有两个或三个特定标签的对象也将更加容易。 - user330315

@a_horse_with_no_name select count(1) from obj where 123456 = ANY( tag_arr ) - 如果 tag_arr 的元素被索引，那么这也是一种高效的方法。 - cc young

@a_horse_with_no_name：许多对多表的问题在于大多数RDBMS中每一行的开销非常大。在Postgres中，它的最小值为24个字节。对于整个数组来说，1D数组的开销是8个字节，这会产生很大的差异。使用unnest()函数轻松生成完整的集合。 - Jim Nasby

Answer 3

这是我的解决方法，因为我没有看到任何针对此操作进行优化的PostgreSQL内部函数

CREATE FUNCTION unnest_with_idx(anyarray) RETURNS 
table(idx integer, val anyelement) AS $$ 
   SELECT generate_series(1,array_upper($1,1)) as idx, unnest($1) as val;
$$ LANGUAGE SQL IMMUTABLE;
-- Test:
SELECT idx,val from unnest_with_idx(array[1,20,3,5]) as t;

如果要检查是否存在内部函数，请参见“如何使用PostgreSQL访问数组内部索引？”问题。

---

在@JimNasby的评论后进行了编辑

适用于pg9.4+的解决方案

SELECT * FROM unnest(array[20,11,3,5]) WITH ORDINALITY;

WITH ORDINALITY 会生成一个名为“ordinality”的新列，该列是数组索引。请参见本教程。

在pg9.5+中，它也可以很好地处理JSON数组！

 SELECT * FROM jsonb_array_elements( '[20,11,3,5]'::JSONB ) WITH ORDINALITY

- Peter Krauss

---

现在您可以使用WITH ORDINALITY选项：SELECT * FROM unnest(array) WITH ORDINALITY;。不过我不确定是哪个版本添加了这个选项。 - Jim Nasby

Answer 4

你需要使用gin__int_ops创建索引。

create index on initial_costs using gin (parking_ids gin__int_ops);

使用@>操作符。

explain analyse SELECT
    initial_costs.id,
    parking_ids
FROM initial_costs
where  initial_costs.parking_ids @> array[9459];


QUERY PLAN
Bitmap Heap Scan on initial_costs  (cost=5.12..108.41 rows=93 width=21) (actual time=0.034..0.095 rows=37 loops=1)
  Recheck Cond: (parking_ids @> '{9459}'::integer[])
  Heap Blocks: exact=26
  ->  Bitmap Index Scan on initial_costs_parking_ids_idx3  (cost=0.00..5.09 rows=93 width=0) (actual time=0.022..0.022 rows=37 loops=1)
        Index Cond: (parking_ids @> '{9459}'::integer[])
Planning Time: 0.192 ms
Execution Time: 0.124 ms

- Ryabchenko Alexander