Обсуждение: Performance of pl/pgsql functions?

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