Overloaded && operator from intarray module prevents index usage.

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Overloaded && operator from intarray module prevents index usage.
Date: 2019-02-28 07:47:11
Message-ID: q583lv$5t8m$1@blaine.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

While testing a query on an integer array with a GIN index, I stumbled over a behaviour which surprised me and which I would consider 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=423281 loops=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=423281 loops=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 uses the intarray operator which can't use the GIN index with the array_ops opclass, so there the query yields the following execution 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) or is 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Steele 2019-02-28 08:40:30 Re: Barman disaster recovery solution
Previous Message Achilleas Mantzios 2019-02-28 07:21:55 Re: Barman disaster recovery solution