From: Steve Atkins <steve@blighty.com>
To: PG mailing List <pgsql-general@lists.postgresql.org>
Subject: Re: Case Insensitive
Date: Thu, 28 Mar 2019 09:30:21 +0000
Message-ID: <3C4DCC9E-C7D0-4DD6-90E7-CF3C30FCE146@blighty.com> (raw)
In-Reply-To: <c316dc7f-1eed-ea88-558a-76f00ada79e8@gmail.com>
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>
> On Mar 28, 2019, at 9:08 AM, Ron <ronljohnsonjr@gmail.com> wrote:
>
> On 3/28/19 3:33 AM, Steve Atkins wrote:
>>
>>> On Mar 28, 2019, at 8:29 AM, Ron <ronljohnsonjr@gmail.com> wrote:
>>>
>>> On 3/28/19 3:23 AM, Sameer Kumar wrote:
>>> [snip]
>>>> You can write a query with upper function:
>>>>
>>>> select * from emp where upper(ename)=upper('aaa');
>>> That's a guaranteed table scan.
>> Unless you have an index on upper(ename).
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: steve@blighty.com, pgsql-general@lists.postgresql.org
Subject: Re: Case Insensitive
In-Reply-To: <3C4DCC9E-C7D0-4DD6-90E7-CF3C30FCE146@blighty.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox