I'm using PostgreSQL 9.5. I have a table with column of array type.
create table test (
id serial primary key,
arr text[] not null
)
Now I want to issue a query like
select id from test where 'value' = any(arr)
This query uses full-scan. I need to optimize it, because table might be large and 'value' occurs only on a few rows.
I tried to create index:
create index on test(arr);
But postres still uses full-scan.
If I rewrite query to
select id from test where array['value'] = arr;
then it uses index, but it's a different query. I think it happens, because postgres indexes array value as a whole. Is it possible to index individual array items?
I understand that I could make two tables with one-to-many relationship, but I need this structure.
