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(at)candle(dot)pha(dot)pa(dot)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