Re: [SQL] optimizer woes ?

>> shouldn't the optimizer convert lower('anick') to lowercase first, then
>> use that value in searching the logins_nick_idx index ?

The system only knows how to use qualifications like "var rel constant"
as index-scan restrictions.  "var rel func(constant)" is not of that
form ... but it could be if there were a preprocessing step that
recognized "func(constant)" as a constant subexpression and replaced it
by its result.

Right now, we don't have any such step.  I've been thinking about it for
6.6 though.

Bruce Momjian <maillist@candle.pha.pa.us> writes:
> We have on our TODO list:
>     * Use index with constants on functions

The TODO entry is insufficiently ambitious: it should read "implement
a general-purpose constant-subexpression-reduction step".  (Actually,
I think that TODO entry might refer to something completely different
... wasn't the complaint that you couldn't make an index on
"date_part('date', field)"? )
        regards, tom lane