Query with "ILIKE ALL" does not use the index

Поиск
Список
Период
Сортировка
От Nicolas Even
Тема Query with "ILIKE ALL" does not use the index
Дата
Msg-id CALghGHM72CTRyb7-ggFU-ubm3LfXqK019Y=WjP-JwzjzT8QDCA@mail.gmail.com
обсуждение исходный текст
Ответы Re: Query with "ILIKE ALL" does not use the index  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
Hi,

I have the following table:

                         Table "public.totoz"
  Column   |           Type           | Collation | Nullable | Default
-----------+--------------------------+-----------+----------+---------
 name      | character varying(512)   |           | not null |
Indexes:
    "totoz_pkey" PRIMARY KEY, btree (name)
    "totoz_name_trgrm_idx" gin (name gin_trgm_ops)



When I run the following query, it uses the totoz_name_trgrm_idx as expected:

explain analyze select name from totoz where name ilike '%tot%';
                                                          QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on totoz  (cost=48.02..59.69 rows=3 width=11)
(actual time=0.205..0.446 rows=88 loops=1)
   Recheck Cond: ((name)::text ~~* '%tot%'::text)
   Heap Blocks: exact=85
   ->  Bitmap Index Scan on totoz_name_trgrm_idx  (cost=0.00..48.02
rows=3 width=0) (actual time=0.177..0.177 rows=88 loops=1)
         Index Cond: ((name)::text ~~* '%tot%'::text)
 Planning time: 0.302 ms
 Execution time: 0.486 ms
(7 rows)



However when I run the same (as far as I understand it) query but with
the ALL operator, the index is not used:

explain analyze select name from totoz where name ilike all(array['%tot%']);
                                                         QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using totoz_pkey on totoz  (cost=0.29..1843.64 rows=3
width=11) (actual time=3.854..20.757 rows=88 loops=1)
   Filter: ((name)::text ~~* ALL ('{%tot%}'::text[]))
   Rows Removed by Filter: 30525
   Heap Fetches: 132
 Planning time: 0.230 ms
 Execution time: 20.778 ms
(6 rows)


I'd have expected the second query to use the totoz_name_trgrm_idx but
it doesn't. Why is that?

Thanks for your help!


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

Предыдущее
От: MichaelDBA
Дата:
Сообщение: Re: Automated bottleneck detection
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Query with "ILIKE ALL" does not use the index