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: