如何在 PostgreSQL 中为数组的元素创建索引? [英] How to create an index for elements of an array in PostgreSQL?

问题描述

使用此架构:

创建表对象(obj_id 串行主键，名称 varchar(80) 不为空唯一，说明文字，tag_arr int[]);创建表标记(tag_id 串行主键，标签 varchar(20) not null 唯一);

一个对象可以附加任意数量的标签.我希望将 tag_id 保存在一个数组中，而不是 object X tag 表，这样它们就可以通过对象记录轻松获取.

如何在 object 上创建索引，以便 tar_arr 的每个元素都是一个索引?

也就是说，有没有更好的方法来解决这个问题?

讨论

这可以通过以下方式实现:

创建表 obj_x_tag(obj_id 引用对象，tag_id 引用标签，约束 obj_x_tag_pk 主键( obj_id, tag_id ));选择 obj_id、名称、描述、array_agg( tag_id )从对象 o使用( obj_id ) 加入 obj_x_tag x按 1、2 分组；

但对我来说，简单地将 tag_id 的数组保留在一列中并省去交叉表和 array_agg()

更有意义

建议使用PostgresQL SQL:将结果转换为数组.如前所述，问题在于这实际上并不索引单个数组值，而是索引整个数组"

还建议使用 pg 的 intarr 和 gist(或 gin)索引.问题 - 对我来说 - 似乎索引是针对标准的基于 pg 集合的数组运算符的，不一定针对查找数组的一个元素进行优化，而是针对其中一个元素进行优化数组包含另一个，与另一个相交 - 对我来说这是违反直觉的，在大小和速度方面，如此广泛的解决方案对于如此狭窄的问题是正确的.此外，intarr 扩展似乎仅限于 int，不包括 int64 或 char，限制了它的实用性.>

解决方案

您可以使用标准 Postgres 在任何一维数组上创建 GIN 索引.
手册中的详细信息(最后一章).

在使用 integer 数组(普通 int4，而不是 int2 或 int8> 并且没有 NULL 值)额外提供的模块 intarray 提供了更多的运算符和通常优越的性能.安装它(每个数据库一次):

CREATE EXTENSION intarray;

您可以在整数数组上创建 GIN 或 GIST 索引.手册中有示例.
CREATE EXTENSION 需要 PostgreSQL 9.1或以后.对于旧版本，您需要运行提供的脚本.

With this schema:

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
);

An object may have any number of tags attached. Instead of an object X tag table, I wish to keep tag_ids in an array so they can be easily fetched with the object record.

How do I create an index on object so that each element of tar_arr is an index?

That said, are there better ways to solve this problem?

Discussion

This could be achieved with:

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;

But to me it makes more sense to simply keep the array of tag_ids in a column and dispense with the cross table and array_agg()

It was suggested to use PostgresQL SQL: Converting results to array. The problem, as noted, is that "this doesn't actually index individual array values, but instead indexes the entire array"

It was also suggested to use pg's intarr and gist (or gin) index. The problem - to me - seems that the index is for the standard pg set-based array operators, not necessarily optimized for finding one element of an array, but rather where one array contains another, intersects with another - for me it's counter-intuitive that, size-wise and speed-wise, such a wide solution is correct for such a narrow problem. Also, the intarr extension seems limited to int, not covering int64 or char, limiting its usefulness.

解决方案

You can create GIN indexes on any 1-dimensional array with standard Postgres.
Details in the manual here (last chapter).

While operating with integer arrays (plain int4, not int2 or int8 and no NULL values) the additional supplied module intarray provides a lot more operators and typically superior performance. Install it (once per database) with:

CREATE EXTENSION intarray;

You can create GIN or GIST indexes on integer arrays. There are examples in the manual.
CREATE EXTENSION requires PostgreSQL 9.1 or later. For older versions you need to run the supplied script.

这篇关于如何在 PostgreSQL 中为数组的元素创建索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！