Can the Postgres Overlay Overlay Operator (&&) use an index?

Question

We have a table with an indexed array column:

CREATE TABLE mention ( id SERIAL, phraseIds integer[], PRIMARY KEY (id) ); CREATE INDEX indx_mentions_phraseIds on mention USING GIN (phraseids public.gin__int_ops); 

Queries using the "overlap" operator in this column do not seem to use the index:

 explain analyze select m.id FROM mention m WHERE m.phraseIds && ARRAY[11638,11639]; Seq Scan on mention m (cost=0.00..933723.44 rows=1404 width=4) (actual time=103.018..3751.525 rows=1101 loops=1) Filter: (phraseids && '{11638,11639}'::integer[]) Rows Removed by Filter: 7019974 Total runtime: 3751.618 ms 

Can I use Postgresql to use the index? Or should we do something else?

Update: I repeated the test by pressing "SET enable_seqscan TO off" and the index is still not in use.

Update: I should have mentioned that I am using 9.2 with the intarray extension.

Update. It looks like the intarray extension is part of this problem. I re-created the table without using the intarray extension, and the index is used as expected. Does anyone know how to get the index to be used with the intarray extension? The docs ( http://www.postgresql.org/docs/9.2/static/intarray.html ) say indexes are supported for & &.

Answer 1

I built a similar table in PostgreSQL 9.2; the difference was USING GIN (phraseids); It looks like I don't have int_ops available in this context. I downloaded several thousand lines of random (ish) data.

Setting enable_seqscan off allows PostgreSQL to use the index.

PostgreSQL has calculated the cost of sequential scanning less than the cost of scanning a heap of a raster map. The actual sequential scan time was 10% of the actual scan time of the raster map heap, but the total execution time for sequential scanning was slightly longer than the total scan time of the bitmap heap.