Postgresql – Why is this (postgres) composite gin(scalar, scalar, array gin__int_ops) index not using the array criteria

indexindex-tuningpostgresql

I have a table

create table r_prot_search(
    openprot_release_id integer,
    annotation_group_sig text,
    dataset_ids integer[]
);

With a composite index:

create index my_index on r_prot_search using gin(
  openprot_release_id,annotation_group_sig,dataset_ids gin__int_ops
);

The table contains a huge number of rows, and the following query is not filtering using the criteria on column "dataset_ids"

explain
  select count(*)
  from r_prot_search q
  where q.openprot_release_id = 75
    and q.annotation_group_sig = '117:118'
    and 195 = any (q.dataset_ids);

Aggregate  (cost=2211461.49..2211461.50 rows=1 width=8)
  ->  Bitmap Heap Scan on r_prot_search q  (cost=9619.83..2211459.47 rows=810 width=0)
        Recheck Cond: ((openprot_release_id = 75) AND (annotation_group_sig = '117:118'::text))
        Filter: (195 = ANY (dataset_ids))
        ->  Bitmap Index Scan on idxps_ds_2_15  (cost=0.00..9619.62 rows=837562 width=0)
              Index Cond: ((openprot_release_id = 75) AND (annotation_group_sig = '117:118'::text))

Any idea how to improve performance ?

Best Answer

It turns out the index gets used with the following array operator:

q.dataset_ids @> ARRAY[195]