Re: Index optimization ?

On Jan 17, 2005, at 0:25, Bo Lorentsen wrote:

> Tom Lane wrote:
>
>> http://developer.postgresql.org/docs/postgres/xfunc-volatility.html
>>
> Ok, thanks I see why there is these three differant function types,
> but I don't quite understand why the value from a volatile function,
> can't be used as a index key. Is this because there is no return type
> garanti, for the voilatile function too ?

I don't believe it has necessarily anything to do with the return type,
but rather the return value. An index only works if you know what the
value is, and the return value for a volatile function is not
guaranteed to be the same for given parameters. Here's a contrived (and
untestsd) example, but one I think makes it clear:

CREATE FUNCTION plus_random ( INTEGER )
RETURNS INTEGER
LANGUAGE SQL AS '
SELECT round( $1 + random() * 100 );
';

One could conceivably attempt to make a functional index using
plus_random(), but the result it gives every time is indeterminant. How
would you be able to usefully search for values in an index that is
based on this function? Would it make sense do to do so?

Does this help? (And if I'm completely off base, someone please let me
know :)

Michael Glaesemann
grzm myrealbox com