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

Rostislav Opocensky <orbis@pictus.org> writes:
> 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

> 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?

Hmm, actually the optimizer should/does regard those as constants within
subsequent queries (internally they are Params instead of Consts, but
that's supposed to be OK).  What I find here is that the optimizer does
consider an indexscan for this query, but there's a bug in its
selectivity estimation routine that causes it not to recognize the
BETWEEN clause as being a range restriction --- and that means it
produces a fairly high cost estimate for the indexscan.  I still got
an indexscan plan for a small test table, but on a larger table you
might not get one.

I've applied the attached patch for 7.0.1 --- if you are in a hurry,
you may care to apply it to your local copy.  It just tweaks the range-
query recognizer to accept Param as well as Const nodes.

			regards, tom lane