From: Steve Atkins <steve@blighty.com>
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 12.2 \(3445.102.3\))
Subject: Re: Case Insensitive
Date: Thu, 28 Mar 2019 09:30:21 +0000
References: 
 <CAGuFTBU1fACZWYdVwU-HiBJWAt2QFRa9fkB+SovmVMoUjVbekw@mail.gmail.com>
 <CADp-Sm4RWjbFQix1_XH7hstj6FY-EZj-f2veSgR_Z7Ut=tdDEA@mail.gmail.com>
 <c86c6b09-31fa-f27d-b373-3d604d4ca2a0@gmail.com>
 <150586A3-A8F4-4B80-9A4B-310DE265B4B3@blighty.com>
 <c316dc7f-1eed-ea88-558a-76f00ada79e8@gmail.com>
To: PG mailing List <pgsql-general@lists.postgresql.org>
In-Reply-To: <c316dc7f-1eed-ea88-558a-76f00ada79e8@gmail.com>
Message-Id: <3C4DCC9E-C7D0-4DD6-90E7-CF3C30FCE146@blighty.com>
Precedence: bulk


> On Mar 28, 2019, at 9:08 AM, Ron <ronljohnsonjr@gmail.com> wrote:
>=20
> On 3/28/19 3:33 AM, Steve Atkins wrote:
>>=20
>>> On Mar 28, 2019, at 8:29 AM, Ron <ronljohnsonjr@gmail.com> wrote:
>>>=20
>>> On 3/28/19 3:23 AM, Sameer Kumar wrote:
>>> [snip]
>>>> You can write a query with upper function:
>>>>=20
>>>> select * from emp where upper(ename)=3Dupper('aaa');
>>> That's a guaranteed table scan.
>> Unless you have an index on upper(ename).
>=20
> Are you sure?  I thought the lpart had to be immutable for the query =
optimizer to decide to use an index (and upper(ename) is mutable).

Yeah. Case insensitive searches like this are pretty much the first =
example given for why you might want to use an expression index.

The expression in an expression index has to be immutable, but upper() =
is - it will always give the same output for a given input. (For values =
of "always" that probably depend on not performing major surgery on =
collations, but that falls into the "lie to the planner, get rotten =
results" category).

Check "\df+ upper"

Cheers,
  Steve