CODEWITHSUNDEEP
sqlarrayspostgresqlquery-optimization
iamdeit
iamdeit

Reputation: 6045

How to use GIN index with array column in postgresql?

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)

Upvotes: 0

Views: 291

Answers (1)

user330315
user330315

Reputation:

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]) );

Upvotes: 2

Related Questions