From: | Nicolas Even <neven(at)ztel(dot)org> |
---|---|
To: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Query with "ILIKE ALL" does not use the index |
Date: | 2018-07-26 15:53:33 |
Message-ID: | CALghGHM72CTRyb7-ggFU-ubm3LfXqK019Y=WjP-JwzjzT8QDCA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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!
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2018-07-26 16:44:14 | Re: Query with "ILIKE ALL" does not use the index |
Previous Message | MichaelDBA | 2018-07-26 12:05:10 | Re: Automated bottleneck detection |