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
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 |