Re: Function-based index not used in a simple query

On Tue, 30 May 2000, Tom Lane wrote:

> The problem here is that the optimizer will only consider an indexscan
> for a clause that looks like index_key OP constant. It doesn't think
> that trunc_to_day('28.5.2000') is a constant, because you haven't told
> it that it can pre-evaluate that function call --- and for all it knows,
[...]

Dear Tom,

thank you for the precise explanation for my problem!

I'll consider having my index function return a `date'. Still one thing
remains unclear to me: why the optimizer doesn't use an indexscan in the
stored procedure I have attached to my previous post. The condition looks
like WHERE trunc_to_day(timestamp) BETWEEN var1 AND var2. var1 and var2
get their values from calling the `volatile' function trunc_to_day, but
from then on, their values can't be changed during the execution of the
query. Is it possible to give the optimizer a hint about it?

Best regards
Orbis

--
Rostislav Opocensky <orbis(at)pictus(dot)org> <orbis(at)unreal(dot)cz> +420 411 825144
Unreal Technology sro., Dobrin 118, 41301 Roudnice n. L. +420 411 825111