overhead of plpgsql functions over simple select

Just to seek some tips on how to efficiently debug PL/SQL.

One thing that bugs me in particular is the inability to trace a SQL line
number in an error message to the line in my PL/PGSQL code.

Thanks,
CYW

From: cyw@dls.net <cyw@dls.net>
Subject: [GENERAL] Tips on how to efficiently debugging PL/PGSQL
To: pgsql-general@postgresql.org
Date: Thursday, 23 October, 2008, 6:19 PM
Just to seek some tips on how to efficiently debug PL/SQL.

One thing that bugs me in particular is the inability to
trace a SQL line
number in an error message to the line in my PL/PGSQL code.

edb have a debugger that intigrates with pgadmin

http://pgfoundry.org/projects/edb-debugger/

Glyn Astill wrote:

From: cyw@dls.net <cyw@dls.net>
Just to seek some tips on how to efficiently debug PL/SQL.

edb have a debugger that intigrates with pgadmin

http://pgfoundry.org/projects/edb-debugger/

This debugger is integrated with pgAdminIII that is shipped with PostgreSQL
8.3.

Just right click the desired function and chose an action from the "Debugging"
context menu.

Rainer

Hello

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
postgres=#

postgres=# select sum(simplefce(i,1)) from generate_series(1,100000) g(i);
sum
------------
5000150000
(1 row)

Time: 255,997 ms
postgres=# select sum(simplefce1(i,1)) from generate_series(1,100000) g(i);
sum
------------
5000150000
(1 row)

Time: 646,791 ms

Regards
Pavel Stehule

2008/10/23 Ivan Sergio Borgonovo <mail@webthatworks.it>:

"Pavel Stehule" <pavel.stehule@gmail.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.

regards, tom lane

2008/10/24 Tom Lane <tgl@sss.pgh.pa.us>:

"Pavel Stehule" <pavel.stehule@gmail.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.

regards
Pavel Stehule

On Fri, 24 Oct 2008 07:03:35 +0200
"Pavel Stehule" <pavel.stehule@gmail.com> wrote:

2008/10/24 Tom Lane <tgl@sss.pgh.pa.us>:

"Pavel Stehule" <pavel.stehule@gmail.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