Thread: Performance of pl/pgsql functions?
Do these tend to perform well? I have some simple formulas in functions like so:
--
Wells Oliver
wellsoliver@gmail.com
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 > > 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 > > > > -- > 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