Postgres Pro
Downloads
Home   >   mailing lists

Thread: GIN Expression Indexes

GIN Expression Indexes

From
Taylor Sarrafian
Date:
Hi friends!

We tried to index an array function like this:

  CREATE INDEX my_arr_index ON my_table USING GIN ( my_arr_func( my_col ) )

But the planner can’t seem to figure out how to use the index with any query! It doesn’t work with any array operators.

  SELECT * FROM my_table WHERE ARRAY[ 123 ]::INT[] && my_arr_func( my_col )

Even weirder, an empty array correctly uses the index:

  SELECT * FROM my_table WHERE ARRAY[]::INT[] && my_arr_func( my_col )

Is there any way to coerce Postgres into using this index? Thanks!

ts

Re: GIN Expression Indexes

From
Adrian Klaver
Date:
On 4/28/20 1:58 PM, Taylor Sarrafian wrote:
> Hi friends!
> 
> We tried to index an array function like this:
> 
>    CREATE INDEX my_arr_index ON my_table USING GIN ( my_arr_func( my_col ) )
> 
> But the planner can’t seem to figure out how to use the index with any 
> query! It doesn’t work with any array operators.
> 
>    SELECT * FROM my_table WHERE ARRAY[ 123 ]::INT[] && my_arr_func( my_col )
> 
> Even weirder, an empty array correctly uses the index:
> 
>    SELECT * FROM my_table WHERE ARRAY[]::INT[] && my_arr_func( my_col )
> 
> Is there any way to coerce Postgres into using this index? Thanks!

Well without:

1) Table schema.

2) EXPLAIN ANALYZE for the query(s)

there is really not information to formulate an answer.

> 
> ts


-- 
Adrian Klaver
adrian.klaver@aklaver.com