Postgres doesn't use index on integer array if intarray extension is installed

user_199607 0
Viewed 1000

I can create an index to speed up array operations.

create table test_array as
  select id, array[id, id+1, id+2]::text[] as codes
  from generate_series(1, 10000) as id;

create index test_array_idx on test_array using GIN (codes);

explain analyze
select * from test_array where codes @> array['123'];
-- Uses "Bitmap Index Scan on test_array_idx"

However, the index doesn't work with integer arrays.

create table test_intarray as
  select id, array[id, id+1, id+2] as codes
  from generate_series(1, 10000) as id;

create index test_intarray_idx on test_intarray using GIN (codes);

explain analyze
select * from test_intarray where codes @> array[123];
-- Uses "Seq Scan on test_intarray"

Why does that happen?

postgresql arrays
1 Answers

This happens if you have installed the "intarray" extension. Let's test it:

drop extension intarray;

explain analyze
select * from test_intarray where codes @> array[123];
-- Uses "Bitmap Index Scan on test_intarray_idx"

The "intarray" extension provides its own operators for integer arrays, such as @>, while the index is designed to work with the generic array operators. This can be demonstrated by using schema-qualified operators:

create extension intarray;

explain analyze
select * from test_intarray where codes @> array[123];
-- Uses "Seq Scan on test_intarray"

explain analyze
select * from test_intarray where codes operator(pg_catalog.@>) array[123];
-- Uses "Bitmap Index Scan on test_intarray_idx"

See this discussion for more details: Overloaded && operator from intarray module prevents index usage.

If you still want to take advantage of "intarray" extension, you can specify its own operator class "gin__int_ops" when creating an index (instead of the default "array_ops"):

create index test_intarray_idx2 on test_intarray using GIN (codes gin__int_ops);

explain analyze
select * from test_intarray where codes @> array[123];
-- Uses "Bitmap Index Scan on test_intarray_idx2"
user_1996070
Related