> 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
expressionindex.
The expression in an expression index has to be immutable, but upper() is - it will always give the same output for a
giveninput. (For values of "always" that probably depend on not performing major surgery on collations, but that falls
intothe "lie to the planner, get rotten results" category).
Check "\df+ upper"
Cheers,
Steve