| 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: | Whole Thread | Raw Message | 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.
| 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 |