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
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)
