3

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Orwell
  • 37
  • 4

1 Answers1

4

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

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228