7

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.

Improve this question
4
  • You should use GIN check this out stackoverflow.com/questions/4058731/… Commented Jun 3, 2012 at 3:15
  • Go for a normalized model and store that information in a table containing the obj_id and the tag_id that will save you a lot of trouble in the long run. Just think of a query that gives you the number how often a tag was used. Commented Jun 3, 2012 at 7:10
  • @a_horse_with_no_name really don't want to get into a back and forth, but seems to me that, if each element of an array were indexed, then things like "a query that gives you the number how often a tag was used" would be the same cost as a cross table (which is really just an index table itself). Commented Jun 3, 2012 at 9:32
  • @JoseP - thanks for the link. addressed using GIN in added Discussion part of the question. Commented Jun 3, 2012 at 9:33

4 Answers 4

Reset to default
9

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

you are really good - thanks again. looked at intarr and do not know that it is correct - have placed my notes in the body of the question.
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.

Improve this answer

7 Comments

agree that a cross table between object and tag is the classical solution - indeed my current solution. but in this case a cross table is expensive and cumbersome for something very light weight. if arrays could be indexed, then seems to me it would be a good solution, despite the lack of RI.
@ccyoung: why is a cross table "expensive". Using tables to store data is what relational databases are made for.
@a_horse_with_no_name I am somewhat of a database bigot, as I suspect you probably are. but I also like many of non-classical elements such as nested tables and using array_agg() function to pull in using one row when the child rows are trivial. to me this makes sense and makes life easier. have expanded question to give better examples.
@ccyoung: just think of a query that shows the count how often each tag was used. That'll be utterly complex with an array but a very easy with a normalized model. Also finding objects that have two or three specific tags will be a lot easier as well.
@a_horse_with_no_name select count(1) from obj where 123456 = ANY( tag_arr ) - if tag_arr elements are indexed, it would be efficient as well
|
0

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
Improve this answer

1 Comment

You can now use the WITH ORDINALITY option: SELECT * FROM unnest(array) WITH ORDINALITY;. Not sure what version added that though.
0

you need to create index with gin__int_ops

create index on initial_costs using gin (parking_ids gin__int_ops);

and use @> operator

explain analyse SELECT
    initial_costs.id,
    parking_ids
FROM initial_costs
where  initial_costs.parking_ids @> array[9459];


QUERY PLAN
Bitmap Heap Scan on initial_costs  (cost=5.12..108.41 rows=93 width=21) (actual time=0.034..0.095 rows=37 loops=1)
  Recheck Cond: (parking_ids @> '{9459}'::integer[])
  Heap Blocks: exact=26
  ->  Bitmap Index Scan on initial_costs_parking_ids_idx3  (cost=0.00..5.09 rows=93 width=0) (actual time=0.022..0.022 rows=37 loops=1)
        Index Cond: (parking_ids @> '{9459}'::integer[])
Planning Time: 0.192 ms
Execution Time: 0.124 ms
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.