From: Matthew Hall <mhall@mhcomputing.net>
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 11.3 \(3445.6.18\))
Subject: Re: Query with "ILIKE ALL" does not use the index
Date: Thu, 26 Jul 2018 10:22:21 -0700
References: 
 <CALghGHM72CTRyb7-ggFU-ubm3LfXqK019Y=WjP-JwzjzT8QDCA@mail.gmail.com>
 <17421.1532623454@sss.pgh.pa.us>
To: pgsql-performance@lists.postgresql.org
In-Reply-To: <17421.1532623454@sss.pgh.pa.us>
Message-Id: <460B4CB5-0D1D-43C8-829B-D7734A84779D@mhcomputing.net>
Precedence: bulk

On Jul 26, 2018, at 9:44 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>=20
> 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%']);
>=20
> There's only index support for "op ANY (array)", not "op ALL (array)".
>=20
> 			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.=