pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
From: Peter <pmc@citylink.dinoex.sub.org>
To: pgsql-performance@lists.postgresql.org
Subject: functions: VOLATILE performs better than STABLE
Date: Sat, 24 Mar 2018 02:27:47 +0100
Message-ID: <20180324012746.GA14631@gate.oper.dinoex.org> (raw)
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.
view thread (4+ messages) latest in thread
Message-ID: <20180324012746.GA14631@gate.oper.dinoex.org> Permalink: ../20180324012746.GA14631@gate.oper.dinoex.org/ Also on: postgresql.org/message-id/20180324012746.GA14631@gate.oper.dinoex.org ·
reply
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: pmc@citylink.dinoex.sub.org, pgsql-performance@lists.postgresql.org Subject: Re: functions: VOLATILE performs better than STABLE In-Reply-To: <20180324012746.GA14631@gate.oper.dinoex.org> * 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