1

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 ?

1 Answer 1

1

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

q.dataset_ids @> ARRAY[195] 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.