Can I index an (unnest) array?

Question

I want to write something like

create index on thread(unnest(tags));

But I get the error

ERROR: index expression cannot return a set

Basically I want to find all docs that have the tagid of 2 in them (see my other question for details) and I'm assuming the below acts like a blob(I won't find [1,2] because 1 isn't in my query)

create index on thread(tags);

[Tip](https://www.postgresql.org/docs/current/static/arrays.html): *Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.* — pozs, Apr 05 '17 at 07:40
@pozs: Another user linked me [to this](http://www.databasesoup.com/2015/01/tag-all-things.html) which shows what your suggesting has poor performance and text[] has better (however my tags has metadata so I can't use text[]) — , Apr 05 '17 at 18:54

score 6 · Answer 1 · answered Apr 05 '17 at 07:22

6

Use the GIN index type:

CREATE INDEX ON thread USING gin (tags);

... and then query the table with the @> operator:

SELECT * FROM thread WHERE tags @> ARRAY[2];

answered Apr 05 '17 at 07:22

Nick Barnes

19,816
3
51
63

Just a few questions. What happens if I don't write `USING gin`? From what I saw before gin seems to be a hash? What happens if I'm using an index/int does it use the int value instead? – Apr 05 '17 at 19:37
@acidzombie: If you don't say `USING gin`, you get a `btree` instead, and the index will only be used by queries involving `=` / `<` / `>` comparisons. Internally, I think a GIN index is just a B-tree, but its keys are the array members rather than the arrays themselves. You can read more about the implementation in the [docs](https://www.postgresql.org/docs/current/static/gin-implementation.html). – Nick Barnes Apr 05 '17 at 23:44
Thanks nick this sounds perfect. I'm mostly nervous bc IDK what to expect. It looks like (from you and docs) the default btree must match the key/array exactly and this doc says it can use multiple index for one row. It sounds perfect. Thanks – Apr 06 '17 at 02:22

score 1 · Answer 2 · answered Apr 05 '17 at 07:12

if you know the exact value you are looking for, you can use smth like

create index on thread(tags @> '{2}');

and if you have higher version (>=9.5) of postgres, you can use array_position

and answering your question: you can't use unnest as function for index

Can I index an (unnest) array?

2 Answers2