Date: Sat, 24 Mar 2018 02:27:47 +0100
From: Peter <pmc@citylink.dinoex.sub.org>
To: pgsql-performance@lists.postgresql.org
Subject: functions: VOLATILE performs better than STABLE
Message-ID: <20180324012746.GA14631@gate.oper.dinoex.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.8.3 (2017-05-23)
Precedence: bulk


Given an arbitrary function fn(x) returning numeric.

Question: how often is the function executed?


A. 
select fn('const'), fn('const');

Answer:
Twice. 

This is not a surprize.


B.
select v,v from fn('const') as v;  [1]

Answer:
Once.


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.


[1] I seem to remember that I was not allowed to do this when I coded
my SQL, because expressions in the from clause must return SETOF, not
a single value. Now it seems to work.