使用此架构:
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
);
一个对象可以附着任意数量的标记。与object X tag
表不同,我希望将tag_id
保存在一个数组中,以便可以使用对象记录轻松获取它们。
如何在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
,限制了其用途。
2条答案
按热度按时间svmlkihl1#
您可以使用标准postgres在任何一维数组上创建GIN索引。
Details in the manual here(最后一章)。
在运行**
integer
**阵列(普通e1d1e,而不是int2
或int8
,没有NULL
值)时,additional supplied module**intarray
**提供了更多运算符,并且通常具有卓越的性能。使用以下命令安装它(每个数据库一次):您可以在整数数组上创建GIN或GIST索引。手册中有一些例子。
CREATE EXTENSION
需要PostgreSQL 9.1或更高版本。对于较旧的版本,您需要运行提供的脚本。lawou6xi2#
传统的解决方案是使用一个标记表,并在标记和对象之间使用多对多。然后,您可以为标记表建立索引,并通过联接在单个SELECT语句中获取所有内容。如果您对编程模型不满意,请咨询您当地友好的ORM供应商。
我无论如何都不是PostgreSQLMaven,但这似乎不是一个很好的数组用例。