While testing a query on an integer array with a GIN index, I stumbled over a behaviour which surprised me and which I
wouldconsider a bug - but maybe I am wrong.
Consider the following table:
create table idlist (ids int[], ... other columns ...);
create index on idlist using gin (ids array_ops);
Now the following query works fine and uses the index:
select *
from idlist
where ids && array[1,2,3,4,6];
The above yields the following execution plan:
Bitmap Heap Scan on public.idlist (cost=1748.30..70780.07 rows=423281 width=240) (actual time=81.321..161.195
rows=423281loops=1)
Output: ids
Recheck Cond: (idlist.ids && '{1,2,3,4,6}'::integer[])
Heap Blocks: exact=67084
Buffers: shared hit=67196
-> Bitmap Index Scan on idlist_ids_idx (cost=0.00..1642.48 rows=423281 width=0) (actual time=70.764..70.764
rows=423281loops=1)
Index Cond: (idlist.ids && '{1,2,3,4,6}'::integer[])
Buffers: shared hit=112
Planning Time: 0.178 ms
Execution Time: 171.245 ms
But when I ran that on a database where the intarray extension is installed (and part of the search_path), Postgres
usesthe intarray operator which can't use the GIN index with the array_ops opclass, so there the query yields the
followingexecution plan:
Seq Scan on public.idlist (cost=0.00..76127.00 rows=423281 width=240) (actual time=0.021..5046.396 rows=423281
loops=1)
Output: ids
Filter: (idlist.ids && '{1,2,3,4,6}'::integer[])
Rows Removed by Filter: 1576719
Buffers: shared hit=67127
Planning Time: 0.123 ms
Execution Time: 5056.144 ms
I can work around that, using "OPERATOR(pg_catalog.&&)" instead of "&&", but that seems like a kludge to me.
The above happens even if the intarray extension is a the end of the search path, e.g. "set search_path = public,
intarray".
If I set the search path to only "public", the the index is used again.
I tried the above with Postgres 11.2 on Windows and CentOS
Is this expected behaviour? Is this caused by the Postgres core (e.g. the optimizer to taking the opclass into account)
oris it a "problem" in the way the intarray module defines its operators?
I would have expected that the optimizer uses the operator that matches the opclass for the index, or at least the
"first"one found in the search path.
Any ideas?
Thomas