How to use GIN index with array column in postgresql?

Question

I have a table that looks similar to this:

id uuid
ancestor_ids text[]

I need to perform a query to retrieve the rows where the first element in the ancestor_ids array is any of the input values. The query looks something like this:

select * from table where ancestor_ids[1] in ('multiple', 'input', 'values');

The query is working fine but I'd like to know if adding an index to the ancestor_ids column would improve the performance given the fact I'm not using any special operator. Is it possible to optimize the query at all? (table re-design is not an option)

score 2 · Accepted Answer · 2021-01-28 14:54:51Z

2

If it's always the first element, no need for a GIN index. A standard B-Tree will do:

create index on the_table ( (ancestor_ids[1]) );

edited Jan 28, 2021 at 14:54

answered Jan 28, 2021 at 6:34

user330315

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

3 Comments

iamdeit Over a year ago

I'm getting an error when trying to create this index: ERROR: syntax error at or near ")", is it possible to index specific array positions?

user330315 Over a year ago

@iamdeit: I missed a closing ) - see my edit

iamdeit Over a year ago

It worked! I didn't know it needed double parenthesis. Thanks!

Collectives™ on Stack Overflow

How to use GIN index with array column in postgresql?

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related