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

问题描述

通过这个模式：

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

这是对象可具有任何数量的附加的标记。相反，一个对象X标记表，我想保留 TAG_ID S IN数组，使他们能够用轻松获取对象记录。

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.

我如何创建对象指数，这样的每个元素 tar_arr 是一个指数？

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?

这可能与实现：

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（）

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

有人建议使用<一个href=\"http://stackoverflow.com/questions/5723851/postgresql-sql-converting-results-to-array\">PostgresQL SQL：转换结果阵列。这个问题，如上所述，是，这实际上并不索引单个阵列的价值，而是索引整个阵列

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"

也有人建议用皮克的将intArr 和要点（或杜松子酒）的索引。这个问题 - 我 - 看来，指数是标准的PG设置基于阵列的运营商，的不的寻找的有一个的数组元素，而是其中一个必然的优化阵列的包含的另一个的相交的另一个 - 对我来说这是反直觉的，大小明智和速度的角度来看，这样一个广泛的解决方案是这样一个狭窄的问题，正确的。此外，将intArr 扩展似乎仅限于 INT ，不覆盖的Int64 或字符，限制了它的实用性。

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.

推荐答案

您可以创建标准的Postgres任何一维数组GIN索引。结果
离子手动（最后一章）。

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

在整数数组操作额外提供的模块intarray 提供很多运营商。与安装（每个数据库一次）

While operating with integer arrays the additional supplied module intarray provide a lot more operators. Install it (once per database) with

CREATE EXTENSION intarray;

您可以创建整型数组GIN或GIST索引。还有就是手册中的例子。结果
创建扩展 需要的PostgreSQL 9.1或更高版本。对于旧版本需要运行提供的脚本。

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

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