Oliver Jowett <oliver@opencloud.com> writes:
> At execution time the executor can cache the results of expressions
> flagged as constant at execution, assuming there's somewhere safe to
> cache the result for just that execution (ExprState?).
That would be the problem; there isn't anyplace appropriate. I'm not
sure this is really worth pursuing...
> I took a quick look through the executor code, but couldn't see where
> STABLE function results are cached (for the same arguments).
They aren't. STABLE is not a cache-enabling modifier: what it is
actually for is to license the function to be used in an indexscan
qualification. Consider
where timestampcol > now() - '1 day';
If now() were considered volatile (ie, we had no guarantees at all about
its behavior --- consider random() as an example) then we could not
generate an indexscan on timestampcol, because an indexscan assumes that
the compared-to value is going to hold still throughout the indexscan.
Remember that the logical model defined by the SQL spec is that the
WHERE condition is evaluated at every row of the table --- we can only
optimize this if we can be sure that we know what the optimized-away
evaluations would produce.
This is why the definition of STABLE refers to holding still throughout
a table scan, rather than other reasonable alternatives one might
consider (such as being constant throughout one transaction).
I suppose you could envision the indexscan machinery as caching the
right-hand-side value in the index ScanKey, but this is far from being
a general purpose expression caching mechanism.
regards, tom lane