Thank you, Jeff. I considered removing the intarray extension as its inclusion operators seem to be slower than the array inclusion operators (on seq scans). The only thing I need from intarray is the idx.
On Mon, Dec 21, 2015 at 2:18 PM, Maeldron T. <maeldron@gmail.com> wrote:
> test2=# explain analyze select * from test where ids && array[77]; > QUERY PLAN > ----------------------------------------------------------------------------------------------------------------- > Bitmap Heap Scan on test (cost=8.01..12.02 rows=1 width=30) (actual > time=0.013..0.013 rows=1 loops=1) > Recheck Cond: (ids && '{77}'::integer[]) > Heap Blocks: exact=1 > -> Bitmap Index Scan on test_gin (cost=0.00..8.01 rows=1 width=0) > (actual time=0.009..0.009 rows=1 loops=1) > Index Cond: (ids && '{77}'::integer[]) > Planning time: 0.049 ms > Execution time: 0.036 ms > (7 rows) > > test2=# create extension intarray; > CREATE EXTENSION > test2=# explain analyze select * from test where ids && array[77]; > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------- > Seq Scan on test (cost=10000000000.00..10000000001.04 rows=1 width=30) > (actual time=0.059..0.060 rows=1 loops=1) > Filter: (ids && '{77}'::integer[]) > Rows Removed by Filter: 2 > Planning time: 0.082 ms > Execution time: 0.067 ms > (5 rows)
intarray creates operators which take precedence over the default operators.
If you want to keep using the existing index, you have to qualify the operators with their schema:
explain analyze select * from test where ids OPERATOR(pg_catalog.&&) array[77];
If you want to use the new versions (which don't tolerate NULLS) you have to create in index for them:
create index test_gin2 on test using gin (ids gin__int_ops);
If you usually want the default version and only sometimes the intarray version, you could load intarray into some other schema which is not in your search_path, and then fully qualify the operators with their schema when you want those ones.