> On Mar 28, 2019, at 9:08 AM, Ron <ronljohnsonjr@xxxxxxxxx> wrote:
>
> On 3/28/19 3:33 AM, Steve Atkins wrote:
>>
>>> On Mar 28, 2019, at 8:29 AM, Ron <ronljohnsonjr@xxxxxxxxx> 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
