functions: VOLATILE performs better than STABLE

From: Peter <pmc(at)citylink(dot)dinoex(dot)sub(dot)org>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: functions: VOLATILE performs better than STABLE
Date: 2018-03-24 01:27:47
Message-ID: 20180324012746.GA14631@gate.oper.dinoex.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


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.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Andres Freund 2018-03-24 01:35:52 Re: Slow planning time for custom function
Previous Message bk 2018-03-23 20:28:22 Slow planning time for custom function