How to use Postgresql GIN index with ARRAY keyword

Question

I'd like to create GIN index on a scalar text column using an ARRAY[] expression like so:

CREATE TABLE mytab (
 scalar_column TEXT
)

CREATE INDEX idx_gin ON mytab USING GIN(ARRAY[scalar_column]);

Postgres reports an error on ARRAY keyword.

I'll use this index later in a query like so:

SELECT * FROM mytab WHERE ARRAY[scalar_column] <@ ARRAY['some', 'other', 'values'];

How do I create such an index?

Are you just trying to check if the value of scalar_column matches one of a list of values? If so, you can just use a regular btree index and filter WHERE scalar_column IN ('some', 'other', 'values') — Blue Star
– Blue Star, Commented Apr 10, 2021 at 2:40
As a workaround I've done this CREATE INDEX idx_gin ON mytab USING GIN(string_to_array(scalar_column, '')); I wonder if there is a proper way to create such an index. — nextstopsun
– nextstopsun, Commented Apr 10, 2021 at 7:47
The index and your query don't make any sense to begin with. Why do you think you need an index on an array expression? — user330315
– user330315, Commented Apr 10, 2021 at 9:58
I think that <@ operator with GIN index is faster than = ANY operator with no index. How would you speed up queries which check value against a given array? — nextstopsun
– nextstopsun, Commented Apr 10, 2021 at 18:12

Laurenz Albe · Accepted Answer · 2021-04-12 06:01:17Z

3

You forgot to add an extra pair of parentheses that is necessary for syntactical reasons:

CREATE INDEX idx_gin ON mytab USING gin ((ARRAY[scalar_column]));

The index does not make a lot of sense. If you need to search for membership in a given array, use a regular B-tree index with = ANY.

edited Apr 12, 2021 at 6:01

answered Apr 10, 2021 at 9:48

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

nextstopsun Over a year ago

Thanks. Would you please be so kind to elaborate on usefulness of this index. I'd like to speed up queries which check if scalar value is in a given array.

Laurenz Albe Over a year ago

I have elaborated.

Collectives™ on Stack Overflow

How to use Postgresql GIN index with ARRAY keyword

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related