GIN index on array column not used, even after setting `enable

As recommended by this comment, I built an intarray GIN index. I even set local enable_seqscan = 'off';. Still, when I EXPLAIN my query, it is doing the sequential scan. For demonstration, I created a dummy table called deletethis.

devapp=> 
devapp=> \d deletethis;
              Table "public.deletethis"
 Column |   Type    | Collation | Nullable | Default 
-------- ----------- ----------- ---------- ---------
 col1   | integer[] |           |          | 
Indexes:
    "deletethis_idx" gin (col1 gin__int_ops)

devapp=> 
devapp=> 
devapp=> BEGIN; set local enable_seqscan = False; SHOW enable_seqscan; EXPLAIN SELECT * from deletethis where 1 = ANY(col1);  COMMIT;
BEGIN
SET
 enable_seqscan 
----------------
 off
(1 row)

                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Seq Scan on deletethis  (cost=10000000000.00..10000000040.60 rows=7 width=32)
   Filter: (1 = ANY (col1))
(2 rows)

COMMIT
devapp=>

Why is it still doing Seq Scan and not using the index despite the enable_seqscan having been set to off?

CodePudding user response：

As I mentioned in another answer to the same question you referenced, the ANY construct cannot tap into a GIN index:

Can PostgreSQL index array columns?

This can use your index:

SELECT * FROM deletethis WHERE col1 @> '{1}';

'{1}' being an array literal. An array constructor (ARRAY[1]) or any variable or column of type integer[] would work, too.

See:

An explicit type cast for the input is optional because the assignment cast for the literal as well as the default type for the constructor happen to work for us.

But while using operator classes of the additional module intarray, be careful to operate with integer (int4) and nothing else. Related: