Overloaded && operator from intarray module prevents index usage.

Поиск
Список
Период
Сортировка
От Thomas Kellerer
Тема Overloaded && operator from intarray module prevents index usage.
Дата
Msg-id q583lv$5t8m$1@blaine.gmane.org
обсуждение исходный текст
Ответы Re: Overloaded && operator from intarray module prevents index usage.  (Andrew Gierth <andrew@tao11.riddles.org.uk>)
Re: Overloaded && operator from intarray module prevents index usage.  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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
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



В списке pgsql-general по дате отправления:

Предыдущее
От: Achilleas Mantzios
Дата:
Сообщение: Re: Barman disaster recovery solution
Следующее
От: David Steele
Дата:
Сообщение: Re: Barman disaster recovery solution