Thread: Query with "ILIKE ALL" does not use the index
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!
Nicolas Even <neven@ztel.org> writes: > 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%']); There's only index support for "op ANY (array)", not "op ALL (array)". regards, tom lane
On Jul 26, 2018, at 9:44 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Nicolas Even <neven@ztel.org> writes: >> 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%']); > > There's only index support for "op ANY (array)", not "op ALL (array)". > > regards, tom lane Nicolas, Could you work around the limitation with a two-clause WHERE? First clause ANY, second clause ALL. I've done some similar tricks on similar sorts of queries. Matthew.
Hi Matthew, I finally used "WHERE name ILIKE arr[1] AND name ILIKE ALL(arr)" which works well enough for my use case. Thank you Nicolas On 26 July 2018 at 19:22, Matthew Hall <mhall@mhcomputing.net> wrote: > On Jul 26, 2018, at 9:44 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> >> Nicolas Even <neven@ztel.org> writes: >>> 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%']); >> >> There's only index support for "op ANY (array)", not "op ALL (array)". >> >> regards, tom lane > > Nicolas, > > Could you work around the limitation with a two-clause WHERE? > > First clause ANY, second clause ALL. > > I've done some similar tricks on similar sorts of queries. > > Matthew.
Thank you Tom On 26 July 2018 at 18:44, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Nicolas Even <neven@ztel.org> writes: >> 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%']); > > There's only index support for "op ANY (array)", not "op ALL (array)". > > regards, tom lane