cc young cc young - 6 months ago 23
SQL Question

How to create an index for elements of an array in PostgreSQL?

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_id
s 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_id
s 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.

Answer

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

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

CREATE EXTENSION intarray;

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.

Comments