Re: functions: VOLATILE performs better than STABLE

On Sun, Mar 25, 2018 at 12:00 AM, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
> Peter wrote:
>> Given an arbitrary function fn(x) returning numeric.
>>
>> Question: how often is the function executed?
>> [...]
>> C.
>> select v.v,v.v from (select fn('const') as v) as v;
>>
>> Answer:
>> Once if declared VOLATILE.
>> Twice if declared STABLE.
>>
>> Now this IS a surprize. It is clear that the system is not allowed to
>> execute the function twice when declared VOLATILE. It IS ALLOWED to
>> execute it twice when STABLE - but to what point, except prolonging
>> execution time?
>>
>> Over all, VOLATILE performs better than STABLE.
>
> The reason is that the subquery with the VOLATILE function can be
> flattened; see the EXPLAIN (VERBOSE) output.
>
> There is not guarantee that less volatility means better performance.

I think you have it backwards. The STABLE query is flattened into
something like:

select fn('const'), v fn('const') v;

The VOLATILE version can't be flattened that way since it's forced to
execute as the user sees it (one for the inner query).

You can probably get the fast plan via:

select v.v,v.v from (select fn('const') as v offset 0) as v;

The contents of the function fn() also matter very much here as we
would want to know if the function is a candidate for inlining.

merlin