Performance of pl/pgsql functions?
Do these tend to perform well? I have some simple formulas in functions
like so:
CREATE OR REPLACE FUNCTION public.stat_foo(a integer, b integer, c integer)
RETURNS numeric AS
$BODY$
declare ret numeric;
begin
select (a+b) / c::numeric into ret;
return round(ret, 3);
end
$BODY$
LANGUAGE plpgsql IMMUTABLE COST 100;
The reason I'm doing this is because i repeat this formula in a bunch of
views and queries, and it's easier to have one function. Would this somehow
be slower than reproducing the formula in every view its used? I'm hoping
not...
--
Wells Oliver
wellsoliver@gmail.com
2012/9/14 Wells Oliver <wellsoliver@gmail.com>:
Do these tend to perform well? I have some simple formulas in functions like
so:CREATE OR REPLACE FUNCTION public.stat_foo(a integer, b integer, c integer)
RETURNS numeric AS
$BODY$declare ret numeric;
begin
select (a+b) / c::numeric into ret;
return round(ret, 3);
end$BODY$
LANGUAGE plpgsql IMMUTABLE COST 100;
it is not good
CREATE OR REPLACE FUNCTION public.stat_foo(a integer, b integer, c integer)
RETURNS numeric AS $$
BEGIN
RETURN round((a + b) / c::numeric), 3)::numeric;
END
$$ LANGUAGE plpgsql IMMUTABLE;
will be significantly faster
probably SQL function will be fastest
CREATE OR REPLACE FUNCTION public.stat_foo(a integer, b integer, c integer)
RETURNS numeric AS $$
SELECT round(($1 + $2) / $3::numeric), 3)::numeric;
$$ LANGUAGE sql;
Regards
Pavel Stehule
Show quoted text
The reason I'm doing this is because i repeat this formula in a bunch of
views and queries, and it's easier to have one function. Would this somehow
be slower than reproducing the formula in every view its used? I'm hoping
not...--
Wells Oliver
wellsoliver@gmail.com
On 09/13/12 10:17 PM, Wells Oliver wrote:
Do these tend to perform well? I have some simple formulas in
functions like so:
if you code your function in SQL instead of plpgsql, and mark it
immutable, it can be inlined by the planner.
--
john r pierce N 37, W 122
santa cruz ca mid-left coast
2012/9/14 John R Pierce <pierce@hogranch.com>:
On 09/13/12 10:17 PM, Wells Oliver wrote:
Do these tend to perform well? I have some simple formulas in functions
like so:if you code your function in SQL instead of plpgsql, and mark it immutable,
it can be inlined by the planner.
you don't need to mark SQL functions - it is not black box for
optimizer and usually better is don't mark SQL functions.
Regards
Pavel
Show quoted text
--
john r pierce N 37, W 122
santa cruz ca mid-left coast--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Fri, Sep 14, 2012 at 1:47 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
2012/9/14 John R Pierce <pierce@hogranch.com>:
On 09/13/12 10:17 PM, Wells Oliver wrote:
Do these tend to perform well? I have some simple formulas in functions
like so:if you code your function in SQL instead of plpgsql, and mark it immutable,
it can be inlined by the planner.you don't need to mark SQL functions - it is not black box for
optimizer and usually better is don't mark SQL functions.
on my workstation:
OP's plpgsql: 63us / call
1-line plpgsql: 43us / call
1-line sql: 38us / call (marking didn't appear to matter in this case)
manually inlined sql: 38us/call
query was tested via:
explain analyze select stat_foo(v,v,v) from generate_series(1,100000) v;
merlin