Re: Index optimization ?

Michael Glaesemann wrote:

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

What you say is that PG can't see the difference between this
"plus_random" and the "currval", right.

But if I have a select (a quite strange one), like this :

SELECT * FROM test_table WHERE id = plus_random( test_col );

I don't understand the problem. The function always return an integer as
specified in the function decl. so why not use the PK index for search,
instead of using seq scan ? The value is totally unpredictable but it is
still an integer and the pk index is still useful regarding performance !

I know there is something I don't understand, so I just have to ask :-)

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

No this time I think missed the point, I understand the volatility of
functions, so the planer know what to expect from the function,
regarding side effect, but I still don't understand why this influences
the choice of valid indexes.

/BL