Re: overhead of plpgsql functions over simple select

On Fri, 24 Oct 2008 07:03:35 +0200
"Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> wrote:

> 2008/10/24 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> > "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:
> >> postgres=# create function simplefce(a int, b int) returns int
> >> as $$select $1 + $2$$ language sql immutable strict;
> >> CREATE FUNCTION
> >> postgres=# create function simplefce1(a int, b int) returns int
> >> as $$begin return a+b; end;$$ language plpgsql immutable strict;
> >> CREATE FUNCTION
> >
> > That's a pretty unfair comparison, because that SQL function is
> > simple enough to be inlined. The place to use plpgsql is when
> > you need some procedural logic; at which point a SQL function
> > simply fails to provide the required functionality.
> >
>
> Yes, this test is maximal unfair to plpgsql - it's too simply
> function. But it was original question. What is overhead plpgsql
> call on simple functions? On every little bit complicated functions
> overhead should be less. And this sample shows sense of using SQL
> functions.

It's just one case. Furthermore I was interested in plain select
statement vs. plsql encapsulating a simple select statement. But
since we are at it, it would be nice to have a larger picture.

I just avoided a test because I didn't know what
to test.

eg. If I'm using a stable function that return records plpgsql
functions are more complicated just to interpret, they are simply
longer, then as I'm learning now they can't be embedded while sql
functions can (am I right?).

To make a meaningful test I should know what are the potential
factors that make the difference between the 2 (3 actually, simple
sql statement, sql functions and plpgsql functions).

I can't even understand if all immutable sql functions can be
embedded.
The more field are returned (unless I've a custom type or a matching
table) the longer will be the plpgsql function etc...

I couldn't think anything other than cost of interpretation (or
does postgresql has a sort of JIT) and cost of call that can impact
the difference.

I can't still understand when and if it is going to make a
difference.
Yeah I understood that at least in immutable functions sql is faster.
I did some simple tests and it looks as being roughly 3 time faster.
With higher numbers the difference seems to get smaller, maybe
because of the higher cost of allocating memory caused by
generate_series(?).
So I know that immutable simple(?) functions are much faster in
sql... anything else to avoid? What are the factors that play a role
in execution times?

--
Ivan Sergio Borgonovo
http://www.webthatworks.it