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@pictus.org> <orbis@unreal.cz> +420 411 825144
Unreal Technology sro., Dobrin 118, 41301 Roudnice n. L. +420 411 825111