I don't think they should optimize based solely on table access. What the function does matters as well. Now the optimizer either does not know what the functio

I don't think they should optimize based solely on table access. What the function does matters as well. Now the optimizer either does not know what the function does(it could be modifying rows, who knows?) or, in the specific case of random() it is intentionally different every single call. The only sane thing to do in this case is avoid moving it around trying to optimize it. Now if you could mark a function as "pure", that is, it's outputs are strictly dependent on it's inputs, the optimizer would be free to, well, optimize it's location. I think this is what postgres is trying to do with it's function volatility syntax(volatile, stable, immutable) one of the examples in the article, cockroachdb, has inherited this syntax. but they either drew a different conclusion as to what it means, or they ignore it.