From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Peter <pmc@citylink.dinoex.sub.org>
To: pgsql-performance@lists.postgresql.org
Subject: Re: functions: VOLATILE performs better than STABLE
Date: Sun, 25 Mar 2018 07:00:43 +0200
Message-ID: <1521954043.2350.15.camel@cybertec.at> (raw)
In-Reply-To: <20180324012746.GA14631@gate.oper.dinoex.org>
References: <20180324012746.GA14631@gate.oper.dinoex.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, pmc@citylink.dinoex.sub.org, pgsql-performance@lists.postgresql.org
Subject: Re: functions: VOLATILE performs better than STABLE
In-Reply-To: <1521954043.2350.15.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox