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

Question

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?

To make this more useful and Google-finable, you should indicate that the intarray extension is installed. E.g. you could change the title to "Postgres doesn't use index on integer array when intarray extension is installed" and some sentence(s) in the question's text. — sticky bit
– sticky bit, Commented Sep 21, 2020 at 16:50

Simon Perepelitsa · Accepted Answer · 2020-09-21 16:33:37Z

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"

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related