From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Peter <pmc(at)citylink(dot)dinoex(dot)sub(dot)org>, pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: functions: VOLATILE performs better than STABLE |
Date: | 2018-03-25 05:00:43 |
Message-ID: | 1521954043.2350.15.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2018-03-25 05:12:08 | Re: Should from_collapse be switched off? (queries 10 times faster) |
Previous Message | David Rowley | 2018-03-24 01:52:29 | Re: Slow planning time for custom function |