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

From: Matthew Hall
Subject: Re: Query with "ILIKE ALL" does not use the index
Date: ,
Msg-id: 460B4CB5-0D1D-43C8-829B-D7734A84779D@mhcomputing.net
(view: Whole thread, Raw)
In response to: Re: Query with "ILIKE ALL" does not use the index  (Tom Lane)
Responses: Re: Query with "ILIKE ALL" does not use the index  (Nicolas Even)
List: pgsql-performance

Tree view

Query with "ILIKE ALL" does not use the index  (Nicolas Even, )
 Re: Query with "ILIKE ALL" does not use the index  (Tom Lane, )
  Re: Query with "ILIKE ALL" does not use the index  (Matthew Hall, )
   Re: Query with "ILIKE ALL" does not use the index  (Nicolas Even, )
  Re: Query with "ILIKE ALL" does not use the index  (Nicolas Even, )

On Jul 26, 2018, at 9:44 AM, Tom Lane <> wrote:
>
> Nicolas Even <> 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.


pgsql-performance by date:

From: Nicolas Even
Date:
Subject: Re: Query with "ILIKE ALL" does not use the index
From: Dinesh Kumar
Date:
Subject: Performance difference in accessing differrent columns in a Postgres Table