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

Question

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.

Answer 1

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.

Answer 2

The traditional solution would be to use a table of tags and a many-many between tag and object. Then you can index the tag table and pull everything in a single select statement via a join. If you're not happy with the programming model, check out your local friendly ORM vendor.

I'm not a PostgreSQL expert by any means, but this doesn't seem like a good use case for arrays.

Answer 3

This is my workaround, because I see no PostgreSQL optimized internal function for do the same,

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;

For check if exists an internal function, see "How to acess array internal index with postgreSQL?" question.

---

Edited after @JimNasby comment

Solution for pg9.4+

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

the WITH ORDINALITY produces a new column "ordinality" that is the array-index. See also this tutorial.

In pg9.5+, it works fine also for JSON arrays!

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