Re: Query with "ILIKE ALL" does not use the index

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(at)mhcomputing(dot)net> wrote:
> On Jul 26, 2018, at 9:44 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>
>> Nicolas Even <neven(at)ztel(dot)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.