pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedfunctions: VOLATILE performs better than STABLE
4+ messages / 4 participants
[nested] [flat]
* functions: VOLATILE performs better than STABLE
@ 2018-03-24 01:27 Peter <pmc@citylink.dinoex.sub.org>
0 siblings, 1 reply; 4+ messages in thread
From: Peter @ 2018-03-24 01:27 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: functions: VOLATILE performs better than STABLE
@ 2018-03-25 05:00 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Peter <pmc@citylink.dinoex.sub.org>
0 siblings, 2 replies; 4+ messages in thread
From: Laurenz Albe @ 2018-03-25 05:00 UTC (permalink / raw)
To: Peter <pmc@citylink.dinoex.sub.org>; pgsql-performance@lists.postgresql.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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: functions: VOLATILE performs better than STABLE
@ 2018-03-25 12:24 David Rowley <david.rowley@2ndquadrant.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 4+ messages in thread
From: David Rowley @ 2018-03-25 12:24 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Peter <pmc@citylink.dinoex.sub.org>; pgsql-performance@lists.postgresql.org
On 25 March 2018 at 18:00, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> Peter wrote:
>> 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.
Although, it would be nice.
TPC-H Q1 does appear to be crafted to allow database with smarter
expression evaluation to get a better score.
It would probably require some sort of recursive expression evaluation
where at each level we check if that expression has already been seen,
if it has, then replace it with some sort of placeholder, then
evaluate each placeholder in the required order.
Probably the first part could be done during planning. It would mean
targetlists would need to carry a bit more weight.
It would be an interesting project to work on, but not planning to personally.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: functions: VOLATILE performs better than STABLE
@ 2018-04-05 14:18 Merlin Moncure <mmoncure@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 4+ messages in thread
From: Merlin Moncure @ 2018-04-05 14:18 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Peter <pmc@citylink.dinoex.sub.org>; pgsql-performance@lists.postgresql.org
On Sun, Mar 25, 2018 at 12:00 AM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> 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.
I think you have it backwards. The STABLE query is flattened into
something like:
select fn('const'), v fn('const') v;
The VOLATILE version can't be flattened that way since it's forced to
execute as the user sees it (one for the inner query).
You can probably get the fast plan via:
select v.v,v.v from (select fn('const') as v offset 0) as v;
The contents of the function fn() also matter very much here as we
would want to know if the function is a candidate for inlining.
merlin
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2018-04-05 14:18 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-03-24 01:27 functions: VOLATILE performs better than STABLE Peter <pmc@citylink.dinoex.sub.org>
2018-03-25 05:00 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-03-25 12:24 ` David Rowley <david.rowley@2ndquadrant.com>
2018-04-05 14:18 ` Merlin Moncure <mmoncure@gmail.com>
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