Re: indexing with lower(...) -> queries are not optimised very well - Please Help

Martin Hampl <Martin(dot)Hampl(at)gmx(dot)de> writes:
> Am 21.11.2003 um 06:54 schrieb Tom Lane:
>> [ bad plan for use of a functional index ]
>>
>> The rows estimate (and therefore also the cost estimate) is a complete
>> guess in this situation, because the system keeps no statistics about
>> the values of lower(word). Improving this situation is on the TODO
>> list.

> Any ideas when this will work? Is it difficult to implement?

It strikes me as a small-but-not-trivial project. Possibly someone will
get it done for 7.5. You can find some discussion in the pghackers
archives, IIRC (look for threads about keeping statistics on functional
indexes).

This brings up a thought for Mark Cave-Ayland's project of breaking out
the datatype dependencies in ANALYZE: it would be wise to ensure that
the API for examine_attribute doesn't depend too much on the assumption
that the value(s) being analyzed are part of the relation proper. They
might be coming from a functional index, or even more likely being
computed on-the-fly based on the definition of a functional index.
Not sure what we'd want to change exactly, but it's something to think
about before the API gets set in stone.

regards, tom lane