Re: Index optimization ?

On Sun, 2005-01-16 at 16:25 +0100, Bo Lorentsen wrote:
[about a volatile function in a where clause not generating index scan]

> Will the only possible way to fix this be to make a volatile function
> with a return type (I know this is not possible now, but in theory) ?

this has nothing to do with the return type. a volatile function is a
function that is not garanteed to return the same value given same
input parameters, (such as currval()).

when a volatile function is used thus:
SELECT * FROM mytable WHERE col=myvolatilefunc();
the planner must call the function once per table row, and assume
possibly different return values each time, so an indexscan will
not improve timings.

on the other hand, if the function is labeled STABLE, the planner
can assume that the same value will alway be returned, so only
one call to it can be made, and an indexscan might be found the
most effective.

hope this helps

gnari